栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

Spring重点之事务管理

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Spring重点之事务管理

数据操作工具之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工程项目

添加依赖

    
    
        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("订单详情插入失败");
            }
        }
    }
}




预想的结果应该是:订单主表和订单详情表要么一起全部成功,要么一起全部失败。

解析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;


@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("订单详情插入失败");
            }
        }
    }
}

运行结果:


用JDBC解决事务问题 问题出现的原因
  • 添加主订单数据和添加订单详情表数据这两个操作没有在同一个事务内
  • 默认情况下,每个操作在执行完preparedStatement.executeUpdate();会自动提交事务
问题解决方案
  • 添加主订单数据和添加订单详情表数据这两个操作放在同一个事务中
  • 关闭自动提交,自己根据执行情况成功/异常来设置提交/回滚

创建一个新的下订单方法,这个方法提交事务,且添加主订单数据和添加订单详情表数据都在这一个方法中(都在同一个事务中)

OrderService

int placeOrder(Order order, List orderDetailList);
    public int placeOrder(Order order, List orderDetailList) {
        return orderDao.placeOrder(order, orderDetailList);
    }

OrderDao

    int placeOrder(Order order, List orderDetailList);
public int placeOrder(Order order, List 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;


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, List 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事务控制的思路一样试一试。

public int placeOrder(Order order, List 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;


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("下订单失败");
        }
    }
}
测试运行
  • 代码无异常,正常情况


  • 代码有异常,异常情况


转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/958941.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号