1,项目目录结构
会使用到四个jar文件:
(1)mysql数据库驱动jar文件
(2)提供连接MySQL数据库的c3p0数据源,需要两个jar文件
(3)commons-dbutils。。。。。jar文件。。。。关于这个jar文件的讲解可以参考commons-dbutils讲解
2,c3p0-config.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 这是默认配置信息 -->
<default-config>
<!-- 连接四大参数配置 -->
<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123</property>
<!-- 池参数配置 -->
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</default-config>
<!-- 专门为oracle提供的配置信息 -->
<named-config name="oracle-config">
<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb1</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">3</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">2</property>
<property name="maxPoolSize">10</property>
</named-config>
</c3p0-config>
3,JDBCUtils.java
package com.improve.jdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 注意必须使用c3p0-config.xml的配置方式。将c3p0-config.xml文件放在src根目录下。
* 默认配置。
*
* */
public class JDBCUtils {
private static ComboPooledDataSource ds=new ComboPooledDataSource();
private static ThreadLocal<Connection> tl=new ThreadLocal<Connection>();
/*使用连接池返回一个连接对象 */
public static Connection getConnection() throws SQLException{
Connection conn=tl.get();
if(conn!=null)
return conn;
else
return ds.getConnection();
}
/*返回一个连接池对象*/
public static DataSource getDataSource(){
return ds;
}
/*为conn赋值,并开启事务*/
public static void beginTransaction() throws SQLException{
Connection conn=tl.get();
if(conn!=null)
throw new RuntimeException("已经开启事务");
conn=getConnection();
conn.setAutoCommit(false);
tl.set(conn);
}
public static void commitTransaction() throws SQLException{
Connection conn=tl.get();
if(conn==null)
throw new SQLException("还没有开启事务,不能提交");
conn.commit();
conn.close();
tl.remove();
}
public static void rollBackTransaction() throws SQLException{
Connection conn=tl.get();
if(conn==null)
throw new SQLException("还没有开启事务,不能回滚");
conn.rollback();
conn.close();
tl.remove();
}
public static void releaseConnection(Connection connection) throws SQLException{
Connection conn=tl.get();
if(conn==null)//说明没有开启事务,没有使用事务专用连接
connection.close();
if(conn!=connection){//虽然有事务专用连接,但是事务连接和传递过来的参数连接不是同一个连接,所以关闭connection连接
connection.close();
}
}
}
4、TXQueryRunner.java该类继承了QueryRunner,将操作中的Connection conn=JDBCUtils.getConnection() 和 conn.releaseConnection()进行了提取,这样在Dao中所有方法中可以避免重复写这两行代码,使得Dao中的操作更为简单。Dao中的QueryRunner qr=new XTQueryRunner();
package com.improve.service;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import com.improve.jdbcUtils.JDBCUtils;
public class TXQueryRunner extends QueryRunner{
@Override
public int[] batch(String sql, Object[][] params) throws SQLException {
Connection conn=JDBCUtils.getConnection();
int[] result=super.batch(conn,sql,params);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, Object param, ResultSetHandler<T> rsh)
throws SQLException {
Connection conn=JDBCUtils.getConnection();
T result=super.query(conn,sql,param,rsh);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, Object[] params, ResultSetHandler<T> rsh)
throws SQLException {
Connection conn=JDBCUtils.getConnection();
T result=super.query(conn,sql,params,rsh);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params)
throws SQLException {
Connection conn=JDBCUtils.getConnection();
T result=super.query(conn,sql,rsh,params);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public <T> T query(String sql, ResultSetHandler<T> rsh) throws SQLException {
Connection conn=JDBCUtils.getConnection();
T result=super.query(conn,sql,rsh);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql) throws SQLException {
Connection conn=JDBCUtils.getConnection();
int result=super.update(conn,sql);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql, Object param) throws SQLException {
Connection conn=JDBCUtils.getConnection();
int result=super.update(conn,sql);
JDBCUtils.releaseConnection(conn);
return result;
}
@Override
public int update(String sql, Object... params) throws SQLException {
Connection conn=JDBCUtils.getConnection();
int result=super.update(conn,sql,params);
JDBCUtils.releaseConnection(conn);
return result;
}
}
5,Dao类
package com.improve.service;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import com.improve.jdbcUtils.JDBCUtils;
public class AccountDao {
public void update(String name,double balance) throws SQLException{
QueryRunner qr=new TXQueryRunner();
String sql="update emp set balance=balance+? where name=?";
Object[] params={balance,name};
qr.update(sql,params);
}
}
6,Service类
package com.improve.service;
import java.sql.SQLException;
import org.junit.Test;
import com.improve.jdbcUtils.JDBCUtils;
public class UserService {
private AccountDao dao=new AccountDao();
@Test
public void addUser() throws SQLException{
JDBCUtils.beginTransaction();
dao.update("zhangsan", 100);
if(true){
throw new RuntimeException("故意测试一下");
}
dao.update("lisi", -100);
// JDBCUtils.rollBackTransaction();
JDBCUtils.commitTransaction();
}
}