CREATE TABLE `account` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`uname` varchar(255) DEFAULT NULL,
`upwd` varchar(255) DEFAULT NULL,
`balance` decimal(8,2) DEFAULT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB;
INSERT INTO `account` VALUES (1, 'tesDest',
'112342', 1200.00);
INSERT INTO `account` VALUES (2, 'segvcw', '223352',
680.50);
创建Web项目，技术栈：JDBC + druid连接池 + Servlet + Ajax 使用带事务处理的JDBC工具类,
3.项目文件
package com.huawei.servlet;
import com.huawei.entity.Account;
import com.huawei.service.TransactionService;
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 java.io.IOException;
import java.io.PrintWriter;
/**
* @avthor HuangJun
* @date 2022/5/2 14:58
*/
@WebServlet(value = "/transfer.do")
public class TransferServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收表单参数
String fromAcc = req.getParameter("fromAcc");
String moneyStr = req.getParameter("money");
String toAcc = req.getParameter("toAcc");
resp.setContentType("text/json;charset=utf-8");
double money = 0.0;
if (moneyStr != null && !"".equals(moneyStr)) {
money = Double.parseDouble(moneyStr);
}
PrintWriter out = resp.getWriter();
//2.创建业务层对象 调用业务层方法
TransactionService tranService = new TransactionService();
if (tranService.chageAccount(fromAcc)) {
if (tranService.chageAccount(toAcc)) {
if (tranService.balanceVerify(fromAcc, money)) {
//3.根据业务方法的返回值进行处理
boolean isFlag = tranService.transactionAccount(fromAcc, toAcc, money);
if (isFlag) {
System.out.println("转账成功！");
//以响应输出成功标志到前端
out.println("转账成功");
} else {
System.out.println("转账失败！");
//以响应输出失败标志到前端
out.println("转账失败");
throw new RuntimeException("转账失败");//TODO 如果没有手动引发异常 转账过滤器是无法回滚的
}
} else {
out.println("" + fromAcc + "账户余额不足");
}
} else {
out.println("" + toAcc + "账户不存在");
}
} else {
out.println("" + fromAcc + "账户不存在");
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
}
package com.huawei.entity;
/**
* @avthor HuangJun
* @date 2022/5/2 15:05
*/
public class Account {
private int aid;
private String uname;
private String upwd;
private double balance;
public Account() {
}
public int getAid() {
return aid;
}
public void setAid(int aid) {
this.aid = aid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUpwd() {
return upwd;
}
public void setUpwd(String upwd) {
this.upwd = upwd;
}
public double getBalance() {
return balance;
}
public void setBalance(double balance) {
this.balance = balance;
}
@Override
public String toString() {
return "Account{" +
"aid=" + aid +
", uname='" + uname + '\'' +
", upwd='" + upwd + '\'' +
", balance=" + balance +
'}';
}
}
package com.huawei.filter;
import com.huawei.util.DBUtils;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import java.io.IOException;
/**
* @avthor HuangJun
* @date 2022/5/1 19:04
*/
@WebFilter(urlPatterns = "/*")
public class TransactionFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
try {
//TODO 只会针对insert update delete
System.out.println("---------请求执行--------");
DBUtils.beginTransaction();//开启事务
filterChain.doFilter(servletRequest, servletResponse);
DBUtils.commitAndClose();// 提交事务
System.out.println("---------给出响应--------");
} catch (Exception e) {
DBUtils.rollbackAndClose();//回滚事务
e.printStackTrace();
}
}
@Override
public void destroy() {
}
}
package com.huawei.service;
import com.huawei.dao.TransactionDao;
import com.huawei.entity.Account;
/**
* @avthor HuangJun
* @date 2022/5/2 15:01
*/
public class TransactionService {
private TransactionDao tranDao;
public TransactionService() {
tranDao = new TransactionDao();
}
//确认账户是否存在
public boolean chageAccount(String accNo) {
//TODO
boolean flag = false;
Account account = tranDao.queryAccountByWhere(accNo);
if (account != null) {
flag = true;
}
return flag;
}
//确认账户余额是否足够
public boolean balanceVerify(String accNo, double balance) {
//TODO
boolean flag = false;
Account account = tranDao.queryAccountByWhere(accNo);
if (account == null) {
return flag;
} else {
if (account.getBalance() < balance) {
return flag;
} else {
flag = true;
}
}
return flag;
}
//转账方法
public boolean transactionAccount(String accNo, String toAccount, double balance) {
//TODO
boolean flag = false;
try {
tranDao.transationOut(accNo, balance);
// int i=1/0;//手动引发异常
tranDao.transationIn(toAccount, balance);
flag = true;
} catch (Exception e) {
e.printStackTrace();
}
return flag;
}
}
package com.huawei.servlet;
import com.huawei.entity.Account;
import com.huawei.service.TransactionService;
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 java.io.IOException;
import java.io.PrintWriter;
/**
* @avthor HuangJun
* @date 2022/5/2 14:58
*/
@WebServlet(value = "/transfer.do")
public class TransferServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收表单参数
String fromAcc = req.getParameter("fromAcc");
String moneyStr = req.getParameter("money");
String toAcc = req.getParameter("toAcc");
resp.setContentType("text/json;charset=utf-8");
double money = 0.0;
if (moneyStr != null && !"".equals(moneyStr)) {
money = Double.parseDouble(moneyStr);
}
PrintWriter out = resp.getWriter();
//2.创建业务层对象 调用业务层方法
TransactionService tranService = new TransactionService();
if (tranService.chageAccount(fromAcc)) {
if (tranService.chageAccount(toAcc)) {
if (tranService.balanceVerify(fromAcc, money)) {
//3.根据业务方法的返回值进行处理
boolean isFlag = tranService.transactionAccount(fromAcc, toAcc, money);
if (isFlag) {
System.out.println("转账成功！");
//以响应输出成功标志到前端
out.println("转账成功");
} else {
System.out.println("转账失败！");
//以响应输出失败标志到前端
out.println("转账失败");
throw new RuntimeException("转账失败");//TODO 如果没有手动引发异常 转账过滤器是无法回滚的
}
} else {
out.println("" + fromAcc + "账户余额不足");
}
} else {
out.println("" + toAcc + "账户不存在");
}
} else {
out.println("" + fromAcc + "账户不存在");
}
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
doPost(req, resp);
}
package com.huawei.test;
import com.huawei.dao.TransactionDao;
import com.huawei.entity.Account;
/**
* @avthor HuangJun
* @date 2022/5/2 15:15
*/
public class DaoTest {
public static void main(String[] args) {
TransactionDao dao=new TransactionDao();
Account account= dao.queryAccountByWhere("test1");
System.out.println(account);
// dao.transationIn("test1",200);
dao.transationOut("test1",200);
}
}
jdbc.url=jdbc:mysql://localhost:3306/teach?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=123456
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/teach?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false
druid.username=root
druid.password=123456
druid.driverClassName=com.mysql.cj.jdbc.Driver
druid.maxActive=8
druid.maxWait=5000
package com.huawei.util;
import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* Jdbc工具类[加入事务控制+线程+Druid连接池]
*
* @avthor HuangJun
* @date 2022/3/30 10:36
*/
public class DBUtils {
static String USER = null;
static String USER_PASS = null;
static String DB_URL = null;
static String DRIVER_CLASS = null;
//容器变量 [ThreadLocal用于隔离多线程并发 产生的数据混乱]
private static ThreadLocal<Connection> conns = new ThreadLocal<>();
private static DataSource dataSource;
/**
* 静态代码块加载属性配置文件
*/
static {
Properties config = new Properties();
try {
//找到文件
InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
//并读取到内存
config.load(is);
//读取属性数据的key
DRIVER_CLASS = config.getProperty("jdbc.driverClassName");
//初始化Durid连接池数据
DruidDataSource dds = new DruidDataSource();
dds.configFromPropety(config);//加载属性文件到数据源对象
dataSource = dds;
} catch (IOException e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return dataSource;
}
/**
* 注册驱动
*/
static {
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConn() {
if (conns.get() == null) {// 当前线程是否有连接对象
Connection conn = null;
try {
conn = dataSource.getConnection();
conns.set(conn);//放入当前线程对象中
} catch (Exception e) {
e.printStackTrace();
}
}
return conns.get();
}
/**
* @throws Exception
*/
public static void beginTransaction() throws Exception {
getConn().setAutoCommit(false);
}
/**
* 提交事务
*/
public static void commit() {
try {
getConn().commit();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
/**
* 回滚
*/
public static void rollback() {
try {
getConn().rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public static Connection getConn(String DB_URL) {
Connection conn = null;
try {
conn = DriverManager.getConnection(DB_URL, USER, USER_PASS);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, Statement stmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.setAutoCommit(true);
conn.close();
conns.remove();
} catch (Exception e) {
e.printStackTrace();
}
}
}
/**
* 通用的DML sql语句执行方法
*
* @param sql
* @param params
* @return
*/
public static int update(String sql, Object... params) throws Exception {
int result = 0;
Connection conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sql);
//获取参数化SQL中的? 个数
int count = psmt.getParameterMetaData().getParameterCount();
if (count != params.length) {
return -1;
}
//遍历参数个数 从动态数组中取出
for (int i = 1; i <= count; i++) {
psmt.setObject(i, params[i - 1]);
}
//执行sql语句
result = psmt.executeUpdate();
close(conn, psmt, null);
return result;
}
/**
* 查询方法
*
* @param sql
* @param params
* @return
* @throws Exception
*/
public static ResultSet query(String sql, Object... params) throws Exception {
ResultSet result = null;
Connection conn = getConn();
PreparedStatement psmt = conn.prepareStatement(sql);
//获取参数化SQL中的? 个数
int count = psmt.getParameterMetaData().getParameterCount();
if (count != params.length) {
return null;
}
//遍历参数个数 从动态数组中取出
for (int i = 1; i <= count; i++) {
psmt.setObject(i, params[i - 1]);
}
//执行sql语句
result = psmt.executeQuery();
return result;
}
/**
* 获取数据库连接池中的连接
*
* @return 如果返回 null,说明获取连接失败
有值就是获取连接成功
*/
public static Connection getConnection() {
Connection conn = conns.get();
if (conn == null) {
try {
conn = dataSource.getConnection();//从数据库连接池中获取连接
conns.set(conn); // 保存到 ThreadLocal 对象中， 供后面的jdbc *** 作使用
conn.setAutoCommit(false); // 设置为手动管理事务
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
/**
* * 提交事务， 并关闭释放连接
*/
public static void commitAndClose() {
Connection connection = conns.get();
if (connection != null) { // 如果不等于 null， 说明 之前使用过连接， *** 作过数据库
try {
connection.commit(); // 提交 事务
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close(); // 关闭连接， 资源资源
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//一定要执行 remove *** 作，否则就会出错。 （因为 Tomcat 服务器底层使用了线程池技术）
conns.remove();
}
/**
* 回滚事务，并关闭释放连接
*/
public static void rollbackAndClose() {
Connection connection = conns.get();
if (connection != null) { // 如果不等于 null， 说明 之前使用过连接， *** 作过数据库
try {
connection.rollback();//回滚事务
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
connection.close(); // 关闭连接， 资源资源
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//一定要执行 remove *** 作，否则就会出错。 （因为 Tomcat服务器底层使用了线程池技术）
conns.remove();
}
/**
* 关闭连接， 放回数据库连接池
*
* @param conn
*/
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>转账title>
<script type="text/javascript" src="js/axios-v0.27.2.js">script>
head>
<body>
<h3>模拟银行转账h3>
<form action="/transfer.do" method="post">
<table width="450px" border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="100px">转账人td>
<td><input name="fromAcc">td>
tr>
<tr>
<td>转账金额td>
<td><input name="money">td>
tr>
<tr>
<td>收款人td>
<td><input name="toAcc">td>
tr>
<tr>
<td colspan="2"><input type="submit" value="提交">td>
tr>
table>
form>
body>
html>
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4fuol3rd-1651747441085)(C:\Users\hj\Desktop\2022KP java全栈-01\博客原件\转账事务控制\03.png)]
" src=“js/axios-v0.27.2.js”>
模拟银行转账
转账人
转账金额
收款人
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）