1.c3p0 sqlserver 的连接方式 + statement (不如preparedStatement,因为sql中的字符串引号太难匹配,而且statement 有sql注入的风险)
如之前文章<<jdbc mysql和sql server 建立连接代码 / tomcat+jdbc会出现的问题No suitable driver found for问题>>所说:
Connection 复用问题, 多线程共用一个Connection 对象,没问题, 他们不停地用这个Connection 对象去查询没问题, 猜测是Connection 类内部优化,
(参考:http://blog.csdn.net/nx188/article/details/49818697)
但是如果一个线程占用一个connection 过长, 导致其他线程无法拿到connection, 所以需要连接池管理, 我们用c3p0这个jdbc连接池
如<<spring + c3p0 连接池配置>>文章, 介绍了 spring + c3p0 的配置, 本文单独使用c3p0,
(参考:http://blog.csdn.net/nx188/article/details/50635349)
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
代码如下:
package c3p0;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0_datasource {
private static ComboPooledDataSource datasource ;
public static void initDatasource() throws PropertyVetoException{
datasource = new ComboPooledDataSource();
datasource.setUser("username");
datasource.setPassword("password");
datasource.setJdbcUrl("jdbc:sqlserver://Archat.qa.ntorp.com:55;DatabaseName=ArcDB");
datasource.setDriverClass("com.microsoft.sqlserver.jdbc.SQLServerDriver");
datasource.setInitialPoolSize(1);
datasource.setMinPoolSize(1);
datasource.setMaxPoolSize(2); //如果这里设置1个线程,那么下面代码只有一个线程能跑,另一个线程在等待connection,所以不用连接池,
datasource.setMaxStatements(50); //对于占用connection 时间长的线程,其他线程不能和他很好的公用一个connection
datasource.setMaxIdleTime(60);
}
public static Connection getConnection() throws SQLException {
return datasource.getConnection();
}
public static void main(String[] args) throws PropertyVetoException, SQLException {
initDatasource();
new Thread(new Runnable() {
@Override
public void run() {
while(true) {
Connection conn;
try {
conn = getConnection();
System.out.println("---------Threadname is " + Thread.currentThread().getName() +" connection name is : "+conn);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select top 10000 hoid,name from hote(nolock) ");
while(rs.next()) {
// System.out.println(rs.getString(1)+", "+rs.getString(2));
Thread.sleep(1000);
}
conn.close();//这句如果不写,connection 不回连接池
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}).start();
new Thread(new Runnable() {
@Override
public void run() {
while(true) {
Connection conn;
try {
conn = getConnection();
System.out.println("---------Threadname is " + Thread.currentThread().getName() +" connection name is : "+conn);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select top 10000 hoid,name from hote(nolock)");
while(rs.next()) {
// System.out.println(rs.getString(1)+", "+rs.getString(2));
Thread.sleep(1000);
}
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}).start();
}
}
2.。preparedStatement 调用方式
@Override
public String getDbValue(String date,String type,String typeValue) {
Connection connection = null;
ResultSet rs = null;
PreparedStatement preparedStatement = null;
String sql = null;
String dataStatisticValue = null;
try {
connection = mysqlClient.getConnection();
if(type.equalsIgnoreCase("ip")) {
sql = "select i_sum from d_statistic where date=? and type=?";
} else if(type.equalsIgnoreCase("device")) {
sql = "select d_sum from d_statistic where date=? and type=?";
} else if(type.equalsIgnoreCase("cellphone")) {
sql = "select c_sum from d_statistic where date=? and type=?";
}
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,date);
preparedStatement.setString(2,typeValue);
rs = preparedStatement.executeQuery();
while(rs.next()) {
dataStatisticValue = String.valueOf(rs.getBigDecimal(1));
}
return dataStatisticValue;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close();//这句如果不写,connection 不回连接池
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
preparedStatement 可以批量执行sql 语句
http://www.blogjava.net/redcoatjk/archive/2012/07/20/383599.html
public static void initDatasource() throws PropertyVetoException {
datasource = new ComboPooledDataSource();
datasource.setUser("username");
datasource.setPassword("password");
datasource.setJdbcUrl("jdbc:mysql://19.68.6.7:3306/dbName?useUnicode=true&characterEncoding=utf8");
datasource.setDriverClass("com.mysql.jdbc.Driver");
datasource.setInitialPoolSize(1);
datasource.setMinPoolSize(1);
datasource.setMaxPoolSize(2);
datasource.setMaxStatements(50);
datasource.setMaxIdleTime(60);
}