package com.bench.dbapi;
import com.bench.common.utils.PropertiesUtil;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.log4j.Logger;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class MSSQLUtil {
private static final Logger logger = Logger.getLogger(MSSQLUtil.class);
Connection _CONN = null;
//private String dbName;
//private int dbType = 0;
private boolean isPool = false;
private String username;
private String password;
private String dburl;
private String dbname;
HikariDataSource ds = null;
public MSSQLUtil(String dbName){
dburl = PropertiesUtil.getProps().get("config.jdbc.mssql." + dbName + ".url");
dbname = dbName;
username = PropertiesUtil.getProps().get("config.jdbc.mssql." + dbName + ".username");
password = PropertiesUtil.getProps().get("config.jdbc.mssql." + dbName + ".password");
}
public MSSQLUtil(String dbName, int dbType, boolean isPool){
String sDBUrl;
this.isPool = isPool;
if(dbType == 0)
sDBUrl = PropertiesUtil.getProps().get("config.jdbc.mssql1.url");
else
sDBUrl = PropertiesUtil.getProps().get("config.jdbc.mssql2.url");
String username,password;
if(dbType == 0) {
username = PropertiesUtil.getProps().get("config.jdbc.mssql1.username");
password = PropertiesUtil.getProps().get("config.jdbc.mssql1.password");
}
else{
username = PropertiesUtil.getProps().get("config.jdbc.mssql2.username");
password = PropertiesUtil.getProps().get("config.jdbc.mssql2.password");
}
initPool(5,20,sDBUrl,username,password);
}
//private HikariDataSource ds;
/**
* 初始化连接池
* @param minimum
* @param Maximum
*/
public void initPool(int minimum,int Maximum,String url,String username,String password){
//连接池配置
String sDBUrl = PropertiesUtil.getProps().get("config.jdbc.mssql.url");
HikariConfig config = new HikariConfig();
config.setDriverClassName("com.mysql.jdbc.Driver");
config.setJdbcUrl(url);
config.setUsername(username);
config.setPassword(password);
config.addDataSourceProperty("cachePrepStmts", true);
config.addDataSourceProperty("prepStmtCacheSize", 500);
config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);
// config.setConnectionTestQuery("SELECT 1");
config.setAutoCommit(true);
//池中最小空闲链接数量
config.setMinimumIdle(minimum);
//池中最大链接数量
config.setMaximumPoolSize(Maximum);
// ds = new HikariDataSource(config);
// Connection conn = ds.getConnection();
// _CONN.close();
}
private Connection GetPoolConn() {
Connection connection = null;
try {
connection = ds.getConnection();
return connection;
}
catch (Exception e){
return null;
}
}
public int BatchInsert(List<String> sqls){
GetConn();
try
{
// _CONN.setAutoCommit(false);
Statement stmt = _CONN.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
for(String sql:sqls){
stmt.addBatch(sql);
}
stmt.executeBatch();
stmt.close();
// _CONN.commit();
}
catch(Exception ex)
{
System.out.println(ex.getMessage());
}
finally
{
// try{
// _CONN.setAutoCommit(true);
// }
// catch (Exception e){
// e.printStackTrace();
// }
}
return 0;
}
//取得连接
private boolean GetConn(String sUser, String sPwd) {
if(_CONN!=null)
return true;
try {
String sDriverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
Class.forName(sDriverName);
DriverManager.setLoginTimeout(7);
_CONN = DriverManager.getConnection(dburl + dbname, sUser, sPwd);
} catch (Exception ex) {
ex.printStackTrace();
logger.error("GetConn JDBC ERROR");
return false;
}
return true;
}
private boolean GetConn()
{
return GetConn(username,password);
}
public Connection getSingleConn(){
GetConn();
return _CONN;
}
//关闭连接
public void CloseConn()
{
try {
_CONN.close();
_CONN = null;
} catch (Exception ex) {
System.out.println(ex.getMessage());
_CONN=null;
}
}
//测试连接
public boolean TestConn() {
if (!GetConn())
return false;
CloseConn();
return true;
}
public ResultSet GetResultSet(String sSQL,Object[] objParams)
{
GetConn();
ResultSet rs=null;
try
{
PreparedStatement ps = _CONN.prepareStatement(sSQL);
if(objParams!=null)
{
for(int i=0;i< objParams.length;i++)
{
ps.setObject(i+1, objParams[i]);
}
}
rs=ps.executeQuery();
}
catch(Exception ex)
{
logger.error("GetResultSet ERROR:" + sSQL);
System.out.println("e" + ex.toString());
CloseConn();
}
finally
{
//CloseConn();
}
return rs;
}
public Object GetSingle(String sSQL,Object... objParams)
{
GetConn();
try
{
PreparedStatement ps = _CONN.prepareStatement(sSQL);
if(objParams!=null)
{
for(int i=0;i< objParams.length;i++)
{
ps.setObject(i+1, objParams[i]);
}
}
ResultSet rs=ps.executeQuery();
if(rs != null) {
if (rs.next())
return rs.getString(1);//索引从1开始
}
}catch(Exception ex)
{
System.out.println(ex.getMessage());
}
finally
{
// CloseConn();
}
return null;
}
public int execUpdate(String sSQL,Object... objParams)
{
GetConn();
try
{
// PreparedStatement ps = _CONN.prepareStatement(sSQL);
Statement ps = _CONN.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
return ps.executeUpdate(sSQL);
}catch(Exception ex)
{
System.out.println(ex.getMessage());
}
finally
{
// CloseConn();
}
return -1;
}
public DataTable GetDataTable(String sSQL,Object... objParams)
{
GetConn();
DataTable dt=null;
try
{
PreparedStatement ps = _CONN.prepareStatement(sSQL);
if(objParams!=null)
{
for(int i=0;i< objParams.length;i++)
{
ps.setObject(i+1, objParams[i]);
}
}
ResultSet rs=ps.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
List<DataRow> row=new ArrayList<DataRow>(); //表所有行集合
List<DataColumn> col=null; //行所有列集合
DataRow r=null;// 单独一�
