mysql事务转账_事务（转账实例）

事务(以转账为例)

事务:

就是一件完整的事情,包含多个操作单元,这些操作要么全部成功,要么全部失败.

例如:转账，包含转出操作和转入操作.

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) {