DBCP
使用dbcp第三方包要导入
三个jar包
使用很简单：
1、纯java方式拿dbcp连接池
@Test public void testDbcpDemo() throws SQLException{ BasicDataSource pool = new BasicDataSource(); pool.setUsername("root"); pool.setPassword(""); pool.setUrl("jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf-8"); pool.setDriverClassName("com.mysql.jdbc.Driver"); System.out.println(pool.getMaxActive());//最大多少个连接对象 System.out.println(pool.getMaxIdle());//最大空闲时间，一个用户多久不用，就收回 System.out.println(pool.getMaxWait());//最多等待多久，超过这个时间就跑出异常 System.out.println(pool.getInitialSize());//初始化有几个连接 System.out.println("---------------我是传说的分隔符-------------------------"); for(int i=0;i<20;i++){ Connection con = pool.getConnection(); System.out.println((i+1)+": "+con.hashCode()); if(i%2==0){ con.close(); } } }2、使用配置文件的方式拿connection
@Test public void testPropertyDpcp() throws Exception { Properties p = new Properties(); p.load(DbcpPoolDemo.class.getResourceAsStream("dbcp.properties")); //这种方式是将配置文件和当前class放在一起 //p.load(DbcpPoolDemo.class.getClassLoader().getResourceAsStream("dpcp.properties")); //这种方式是将配置文件放在与src和bin同级 DataSource pool = BasicDataSourceFactory.createDataSource(p); //System.out.println(pool); for(int i=0;i<10;i++){ Connection con = pool.getConnection(); System.out.println((i+1)+": "+con.hashCode()); if(i%2==0){ con.close(); System.out.println(con.hashCode()); System.out.println(con.toString()); } } }}3、做一个由dbcp数据连接池的工具类
package cn.hncu.dbcp;import java.lang.reflect.InvocationHandler;import java.lang.reflect.Method;import java.lang.reflect.Proxy;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Properties;import javax.sql.DataSource;import org.apachemons.dbcp.BasicDataSourceFactory;public class DbcpUtil { private static DataSource pool ; private static ThreadLocal threads = new ThreadLocal(); static{ //采用配置文件的方式连接数据库 try { Properties p = new Properties(); p.load(DbcpUtil.class.getResourceAsStream("jdbc.properties")); pool = BasicDataSourceFactory.createDataSource(p); } catch (Exception e) { e.printStackTrace(); } } //返回pool池 public static DataSource getDataSource(){ return pool ; } public static synchronized Connection getCon() throws InterruptedException, SQLException{ Connection con = threads.get(); if(con==null){ con = pool.getConnection(); threads.set(con); } return con; }// public static void main(String[] args) throws InterruptedException {// System.out.println(getCon());// }} 4、配置文件信息
driver=com.mysql.jdbc.Driverurl=jdbc\:mysql\://127.0.0.1\:3306/aa?useUnicode\=true&characterEncoding\=UTF-8username=rootpassword=
C3P0
c3p0这个比较稳定，速度也比较快，hibernate框架使用的就是这个数据库连接池
需要导入一个jar包，我们要注意的是使用配置文件使用c3p0的时候，它的配置文件我们要放在classpath目录下，空参new是使用默认的配置文件，而带参数名new是使用名为
1、纯java方式拿C3P0连接池
//纯java的方式使用c3p0数据库连接池 @Test public void testC3p0() throws PropertyVetoException, SQLException{ ComboPooledDataSource pool = new ComboPooledDataSource(); pool.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf-8"); pool.setUser("root"); pool.setPassword(""); pool.setDriverClass("com.mysql.jdbc.Driver"); for(int i=0;i<20;i++){ Connection con = pool.getConnection(); System.out.println((i+1)+": "+con.hashCode()); if(i%2==0){ con.close(); } } //注意 c3p0和dbcp的close方法不同的是，c3p0如果关了就释放内存，下次取时重新开内存，内存地址不共用（不会重复） }2、使用配置文件的方式使用c3p0
@Test public void testPropertiesDemo() throws Exception{ //空参-去classpath目录下加载配置文件"c3p0-config.xml"，且使用配置文件当中的默认配置// ComboPooledDataSource pool = new ComboPooledDataSource(); //指定配置文件中的nfig name="hncu"> 加载这个指定的配置 ComboPooledDataSource pool = new ComboPooledDataSource("hncu"); for(int i=0;i<20;i++){ Connection con = pool.getConnection(); System.out.println((i+1)+": "+con.hashCode());// if(i%2==0){// con.close();// } } } 3、使用c3p0做一个拿数据库连接对象工具类
package cn.hncu.c3p0;import java.sql.Connection;import java.sql.SQLException;import javax.sql.DataSource;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3p0Pool { private static ComboPooledDataSource pool ; private static ThreadLocal threads = new ThreadLocal(); static{ pool = new ComboPooledDataSource("hcun"); } public static DataSource getDataSource(){ return pool; } public static Connection getConnection() throws Exception{ Connection con = threads.get(); if(con==null){ con = pool.getConnection(); threads.set(con); } return con; }} c3p0和dbcp的close方法的区别：
c3p0和dbcp的close方法不同的是，c3p0如果关了就释放内存，下次取时重新开内存，内存地址不共用（不会重复）
DBUTILS
使用dbutlis我们也要导入相关的jar包，这点一定要注意
1、使用dbutls做查询–使用BeanListHandler
我们要注意的是，只要值对象的setter-getter的函数名和数据库的 段名一致即可， 博主测试可行。
@Test public void dbUtilsQuery() throws SQLException{ QueryRunner run = new QueryRunner(C3p0Pool.getDataSource()); String sql ="select * from test1"; List studs = run.query(sql, new BeanListHandler(Stud.class)); System.out.println(studs); } 2、使用dbutls做查询–使用MapListHandler
@Test public void dbUtilsQuery2() throws SQLException{ QueryRunner run = new QueryRunner(C3p0Pool.getDataSource()); String sql ="select * from test1"; List> studs = run.query(sql, new MapListHandler()); System.out.println(studs); } 3、使用dbutlis做保存
@Test public void save() throws SQLException{ QueryRunner run = new QueryRunner(C3p0Pool.getDataSource()); String sql = "insert into dbutil(id,name,address,age) values(?,?,?,?) "; run.update(sql,"A001","Aike","中国上海",25); }4、使用dbutlis做保存，同时开启事务
注意：要想将dbutils也用事务绑定，那么必须在执行的时候是同一个con连接。
@Test public void save2() throws Exception{ QueryRunner run = new QueryRunner(C3p0Pool.getDataSource()); Connection con = C3p0Pool.getConnection(); try { con.setAutoCommit(false); //要想将dbutils也用事务绑定，那么必须在执行的时候是同一个con连接。 run.update(con,"insert into dbutil(id,name,address,age) values(?,?,?,?)", "A006","Tom","中国杭州",27); run.update(con,"insert into dbutil(id,name,address,age) values(?,?,?,?)", "A004","Mike","中国武汉",29); conmit(); } catch (Exception e) { con.rollback(); System.out.println("事务回滚了"); }finally{ con.setAutoCommit(true); con.close(); } }5、dbutil的批处理
这里要注意的是参数是一个二维参数数组。
@Test public void batch() throws SQLException{ QueryRunner run = new QueryRunner(C3p0Pool.getDataSource()); for(int i=0;i<100;i++){ String sql = "insert into person3(id,name) values(?,?) "; String str = "000"+i; str = str.substring(str.length()-3,str.length()); String id1 ="A"+str; String id2="B"+str; String params[][]= {{id1,"Alice"+i},{id2,"Bob"+i}}; run.batch(sql, params); } }扩展包commons-dbutilss-ext.jar的功能
注意的是，扩展包的功能要实现，我们必须在值对象那边加注解,并且保证字段名和setter-getter的函数名一致
不执行sql语句，直接查询
@Test public void query4(){ ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource()); List dbutils = run.query(DbUtil.class); System.out.println(dbutils); } 不执行sql语句，直接做保存数据
@Test public void save3(){ ExtQueryRunner run = new ExtQueryRunner(C3p0Pool.getDataSource()); DbUtil db = new DbUtil(); db.setId("A007"); db.setName("森"); db.setAddress("衡山店门"); db.setAge(22); run.save(db); }值对象- - - Stud
package cn.hncu.dbutils;public class Stud { private String id; private String name; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getNm() { return name; } public void setNm(String nm) { this.name = nm; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((id == null) ? 0 : id.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; Stud other = (Stud) obj; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } @Override public String toString() { return id + ", " + name ; }}值对象- - - -DbUtil - - - - - - - -加注解
package cn.hncu.dbutils;import java.beans.ConstructorProperties;import org.apachemons.dbutils.ext.Column;import org.apachemons.dbutils.ext.Table;@Table(value="dbutil")public class DbUtil { @Column private String id; @Column private String name; @Column private String address; @Column private Integer age; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public int hashCode() { final int prime = 31; int result = 1; result = prime * result + ((id == null) ? 0 : id.hashCode()); return result; } @Override public boolean equals(Object obj) { if (this == obj) return true; if (obj == null) return false; if (getClass() != obj.getClass()) return false; DbUtil other = (DbUtil) obj; if (id == null) { if (other.id != null) return false; } else if (!id.equals(other.id)) return false; return true; } @Override public String toString() { return id + ", " + name + "," + address + "," + age; }}