数据操作工具之JdbcTemplate
简单了解
JdbcTemplate是操作数据的工具,是Spring中基于JDBC封装的一个组件。
具体使用
需求说明:实现用户的添加和列表的查询
创建User数据表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
创建Maven工程项目
添加依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
</dependencies>
新建实体对象
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}
新建Dao层
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IUserDao {
/**
* 保存用户信息
*
* @param user
* @return
*/
Integer saveUser(User user);
/**
* 查询所有用户列表
*
* @return
*/
List<User> listUsers();
}
package com.example.dao.impl;
import com.example.dao.IUserDao;
import com.example.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class UserDaoImpl implements IUserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 保存用户信息
*
* @param user
* @return
*/
public Integer saveUser(User user) {
final String sql = "INSERT INTO user(id,name,age,email) VALUES(?,?,?,?)";
int count = jdbcTemplate.update(sql, user.getId(), user.getName(), user.getAge(), user.getEmail());
return count;
}
/**
* 查询所有用户列表
*
* @return
*/
public List<User> listUsers() {
final String sql = "SELECT * FROM `user`";
/**
* 将数据库中查询出来的数据和JavaBean对应
*/
List<User> userList = jdbcTemplate.query(sql, new RowMapper<User>() {
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getLong("id"));
user.setName(rs.getString("name"));
user.setAge(rs.getInt("age"));
user.setEmail(rs.getString("email"));
return user;
}
});
return userList;
}
}
新建service层
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IUserService {
/**
* 保存用户信息
*
* @param user
* @return
*/
Integer saveUser(User user);
/**
* 查询所有用户列表
*
* @return
*/
List<User> listUsers();
}
package com.example.service.impl;
import com.example.dao.IUserDao;
import com.example.entity.User;
import com.example.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
@Service
public class UserServiceImpl implements IUserService {
@Autowired
private IUserDao userDao;
public Integer saveUser(User user) {
return userDao.saveUser(user);
}
public List<User> listUsers() {
return userDao.listUsers();
}
}
新建Java配置类
package com.example;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.DependsOn;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/**
* @author Administrator
* @date 2022/06/04
*/
@Component
@ComponentScan
public class JavaConfig {
@Bean
public DriverManagerDataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/db2022");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
@Bean
@DependsOn("dataSource")// @DependsOn("dataSource")的作用是标记jdbcTemplate要创建在dataSource之后
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
测试
import com.example.JavaConfig;
import com.example.entity.User;
import com.example.service.IUserService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class TestDemo {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IUserService userService = applicationContext.getBean(IUserService.class);
// saveUserTest(userService);
queryTest(userService);
}
/**
* 查询用户列表测试
*
* @param userService
*/
private static void queryTest(IUserService userService) {
List<User> userList = userService.listUsers();
for (User user : userList) {
System.out.println(user);
}
}
/**
* 添加用户测试
*
* @param userService
*/
private static void saveUserTest(IUserService userService) {
User user = new User();
user.setId(12L);
user.setName("test");
user.setAge(21);
user.setEmail("test@qq.com");
int count = userService.saveUser(user);
if (count > 0) {
System.out.println("插入成功");
} else {
System.out.println("插入失败");
}
}
}
拓展知识
@DependsOn注解
没有使用事务的情况下的问题分析
事务的相关概念
- 事务仅与数据库有关
- 事务必须满足ACID原则
- 原子性(Atomicity)
- 一致性(Consistency)
- 隔离性(Isolation)
- 持久性(Durability)
应用场景下事务要解决的问题
了解应用场景
以下订单
业务场景为例:下订单一般都会有两个表,一个是订单主表,还有一个是订单详情表。
订单表(order)
订单表中的字段有:订单ID(order_id)、总金额(total_money)、总数量(total_count)、下单时间(order_create_time)等。
订单详情表(order_detail)
订单详情表中的字段有:订单详情ID(order_detail_id)、订单ID(order_id)、商品名称(goods_name)、商品价格(goods_price)、商品数量(goods_count)、商品总金额(goods_total_money)等。
订单主表中记录的信息是本次购买商品的总金额、下单时间等。而订单详情表当中记录的是购买的每一个商品的详细信息,一次下订单,订单主表当中只会有一条记录,而订单详情表中则是每个商品都会对应一条记录信息。当点击下订单后往订单主表和订单详情表当中添加记录的时候是一个不可分割的整体。两者必须全部成功或者全部失败,否则就会出现业务数据异常的情况。
实际演示
创建数据表
订单主表
CREATE TABLE `order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`total_money` decimal(10,2) DEFAULT NULL COMMENT '总金额',
`total_count` int(255) DEFAULT NULL COMMENT '总数量',
`order_create_time` datetime DEFAULT NULL COMMENT '下单时间',
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
订单详情表
CREATE TABLE `order_detail` (
`order_detail_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '订单详情ID',
`order_id` int(11) DEFAULT NULL COMMENT '订单ID',
`goods_name` varchar(50) DEFAULT NULL COMMENT '商品名称',
`goods_price` decimal(10,2) DEFAULT NULL COMMENT '商品价格',
`goods_count` int(11) DEFAULT NULL COMMENT '商品数量',
`goods_total_money` decimal(10,2) DEFAULT NULL COMMENT '商品总金额',
PRIMARY KEY (`order_detail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
创建Maven工程项目
添加依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
</dependencies>
新建实体对象
Order
/**
* @author Administrator
* @date 2022/06/04
*/
@Data
public class Order {
private Long orderId;
private BigDecimal totalMoney;
private Integer totalCount;
private Date orderCreateTime;
}
OrderDetail
/**
* @author Administrator
* @date 2022/06/04
*/
@Data
public class OrderDetail {
private Long orderDetailId;
private Long orderId;
private String goodsName;
private BigDecimal goodsPrice;
private Integer goodsCount;
private BigDecimal goodsTotalMoney;
}
新建Dao层
Order
package com.example.dao;
import com.example.entity.Order;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderDao {
/**
* 保存订单信息
*
* @param order
* @return
*/
int saveOrder(Order order);
/**
* 获取最新订单ID
*
* @return
*/
Long getLastOrderId();
}
package com.example.dao.impl;
import com.example.dao.IOrderDao;
import com.example.entity.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDaoImpl implements IOrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 保存订单信息
*
* @param order
* @return
*/
public int saveOrder(Order order) {
final String sql = "INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)";
int count = jdbcTemplate.update(sql, order.getTotalMoney(), order.getTotalCount(), order.getOrderCreateTime());
return count;
}
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() {
final String sql = "SELECT MAX(order_id) FROM `order`";
Long maxOrderId = jdbcTemplate.queryForObject(sql, Long.class);
return maxOrderId;
}
}
OrderDetail
package com.example.dao;
import com.example.entity.OrderDetail;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderDetailDao {
/**
* 保存订单详情
*
* @param orderDetail
* @return
*/
int saveOrderDetail(OrderDetail orderDetail);
}
package com.example.dao.impl;
import com.example.dao.IOrderDetailDao;
import com.example.entity.OrderDetail;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDetailImpl implements IOrderDetailDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public int saveOrderDetail(OrderDetail orderDetail) {
final String sql = "INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)";
int count = jdbcTemplate.update(sql, orderDetail.getOrderId(), orderDetail.getGoodsName(), orderDetail.getGoodsPrice(), orderDetail.getGoodsCount(), orderDetail.getGoodsTotalMoney());
return count;
}
}
新建service层
Order
package com.example.service;
import com.example.entity.Order;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderService {
/**
* 保存订单信息
*
* @param order
* @return
*/
int saveOrder(Order order);
/**
* 获取最新订单ID
*
* @return
*/
Long getLastOrderId();
}
package com.example.service.impl;
import com.example.dao.IOrderDao;
import com.example.entity.Order;
import com.example.service.IOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @author Administrator
* @date 2022/06/04
*/
@Service
public class OrderServiceImpl implements IOrderService {
@Autowired
private IOrderDao orderDao;
/**
* 保存订单信息
*
* @param order
* @return
*/
public int saveOrder(Order order) {
return orderDao.saveOrder(order);
}
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() {
return orderDao.getLastOrderId();
}
}
OrderDetail
package com.example.service;
import com.example.entity.OrderDetail;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderDetailService {
/**
* 保存订单详情
*
* @param orderDetail
* @return
*/
int saveOrderDetail(OrderDetail orderDetail);
}
package com.example.service.impl;
import com.example.dao.IOrderDetailDao;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* @author Administrator
* @date 2022/06/04
*/
@Service
public class OrderDetailServiceImpl implements IOrderDetailService {
@Autowired
private IOrderDetailDao orderDetailDao;
public int saveOrderDetail(OrderDetail orderDetail) {
return orderDetailDao.saveOrderDetail(orderDetail);
}
}
新建Java配置类
package com.example;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.DependsOn;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/**
* @author Administrator
* @date 2022/06/04
*/
@Component
@ComponentScan
public class JavaConfig {
@Bean
public DriverManagerDataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/db2022");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
@Bean
@DependsOn("dataSource")// @DependsOn("dataSource")的作用是标记jdbcTemplate要创建在dataSource之后
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
测试
代码无异常,正常情况
import com.example.JavaConfig;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class Test {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IOrderService orderService = applicationContext.getBean(IOrderService.class);
IOrderDetailService orderDetailService = applicationContext.getBean(IOrderDetailService.class);
// 测试下订单
createOrder(orderService, orderDetailService);
}
private static void createOrder(IOrderService orderService, IOrderDetailService orderDetailService) {
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.saveOrder(order);// 保存主订单信息
if (orderCount > 0) {
System.out.println("订单插入成功");
} else {
System.out.println("订单插入失败");
}
// 保存订单详情信息
Long lastOrderId = orderService.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
int orderDetailCount = orderDetailService.saveOrderDetail(orderDetail);
if (orderDetailCount > 0) {
System.out.println("订单详情插入成功");
} else {
System.out.println("订单详情插入失败");
}
}
}
}
代码有异常,异常情况
import com.example.JavaConfig;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class Test {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IOrderService orderService = applicationContext.getBean(IOrderService.class);
IOrderDetailService orderDetailService = applicationContext.getBean(IOrderDetailService.class);
// 测试下订单
createOrder(orderService, orderDetailService);
}
private static void createOrder(IOrderService orderService, IOrderDetailService orderDetailService) {
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.saveOrder(order);// 保存主订单信息
if (orderCount > 0) {
System.out.println("订单插入成功");
} else {
System.out.println("订单插入失败");
}
System.out.println(10/0);// TODO 此处代码执行异常
// 保存订单详情信息
Long lastOrderId = orderService.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
int orderDetailCount = orderDetailService.saveOrderDetail(orderDetail);
if (orderDetailCount > 0) {
System.out.println("订单详情插入成功");
} else {
System.out.println("订单详情插入失败");
}
}
}
}
预想的结果应该是:订单主表和订单详情表要么一起全部成功,要么一起全部失败。
解析Jdbc操作事务的本质
此案例演示基于上节【没有使用事务的情况下的问题分析】中的案例。
JdbcTemple操作数据库更换成JDBC
Order
package com.example.dao.impl;
import com.example.dao.IOrderDao;
import com.example.entity.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDaoImpl implements IOrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 保存订单信息
*
* @param order
* @return
*/
public int saveOrder(Order order) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)");
preparedStatement.setBigDecimal(1, order.getTotalMoney());
preparedStatement.setInt(2, order.getTotalCount());
preparedStatement.setDate(3, new Date(order.getOrderCreateTime().getTime()));
return preparedStatement.executeUpdate();// 执行完成会自动提交事务
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() {
final String sql = "SELECT MAX(order_id) FROM `order`";
Long maxOrderId = jdbcTemplate.queryForObject(sql, Long.class);
return maxOrderId;
}
}
OrderDetail
package com.example.dao.impl;
import com.example.dao.IOrderDetailDao;
import com.example.entity.OrderDetail;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDetailImpl implements IOrderDetailDao {
public int saveOrderDetail(OrderDetail orderDetail) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)");
preparedStatement.setLong(1, orderDetail.getOrderId());
preparedStatement.setString(2, orderDetail.getGoodsName());
preparedStatement.setBigDecimal(3, orderDetail.getGoodsPrice());
preparedStatement.setInt(4, orderDetail.getGoodsCount());
preparedStatement.setBigDecimal(5, orderDetail.getGoodsTotalMoney());
return preparedStatement.executeUpdate();// 执行完成会自动提交事务
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
}
测试
代码无异常,正常情况
测试类
import com.example.JavaConfig;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class Test {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IOrderService orderService = applicationContext.getBean(IOrderService.class);
IOrderDetailService orderDetailService = applicationContext.getBean(IOrderDetailService.class);
// 测试下订单
createOrder(orderService, orderDetailService);
}
private static void createOrder(IOrderService orderService, IOrderDetailService orderDetailService) {
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.saveOrder(order);// 保存主订单信息
if (orderCount > 0) {
System.out.println("订单插入成功");
} else {
System.out.println("订单插入失败");
}
// 保存订单详情信息
Long lastOrderId = orderService.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
int orderDetailCount = orderDetailService.saveOrderDetail(orderDetail);
if (orderDetailCount > 0) {
System.out.println("订单详情插入成功");
} else {
System.out.println("订单详情插入失败");
}
}
}
}
运行结果:
代码有异常,异常情况
测试类
import com.example.JavaConfig;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class Test {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IOrderService orderService = applicationContext.getBean(IOrderService.class);
IOrderDetailService orderDetailService = applicationContext.getBean(IOrderDetailService.class);
// 测试下订单
createOrder(orderService, orderDetailService);
}
private static void createOrder(IOrderService orderService, IOrderDetailService orderDetailService) {
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.saveOrder(order);// 保存主订单信息
if (orderCount > 0) {
System.out.println("订单插入成功");
} else {
System.out.println("订单插入失败");
}
System.out.println(10/0);// TODO 此处代码执行异常
// 保存订单详情信息
Long lastOrderId = orderService.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
int orderDetailCount = orderDetailService.saveOrderDetail(orderDetail);
if (orderDetailCount > 0) {
System.out.println("订单详情插入成功");
} else {
System.out.println("订单详情插入失败");
}
}
}
}
运行结果:
用JDBC解决事务问题
问题出现的原因
- 添加主订单数据和添加订单详情表数据这两个操作没有在同一个事务内
- 默认情况下,每个操作在执行完preparedStatement.executeUpdate();会自动提交事务
问题解决方案
- 添加主订单数据和添加订单详情表数据这两个操作放在同一个事务中
- 关闭自动提交,自己根据执行情况成功/异常来设置提交/回滚
创建一个新的下订单方法,这个方法提交事务,且添加主订单数据和添加订单详情表数据都在这一个方法中(都在同一个事务中)
OrderService
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
int placeOrder(Order order, List<OrderDetail> orderDetailList);
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
public int placeOrder(Order order, List<OrderDetail> orderDetailList) {
return orderDao.placeOrder(order, orderDetailList);
}
OrderDao
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
int placeOrder(Order order, List<OrderDetail> orderDetailList);
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
public int placeOrder(Order order, List<OrderDetail> orderDetailList) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
connection.setAutoCommit(false);// 默认为true,自动提交,设置成false,取消自动提交,换成手动提交
// 创建主订单
preparedStatement = connection.prepareStatement("INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)");
preparedStatement.setBigDecimal(1, order.getTotalMoney());
preparedStatement.setInt(2, order.getTotalCount());
preparedStatement.setDate(3, new Date(order.getOrderCreateTime().getTime()));
preparedStatement.executeUpdate();// 执行完成不会自动提交事务
Long lastOrderId = 0L;// 最新订单ID
ResultSet resultSet = preparedStatement.executeQuery("SELECT LAST_INSERT_ID()");
while (resultSet.next()) {
lastOrderId = resultSet.getLong(1);
}
// 创建订单详情信息
for (OrderDetail orderDetail : orderDetailList) {
preparedStatement = connection.prepareStatement("INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)");
preparedStatement.setLong(1, lastOrderId);
preparedStatement.setString(2, orderDetail.getGoodsName());
preparedStatement.setBigDecimal(3, orderDetail.getGoodsPrice());
preparedStatement.setInt(4, orderDetail.getGoodsCount());
preparedStatement.setBigDecimal(5, orderDetail.getGoodsTotalMoney());
preparedStatement.executeUpdate();// 执行完成不会自动提交事务
}
connection.commit();// 执行成功,提交事务
return 1;
} catch (Exception e) {
try {
connection.rollback();// 执行异常,回滚事务
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);// 自动提交设置成true
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return 0;
}
测试
测试类
import com.example.JavaConfig;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderDetailService;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class JdbcTest {
public static void main(String[] args) {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
IOrderService orderService = applicationContext.getBean(IOrderService.class);
IOrderDetailService orderDetailService = applicationContext.getBean(IOrderDetailService.class);
// 测试下订单
createOrder(orderService, orderDetailService);
}
private static void createOrder(IOrderService orderService, IOrderDetailService orderDetailService) {
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.placeOrder(order, orderDetailList);
}
}
测试结果
-
代码无异常,正常情况
-
代码有异常,异常情况
手动添加一行异常代码
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
public int placeOrder(Order order, List<OrderDetail> orderDetailList) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
connection.setAutoCommit(false);// 默认为true,自动提交,设置成false,取消自动提交,换成手动提交
// 创建主订单
preparedStatement = connection.prepareStatement("INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)");
preparedStatement.setBigDecimal(1, order.getTotalMoney());
preparedStatement.setInt(2, order.getTotalCount());
preparedStatement.setDate(3, new Date(order.getOrderCreateTime().getTime()));
preparedStatement.executeUpdate();// 执行完成不会自动提交事务
Long lastOrderId = 0L;// 最新订单ID
ResultSet resultSet = preparedStatement.executeQuery("SELECT LAST_INSERT_ID()");
while (resultSet.next()) {
lastOrderId = resultSet.getLong(1);
}
System.out.println(10/0);// TODO 此行代码异常
// 创建订单详情信息
for (OrderDetail orderDetail : orderDetailList) {
preparedStatement = connection.prepareStatement("INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)");
preparedStatement.setLong(1, lastOrderId);
preparedStatement.setString(2, orderDetail.getGoodsName());
preparedStatement.setBigDecimal(3, orderDetail.getGoodsPrice());
preparedStatement.setInt(4, orderDetail.getGoodsCount());
preparedStatement.setBigDecimal(5, orderDetail.getGoodsTotalMoney());
preparedStatement.executeUpdate();// 执行完成不会自动提交事务
}
connection.commit();// 执行成功,提交事务
return 1;
} catch (Exception e) {
try {
connection.rollback();// 执行异常,回滚事务
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);// 自动提交设置成true
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return 0;
}
这是正常期待的结果,一组操作要么全部执行成功,要么全部执行失败。
使用JDBCTemple如何关闭事务自动提交,换成手动提交
采取跟原生JDBC事务控制的思路一样试一试。
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
public int placeOrder(Order order, List<OrderDetail> orderDetailList) {
Connection connection = null;
try {
connection = jdbcTemplate.getDataSource().getConnection();
connection.setAutoCommit(false);// 关闭事务自动提交
// 添加主订单信息
String sql = "INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)";
jdbcTemplate.update(sql, order.getTotalMoney(), order.getTotalCount(), order.getOrderCreateTime());
System.out.println(10 / 0);// TODO 此行代码异常
// 最新订单ID
Long lastOrderId = getLastOrderId();
// 保存订单详情信息
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
sql = "INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)";
jdbcTemplate.update(sql, orderDetail.getOrderId(), orderDetail.getGoodsName(), orderDetail.getGoodsPrice(), orderDetail.getGoodsCount(), orderDetail.getGoodsTotalMoney());
}
connection.commit();// 事务提交
return 1;
} catch (Exception throwables) {
try {
connection.rollback();// 归滚事务
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);// 还原成自动提交
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return 0;
}
期望结果:在添加主订单数据后有一行异常代码,正常结果是订单主表和订单详情表中的数据都不应该插入。
实际结果:
超出了自己期待的样子,不应该不一样啊,怎么在JdbcTemple中就不一样了?疑惑。首先检查了自己控制事务的代码是否有错,发现和原生JDBC控制事务的方式代码都一样啊,既然运行结果没有拿到控制事务的效果,就只能说我们获取的这个Connection连接对象设置的取消自动提交没有生效。是连接对象不同,还是底层又设置了true呢?先把程序中获取的连接对象给打印出来,然后再断点调试。
内部执行方法中,自己又获取了一个Collection连接对象,这和我们程序中手动获取的连接对象果然不一样,所以导致了事务控制的效果不生效。
总结:在JdbcTemplate中这种事务的控制方式是不可行的,如果使用了JdbcTemplate且需要手动控制事务,需要更换成其它的事务解决方案。
解决方案:
- TransactionTemplate编程式事务
疑问补充
一个事务中执行了添加操作A,执行了添加操作B,执行完添加操作A之后,执行到了添加操作B,事务没有提交,在执行添加操作B之前,能不能够查询到添加操作A添加的数据?
答案:是可以的。
注意细节
- JDBC的查询SQL语句如果有关键字会执行报错,尽量避免使用关键字或者用``把关键字给包裹起来。
基于JDK代理模式的方式实现事务管理
要解决的问题
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
connection.setAutoCommit(false);// 默认为true,自动提交,设置成false,取消自动提交,换成手动提交
connection.commit();// 执行成功,提交事务
return 1;
} catch (Exception e) {
try {
connection.rollback();// 执行异常,回滚事务
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
try {
connection.setAutoCommit(true);// 自动提交设置成true
} catch (SQLException throwables) {
throwables.printStackTrace();
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return 0;
}
这两段代码一段是获取连接并关闭自动提交,后面一段代码是根据代码执行情况提交/归滚且关闭连接。想把这两段代码给提取出来,不放到业务代码中。
具体实现
基于前面的案例进行更改。
创建一个DBUtils工具类
package com.example.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author Administrator
* @date 2022/06/05
*/
public class DBUtils {
public static Connection connection;
/**
* 获取连接
*
* @return
*/
public static Connection getConnection() {
try {
if (connection == null) {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db2022", "root", "root");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
/**
* 关闭连接
*/
public static void closeConnection() {
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
更改Dao层操作数据库的代码
Order
package com.example.dao.impl;
import com.example.dao.IOrderDao;
import com.example.entity.Order;
import com.example.utils.DBUtils;
import org.springframework.stereotype.Repository;
import java.sql.*;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDaoImpl implements IOrderDao {
/**
* 保存订单信息
*
* @param order
* @return
*/
public int saveOrder(Order order) {
try {
Connection connection = DBUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)");
preparedStatement.setBigDecimal(1, order.getTotalMoney());
preparedStatement.setInt(2, order.getTotalCount());
preparedStatement.setDate(3, new Date(order.getOrderCreateTime().getTime()));
return preparedStatement.executeUpdate();// 执行完成会自动提交事务
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() throws SQLException {
final String sql = "SELECT MAX(order_id) FROM `order`";
Long maxOrderId = 0L;
Connection connection = DBUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
maxOrderId = resultSet.getLong(1);
}
return maxOrderId;
}
}
OrderDetail
package com.example.dao.impl;
import com.example.dao.IOrderDetailDao;
import com.example.entity.OrderDetail;
import com.example.utils.DBUtils;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.PreparedStatement;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDetailImpl implements IOrderDetailDao {
public int saveOrderDetail(OrderDetail orderDetail) {
try {
Connection connection = DBUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)");
preparedStatement.setLong(1, orderDetail.getOrderId());
preparedStatement.setString(2, orderDetail.getGoodsName());
preparedStatement.setBigDecimal(3, orderDetail.getGoodsPrice());
preparedStatement.setInt(4, orderDetail.getGoodsCount());
preparedStatement.setBigDecimal(5, orderDetail.getGoodsTotalMoney());
return preparedStatement.executeUpdate();// 执行完成会自动提交事务
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
}
更改OrderService代码
package com.example.service.impl;
import com.example.dao.IOrderDao;
import com.example.dao.IOrderDetailDao;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.SQLException;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
@Service
public class OrderServiceImpl implements IOrderService {
@Autowired
private IOrderDao orderDao;
@Autowired
private IOrderDetailDao orderDetailDao;
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
public int placeOrder(Order order, List<OrderDetail> orderDetailList) throws SQLException {
// 保存订单
orderDao.saveOrder(order);
// System.out.println(10 / 0);// TODO 异常代码
// 获取最新订单ID
Long lastOrderId = orderDao.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
orderDetailDao.saveOrderDetail(orderDetail);
}
return 1;
}
}
创建事务管理调用处理器
package com.example.proxy;
import com.example.utils.DBUtils;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author Administrator
* @date 2022/06/05
*/
public class TransactionalManagerHandler<T> implements InvocationHandler {
private T target;
public TransactionalManagerHandler(T target) {
this.target = target;
}
public Object invoke(Object proxy, Method method, Object[] args) {
Object result = null;
Connection connection = DBUtils.getConnection();
try {
connection.setAutoCommit(false);
result = method.invoke(target, args);
System.out.println("提交成功...");
connection.commit();
} catch (Exception ex) {
System.out.println("提交失败,数据归滚...");
try {
connection.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
ex.getMessage();
} finally {
DBUtils.closeConnection();
}
return result;
}
}
创建测试类
package com.example;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.proxy.TransactionalManagerHandler;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.lang.reflect.Proxy;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class JdbcTest {
public static void main(String[] args) throws SQLException {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
// 创建目标对象
IOrderService orderService = applicationContext.getBean(IOrderService.class);
// 创建代理对象
orderService = (IOrderService) Proxy.newProxyInstance(JdbcTest.class.getClassLoader(), orderService.getClass().getInterfaces(), new TransactionalManagerHandler<IOrderService>(orderService));
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.placeOrder(order, orderDetailList);
if (orderCount > 0) {
System.out.println("下订单成功");
} else {
System.out.println("下订单失败");
}
}
}
测试运行
-
代码无异常,正常情况
-
代码有异常,异常情况
基于Aspect J的方式自定义事务实现
基于Aspect J的方式自定义事务实现比基于JDK代理模式的方式实现事务管理要简单一些。
备注:
- 代码演示基于基于JDK代理模式的方式实现事务管理的项目。
- Aspject通知类型采用注解的方式
引入Aspject依赖
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.6</version>
</dependency>
开启Aspject
package com.example;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
/**
* @author Administrator
* @date 2022/06/04
*/
@Component
@ComponentScan
@EnableAspectJAutoProxy
public class JavaConfig {
@Bean
public DriverManagerDataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/db2022");
dataSource.setUsername("root");
dataSource.setPassword("root");
return dataSource;
}
@Bean
@DependsOn("dataSource")// @DependsOn("dataSource")的作用是标记jdbcTemplate要创建在dataSource之后
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
新建事务注解
package com.example.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author Administrator
* @date 2022/06/12
*/
@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface AspjectTx {
}
创建事务切面类
package com.example.aspject;
import com.example.utils.DBUtils;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Component;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author Administrator
* @date 2022/06/12
*/
@Aspect
@Component
public class AspjectTx {
/**
* 前置通知,获取连接,关闭事务的自动提交
*/
@Before("@annotation(com.example.annotation.AspjectTx)")
public void before() throws SQLException {
Connection connection = DBUtils.getConnection();
connection.setAutoCommit(false);
}
/**
* 后置通知,提交事务
*/
@AfterReturning("@annotation(com.example.annotation.AspjectTx)")
public void afterReturning() throws SQLException {
Connection connection = DBUtils.getConnection();
connection.commit();
}
/**
* 异常通知,事务回滚
*/
@AfterThrowing("@annotation(com.example.annotation.AspjectTx)")
public void afterThrowing() throws SQLException {
Connection connection = DBUtils.getConnection();
connection.rollback();
}
/**
* 最终通知,关闭连接
*/
@AfterThrowing("@annotation(com.example.annotation.AspjectTx)")
public void after() throws SQLException {
Connection connection = DBUtils.getConnection();
connection.close();
}
}
在需要控制事务的方法上添加事务注解
/**
* 下订单
*
* @param order
* @param orderDetailList
* @return
*/
@AspjectTx
public int placeOrder(Order order, List<OrderDetail> orderDetailList) throws SQLException {
// 保存订单
orderDao.saveOrder(order);
System.out.println(10 / 0);// TODO 异常代码
// 获取最新订单ID
Long lastOrderId = orderDao.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
orderDetailDao.saveOrderDetail(orderDetail);
}
return 1;
}
创建测试类
package com.example;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public class JdbcTest {
public static void main(String[] args) throws SQLException {
ApplicationContext applicationContext = new AnnotationConfigApplicationContext(JavaConfig.class);
// 创建目标对象
IOrderService orderService = applicationContext.getBean(IOrderService.class);
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
int orderCount = orderService.placeOrder(order, orderDetailList);
if (orderCount > 0) {
System.out.println("下订单成功");
} else {
System.out.println("下订单失败");
}
}
}
测试
代码无异常,正常情况
代码有异常,异常情况
Spring中事务的实现
准备工作
构建一个新的项目
引入相关依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>spring_jdbc_tx04_xml</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-orm -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>5.3.18</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.6</version>
</dependency>
</dependencies>
</project>
构建数据库连接配置信息文件
jdbc_url=jdbc:mysql://127.0.0.1:3306/db2022
jdbc_driverClassName=com.mysql.jdbc.Driver
jdbc_username=root
jdbc_password=root
构建applicationContext.xml文件
这里采用xml文件的方式来管理/配置IOC容器。
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">
<!--扫描指定包,并将包下的组件初始化到IOC容器中-->
<context:component-scan base-package="com.example.*"/>
<!-- 加载指定的数据源配置文件 -->
<context:property-placeholder location="db.properties"/>
<!-- 将数据源注入到IOC容器当中 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc_driverClassName}"/>
<property name="url" value="${jdbc_url}"/>
<property name="username" value="${jdbc_username}"/>
<property name="password" value="${jdbc_password}"/>
</bean>
<!-- 将JdbcTemplate注入到IOC容器中 -->
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 指定数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
构建业务代码
Entity层
package com.example.entity;
import lombok.Data;
import java.math.BigDecimal;
import java.util.Date;
/**
* @author Administrator
* @date 2022/06/04
*/
@Data
public class Order {
private Long orderId;
private BigDecimal totalMoney;
private Integer totalCount;
private Date orderCreateTime;
}
package com.example.entity;
import lombok.Data;
import java.math.BigDecimal;
/**
* @author Administrator
* @date 2022/06/04
*/
@Data
public class OrderDetail {
private Long orderDetailId;
private Long orderId;
private String goodsName;
private BigDecimal goodsPrice;
private Integer goodsCount;
private BigDecimal goodsTotalMoney;
}
Dao层
package com.example.dao;
import com.example.entity.Order;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderDao {
/**
* 保存订单信息
*
* @param order
* @return
*/
int saveOrder(Order order);
/**
* 获取最新订单ID
*
* @return
*/
Long getLastOrderId();
}
package com.example.dao.impl;
import com.example.dao.IOrderDao;
import com.example.entity.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDaoImpl implements IOrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 保存订单信息
*
* @param order
* @return
*/
public int saveOrder(Order order) {
final String sql = "INSERT INTO `order` (total_money,total_count,order_create_time) VALUES(?,?,?)";
int count = jdbcTemplate.update(sql, order.getTotalMoney(), order.getTotalCount(), order.getOrderCreateTime());
return count;
}
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() {
final String sql = "SELECT MAX(order_id) FROM `order`";
Long maxOrderId = jdbcTemplate.queryForObject(sql, Long.class);
return maxOrderId;
}
}
package com.example.dao;
import com.example.entity.OrderDetail;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderDetailDao {
/**
* 保存订单详情
*
* @param orderDetail
* @return
*/
int saveOrderDetail(OrderDetail orderDetail);
}
package com.example.dao.impl;
import com.example.dao.IOrderDetailDao;
import com.example.entity.OrderDetail;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
/**
* @author Administrator
* @date 2022/06/04
*/
@Repository
public class OrderDetailImpl implements IOrderDetailDao {
@Autowired
private JdbcTemplate jdbcTemplate;
public int saveOrderDetail(OrderDetail orderDetail) {
final String sql = "INSERT INTO order_detail (order_id,goods_name,goods_price,goods_count,goods_total_money) VALUES(?,?,?,?,?)";
int count = jdbcTemplate.update(sql, orderDetail.getOrderId(), orderDetail.getGoodsName(), orderDetail.getGoodsPrice(), orderDetail.getGoodsCount(), orderDetail.getGoodsTotalMoney());
return count;
}
}
Service层
package com.example.service;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
public interface IOrderService {
/**
* 获取最新订单ID
*
* @return
*/
Long getLastOrderId();
void placeOrder(Order order, List<OrderDetail> orderDetailList);
}
package com.example.service.impl;
import com.example.dao.IOrderDao;
import com.example.dao.IOrderDetailDao;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/04
*/
@Service
public class OrderServiceImpl implements IOrderService {
@Autowired
private IOrderDao orderDao;
@Autowired
private IOrderDetailDao orderDetailDao;
/**
* 获取最新订单ID
*
* @return
*/
public Long getLastOrderId() {
return orderDao.getLastOrderId();
}
public void placeOrder(Order order, List<OrderDetail> orderDetailList) {
// 保存订单
orderDao.saveOrder(order);
// System.out.println(10 / 0);// TODO 异常代码
// 获取最新订单ID
Long lastOrderId = orderDao.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
orderDetailDao.saveOrderDetail(orderDetail);
}
}
}
构建测试类并测试
package com.example;
import com.example.entity.Order;
import com.example.entity.OrderDetail;
import com.example.service.IOrderService;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author Administrator
* @date 2022/06/12
*/
public class JavaMain {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
IOrderService bean = context.getBean(IOrderService.class);
// 构建订单详情信息
List<OrderDetail> orderDetailList = new ArrayList<OrderDetail>();
OrderDetail orderDetail1 = new OrderDetail();
orderDetail1.setGoodsName("八宝粥");
orderDetail1.setGoodsCount(2);
orderDetail1.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail1.setGoodsTotalMoney(orderDetail1.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail1.getGoodsCount())));
orderDetailList.add(orderDetail1);
OrderDetail orderDetail2 = new OrderDetail();
orderDetail2.setGoodsName("方便面");
orderDetail2.setGoodsCount(1);
orderDetail2.setGoodsPrice(BigDecimal.valueOf(4.5));
orderDetail2.setGoodsTotalMoney(orderDetail2.getGoodsPrice().multiply(BigDecimal.valueOf(orderDetail2.getGoodsCount())));
orderDetailList.add(orderDetail2);
// 构建订单信息
Order order = new Order();
int totalCount = 0;
BigDecimal totalMoney = BigDecimal.ZERO;
for (OrderDetail orderDetail : orderDetailList) {
totalCount += orderDetail.getGoodsCount();
totalMoney = totalMoney.add(orderDetail.getGoodsTotalMoney());
}
order.setTotalCount(totalCount);
order.setTotalMoney(totalMoney);
order.setOrderCreateTime(new Date());
bean.placeOrder(order, orderDetailList);
System.out.println("下单成功");
}
}
运行结果:
注意事项
注意:事务实现的原理底层是AOP,AOP的实现产品使用的是Aspject,所以需要引入Aspject的依赖。
Spring只是集成了Aspject,Aspject并不是Spring研发的。
配置文件和注解两种实现方式
配置文件实现
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx https://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--扫描指定包,并将包下的组件初始化到IOC容器中-->
<context:component-scan base-package="com.example.*"/>
<!-- 加载指定的数据源配置文件 -->
<context:property-placeholder location="db.properties"/>
<!-- 将数据源注入到IOC容器当中 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc_driverClassName}"/>
<property name="url" value="${jdbc_url}"/>
<property name="username" value="${jdbc_username}"/>
<property name="password" value="${jdbc_password}"/>
</bean>
<!-- 将JdbcTemplate注入到IOC容器中 -->
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 指定数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 注入事务管理器到IOC容器当中 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 配置事务管理方法的信息 -->
<tx:advice id="txAdvice" transaction-manager="txManager">
<tx:attributes>
<tx:method name="placeOrder*" propagation="REQUIRED"/>
<tx:method name="*"></tx:method>
</tx:attributes>
</tx:advice>
<!-- 利用AOP指定哪些方法会有事务生效 -->
<aop:config>
<aop:pointcut id="tx" expression="execution(* com.example.service..*.*(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="tx"></aop:advisor>
</aop:config>
</beans>
注解方式实现
在配置applicationContext.xml进行配置。
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx https://www.springframework.org/schema/tx/spring-tx.xsd">
<!--扫描指定包,并将包下的组件初始化到IOC容器中-->
<context:component-scan base-package="com.example.*"/>
<!-- 加载指定的数据源配置文件 -->
<context:property-placeholder location="db.properties"/>
<!-- 将数据源注入到IOC容器当中 -->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${jdbc_driverClassName}"/>
<property name="url" value="${jdbc_url}"/>
<property name="username" value="${jdbc_username}"/>
<property name="password" value="${jdbc_password}"/>
</bean>
<!-- 将JdbcTemplate注入到IOC容器中 -->
<bean class="org.springframework.jdbc.core.JdbcTemplate">
<!-- 指定数据源 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 注入事务管理器到IOC容器当中 -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 开启事务的注解支持 -->
<tx:annotation-driven transaction-manager="txManager"/>
</beans>
在需要事务的业务方法上添加@Transactional
注解
/**
* @Transactional 表示开启事务支持
* @param order
* @param orderDetailList
*/
@Transactional
public void placeOrder(Order order, List<OrderDetail> orderDetailList) {
// 保存订单
orderDao.saveOrder(order);
// System.out.println(10 / 0);// TODO 异常代码
// 获取最新订单ID
Long lastOrderId = orderDao.getLastOrderId();
for (OrderDetail orderDetail : orderDetailList) {
orderDetail.setOrderId(lastOrderId);
orderDetailDao.saveOrderDetail(orderDetail);
}
}