cn.opencodes.db.JdbcHelper Maven / Gradle / Ivy
The newest version!
package cn.opencodes.db;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 数据库访问帮助类
*
* @author HJ
*
*/
public class JdbcHelper {
private static ThreadLocal db = new ThreadLocal<>();
/**
* 设置 数据库配置
* @param dbCfg
*/
public static void setDbConfig(DbConfig dbCfg){
db.set(dbCfg);
}
/**
* 用于查询，返回结果集
*
* @param sql
* sql语句
* @return 结果集
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List query(String sql) throws SQLException {
ResultSet rs = null;
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(rs);
}
}
/**
* 用于带参数的查询，返回结果集
*
* @param sql
* sql语句
* @param paramters
* 参数集合
* @return 结果集
* @throws SQLException
*/
public static List> query(String sql, Object... paramters)throws SQLException {
ResultSet rs = null;
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(rs);
}
}
/**
* 返回单个结果的值，如count\min\max等等
*
* @param sql
* sql语句
* @return 结果集
* @throws SQLException
*/
public static Object getSingle(String sql) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(rs);
}
}
/**
* 返回单个结果值，如count\min\max等
*
* @param sql
* sql语句
* @param paramters
* 参数列表
* @return 结果
* @throws SQLException
*/
public static Object getSingle(String sql, Object... paramters)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
rs = preparedStatement.executeQuery();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(rs);
}
}
/**
* 用于增删改
*
* @param sql
* sql语句
* @return 影响行数
* @throws SQLException
*/
public static int update(String sql) throws SQLException {
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(null);
}
}
/**
* 用于增删改（带参数）
*
* @param sql
* sql语句
* @param paramters
* sql语句
* @return 影响行数
* @throws SQLException
*/
public static int update(String sql, Object... paramters)
throws SQLException {
try {
PreparedStatement preparedStatement = db.get().getPreparedStatement(sql);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
return preparedStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freePrep(null);
}
}
/**
* 插入值后返回主键值
*
* @param sql
* 插入sql语句
* @return 返回结果
* @throws Exception
*/
public static Object insertWithReturnPrimeKey(String sql)
throws SQLException {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Object result = null;
try {
conn = db.get().getConnection();
preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}finally {
db.get().free(conn, preparedStatement, rs);
}
}
/**
* 插入值后返回主键值
*
* @param sql
* 插入sql语句
* @param paramters
* 参数列表
* @return 返回结果
* @throws SQLException
*/
public static Object insertWithReturnPrimeKey(String sql, Object... paramters) throws SQLException {
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet rs = null;
Object result = null;
try {
conn = db.get().getConnection();
preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
for (int i = 0; i < paramters.length; i++) {
preparedStatement.setObject(i + 1, paramters[i]);
}
preparedStatement.execute();
rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
}finally {
db.get().free(conn, preparedStatement, rs);
}
}
/**
* 调用存储过程执行查询
*
* @param procedureSql
* 存储过程
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List callableQuery(String procedureSql) throws SQLException {
ResultSet rs = null;
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(rs);
}
}
/**
* 调用存储过程（带参数）,执行查询
*
* @param procedureSql
* 存储过程
* @param paramters
* 参数表
* @return
* @throws SQLException
*/
@SuppressWarnings("rawtypes")
public static List callableQuery(String procedureSql, Object... paramters)
throws SQLException {
ResultSet rs = null;
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
return ResultToListMap(rs);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(rs);
}
}
/**
* 调用存储过程，查询单个值
*
* @param procedureSql
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql)
throws SQLException {
Object result = null;
ResultSet rs = null;
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(rs);
}
}
/**
* 调用存储过程(带参数)，查询单个值
*
* @param procedureSql
* @param parameters
* @return
* @throws SQLException
*/
public static Object callableGetSingle(String procedureSql,
Object... paramters) throws SQLException {
Object result = null;
ResultSet rs = null;
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
for (int i = 0; i < paramters.length; i++) {
callableStatement.setObject(i + 1, paramters[i]);
}
rs = callableStatement.executeQuery();
while (rs.next()) {
result = rs.getObject(1);
}
return result;
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(rs);
}
}
public static Object callableWithParamters(String procedureSql)
throws SQLException {
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
callableStatement.registerOutParameter(0, Types.OTHER);
callableStatement.execute();
return callableStatement.getObject(0);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(null);
}
}
/**
* 调用存储过程，执行增删改
*
* @param procedureSql
* 存储过程
* @return 影响行数
* @throws SQLException
*/
public static int callableUpdate(String procedureSql) throws SQLException {
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(null);
}
}
/**
* 调用存储过程（带参数），执行增删改
*
* @param procedureSql
* 存储过程
* @param parameters
* @return 影响行数
* @throws SQLException
*/
public static int callableUpdate(String procedureSql, Object... parameters)
throws SQLException {
try {
CallableStatement callableStatement = db.get().getCallableStatement(procedureSql);
for (int i = 0; i < parameters.length; i++) {
callableStatement.setObject(i + 1, parameters[i]);
}
return callableStatement.executeUpdate();
} catch (SQLException e) {
throw new SQLException(e);
} finally {
db.get().freepCall(null);
}
}
/**
* 批量更新数据
*
* @param sqlList
* 一组sql
* @return
*/
public static int[] batchUpdate(List sqlList) {
int[] result = new int[] {};
Connection conn = null;
Statement statenent = null;
try {
conn = db.get().getConnection();
conn.setAutoCommit(false);
statenent = conn.createStatement();
for (String sql : sqlList) {
statenent.addBatch(sql);
}
result = statenent.executeBatch();
conn.commit();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
throw new ExceptionInInitializerError(e1);
}
throw new ExceptionInInitializerError(e);
} finally {
db.get().free(conn, statenent, null);
}
return result;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
private static List> ResultToListMap(ResultSet rs) throws SQLException {
List list = new ArrayList();
while (rs.next()) {
ResultSetMetaData md = rs.getMetaData();
Map map = new HashMap();
for (int i = 1; i <= md.getColumnCount(); i++) {
map.put(md.getColumnLabel(i), rs.getObject(i));
}
list.add(map);
}
return list;
}
}