java c3p0连接池 不用spring单独使用c3p0

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 

3.mysql 的连接方式

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);
}