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工程项目 添加依赖
新建实体对象org.springframework spring-context 5.3.18 org.springframework spring-orm 5.3.18 mysql mysql-connector-java 5.1.17 org.projectlombok lombok 1.18.20 provided
@Data
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}
新建Dao层
public interface IUserDao {
Integer saveUser(User user);
List 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;
@Repository
public class UserDaoImpl implements IUserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
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;
}
public List listUsers() {
final String sql = "SELECT * FROM `user`";
List userList = jdbcTemplate.query(sql, new RowMapper() {
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层
public interface IUserService {
Integer saveUser(User user);
List 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;
@Service
public class UserServiceImpl implements IUserService {
@Autowired
private IUserDao userDao;
public Integer saveUser(User user) {
return userDao.saveUser(user);
}
public List 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;
@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;
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);
}
private static void queryTest(IUserService userService) {
List userList = userService.listUsers();
for (User user : userList) {
System.out.println(user);
}
}
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工程项目 添加依赖
新建实体对象org.springframework spring-context 5.3.18 org.springframework spring-orm 5.3.18 mysql mysql-connector-java 5.1.17 org.projectlombok lombok 1.18.20 provided
Order
@Data
public class Order {
private Long orderId;
private BigDecimal totalMoney;
private Integer totalCount;
private Date orderCreateTime;
}
OrderDetail
@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;
public interface IOrderDao {
int saveOrder(Order order);
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;
@Repository
public class OrderDaoImpl implements IOrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
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;
}
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;
public interface IOrderDetailDao {
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;
@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;
public interface IOrderService {
int saveOrder(Order order);
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;
@Service
public class OrderServiceImpl implements IOrderService {
@Autowired
private IOrderDao orderDao;
public int saveOrder(Order order) {
return orderDao.saveOrder(order);
}
public Long getLastOrderId() {
return orderDao.getLastOrderId();
}
}
OrderDetail
package com.example.service;
import com.example.entity.OrderDetail;
public interface IOrderDetailService {
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;
@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;
@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;
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 orderDetailList = new ArrayList();
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;
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 orderDetailList = new ArrayList();
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("订单详情插入失败");
}
}
}
}
预想的结果应该是:订单主表和订单详情表要么一起全部成功,要么一起全部失败。
此案例演示基于上节【没有使用事务的情况下的问题分析】中的案例。
JdbcTemple操作数据库更换成JDBCOrder
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;
@Repository
public class OrderDaoImpl implements IOrderDao {
@Autowired
private JdbcTemplate jdbcTemplate;
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;
}
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;
@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;
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 orderDetailList = new ArrayList();
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;
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 orderDetailList = new ArrayList();
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("订单详情插入失败");
}
}
}
}
运行结果:
- 添加主订单数据和添加订单详情表数据这两个操作没有在同一个事务内
- 默认情况下,每个操作在执行完preparedStatement.executeUpdate();会自动提交事务
- 添加主订单数据和添加订单详情表数据这两个操作放在同一个事务中
- 关闭自动提交,自己根据执行情况成功/异常来设置提交/回滚
创建一个新的下订单方法,这个方法提交事务,且添加主订单数据和添加订单详情表数据都在这一个方法中(都在同一个事务中)
OrderService
int placeOrder(Order order, ListorderDetailList);
public int placeOrder(Order order, List orderDetailList) {
return orderDao.placeOrder(order, orderDetailList);
}
OrderDao
int placeOrder(Order order, List orderDetailList);
public int placeOrder(Order order, ListorderDetailList) { 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;
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 orderDetailList = new ArrayList();
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);
}
}
测试结果
-
代码无异常,正常情况
-
代码有异常,异常情况
手动添加一行异常代码
public int placeOrder(Order order, ListorderDetailList) { 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; }
这是正常期待的结果,一组操作要么全部执行成功,要么全部执行失败。
采取跟原生JDBC事务控制的思路一样试一试。
public int placeOrder(Order order, ListorderDetailList) { 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语句如果有关键字会执行报错,尽量避免使用关键字或者用``把关键字给包裹起来。
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;
public class DBUtils {
public static Connection connection;
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.*;
@Repository
public class OrderDaoImpl implements IOrderDao {
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;
}
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;
@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;
@Service
public class OrderServiceImpl implements IOrderService {
@Autowired
private IOrderDao orderDao;
@Autowired
private IOrderDetailDao orderDetailDao;
public int placeOrder(Order order, List 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; public class TransactionalManagerHandler创建测试类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;
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(orderService));
// 构建订单详情信息
List orderDetailList = new ArrayList();
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("下订单失败");
}
}
}
测试运行
-
代码无异常,正常情况
-
代码有异常,异常情况



