C3P0+DBUtils的使用

最新推荐文章于 2021-02-13 08:45:34 发布

张怼怼吖

最新推荐文章于 2021-02-13 08:45:34 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/qq_35027240/article/details/80956779

版权

采用配置文件的方式：

配置连接池：c3p0-config.xml

<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
<default-config>
<!-- 基本配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/scf</property>
<property name="user">root</property>
<property name="password">root</property>

<!--扩展配置-->
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config> 

</c3p0-config>

l 使用连接池

@Test

/**

 * 采用配置文件的方式：

 */

public void demo2(){

Connection conn = null;

PreparedStatement pstmt = null;

ResultSet rs = null;

try{

// 获得连接：从连接池中获取：

// 创建连接池：//创建连接池默认去类路径下查找c3p0-config.xml

ComboPooledDataSource dataSource = new ComboPooledDataSource();

// 从连接池中获得连接:

conn = dataSource.getConnection();

// 编写SQL：

String sql = "select * from account";

// 预编译SQL:

pstmt = conn.prepareStatement(sql);

// 执行SQL：

rs = pstmt.executeQuery();

while(rs.next()){

System.out.println(rs.getInt("id")+" "+rs.getString("name")+" "+rs.getDouble("money"));

}

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.release(rs, pstmt, conn);

}

}

1.1 改写工具类

package com.day01.c3p0;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
 * c3p0连接池
 * @author ZWT
 *
 */
public class JdbcUtil {

public static final ComboPooledDataSource ds = new ComboPooledDataSource();
//获得连接
public static Connection getConnection() throws Exception{
return ds.getConnection();
}
//获得dataSource
public static DataSource getDataSource(){
return ds;
}

public static void release(PreparedStatement prst, Connection conn) {
release(null, prst, conn);
}
//释放资源
public static void release(ResultSet rs, PreparedStatement prst, Connection conn) {
if (rs != null) {
        try {
            rs.close();
        } catch (Exception sqlEx) {
        
        }
        rs = null;
}
if (prst != null) {
try {
prst.close();
} catch (Exception sqlEx) {
 
}
prst = null;
}
if (conn != null) {
try {
conn.close();
} catch (Exception sqlEx) {
 
}
conn = null;
}
}

}

再次使用

package com.day01.c3p0;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;


public class C3P0DBUtilsDemo {

@Test
public void selectOne() throws Exception{

QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
Account account = qr.query("select * from account where id=?",
new ResultSetHandler<Account>() {


@Override
public Account handle(ResultSet rs) throws SQLException {
Account account = new Account();
if(rs.next()){
account.setId(rs.getInt("id"));
account.setName(rs.getString("name"));
account.setMoney(rs.getDouble("money"));

}
return account;
}
},1);
System.out.println(account);
}
//删除
@Test
public void delete() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
int num = qr.update("delete from account where id=?",1);
System.out.println(num);
}
//查询一个-------BeanHandler
@Test
public void selectOne1() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
Account account = qr.query("select * from account where id=?",new BeanHandler<Account>(Account.class),2);
System.out.println(account);
}
//查询全部------BeanListHandler
@Test
public void select() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
List<Account> query = qr.query("select * from account",new BeanListHandler<Account>(Account.class));
System.out.println(query);
}
//聚合函数----ScalarHander
@Test
public void count() throws Exception{
QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
long query = (long) qr.query("select count(*) from account",new ScalarHandler());
System.out.println(query);
}

}

1.1.1 为什么要学习DBUtils

因为JDBC手写比较麻烦，而且有非常多的代码是类似的。比如获得连接，预编译SQL，释放资源等..那么可以将这些代码抽取出来放到工具类中。将类似的代码进行抽取。大大简化JDBC的编程。

1.1 DBUtils的API

1.1.1 DBUtils的API的概述

1.1.1.1 QueryRunner对象：核心运行类

l 构造方法：

l 方法：

在一般情况下如果执行CRUD的操作：

构造：

QueryRunner(DataSource ds);

方法：

int update(String sql,Object… args);

T query(String sql,ResultSetHandler rsh,Object… args);

1.1 DBUtils的使用之增删改的操作

1.1.1 DBUtils的添加操作

@Test

/**

 * 添加操作

 */

public void demo1() throws SQLException{

// 创建核心类：QueryRunner:

QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());

queryRunner.update("insert into account values (null,?,?)", "ddd",10000);

}

1.1.2 DBUtils的修改操作
@Test

/**

 * 修改操作

 */

public void demo2() throws SQLException{

// 创建核心类:

QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());

queryRunner.update("update account set name=?,money=? where id =?", "eee",20000,4);

}

1.1.3 DBUtils的删除操作
@Test

/**

 * 删除操作

 */

public void demo3() throws SQLException{

// 创建核心类:

QueryRunner queryRunner = new QueryRunner(JDBCUtils2.getDataSource());

queryRunner.update("delete from account where id = ?", 3);

}