项目结构：
数据库：
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.53 : Database - threadlocal
*********************************************************************
*/ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`threadlocal` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `threadlocal`; /*Table structure for table `person` */ DROP TABLE IF EXISTS `person`; CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; /*Data for the table `person` */ insert into `person`(`id`,`name`) values (12,'ccc'),(13,'ddd'),(15,'ccc'),(16,'ddd'); /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
项目代码：
com.gordon.dao:
--SaveDao.java
package com.gordon.dao; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException; import com.gordon.domain.Person;
import com.gordon.utils.DataSourceUtil4threadLocal; public class SaveDao { public void add1(Person p) throws SQLException {
String sql = "insert into person (name) values (?)";
Connection connection = DataSourceUtil4threadLocal.getConnection();
PreparedStatement pst = connection.prepareStatement(sql);
pst.setString(1, p.getName());
pst.executeUpdate();
} public void add2(Person p) throws SQLException {
String sql = "insert into person (name) values (?)";
Connection connection = DataSourceUtil4threadLocal.getConnection();
PreparedStatement pst = connection.prepareStatement(sql);
pst.setString(1, p.getName());
pst.executeUpdate();
} }
com.gordon.domain
--Person.java
package com.gordon.domain;
public class Person {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
com.gordon.service
--SaveService.java
package com.gordon.service; import java.sql.Connection;
import java.sql.SQLException; import com.gordon.dao.SaveDao;
import com.gordon.domain.Person;
import com.gordon.utils.DataSourceUtil4threadLocal; public class SaveService { public void thread_add(Person p1, Person p2) throws SQLException { Connection connection = DataSourceUtil4threadLocal.getConnection();
DataSourceUtil4threadLocal.startTransaction(); SaveDao saveDao = new SaveDao(); try {
saveDao.add1(p1); //int i = 1 / 0; saveDao.add2(p2); DataSourceUtil4threadLocal.commitTransaction();
} catch (Exception e) {
DataSourceUtil4threadLocal.rollbackTransaction();
e.printStackTrace();
} DataSourceUtil4threadLocal.closeConn(connection);
} public void add(Person p1, Person p2) { SaveDao saveDao = new SaveDao(); try {
saveDao.add1(p1); // int i = 1 / 0; saveDao.add2(p2);
} catch (Exception e) {
e.printStackTrace();
}
}
}
com.gordon.utils
--DataSourceUtil.java
package com.gordon.utils; import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DataSourceUtil {
private static ComboPooledDataSource ds = new ComboPooledDataSource(); /**
* 获取数据源
*
* @return 连接池
*/
public static DataSource getDataSource() {
return ds;
} /**
* 获取连接
*
* @return 连接
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
} /**
* 释放资源
*
* @param conn
* 连接
* @param st
* 语句执行者
* @param rs
* 结果集
*/
public static void closeResource(Connection conn, Statement st, ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
closeConn(conn);
} /**
* 释放连接
*
* @param conn
* 连接
*/
public static void closeConn(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
} } /**
* 释放语句执行者
*
* @param st
* 语句执行者
*/
public static void closeStatement(Statement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st = null;
} } /**
* 释放结果集
*
* @param rs
* 结果集
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
} }
}
--DataSourceUtil4ThreadLocal.java
package com.gordon.utils; import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement; import javax.sql.DataSource; import com.mchange.v2.c3p0.ComboPooledDataSource; public class DataSourceUtil4threadLocal {
private static ComboPooledDataSource ds = new ComboPooledDataSource(); private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>(); /**
* 获取数据源
*
* @return 连接池
*/
public static DataSource getDataSource() {
return ds;
} /**
* 获取连接
*
* @return 连接
* @throws SQLException
*/
public static Connection getConnection() throws SQLException { Connection connection = threadLocal.get(); if (connection == null) {
connection = ds.getConnection();
threadLocal.set(connection);
} return connection;
} public static void startTransaction() throws SQLException {
Connection connection = getConnection();
if (connection != null) {
connection.setAutoCommit(false);
}
} public static void commitTransaction() throws SQLException {
Connection connection = getConnection();
if (connection != null) {
connection.commit();
}
} public static void rollbackTransaction() throws SQLException {
Connection connection = getConnection();
if (connection != null) {
connection.rollback();
}
} public static void closeConn(Connection conn) {
if (conn != null) {
try {
conn.close();
threadLocal.remove();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
} /**
* 释放资源
*
* @param conn
* 连接
* @param st
* 语句执行者
* @param rs
* 结果集
*/
public static void closeResource(Connection conn, Statement st, ResultSet rs) {
closeResultSet(rs);
closeStatement(st);
closeConn(conn);
} /**
* 释放语句执行者
*
* @param st
* 语句执行者
*/
public static void closeStatement(Statement st) {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st = null;
} } /**
* 释放结果集
*
* @param rs
* 结果集
*/
public static void closeResultSet(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
} }
}
com.gordon.web.servlet
--SaveServlet.java
package com.gordon.web.servlet; import java.io.IOException;
import java.sql.SQLException; import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import com.gordon.domain.Person;
import com.gordon.service.SaveService; /**
* Servlet implementation class SaveServlet
*/
@WebServlet("/saveServlet")
public class SaveServlet extends HttpServlet {
private static final long serialVersionUID = 1L; /**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException { Person p1 = new Person();
p1.setName("ccc"); Person p2 = new Person();
p2.setName("ddd"); // new SaveService().add(p1, p2); // 原始方法 try {
new SaveService().thread_add(p1, p2); // 通过线程绑定连接
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} } /**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
} }
c3p0-config.xml
<c3p0-config>
<!-- 默认配置，如果没有指定则使用这个配置 -->
<default-config>
<!-- 基本配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/threadlocal</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> <!-- 命名的配置 -->
<named-config name="itcast">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/xxxx</property>
<property name="user">root</property>
<property name="password">1234</property> <!-- 如果池中数据连接不够时一次增长多少个 -->
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">20</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">40</property>
<property name="maxStatements">20</property>
<property name="maxStatementsPerConnection">5</property>
</named-config>
</c3p0-config>