Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.mario6.common.db.JdbcTemplate Maven / Gradle / Ivy
package com.mario6.common.db;
import com.mario6.common.db.mapper.MapRowMapper;
import com.mario6.common.db.mapper.RowMapper;
import com.mario6.common.db.mapper.SingleColumnRowMapper;
import com.mario6.common.db.util.JdbcUtils;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
/**
* 数据库访问工具类
*
* 线程安全的
*/
public class JdbcTemplate {
// 所使用的数据源
private DataSource dataSource;
/* 标志，是否每次执行访问方法后关闭连接, null或true代表自动关闭 */
private static ThreadLocal AUTO_CLOSE = new ThreadLocal<>();
public JdbcTemplate(DataSource dataSource) {
this.dataSource = dataSource;
}
//---------------------------------------------------------
// SQL执行的基本方法
//---------------------------------------------------------
//----------------------------------------------------------------------------
// 查询，预期结果集有任意行结果
//----------------------------------------------------------------------------
public List query(String sql, RowMapper rowMapper) throws DataAccessException {
return query(sql, null, rowMapper);
}
public List query(String sql, RowMapper rowMapper, Object...params) throws DataAccessException {
List paramsToUse = Arrays.asList(params);
return query(sql, paramsToUse, rowMapper);
}
public List query(String sql, List params, RowMapper rowMapper) throws DataAccessException {
Connection conn = getConnection();
PreparedStatement stat = null;
ResultSet rs = null;
List result = new ArrayList<>();
try {
stat = conn.prepareStatement(sql);
setPreparedStatement(stat, params);
rs = stat.executeQuery();
// 获得表列信息
result = resultToList(rs, rowMapper);
} catch (SQLException e) {
closeConnection();
conn = null;
throw new DataAccessException(e);
} finally {
closeResultSet(rs);
closeStatement(stat);
// 尝试关闭连接
tryToCloseConnection();
}
return result;
}
//------查询结果单个实体是Map
public List> query(String sql, List params) throws DataAccessException {
return query(sql, params, MapRowMapper.newInstance());
}
public List> query(String sql) throws DataAccessException {
return query(sql, new ArrayList(0));
}
public List> query(String sql, Object...params) throws DataAccessException {
List paramsToUse = Arrays.asList(params);
return query(sql, paramsToUse);
}
//----------------------------------------------------------------------------
// 查询，预期结果集只有单行结果
//----------------------------------------------------------------------------
public T queryForObject(String sql, List params, RowMapper rowMapper) throws DataAccessException {
Connection conn = getConnection();
PreparedStatement stat = null;
ResultSet rs = null;
T result = null;
try {
stat = conn.prepareStatement(sql);
setPreparedStatement(stat, params);
rs = stat.executeQuery();
if(rs.next()){
result = rowMapper.rowMap(rs);
}
if(rs.next()) {
throw new DataAccessException("结果集数据不止一行");
}
} catch (SQLException e) {
closeConnection();
conn = null;
throw new DataAccessException(e);
} finally {
closeResultSet(rs);
closeStatement(stat);
// 尝试关闭连接
tryToCloseConnection();
}
return result;
}
public T queryForObject(String sql, RowMapper rowMapper) throws DataAccessException {
return queryForObject(sql, null, rowMapper);
}
public T queryForObject(String sql, RowMapper rowMapper, Object...params) throws DataAccessException {
return queryForObject(sql, Arrays.asList(params), rowMapper);
}
//------查询结果单个实体是Map
public Map queryForObject(String sql) throws DataAccessException {
return queryForObject(sql, new ArrayList(0));
}
public Map queryForObject(String sql, Object...params) throws DataAccessException {
List paramsToUse = Arrays.asList(params);
return queryForObject(sql, paramsToUse);
}
public Map queryForObject(String sql, List params) throws DataAccessException {
return queryForObject(sql, params, MapRowMapper.newInstance());
}
//----------------------------------------------------------------------------
// 查询，预期单列结果
//----------------------------------------------------------------------------
public List queryForList(String sql, Class elementType) throws DataAccessException {
return queryForList(sql, null, elementType);
}
public List queryForList(String sql, Class elementType, Object...params) throws DataAccessException {
return queryForList(sql, Arrays.asList(params), elementType);
}
public List queryForList(String sql, List params, Class elementType) throws DataAccessException {
RowMapper mapper = SingleColumnRowMapper.newInstance(elementType);
return query(sql, params, mapper);
}
//----------------------------------------------------------------------------
// 查询，预期单行&单列结果
//----------------------------------------------------------------------------
public T queryForObject(String sql, Class requireType) throws DataAccessException {
return queryForObject(sql, null, requireType);
}
public T queryForObject(String sql, Class requireType, Object...params) throws DataAccessException {
return queryForObject(sql, Arrays.asList(params), requireType);
};
public T queryForObject(String sql, List params, Class requireType) throws DataAccessException {
RowMapper mapper = SingleColumnRowMapper.newInstance(requireType);
return queryForObject(sql, params, mapper);
};
//----------------------------------------------------------------------------
// 更新相关
//----------------------------------------------------------------------------
public int update(String sql) throws DataAccessException {
return update(sql, new ArrayList(0));
}
public int update(String sql, Object...params) throws DataAccessException {
List paramsToUse = Arrays.asList(params);
return update(sql, paramsToUse);
}
public int update(String sql, List params) throws DataAccessException {
return update(sql, params, null);
}
public int update(String sql, AutoKey key) throws DataAccessException {
return update(sql, new ArrayList(0), key);
}
public int update(String sql, AutoKey key, Object...params) throws DataAccessException {
return update(sql, Arrays.asList(params), key);
}
public int update(String sql, List params, AutoKey key) throws DataAccessException {
Connection conn = getConnection();
PreparedStatement stat = null;
int rows = 0;
try {
if(key != null) {
stat = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
} else {
stat = conn.prepareStatement(sql);
}
setPreparedStatement(stat, params);
rows = stat.executeUpdate();
if(key != null && rows >0) {
ResultSet keys = stat.getGeneratedKeys();
resolveAutoKey(keys, key);
}
} catch (SQLException e) {
closeConnection();
conn = null;
throw new DataAccessException(e);
} finally {
closeStatement(stat);
// 尝试关闭连接
tryToCloseConnection();
}
return rows;
}
//------------------------------------------------------------
// 存储过程相关
//------------------------------------------------------------
public Map call(String call, Object[] inArgs, int[] outArgs) throws DataAccessException {
Connection conn = getConnection();
CallableStatement stat = null;
ResultSet rs = null;
try {
stat = conn.prepareCall(call);
// 设置输入参数
setPreparedStatement(stat, Arrays.asList(inArgs));
final int start = (inArgs==null)?(1):(inArgs.length+1);
if(outArgs != null) {
// 注册输出参数
registerOutParameter(stat, start, outArgs);
}
stat.execute();
Map result = new HashMap<>();
for(int i=0; i结构
private List resultToList(ResultSet rs, RowMapper rowMapper) throws SQLException {
List results = new ArrayList<>();
while(rs.next()){
T one = rowMapper.rowMap(rs);
results.add(one);
}
return results;
}
// 设置PreparedStatement参数
private void setPreparedStatement(PreparedStatement stat, List params) throws SQLException {
if(params == null || params.size() == 0) {
return;
}
for(int i = 0; i < params.size(); i++) {
stat.setObject(i+1, params.get(i));
}
}
private void registerOutParameter(CallableStatement stat, int start, int[] types) throws SQLException {
for(int i = 0; i < types.length; i++) {
stat.registerOutParameter(start+i, types[i]);
}
}
private Connection getConnection() {
try {
return DataSourceManager.getConnection(dataSource);
} catch (SQLException e) {
throw new DataAccessException("获取数据库连接失败", e);
}
}
public void closeConnection() {
Connection conn = DataSourceManager.getCurrentConnection(dataSource);
JdbcUtils.closeConnection(conn);
}
private void closeResultSet(ResultSet rs) {
JdbcUtils.closeResultSet(rs);
}
private void closeStatement(PreparedStatement stat) {
JdbcUtils.closeStatement(stat);
}
private boolean tryToCloseConnection() {
if(!DataSourceManager.isTransactionFlag() && isAutoClose()) {
closeConnection();
return true;
}
return false;
}
}
