事务(以转账为例)
事务:
就是一件完整的事情,包含多个操作单元,这些操作要么全部成功,要么全部失败.
例如:转账,包含转出操作和转入操作.
mysql中的事务:
mysql中事务默认是自动提交,一条sql语句就是一个事务.
开启手动事务方式
方式1:关闭自动事务.(了解)
set autocommit=off;
方式2:手动开启一个事务.(理解)
start transaction;--开启一个事务
commit;--事务提交
rollback;--事务回滚
扩展:
oracle中事务默认是手动的,必须手动提交才可以.
java中的事务:
Connection接口的api:★
setAutoCommit(false);//手动开启事务
commit():事务提交
rollback():事务回滚
扩展:了解 Savepoint还原点voidrollback(Savepoint savepoint) :还原到那个还原点
Savepoint setSavepoint() :设置还原点
例如:创建数据库和表
create database hjh;
use hjh;
create table account(
name varchar(20),
moneyint);
insert into account values('hejh','1000');
insert into account values('swy','1000');
完成 hejh给swy转500;
update account set money= money - 100 where name='';
update account set money= money + 100 where name='swy';
转账案例:
步骤分析:1.数据库和表2.新建一个项目 transfer3.导入jar包和工具类
驱动 jdbcUtils
c3p0及其配置文件和工具类
dbutils4.新建一个account.jsp 表单5.accountservlet:
接受三个参数
调用accountservice.account方法完成转账操作
打印信息6.account方法中:
使用jdbc不考虑事务
调用dao完成转出操作
调用dao完成转入操作7.dao中
代码实现:
web.xml
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
id="WebApp_ID" version="3.1">
AccountServlet
com.hjh.servlet.AccountServlet
AccountServlet
/account
account.jsp
转账页面付款人: | |
收款人: | |
转账金额: | |
AccountServlet.java
packagecom.hjh.servlet;importjava.io.IOException;importjava.io.PrintWriter;importjava.sql.SQLException;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.hjh.service.AccountService;/*** 转账案例*/
public class AccountServlet extendsHttpServlet {private static final long serialVersionUID = 1L;protected void doGet(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {//设置编码
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");//获取输出流
PrintWriter w =response.getWriter();//接收jsp页面传来的三个参数
String fromUser = request.getParameter("fromUser");
String toUser= request.getParameter("toUser");int money = Integer.parseInt(request.getParameter("money"));//调用AccountService的transterAccount(fromUser,toUser,money)方法
try{newAccountService().transterAccount(fromUser,toUser,money);
}catch(Exception e) {
e.printStackTrace();
w.println("转账失败");return;
}//打印提示信息
w.print("转账成功");
}protected void doPost(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {
doGet(request, response);
}
}
AccountService.java
packagecom.hjh.service;importjava.sql.SQLException;importcom.hjh.dao.AccountDao;public classAccountService {public void transterAccount(String fromUser, String toUser, int money) throwsException {
AccountDao dao= newAccountDao();//转出方,出钱
dao.accountFrom(fromUser,money);//转入方,进钱
dao.accountTo(toUser,money);
}
}
AccountDao.java
packagecom.hjh.dao;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importcom.hjh.utils.JDBCUtil;public classAccountDao {//出账
public void accountFrom(String fromUser, int money) throwsSQLException {
Connection conn= null;
PreparedStatement st= null;try{//获取连接
conn =JDBCUtil.getConnection();//编写sql
String sql="update account set money = money - ? where name = ?";//获取sql语句执行者
st =conn.prepareStatement(sql);//设置sql参数
st.setInt(1, money);
st.setString(2, fromUser);//执行sql
int i =st.executeUpdate();
System.out.println("转出钱成功"+i);
}catch(SQLException e) {
e.printStackTrace();throwe;
}finally{
JDBCUtil.closeResourse(conn, st);
}
}//入账
public void accountTo(String toUser, int money) throwsSQLException {
Connection conn= null;
PreparedStatement st= null;try{//获取连接
conn =JDBCUtil.getConnection();//编写sql
String sql="update account set money = money + ? where name = ?";//获取sql语句执行者
st =conn.prepareStatement(sql);//设置sql参数
st.setInt(1, money);
st.setString(2, toUser);//执行sql
int i =st.executeUpdate();
System.out.println("转入钱成功"+i);
}catch(SQLException e) {
e.printStackTrace();throwe;
}finally{
JDBCUtil.closeResourse(conn, st);
}
}
}
JDBCUtil.java
packagecom.hjh.utils;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.sql.Statement;public classJDBCUtil {final static String driver = "com.mysql.jdbc.Driver";final static String url = "jdbc:mysql://localhost/hjh?useUnicode=true&characterEncoding=UTF-8";final static String user = "root";final static String password = "root";
Connection conn= null;
PreparedStatement ps= null;
Statement st= null;
ResultSet rs= null;/**获取连接*/
public static Connection getConnection() throwsSQLException {
Connection conn= null;try{//注册驱动
Class.forName(driver);//获取连接
conn =DriverManager.getConnection(url, user, password);
}catch(ClassNotFoundException e) {
e.printStackTrace();
}returnconn;
}/**关闭资源closeResourse(conn,st)*/
public static voidcloseResourse(Connection conn,Statement st) {try{if(st!=null) {
st.close();
}else{
st= null;
}
}catch(SQLException e) {
e.printStackTrace();
}try{if(conn!=null) {