SpringBoot+thymeleaf+Mybatis+MyS

经过之前的学习积累，今天将各部分知识结合了下，做了个Demo，参考《深入浅出SpringBoot2.x》的抢购商品Demo做了个升级，书中是使用jsp来做视图，我将它改变为使用thymeleaf模板引擎，因为这样将前后端分离，也可以减少一些配置，方便了编写与查看；书中在配置Mapper是使用的xml，我将其修改为注解，我感觉这样更直观，xml写法的信噪比太低了（ps:学通信的嘛：)），好多无效信息，看的比较烦。下面是整个Demo的介绍：

1、数据库表设计

分为两张表，一张为产品表（t_product），一张为购买信息表（t_purchase_record）:

产品表（t_product） t_purchase_record

[图片上传失败...(image-bf4dee-1558966758263)]

CREATE TABLE `t_product` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(60) NOT NULL,
  `stock` int(10) NOT NULL,
  `price` decimal(16,2) NOT NULL,
  `version` int(10) NOT NULL DEFAULT '0',
  `note` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
    
CREATE TABLE `t_purchase_record` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `user_id` int(12) NOT NULL,
  `product_id` int(12) NOT NULL,
  `price` decimal(16,2) NOT NULL,
  `quantity` int(12) NOT NULL,
  `sum` decimal(16,2) NOT NULL,
  `purchase_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `note` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

2、创建POJO，实体类对象

@Alias("product")
public class ProductPo implements Serializable{
    private Long id;
    private String productName;
    private int stock;
    private double price;
    private int version;
    private String note;

    /*Getter and Setter*/
}

@Alias("purchaseRecord")
public class PurchaseRecordPo implements Serializable {

    private Long id;
    private Long userId;
    private Long productId;
    private double price;
    private int quantity;
    private double sum;
    private Timestamp purchaseTime;
    private String note;

    /*Getter and Setter*/
}

3、持久层，Mybatis接口定义，使用注解方法生成Mapper映射

@Mapper
public interface ProductMapper {

    @Select("SELECT id, product_name as productName, stock, price, version, note FROM t_product where id=#{id}")
    ProductPo getProduct(Long id);

    @Update("UPDATE t_product SET stock = stock - #{quantity} WHERE id = #{id} ")
    int decreaseProduct(@Param("id") Long id, @Param("quantity") int quantity);
}

这里之前@Param没写，导致请求/test时无法获取到quantity的值，调试了好久，最后锁定在初始化购买记录时quantity的值无法传给SQL语句，便去查看Mapper文件中的SQL有没有写正确，一看也没写错啊，后来看到书上他使用了@Param来标注参数，一查才发现，当需要传递多个参数给MyBatis时，需要用@Param来标注。

@Mapper
public interface PurchaseRecordMapper {

    @Insert("insert into t_purchase_record(\n" +
            "        user_id,product_id,price,quantity,sum,purchase_date,note)\n" +
            "        values(#{userId},#{productId},#{price},#{quantity},#{sum},now(),#{note})")
    int insertPurchaseRecord(PurchaseRecordPo pr);
}

4、使用Spring开发业务层

public interface PurchaseService {
    /*
    * 处理购买业务
    * @param userId: 用户编号
    * @param productId: 产品编号
    * @param quantity: 购买数量
    * @return 成功 or 失败
    * */
    public boolean purchase(Long userId, Long productId, int quantity);
}

@Service
public class PurchaseServiceImpl implements PurchaseService {

    @Autowired
    private ProductMapper productMapper = null;

    @Autowired
    private PurchaseRecordMapper purchaseRecordMapper = null;

     @Override
     // 启动Spring数据库事务机制
     @Transactional
     public boolean purchase(Long userId, Long productId, int quantity) {
         // 获取产品
         ProductPo product = productMapper.getProduct(productId);
         // 比较库存和购买数量
         if (product.getStock() < quantity) {
         // 库存不足
         return false;
         }
         // 扣减库存
         productMapper.decreaseProduct(productId, quantity);
         // 初始化购买记录
         PurchaseRecordPo pr = this.initPurchaseRecord(userId, product, quantity);
         // 插入购买记录
         purchaseRecordMapper.insertPurchaseRecord(pr);
         return true;
     }

    private PurchaseRecordPo initPurchaseRecord(Long userId, ProductPo product, int quantity) {
        PurchaseRecordPo pr = new PurchaseRecordPo();
        pr.setNote("购买日志，时间：" + System.currentTimeMillis());
        pr.setPrice(product.getPrice());
        pr.setProductId(product.getId());
        pr.setQuantity(quantity);
        double sum = product.getPrice() * quantity;
        pr.setSum(sum);
        pr.setUserId(userId);
        return pr;
    }
}

5、控制层以及HTML页面

@RestController
public class PurchaseController {

    @Autowired
    PurchaseService purchaseService = null;

    @GetMapping("/test")
    public ModelAndView testPage(){
        ModelAndView mv = new ModelAndView("test");
        return mv;
    }

    @PostMapping("/purchase")
    public Result purchase(Long userId, Long productId, Integer quantity){
        boolean success = purchaseService.purchase(userId, productId, quantity);
        String message = success?"抢购成功":"抢购失败";
        Result result = new Result(success, message);
        return result;
    }

    class Result {
        private boolean success = false;
        private String message;
        public Result(){};

        public Result(Boolean success, String message){
            this.success = success;
            this.message = message;
        }

        public boolean isSuccess() {
            return success;
        }

        public void setSuccess(boolean success) {
            this.success = success;
        }

        public String getMessage() {
            return message;
        }

        public void setMessage(String message) {
            this.message = message;
        }
    }
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script type="text/javascript"
            src="https://code.jquery.com/jquery-3.2.1.min.js"></script>

    <script type="text/javascript">
            var params = {
                userId : 1,
                productId : 1,
                quantity : 2
            };
            // 通过POST请求后端
            $.post("./purchase", params, function(result) {
                alert(result.message);
            });
    </script>

</head>
<body>
<h1>抢购产品测试</h1>
</body>
</html>

6、pom.xml以及application.properties设置

在pom.xml中添加web, Mybatis, MySQL, thymeleaf的相关依赖

<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!-- Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.0.1</version>
        </dependency>

        <!-- ThymeLeaf 依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
        <!-- mysql 依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

# 数据库配置
spring.datasource.url= jdbc:mysql://localhost:3306/shopping?serverTimezone=UCT
spring.datasource.username=root
spring.datasource.password=root

spring.datasource.tomcat.max-idle=10
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.initial-size=5

# 事务的隔离级别设置为 读写提交
spring.datasource.tomcat.default-transaction-isolation=2

mybatis.type-aliases-package=com.wayne.springboot.pojo

7、运行测试

启动项目后在在浏览器输入localhost:8080/test，便可以执行一次购买，在数据库的t_product表中会将产品库存减去所购数量，如果数量不够的话，就购买失败，如果数量足够的话就购买成功，并向t_purchase_record表中插入一条购买记录。

抢购成功 购买记录

下一篇来介绍如何应对高并发：使用悲观锁，乐观锁，以及Redis来提高高并发能力。