package com.table_sql;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.sql.Statement;
|
import java.util.ArrayList;
|
import java.util.List;
|
|
import com.mchange.v2.c3p0.ComboPooledDataSource;
|
|
public class MySqlPool {
|
private ComboPooledDataSource mysql_ds = new ComboPooledDataSource();//Êý¾ÝÔ´±»¹Ø±Õºó²»ÄÜÔÙµ÷ÓÃgetconn£¨£©·½·¨
|
private int mSqlPort = 3360;
|
//private String mSqlIp="118.89.139.230";
|
//private String mSqlIp="127.0.0.1";
|
private String mSqlIp="192.168.10.79";
|
private String user="root";
|
private String upassword="lmx8688139";
|
private int conncount_max=200;
|
private int conncount_min=2;
|
private int MaxStatements=50;
|
private int MaxIdleTime=60;
|
private int InitialPoolSize=2;
|
|
public ComboPooledDataSource getMysql_ds() {
|
return mysql_ds;
|
}
|
public MySqlPool() {
|
//System.out.println("mSqlIp:"+mSqlIp);
|
|
try {
|
//Thread.sleep(1000*60);
|
mysql_ds.setDriverClass("com.mysql.jdbc.Driver");
|
mysql_ds.setJdbcUrl("jdbc:mysql://" + mSqlIp + ":" + mSqlPort);
|
mysql_ds.setUser(user);
|
mysql_ds.setPassword(upassword);
|
mysql_ds.setMaxPoolSize(conncount_max);
|
mysql_ds.setMinPoolSize(conncount_min);
|
mysql_ds.setIdleConnectionTestPeriod(60);
|
// ÉèÖÃÁ¬½Ó³ØÖеÄ×î´óStatementsÊýÁ¿£¡
|
mysql_ds.setMaxStatements(MaxStatements);
|
// ÉèÖÃÁ¬½Ó³ØµÄ×î´ó¿ÕÏÐʱ¼ä£¡
|
mysql_ds.setMaxIdleTime(MaxIdleTime);
|
// ÉèÖóõʼÁ¬½Ó³ØµÄ´óС£¡
|
mysql_ds.setInitialPoolSize(InitialPoolSize);
|
//System.out.println("11111111111111111111111111");
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
public Connection getConn(){
|
Connection con = null;
|
try {
|
con=mysql_ds.getConnection();
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
return con;
|
}
|
|
public static boolean makeManualCommit(Connection mysql_con,ArrayList<String> al_sql_strs)
|
{
|
boolean exe_res = true;
|
try {
|
mysql_con.setAutoCommit(false);
|
|
for(int n=0; n<al_sql_strs.size(); n++) {
|
if(true == exe_res) {
|
exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
|
} else {
|
break;
|
}
|
}
|
|
if(true == exe_res) {
|
mysql_con.commit();
|
}
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
exe_res = false;
|
} finally {
|
try {
|
if(false == exe_res) {
|
mysql_con.rollback();
|
}
|
mysql_con.setAutoCommit(true);
|
} catch (SQLException e1) {
|
// TODO Auto-generated catch block
|
e1.printStackTrace();
|
}finally {
|
if(mysql_con!=null)
|
try {
|
mysql_con.close();
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
}
|
|
return exe_res;
|
}
|
//Ö´ÐÐsqlÓï¾äwith no colse
|
public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
|
{
|
PreparedStatement ps=null;
|
|
boolean rest = true;
|
try
|
{
|
ps = mysql_con.prepareStatement(sql_str);
|
ps.setQueryTimeout(600);
|
ps.execute();
|
}
|
catch(SQLException ex)
|
{
|
ex.printStackTrace();
|
rest = false;
|
|
}finally {
|
try {
|
ps.close();
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
return rest;
|
}
|
|
//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql²éѯÓï¾ä
|
public ResultSet sqlMysqlQuery(Connection mysql_con,String sql_str)
|
{
|
ResultSet res = null;
|
try
|
{
|
Statement sql = mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
res = sql.executeQuery(query);
|
}
|
catch(SQLException ex)
|
{
|
System.err.println("SQLException:" + ex.getMessage());
|
}
|
|
return res;
|
}
|
|
//¸ù¾ÝsqlÓï¾äÖ´ÐÐsql¸üÐÂÓï¾ä
|
public boolean sqlMysqlUpdate(Connection mysql_con,String sql_str)
|
{ boolean bl=true;
|
Statement sql=null;
|
try
|
{
|
sql= mysql_con.createStatement();
|
sql.setQueryTimeout(30);
|
String query = sql_str;
|
bl=sql.execute(query);
|
}
|
catch(SQLException ex)
|
{
|
bl=false;
|
System.out.println("SQLException:" + ex.getMessage());
|
}finally {
|
try {
|
if(sql!=null)sql.close();
|
if(mysql_con!=null)mysql_con.close();
|
} catch (SQLException e) {
|
// TODO Auto-generated catch block
|
e.printStackTrace();
|
}
|
}
|
return bl;
|
}
|
//¹Ø±ÕÁ¬½Ó
|
public void close_con(Connection mysql_con)
|
{
|
try {
|
mysql_con.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
public static void main(String[] args) {
|
MySqlPool mPool=new MySqlPool();
|
Connection conn=mPool.getConn();
|
System.out.println("conn:"+conn);
|
String sql="show databases";
|
//boolean bl=mPool.executeUpdate(sql, conn);
|
|
|
}
|
}
|
