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 {
/*
* 注意:在使用配置文件拿dbcp池的时候,我们要将配置文件中的key值与pool池的私有变量名一致才能拿到
*/
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.apache.commons.dbcp.BasicDataSourceFactory;
public class DbcpUtil {
private static DataSource pool ;
private static ThreadLocal<Connection> threads = new ThreadLocal<Connection>();
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.Driver
url=jdbc\:mysql\://127.0.0.1\:3306/aa?useUnicode\=true&characterEncoding\=UTF-8
username=root
password=
C3P0
c3p0这个比较稳定,速度也比较快,hibernate框架使用的就是这个数据库连接池
需要导入一个jar包,我们要注意的是使用配置文件使用c3p0的时候,它的配置文件我们要放在classpath目录下,空参new是使用默认的配置文件,而带参数名new是使用名为<named-config name="hncu">
这个name指定的配置。
C3P0配置文件
<c3p0-config>
<!-- 默认配置,如果没有指定则使用这个配置 -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
<![CDATA[jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=UTF-8]]>
</property>
<property name="user">root</property>
<property name="password"></property>
<!-- 初始化池大小 -->
<property name="initialPoolSize">2</property>
<!-- 最大空闲时间 -->
<property name="maxIdleTime">30</property>
<!-- 最多有多少个连接 -->
<property name="maxPoolSize">10</property>
<!-- 最少几个连接 -->
<property name="minPoolSize">2</property>
<!-- 每次最多可以执行多少个批处理语句 -->
<property name="maxStatements">50</property>
</default-config>
<!-- 命名的配置 -->
<named-config name="hncu">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
<![CDATA[jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=UTF-8]]>
</property>
<property name="user">root</property>
<property name="password"></property>
<property name="acquireIncrement">5</property><!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property> <!-- he's important, but there's only one of him -->
</named-config>
</c3p0-config>
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();
//指定配置文件中的<named-config 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<Connection> threads = new ThreadLocal<Connection>();
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<Stud> studs = run.query(sql, new BeanListHandler<Stud>(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<Map<String, Object>> 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);
con.commit();
} 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<DbUtil> 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.apache.commons.dbutils.ext.Column;
import org.apache.commons.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;
}
}