概述:事务指的是逻辑上的一组操作,组成这组操作的各个逻辑单元要么一起成功,要么一起失败.
mysql.sql
create table account(
id int primary key auto_increment,
name varchar(20),
money double
);
insert into account values (null,'张三',10000);
insert into account values (null,'凤姐',10000);
JDBC中事务的管理:
Dbutils实现事务管理:
界面:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>转账页面</h1>
<form action="${ pageContext.request.contextPath }/这里写Servlet" method="post">
付款人:<input type="text" name="from"><br/>
收款人:<input type="text" name="to"><br/>
转账金额:<input type="text" name="money"><br/>
<input type="submit" value="转账">
</form>
</body>
</html>
jar包:
方式一:
把多个对于数据库的操作所有的Connection绑定到当前线程中。该业务的操作所用到的Connection全部由当前线程管理。
JDBCUtilsShiwu.java(jdbc工具类)
package com.it.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;
/**
* JDBC的工具类:把多个对于数据库的操作所有的Connection绑定到当前线程中。
该业务的操作所用到的Connection全部由当前线程管理
* @author admin
*/
public class JDBCUtilsShiwu {
private static final ComboPooledDataSource DATA_SOURCE =new ComboPooledDataSource();
private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
/**
* 获得连接的方法
*/
public static Connection getConnection(){
Connection conn = null;
try {
conn = tl.get();
if(conn == null){
conn = DATA_SOURCE.getConnection();
tl.set(conn);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void beginTransaction() throws SQLException{
Connection conn = null;
conn = tl.get();
if(conn == null){
conn = DATA_SOURCE.getConnection();
tl.set(conn);
}
conn.setAutoCommit(false);
}
public static void commitTransaction() throws SQLException{
Connection conn = tl.get();
conn.commit();
}
public static void rollBackTransaction() throws SQLException{
Connection conn = tl.get();
conn.rollback();
}
public static DataSource getDataSource(){
return DATA_SOURCE;
}
}
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- 默认配置 -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///day315</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
Servlet02.java
package com.it.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.it.services.Services02;
/**
* @author 侯青华
* @version 创建时间:2018年3月15日 下午8:59:03
*/
public class Servlet02 extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
//接收表单提交数据
String from = request.getParameter("from");
String to = request.getParameter("to");
double money = Double.parseDouble(request.getParameter("money"));
//调用service层处理
Services02 services02 = new Services02();
try {
services02.trans(from,to,money);
} catch (Exception e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
Services02.java
package com.it.services;
import java.sql.SQLException;
import com.it.dao.Dao02;
import com.it.utils.JDBCUtilsShiwu;
public class Services02 {
/**
* 处理方案二: 思想: 把多个对于数据库的操作所有的Connection绑定到当前线程中。 该业务的操作所用到的Connection全部由当前线程管理
*
* @param from
* @param to
* @param money
* @throws Exception
*/
public void trans(String from, String to, double money) throws Exception {
Dao02 dao02 = new Dao02();
try {
//开启事务
JDBCUtilsShiwu.beginTransaction();
//处理业务
//扣钱
dao02.outmoney(from,money);
//加钱
dao02.inmoney(to,money);
//提交事务
JDBCUtilsShiwu.commitTransaction();
} catch (SQLException e) {
//异常回滚事务
try {
JDBCUtilsShiwu.rollBackTransaction();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
Dao02.java
package com.it.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import com.it.utils.JDBCUtilsShiwu;
public class Dao02 {
public void outmoney(String from, double money) throws Exception {
// 从工具类中获得连接,工具类会自动从线程中拿
Connection conn = JDBCUtilsShiwu.getConnection();
String sql = "update account set money = money - ? where name = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setDouble(1, money);
pst.setString(2, from);
pst.executeUpdate();
pst.close();
}
public void inmoney(String to, double money) throws Exception {
// 从工具类中获得连接,工具类会自动从线程中拿
Connection conn = JDBCUtilsShiwu.getConnection();
String sql = "update account set money = money + ? where name = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setDouble(1, money);
pst.setString(2, to);
pst.executeUpdate();
pst.close();
}
}
方式二:
把多个对于数据库的操作所有的Connection提取到Service层。也就是说,由Service层来给dao层传递Connection。
c3p0jdbcUtils.java(工具类)
package com.it.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;
/**
* jdbc c3p0工具类
*
* @author 侯青华
*
*/
public class c3p0jdbcUtils {
// 私有构造
private c3p0jdbcUtils() {
}
// c3p0数据源
private static final ComboPooledDataSource DATA_SOURCE = new ComboPooledDataSource();
/**
* 获得连接
*/
public static Connection getConnection() {
Connection conn = null;
try {
conn = DATA_SOURCE.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static DataSource getDataSource() {
return DATA_SOURCE;
}
/**
* 释放资源
*/
/**
* 释放资源的方法
*/
public static void release(ResultSet rs, Statement pst, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 垃圾回收尽快回收对象.
rs = null;
}
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 垃圾回收尽快回收对象.
pst = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 垃圾回收尽快回收对象.
conn = null;
}
}
public static void release(Statement pst, Connection conn) {
if (pst != null) {
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 垃圾回收尽快回收对象.
pst = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
// 垃圾回收尽快回收对象.
conn = null;
}
}
}
Servlet01.java
package com.it.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.it.services.Services01;
/**
* @author 侯青华
* @version 创建时间:2018年3月15日 下午8:43:31
*/
public class Servlet01 extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码post
request.setCharacterEncoding("UTF-8");
String from = request.getParameter("from");
String to = request.getParameter("to");
double money = Double.parseDouble(request.getParameter("money"));
//service层处理业务
Services01 services01 = new Services01();
services01.trans(from,to,money);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
Services01.java
package com.it.services;
import java.sql.Connection;
import java.sql.SQLException;
import com.it.dao.Dao01;
import com.it.utils.c3p0jdbcUtils;
public class Services01 {
/**
* 处理方案一: 思想: 把多个对于数据库的操作所有的Connection提取到Service层.
* 也就是说,由Service层来给dao层传递Connection
*
* @param from
* @param to
* @param money
*/
public void trans(String from, String to, double money) {
Dao01 dao01 = new Dao01();
// 初始化链接
Connection conn = null;
try {
conn = c3p0jdbcUtils.getConnection();
// 禁止事务自动提交
conn.setAutoCommit(false);
// 扣钱
dao01.kouqian(from, money, conn);
/*
* 测试事务 int x = 2 / 0;
*/
// 增加钱
dao01.jiaqian(to, money, conn);
// 提交事务
conn.commit();
} catch (Exception e) {
// 出现异常回滚事务
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
}
Dao01.java
package com.it.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class Dao01 {
public void kouqian(String from, double money, Connection conn) throws Exception {
String sql ="update account set money = money - ? where name = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setDouble(1, money);
pst.setString(2, from);
pst.executeUpdate();
}
public void jiaqian(String to, double money, Connection conn) throws Exception {
String sql ="update account set money = money + ? where name = ?";
PreparedStatement pst = conn.prepareStatement(sql);
pst.setDouble(1, money);
pst.setString(2, to);
pst.executeUpdate();
}
}
方式三:(使用Dbutils)
底层使用的是“方式二”。 区别在于,当使用DBUtils做事务管理的时候,使用DbUtils来提交或者回滚事务,
把多个对于数据库的操作所有的Connection提取到Service层,也就是说,由Service层来给dao层传递Connection。(使用方式二中的工具类)
Servlet03.java
package com.it.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.it.services.Services02;
import com.it.services.Services03;
/**
* @author 侯青华
* @version 创建时间:2018年3月15日 下午8:59:03
*/
public class Servlet03 extends HttpServlet {
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//解决中文乱码
request.setCharacterEncoding("utf-8");
//接收表单提交数据
String from = request.getParameter("from");
String to = request.getParameter("to");
double money = Double.parseDouble(request.getParameter("money"));
//调用service层处理
Services03 services02 = new Services03();
try {
services02.trans(from,to,money);
} catch (Exception e) {
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
Services03.java
package com.it.services;
import java.sql.Connection;
import org.apache.commons.dbutils.DbUtils;
import com.it.dao.Dao03;
import com.it.utils.c3p0jdbcUtils;
public class Services03 {
/**
* 底层原理:
底层使用的是“事务管理处理方案一”。
区别在于,当使用DBUtils做事务管理的时候,使用DbUtils来提交或者回滚事务
思想:
把多个对于数据库的操作所有的Connection提取到Service层.
也就是说,由Service层来给dao层传递Connection
* @param from
* @param to
* @param money
*/
public void trans(String from, String to, double money) {
Dao03 dao03 = new Dao03();
//禁止事务自动提交
Connection conn = null;
try {
//获得链接
conn = c3p0jdbcUtils.getConnection();
//处理业务
dao03.outMoney(conn,from,money);
dao03.inMoney(conn,to,money);
//利用Dbutils提交事务
DbUtils.commitAndCloseQuietly(conn);
} catch (Exception e) {
//利用Dbutils回滚事务
DbUtils.rollbackAndCloseQuietly(conn);
// TODO: handle exception
}
}
}
Dao03.java
package com.it.dao;
import java.sql.Connection;
import org.apache.commons.dbutils.QueryRunner;
public class Dao03 {
public void outMoney(Connection conn, String from, double money) throws Exception {
QueryRunner qry = new QueryRunner();
String sql = "update account set money = money - ? where name = ?";
qry.update(conn, sql, money ,from);
}
public void inMoney(Connection conn, String to, double money) throws Exception {
QueryRunner qry = new QueryRunner();
String sql = "update account set money = money + ? where name = ?";
qry.update(conn, sql, money ,to);
}
}
最后附录上web.xml吧,这里只做事务demo演示简单的事务处理方式。
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>shiwu01</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<servlet>
<description></description>
<display-name>Servlet01</display-name>
<servlet-name>Servlet01</servlet-name>
<servlet-class>com.it.servlet.Servlet01</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Servlet01</servlet-name>
<url-pattern>/Servlet01</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>Servlet02</display-name>
<servlet-name>Servlet02</servlet-name>
<servlet-class>com.it.servlet.Servlet02</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Servlet02</servlet-name>
<url-pattern>/Servlet02</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>Servlet03</display-name>
<servlet-name>Servlet03</servlet-name>
<servlet-class>com.it.servlet.Servlet03</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Servlet03</servlet-name>
<url-pattern>/Servlet03</url-pattern>
</servlet-mapping>
</web-app>