SSM项目秒杀系统---（一）业务分析与Dao层

学习的是慕课网上的秒杀系统，记录一下设计的整个过程。

---

一、相关技术介绍

1、MySQL

表设计、SQL技巧、事务和行级锁

2、MyBatis

Dao层设计与开发、MyBatis合理使用、MyBatis与Spring整合

3、Spring

Spring IOC整合Service、声明式事务运用

4、SpringMVC

Restful接口设计和使用、框架运作流程、Controller开发技巧

5、前端

交互设计、Bootstrap、jQuery

6、高并发

高并发点和高并发分析、优化思路并实现

二、创建项目和依赖

1、使用idea直接创建maven项目，使用maven-archetype-webapp框架。

新增配置加快拉包速度。
然后在项目加载完后，点击右下角弹窗的import changes等待拉包后，项目结构会出来的。

2、替换原有的servlet版本

原有的为2.3的版本不支持java el，故替换为新版本。
src—main—webapp—WEB-INF—web.xml中，将（其实就是全部）：

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >
<web-app>
  <display-name>Archetype Created Web Application</display-name>
</web-app>

替换为：

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee                             http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1"
         metadata-complete="true">
  <!--修改servlet版本为3.1-->
</web-app>

3、添加目录结构

File—Project Structure—Modules中，添加main—java、src—test—java、resources，注意标记Mark as

4、pom.xml中添加相关依赖

注意：很多问题的出错来自于依赖冲突。

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <!--补全项目依赖-->
    <!--1.日志：java日志：slf4j、log4j、logback、common-logging
      slf4j是规范/接口
      日志实现：log4j、logback、common-logging
      使用slf4j+logback
      -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.25</version>
    </dependency>
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-core</artifactId>
      <version>1.2.3</version>
    </dependency>
    <!--实现slf4j接口并整合-->
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.2.3</version>
      <scope>test</scope>
    </dependency>

    <!--2.数据库相关依赖-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.15</version>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>com.mchange</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.5.2</version>
    </dependency>
    <!--DAO框架：mybatis依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <!--mybatis整合spring依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.2</version>
    </dependency>

    <!--3.servlet web相关依赖-->
    <!--jsp标签库-->
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <!--jackson-->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.10.0</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
      <scope>provided</scope>
    </dependency>

    <!--4.spring依赖-->
    <!--1)spring核心依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <!--2)spring dao层依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <!--3)spring web相关依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>5.2.2.RELEASE</version>
    </dependency>
    <!--4)spring test相关依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>5.2.2.RELEASE</version>
      <scope>test</scope>
    </dependency>
    <!--引入java-redis客户端：Jedis-->
    <dependency>
      <groupId>redis.clients</groupId>
      <artifactId>jedis</artifactId>
      <version>3.1.0</version>
    </dependency>
    <!--redis过程中需要自定义序列化的相关依赖-->
    <dependency>
      <groupId>com.dyuproject.protostuff</groupId>
      <artifactId>protostuff-core</artifactId>
      <version>1.1.5</version>
    </dependency>
    <dependency>
      <groupId>com.dyuproject.protostuff</groupId>
      <artifactId>protostuff-runtime</artifactId>
      <version>1.1.5</version>
    </dependency>
    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2.2</version>
    </dependency>
  </dependencies>

三、秒杀业务分析

1、秒杀系统业务流程

秒杀业务的核心->库存的处理
（1）用户针对库存业务分析
减库存、记录购买明细–>完整事务–>数据落地
购买行为：谁购买成功了，成功的时间/有效期，付款/发货信息
（2）数据落地MySQL vs NoSQL
事务机制依然是目前最可靠的落地方案。

2、MySQL实现秒杀难点分析

（1）竞争
反映到SQL是事务和行级锁。
事务：Start Transaction—Update库存数量（竞争出现的地方）—Insert购买明细—Commit
行级锁：update table set num=num-1 where id=10 and num>1，很多用户都要执行该命令，但是行级锁保证在某个用户执行该命令时一直到执行完毕才允许下一个用户执行。
（2）秒杀的难点是如何处理高效的处理竞争
后续将解决。

3、实现哪些秒杀功能

（1）秒杀接口暴露
（2）执行秒杀
（3）相关查询

四、Dao层设计与开发

1、数据库设计与编码

-- 数据库初始化脚本
drop database seckill;
-- 创建数据库
create database seckill;
-- 使用数据库
use seckill;
-- 创建秒杀库存表
create table seckill(
`seckill_id` bigint not null auto_increment comment '商品库存id',
`name` varchar(120) not null comment '商品名称',
`number` int not null comment '库存数量',
`start_time` timestamp not null comment '秒杀开启时间',
`end_time` timestamp not null comment '秒杀结束时间',
`create_time` timestamp not null default current_timestamp comment '创建时间',
primary key(seckill_id),
key idx_start_time(start_time),
key idx_end_time(end_time),
key idx_create_time(create_time)
)ENGINE=InnoDB auto_increment=1000 default charset=utf8 comment='秒杀库存表';

-- 初始化数据
insert into seckill(name,number,start_time,end_time)
values
    ('1000元秒杀iphoneXR',100,'2020-01-30 00:00:00','2020-01-31 00:00:00'),
    ('500元秒杀ipad2018',200,'2020-01-30 00:00:00','2020-01-31 00:00:00'),
    ('300元秒杀小米8',300,'2020-01-30 00:00:00','2020-01-31 00:00:00'),
    ('200元秒杀红米note2',400,'2020-01-30 00:00:00','2020-01-31 00:00:00');

-- 秒杀成功明细表
-- 用户登录认证相关信息
create table success_killed(
`seckill_id` bigint not null comment '秒杀商品id',
`user_phone` bigint not null comment '用户手机号',
`state` tinyint not null default -1 comment '状态提示：-1：无效 0：成功 1：已付款',
`create_time` timestamp not null default current_timestamp comment '创建时间',
primary key(seckill_id,user_phone),/*联合主键*/
key idx_create_time(create_time)
)ENGINE=InnoDB default charset=utf8 comment='秒杀成功明细表';

注意：
如果遇到end_time报错，详见：https://www.cnblogs.com/luckyliulin/p/10345448.html
在终端进入mysql，mysql -uroot -pwawj6241，执行如下命令：set global explicit_defaults_for_timestamp = ON;，且重启mysql生效。

2、Dao实体和接口编码

table对应entity
main—java下创建包org.seckill.entity/dao
在entity包下创建两张表对应的实体Seckill、SuccessKilled，定义变量，然后自动生成getter、setter、toString方法。
Seckill.java

package org.seckill.entity;

import java.util.Date;

/**
 * Created by luyangsiyi on 2020/1/27
 */
public class Seckill {

    private long seckillId;

    private String name;

    private int number;

    private Date startTime;

    private Date endTime;

    private Date createTime;

    public long getSeckillId() {
        return seckillId;
    }

    public void setSeckillId(long seckillId) {
        this.seckillId = seckillId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    @Override
    public String toString() {
        return "Seckill{" +
                "seckillId=" + seckillId +
                ", name='" + name + '\'' +
                ", number=" + number +
                ", startTime=" + startTime +
                ", endTime=" + endTime +
                ", createTime=" + createTime +
                '}';
    }
}

SuccessKilled.java

package org.seckill.entity;

import java.util.Date;

/**
 * Created by luyangsiyi on 2020/1/27
 */
public class SuccessKilled {

    private long seckillId;

    private long userPhone;

    private short state;

    private Date createTime;

    //变通
    //多对一的复合属性
    private Seckill seckill;

    public long getSeckillId() {
        return seckillId;
    }

    public void setSeckillId(long seckillId) {
        this.seckillId = seckillId;
    }

    public long getUserPhone() {
        return userPhone;
    }

    public void setUserPhone(long userPhone) {
        this.userPhone = userPhone;
    }

    public short getState() {
        return state;
    }

    public void setState(short state) {
        this.state = state;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Seckill getSeckill() {
        return seckill;
    }

    public void setSeckill(Seckill seckill) {
        this.seckill = seckill;
    }

    @Override
    public String toString() {
        return "SuccessKilled{" +
                "seckillId=" + seckillId +
                ", userPhone=" + userPhone +
                ", state=" + state +
                ", createTime=" + createTime +
                '}';
    }
}

针对两个实体分别编写dao层接口，实际上就是编写CRUD相关的函数：
SeckillDao.java

package org.seckill.dao;

import org.apache.ibatis.annotations.Param;
import org.seckill.entity.Seckill;

import java.util.Date;
import java.util.List;

/**
 * Created by luyangsiyi on 2020/1/27
 */
public interface SeckillDao {

    /**
     * 减库存
     * @param seckillId
     * @param killTime
     * @return 如果影响行数>1，表示更新的记录行数
     */
    int reduceNumber(@Param("seckillId") long seckillId, @Param("killTime") Date killTime);

    /**
     * 根据id查询秒杀的对象
     * @param seckillId
     * @return
     */
    Seckill queryById(@Param("seckillId") long seckillId);

    /**
     * 根据偏移量查询秒杀商品列表
     * java没有保存形参的记录：queryAll(int offset, int limit)->queryAll(arg0,arg1)
     * 使用@Param(""）告诉java参数名
     * @param offset
     * @param limit
     * @return
     */
    List<Seckill> queryAll(@Param("offset") int offset, @Param("limit") int limit);
}

SuccessKilledDao.java

package org.seckill.dao;

import org.apache.ibatis.annotations.Param;
import org.seckill.entity.SuccessKilled;

/**
 * Created by luyangsiyi on 2020/1/27
 */
public interface SuccessKilledDao {

    /**
     * 插入购买明细，可过滤重复
     * @param seckillId
     * @param user_phone
     * @return
     * 插入的行数
     */
    int insertSuccessKilled(@Param("seckillId") long seckillId,@Param("user_phone") long user_phone);

    /**
     * 根据id查询SuccessKilled并携带秒杀产品对象实体
     * @param seckillId
     * @return
     */
    SuccessKilled queryByIdWithSeckill(@Param("seckillId") long seckillId, @Param("userPhone") long userPhone);

}

3、基于mybatis实现Dao

（1）mybatis
作用：数据库<—>映射（mybatis工作在这一层）<—>对象
特点：参数+SQL=Entity/List
SQL写在哪儿：XML提供SQL（建议）、注解提供SQL（修改需要反复编译java类）
如何实现Dao接口：Mapper自动实现Dao接口（建议）、API编程方式实现Dao接口
（2）实现Dao编程
main—resources下添加mapper文件夹、mybatis-config.xml
配置mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--配置全局设置-->
    <settings>
        <!--使用jdbc的getGeneratedKeys获取数据库自增主键值-->
        <setting name="useGeneratedKeys" value="true"/>
        <!--使用列别名替换列名，默认为true，这边只是为了强调
            select name as title from table
            mybatis会自动将表中的name映射为类中的title
        -->
        <setting name="useColumnLabel" value="true"/>
        <!--开启驼峰命名转换-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

在mapper文件夹下，分别编写dao层文件对应的xml，在中分别实现dao层的接口函数：
SeckillDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.seckill.dao.SeckillDao">
    <!--目的：为dao接口方法提供sql语句配置-->
    <update id="reduceNumber"><!--id对应dao层的方法名-->
        <!--具体sql-->
        update
            seckill
        set
            number = number -1
        where seckill_id = #{seckillId}
        and start_time <![CDATA[<=]]> #{killTime} <!--<![CDATA[<=]]>说明<=不是xml自带的-->
        and end_time >= #{killTime}
        and number > 0;
    </update>

    <select id="queryById" resultType="Seckill" parameterType="long">
        select seckill_id, name, number, start_time, end_time, create_time
        from seckill
        where seckill_id = #{seckillId}
    </select>

    <select id="queryAll" resultType="Seckill">
        select seckill_id, name, number, start_time, end_time, create_time
        from seckill
        order by create_time desc
        limit #{offset},#{limit}
    </select>
</mapper>

SuccessKilledDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.seckill.dao.SuccessKilledDao">
    <insert id="insertSuccessKilled">
        <!--主键冲突，报错，返回0-->
        insert ignore into success_killed(seckill_id,user_phone)
        values(#{seckillId},#{user_phone})
    </insert>
    <select id="queryByIdWithSeckill" resultType="SuccessKilled">
        <!--根据id查询SuccessKilled并携带秒杀产品对象实体-->
        <!--如何告诉mybatis把结果映射到SuccessKilled同时映射seckill属性-->
        <!--可以自由控制SQL-->
        select
            sk.seckill_id,
            sk.user_phone,
            sk.create_time,
            sk.state,
            s.seckill_id "seckill.seckill_id",<!--seckill对象的seckill_id属性-->
            s.name "seckill.name",
            s.number "seckill.number",
            s.start_time "seckill.start_time",
            s.end_time "seckill.end_time",
            s.create_time "seckill.create_time"
        from success_killed sk
        inner join seckill s on sk.seckill_id = s.seckill_id
        where sk.seckill_id = #{seckillId} and sk.user_phone = #{userPhone}
    </select>
</mapper>

4、mybatis整合spring

（1）理论
目标：更少的编码（只写接口，不写实现）、更少的配置（别名，mybatis实现了package scan；自动扫描配置文件）、足够的灵活性
（2）编码
main—resources—jdbc.properties给数据库相关参数：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/seckill?useUnicode=true&characterEncoding=utf8
username=root
password=wawj6241

main—resources下创建目录spring—spring-dao.xml：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
    <!--配置整合mybatis过程-->
    <!--1:配置数据库相关参数properties的属性:${url}-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--2:数据库连接池-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <!--配置连接池属性-->
        <property name="driverClass" value="${driver}"/>
        <property name="jdbcUrl" value="${url}"/>
        <property name="user" value="${username}"/>
        <property name="password" value="${password}"/>

        <!--c3p0连接池的私有属性-->
        <property name="maxPoolSize" value="30"/>
        <property name="minPoolSize" value="10"/>
        <!--关闭连接后不自动commit-->
        <property name="autoCommitOnClose" value="false"/>
        <!--获取连接超时时间-->
        <property name="checkoutTimeout" value="1000"/>
        <!--当获取连接失败重试次数-->
        <property name="acquireIncrement" value="2"/>
    </bean>

    <!--3:配置SqlSessionFactory对象-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池-->
        <property name="dataSource" ref="dataSource"/><!--11行id-->
        <!--配置mybatis全局配置文件-->
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <!--扫描entity包 别名org.seckill.entity.Seckill==>Seckill-->
        <property name="typeAliasesPackage" value="org.seckill.entity"/>
        <!--扫描sql配置文件：mapper需要的xml文件-->
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    </bean>

    <!--4:配置扫描dao接口包，动态实现dao接口，并注入到spring容器中-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注入sqlSessionFactory-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/><!--30行id-->
        <!--给出需要扫描dao接口包-->
        <property name="basePackage" value="org.seckill.dao"/>
    </bean>
</beans>

5、单元测试dao层

放在test下面。
选中dao层的接口名，右键Generate—Test，选择JUnit4，选中所有的方法：

在下述目录会新建一个Test文件:

SeckillDaoTest.java
右键每个函数测试。

package org.seckill.dao;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.seckill.entity.Seckill;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * 配置spring和junit整合，junit启动时加载springIOC容器
 * Created by luyangsiyi on 2020/1/27
 */
//junit启动时加载springIOC容器
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit spring配置文件
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SeckillDaoTest {

    //注入dao实现类依赖
    @Resource
    private SeckillDao seckillDao;

    @Test
    public void queryById() {
        long id = 1000;
        Seckill seckill = seckillDao.queryById(id);
        System.out.println(seckill.getName());
        System.out.println(seckill);
    }

    @Test
    //Parameter 'offset' not found. Available parameters are [arg1, arg0, param1, param2]
    //java没有保存形参的记录：queryAll(int offset, int limit)->queryAll(arg0,arg1)
    //List<Seckill> queryAll(@Param("offset") int offset, @Param("limit") int limit);
    public void queryAll() throws Exception{
        List<Seckill> seckills = seckillDao.queryAll(0,100);
        for(Seckill seckill : seckills){
            System.out.println(seckill);
        }

    }

    @Test
    public void reduceNumber() throws Exception {
        Date killTime = new Date();
        int updateCount = seckillDao.reduceNumber(1000L,killTime);
        System.out.println("updateCount="+updateCount);
    }

}

SuccessKilledDaoTest.java

package org.seckill.dao;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.seckill.entity.SuccessKilled;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;

/**
 * Created by luyangsiyi on 2020/1/27
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SuccessKilledDaoTest {

    @Resource
    private SuccessKilledDao successKilledDao;

    @Test
    public void insertSuccessKilled() throws Exception{
        long id = 1000L;
        long phone = 17816855860L;
        int insertCount = successKilledDao.insertSuccessKilled(id,phone);
        System.out.println("insertCount="+insertCount);
    }

    @Test
    public void queryByIdWithSeckill() {
        long id = 1000L;
        long phone = 17816855860L;
        SuccessKilled successKilled = successKilledDao.queryByIdWithSeckill(id,phone);
        System.out.println(successKilled);
        System.out.println(successKilled.getSeckill());
    }
}