com.app.common.db.DBUtils Maven / Gradle / Ivy
The newest version!
package com.app.common.db;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import javax.annotation.PreDestroy;
import com.alibaba.druid.pool.DruidDataSource;
import com.app.common.encrypt.AES;
import com.app.common.utils.Consts;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
@Configuration
@Component
public class DBUtils {
@Value("${dbpool.cfg}")
private String configPath;
public String getConfigPath() {
return configPath;
}
public void setConfigPath(String configPath) {
this.configPath = configPath;
}
public String getDbSourceName() {
return dbSourceName;
}
public void setDbSourceName(String dbSourceName) {
this.dbSourceName = dbSourceName;
}
@Value("${dbpool.default}")
private String dbSourceName;
@Bean(initMethod = "initDBClient")
public void initDBClient() throws Exception {
init(dbSourceName,configPath);
}
@PreDestroy
public void closeAll() {
if(dbpool!=null) {
dbpool.closeAll();
}
}
public static IDBPool dbpool;
public static void init(String dbSourceName,String configPath) throws Exception {
dbpool=DBPool.instance(dbSourceName, configPath);
}
private static Logger logger = LoggerFactory.getLogger(DBUtils.class);
private static Hashtable, Integer> htDataType = null;
public static int Max_Batch_Count = 400;
static {
htDataType = new Hashtable, Integer>();
htDataType.put(int.class, Types.INTEGER);
htDataType.put(long.class, Types.INTEGER);
htDataType.put(BigDecimal.class, Types.NUMERIC);
htDataType.put(String.class, Types.VARCHAR);
htDataType.put(char.class, Types.CHAR);
htDataType.put(RowId.class, Types.ROWID);
}
private static IDatabaseConnection databaseConnection=new IDatabaseConnection() {
@Override
public Connection getConnection() {
try {
return dbpool.getConnection();
}
catch(Exception e) {
return null;
}
}
@Override
public void freeConnection(Connection connection) {
try {
dbpool.freeConnection(connection);
}
catch(Exception e) {
}
}
};
public static IDatabaseConnection getDatabaseConnection() {
return databaseConnection;
}
public static void setDatabaseConnection(IDatabaseConnection databaseConnection) {
DBUtils.databaseConnection = databaseConnection;
}
public static DruidDataSource getDruidDataSource(String sourceName) {
return dbpool.getDruidDataSource(sourceName);
}
public static Object querySimpleObject(String sql, Object[] params, Class clazz, Connection... connections)
throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Object result = null;
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
int type = htDataType.get(clazz);
switch (type) {
case Types.INTEGER:
result = resultSet.getInt(1);
break;
case Types.VARCHAR:
case Types.CHAR:
result = resultSet.getString(1);
break;
case Types.NUMERIC:
result = resultSet.getBigDecimal(1);
break;
default:
result = resultSet.getObject(1);
break;
}
}
} catch (Exception e) {
// logger.error("queryCount: {}", sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, resultSet);
}
return result;
}
public static List querySimpleList(String sql, Object[] params, Class clazz, Connection... connections)
throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List list = new ArrayList();
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object result = null;
int type = htDataType.get(clazz);
switch (type) {
case Types.INTEGER:
result = resultSet.getInt(1);
break;
case Types.VARCHAR:
case Types.CHAR:
result = resultSet.getString(1);
break;
case Types.NUMERIC:
result = resultSet.getBigDecimal(1);
break;
default:
result = resultSet.getObject(1);
break;
}
if (result != null) {
list.add((T) result);
}
}
} catch (Exception e) {
// logger.error("queryCount: {}", sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, resultSet);
}
return list;
}
public static List queryList(String sql, Object[] params, Class type, Connection... connections) {
try {
return queryListThrowsException(sql, params, type, connections);
} catch (Exception e) {
logger.error("queryList: {}, type={}", sql, type);
logger.error("", e);
}
return new ArrayList();
}
public static List queryListThrowsException(String sql, Object[] params, Class type,
Connection... connections) throws Exception {
return queryListThrowsException(sql, params, type, 100, connections);
}
public static List queryListThrowsException(String sql, Object[] params, Class type, int fecthSize,
Connection... connections) throws Exception {
List list = new ArrayList();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
resultSet.setFetchSize(fecthSize);
Field[] fields = type.getFields();
// int count = 0;
while (resultSet.next()) {
T obj = type.newInstance();
for (Field field : fields) {
try {
Object val = null;
Integer jdbcType = htDataType.get(field.getType());
if (jdbcType == null) {
val = resultSet.getObject(field.getName());
} else {
switch (jdbcType) {
case Types.INTEGER:
val = resultSet.getInt(field.getName());
break;
default:
val = resultSet.getObject(field.getName());
break;
}
}
field.set(obj, val);
} catch (Exception e) {
}
}
list.add(obj);
// count++;
// if (count >= 10000) {
// break;
// }
}
} catch (Exception e) {
// logger.error("queryList: {}, type={}", sql, type);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, resultSet);
}
return list;
}
public static List> queryListThrowsException(String sql, Object[] params,
Connection... connections) throws Exception {
return queryListThrowsException(sql, params, 100, connections);
}
public static List> queryListThrowsException(String sql, Object[] params,int fecthSize,
Connection... connections) throws Exception {
List> list = new ArrayList>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
preparedStatement.setObject(i + 1, params[i]);
}
}
resultSet = preparedStatement.executeQuery();
resultSet.setFetchSize(fecthSize);
// int count = 0;
while (resultSet.next()) {
Map obj = mapRow(resultSet);
list.add(obj);
// count++;
// if (count >= 10000) {
// break;
// }
}
} catch (Exception e) {
// logger.error("queryList: {}, type={}", sql, type);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, resultSet);
}
return list;
}
public static Map mapRow(ResultSet rs) throws SQLException {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Map mapOfColValues = new HashMap(columnCount);
for(int i = 1; i <= columnCount; ++i) {
String key =lookupColumnName(rsmd, i);
Object obj = getResultSetValue(rs, i);
mapOfColValues.put(key, obj);
}
return mapOfColValues;
}
public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
String name = resultSetMetaData.getColumnLabel(columnIndex);
if (name == null || name.length() < 1) {
name = resultSetMetaData.getColumnName(columnIndex);
}
return name;
}
public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
Object obj = rs.getObject(index);
String className = null;
if (obj != null) {
className = obj.getClass().getName();
}
if (obj instanceof Blob) {
Blob blob = (Blob)obj;
obj = blob.getBytes(1L, (int)blob.length());
} else if (obj instanceof Clob) {
Clob clob = (Clob)obj;
obj = clob.getSubString(1L, (int)clob.length());
} else if (!"oracle.sql.TIMESTAMP".equals(className) && !"oracle.sql.TIMESTAMPTZ".equals(className)) {
if (className != null && className.startsWith("oracle.sql.DATE")) {
String metaDataClassName = rs.getMetaData().getColumnClassName(index);
if (!"java.sql.Timestamp".equals(metaDataClassName) && !"oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
obj = rs.getDate(index);
} else {
obj = rs.getTimestamp(index);
}
} else if (obj != null && obj instanceof Date && "java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
obj = rs.getTimestamp(index);
}
} else {
obj = rs.getTimestamp(index);
}
return obj;
}
public static int updateObject(T obj, String table, String[] keys, Connection... connections) throws Exception {
return updateObject(obj, obj.getClass(), table, keys);
}
public static int updateObject(T obj, Class class1, String table, String[] keys, Connection... connections)
throws Exception {
DynamicSql dynamicSql = DynamicSql.generateUpdate(class1, table, keys);
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(dynamicSql.sql);
setValues(obj, preparedStatement, dynamicSql.fields, dynamicSql.keys);
return preparedStatement.executeUpdate();
} catch (Exception e) {
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return -1;
}
public static int[] updateObjectList(List list, String table, String[] keys, Connection... connections)
throws Exception {
if (list.size() <= 0) {
return null;
}
T obj = list.get(0);
return updateObjectList(list, obj.getClass(), table, keys, connections);
}
public static int[] updateObjectList(List list, Class class1, String table, String[] keys,
Connection... connections) throws Exception {
// if (list.size() <= 0) {
// return null;
// }
// T obj = list.get(0);
DynamicSql dynamicSql = DynamicSql.generateUpdate(class1, table, keys);
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
UpdateListResult listResult = new UpdateListResult(list.size());
// connection = databaseConnection.getConnection();
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(dynamicSql.sql);
int count = 0;
for (T item : list) {
setValues(item, preparedStatement, dynamicSql.fields, dynamicSql.keys);
preparedStatement.addBatch();
count++;
if (count % Max_Batch_Count == 0) {
listResult.add(preparedStatement.executeBatch());
}
}
listResult.add(preparedStatement.executeBatch());
return listResult.getResults();
} catch (Exception e) {
// logger.error("updateObjectList: table={}, object={}, sql={}",
// table, obj, dynamicSql.sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return null;
}
/**
*
* @param sql
* @param args
* ���������null
* @param types
* ������Ӧ������
* @return
* @throws Exception
*/
public static int updateNullable(String sql, Object[] args, Class[] types) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = databaseConnection.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
if (args[i] != null) {
preparedStatement.setObject(i + 1, args[i]);
} else {
preparedStatement.setNull(i + 1, htDataType.get(types[i]));
}
}
return preparedStatement.executeUpdate();
} catch (Exception e) {
// logger.error("updateNullable: sql={}", sql);
// logger.error("", e);
throw e;
} finally {
close(connection, preparedStatement, null);
}
// return -1;
}
/**
*
* @param sql
* @param args
* ���������null
* @param types
* ������Ӧ������
* @return
* @throws Exception
*/
public static int[] updateNullableList(String sql, List args, Class[] types, Connection... connections)
throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
UpdateListResult listResult = new UpdateListResult(args.size());
// connection = databaseConnection.getConnection();
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
int count = 0;
for (int i = 0; i < args.size(); i++) {
Object[] objs = args.get(i);
for (int j = 0; j < objs.length; j++) {
if (objs[j] != null) {
preparedStatement.setObject(j + 1, objs[j]);
} else {
preparedStatement.setNull(j + 1, htDataType.get(types[j]));
}
}
preparedStatement.addBatch();
count++;
if (count % Max_Batch_Count == 0) {
listResult.add(preparedStatement.executeBatch());
// logger.info("executeBatch upto Max_Batch_Count: {}",
// count);
}
}
listResult.add(preparedStatement.executeBatch());
return listResult.getResults();
} catch (Exception e) {
// logger.error("updateNullableList: sql={}", sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return null;
}
/**
* update sql
*
* @param sql
* @param args
* ������null
* @return
* @throws Exception
*/
public static int update(String sql, Object[] args, Connection... connections) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
return preparedStatement.executeUpdate();
} catch (Exception e) {
// logger.error("update: sql={}", sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return -1;
}
/**
*
* @param sql
* @param args
* ����ֵ������null
* @return
* @throws Exception
*/
public static int[] updateList(String sql, List args, Connection... connections) throws Exception {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
UpdateListResult listResult = new UpdateListResult(args.size());
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(sql);
int count = 0;
for (int i = 0; i < args.size(); i++) {
Object[] objs = args.get(i);
for (int j = 0; j < objs.length; j++) {
preparedStatement.setObject(j + 1, objs[j]);
}
preparedStatement.addBatch();
count++;
if (count % Max_Batch_Count == 0) {
listResult.add(preparedStatement.executeBatch());
// logger.info("executeBatch upto Max_Batch_Count: {}",
// count);
}
}
listResult.add(preparedStatement.executeBatch());
return listResult.getResults();
} catch (Exception e) {
// logger.error("updateList: sql={}", sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return null;
}
public static int insert(T obj, String table, Connection... connections) throws Exception {
return insert(obj, obj.getClass(), table, connections);
}
public static int insert(T obj, Class class1, String table, Connection... connections) throws Exception {
DynamicSql dynamicSql = DynamicSql.generateInsert(class1, table);
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
// connection = databaseConnection.getConnection();
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(dynamicSql.sql);
Field[] fields = dynamicSql.fields;
setValues(obj, preparedStatement, fields);
return preparedStatement.executeUpdate();
} catch (Exception e) {
// logger.error("insert: table={}, object={}, sql={}", table, obj,
// dynamicSql.sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return -1;
}
public static int[] insertList(List list, String table, Connection... connections) throws Exception {
if (list.size() <= 0) {
return null;
}
T obj = list.get(0);
DynamicSql dynamicSql = DynamicSql.generateInsert(obj, table);
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
UpdateListResult listResult = new UpdateListResult(list.size());
connection = getOrOpenConnection(connections);
preparedStatement = connection.prepareStatement(dynamicSql.sql);
Field[] fields = dynamicSql.fields;
int count = 0;
for (T item : list) {
setValues(item, preparedStatement, fields);
preparedStatement.addBatch();
count++;
if (count % Max_Batch_Count == 0) {
listResult.add(preparedStatement.executeBatch());
}
}
int[] result = preparedStatement.executeBatch();
listResult.add(result);
return listResult.getResults();
} catch (Exception e) {
// logger.error("insertList: table={}, object={}, sql={}", table,
// obj, dynamicSql.sql);
// logger.error("", e);
throw e;
} finally {
close(getToCloseConnection(connection, connections), preparedStatement, null);
}
// return null;
}
public static void setValues(T obj, PreparedStatement preparedStatement, Field[] fields) throws Exception {
int startIndex = 1;
setValues(obj, preparedStatement, fields, startIndex);
}
public static void setValues(T obj, PreparedStatement preparedStatement, Field[] fields, Field[] keys)
throws Exception {
int startIndex = 1;
setValues(obj, preparedStatement, fields, startIndex);
if (keys != null) {
startIndex += fields.length;
setValues(obj, preparedStatement, keys, startIndex);
}
}
public static void setValues(T obj, PreparedStatement preparedStatement, Field[] fields, int startIndex)
throws Exception {
for (int i = 0; i < fields.length; i++) {
try {
Field field = fields[i];
Object val = fields[i].get(obj);
if (val != null) {
preparedStatement.setObject(i + startIndex, val);
} else {
preparedStatement.setNull(i + startIndex, htDataType.get(field.getType()));
}
} catch (Exception e) {
logger.info("setValues: {},{}", obj, fields[i].getName());
throw e;
}
}
}
public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
}
}
if (connection != null) {
databaseConnection.freeConnection(connection);
}
}
private static Connection getOrOpenConnection(Connection... connections) {
if (connections != null && connections.length >= 1) {
return connections[0];
} else {
return databaseConnection.getConnection();
}
}
private static Connection getToCloseConnection(Connection connection, Connection... connections) {
if (connections != null && connections.length >= 1) {
return null;
} else {
return connection;
}
}
private static final String dbKey="DBPOOL.";
public static List readConfig(String path) throws Exception
{
List temp = new ArrayList();
Properties properties = new Properties();
properties.load(new FileInputStream(path));
int dBCount = 0;
String dBCountStr = properties.getProperty(dbKey+DBPoolParam.KEY.DBCount.toString());
try
{
dBCount = Integer.valueOf(dBCountStr).intValue();
if (dBCount <= 0)
{
throw new Exception("dBCount <= 0, dBCount=" + dBCount);
}
}
catch (Exception e)
{
e.printStackTrace();
throw new Exception(String.format("DBCount is Error, DBCount=%s, Exception=%s", new Object[] { dBCountStr, e }));
}
for (int i = 0; i < dBCount; i++)
{
try
{
DBPoolParam dbParams = new DBPoolParam();
dbParams.SourceName = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBSourceName.toString()+"_"+i) );
dbParams.Driver = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBDriver.toString()+"_"+i));
dbParams.Url = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBUrl.toString()+"_"+i));
dbParams.Username = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBUsername.toString()+"_"+i));
dbParams.Passwd = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBPasswd.toString()+"_"+i));
dbParams.MaxCount = Integer.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBMaxCount.toString()+"_"+i)));
dbParams.MinCount = Integer.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBMinCount.toString()+"_"+i)));
dbParams.CheckSql = properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBCheckSql.toString()+"_"+i));
dbParams.IsEncrypt = Boolean.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBIsEncrypt.toString()+"_"+i))).booleanValue();
if(dbParams.IsEncrypt) {
dbParams.Passwd =AES.decryptFromBase64(dbParams.Passwd, Consts.AESKey);
}
dbParams.DBConnOutTime = Integer.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBConnOutTime.toString()+"_"+i))).intValue();
dbParams.DBConnCheckNumber = Integer.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBConnCheckNumber.toString()+"_"+i))).intValue();
dbParams.DBStrategy = Boolean.valueOf(properties.getProperty(dbKey+String.format(DBPoolParam.KEY.DBStrategy.toString()+"_"+i))).booleanValue();
temp.add(dbParams);
}
catch (Exception e)
{
e.printStackTrace();
}
}
return temp;
}
}