Java高并发秒杀API之DAO层实现（一）

最新推荐文章于 2022-02-12 00:44:13 发布

lcja

最新推荐文章于 2022-02-12 00:44:13 发布

阅读量714

点赞数

CC 4.0 BY-SA版权

分类专栏：学习文章标签： java spring mybatis ibatis

本文链接：https://blog.youkuaiyun.com/u013123635/article/details/78317553

学习专栏收录该内容

30 篇文章

订阅专栏

本文详细介绍了一个基于Spring MVC框架的秒杀系统搭建过程，涵盖了技术选型、项目准备、业务分析、表设计、实体编写及单元测试等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一项目搭建和业务分析

1.前述

根据慕课网http://www.imooc.com/u/2145618/courses?sort=publish学习笔记
主要用到的技术: bootstrap,springMVC，spring，mybatis，mysql,redis,存储过程
一下为开发完成后的展示
这里写图片描述

2.前期项目准备

官网	地址
logback	https://logback.qos.ch/manual/configuration.html
spring	http://docs.spring.io/spring/docs
mybatis	http://www.mybatis.org/mybatis-3/zh/index.html

maven命令创建项目骨架

mvn archetype:create _DgroupId=org.seckill -DartifactId=seckill _DarchetypeArtifactId=maven-archetype-webapp

创建的web.xml是2.3的版本，默认不支持jstl表达式，可以去tomcat的examples下WEB-INF中web.xml中把3.1的头拷贝出来替换

<web-app xmlns="http://java.sun.com/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
                      http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
  version="3.0"
  metadata-complete="true">

（这个是3.0版也可以凑乎用）
补全目录结构
这里写图片描述

进入pom.xml,修改junit的版本为4.11 4使用注解，3使用编程

<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

补全其他依赖

 <!--  日志 slf4j  log4j logback  common-logging
     slf4j 是接口
     使用 slf4j+logback
    -->
     <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.12</version>
    </dependency>
  <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-core</artifactId>
      <version>1.1.1</version>
    </dependency>
      <dependency>
      <!--实现slf4j 并整合 -->
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.1.1</version>
    </dependency>
    <!-- 数据库相关依赖 -->
     <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.35</version>
    </dependency>
     <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
     <!--2.dao框架:MyBatis依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.3.0</version>
    </dependency>
    <!--mybatis自身实现的spring整合依赖-->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.3</version>
    </dependency>
      <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>
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.5.4</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
    </dependency>

    <!--4:spring依赖-->
    <!--1)spring核心依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <!--2)spring dao层依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <!--3)springweb相关依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <!--4)spring test相关依赖-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.1.7.RELEASE</version>
    </dependency>
    <!-- redis -->
    <dependency>
      <groupId>redis.clients</groupId>
      <artifactId>jedis</artifactId>
      <version>2.7.3</version>
    </dependency>
    <!-- protostuff序列化依赖 -->    
        <dependency>
      <groupId>com.dyuproject.protostuff</groupId>
      <artifactId>protostuff-core</artifactId>
      <version>1.0.8</version>
    </dependency>
    <dependency>
      <groupId>com.dyuproject.protostuff</groupId>
      <artifactId>protostuff-runtime</artifactId>
      <version>1.0.8</version>
    </dependency>

    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2</version>
    </dependency>

3.秒杀业务分析

商家- - - -库存- - - -用户
商家添加库存，用户减库存，核心库存的处理

用户购买成功时需要减库存，记录购买明细，需要事务一致，完成数据落地

难点 :多用户竞争

流程
1.开启事务
2.update更新库存
3.insert 插入购买明细
4.commit
锁竞争在第二步，update更新库存
其他用户在减库存时，只能在commit之后减库存

4.表设计

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='秒杀库存表';

AUTO_INCREMENT 从1000开始
注意：
timestamp有两个属性，分别是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP两种，设置为timestamp时会自动分配这两个属性中的一个属性。
1. CURRENT_TIMESTAMP
当要向数据库执行insert操作时，如果有个timestamp字段属性设为 CURRENT_TIMESTAMP，则无论这个字段有没有set值都插入当前系统时间
2. ON UPDATE CURRENT_TIMESTAMP
当执行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP属性。则字段无论值有没有变化，它的值也会跟着更新为当前UPDATE操作时的时间。

-- 秒杀成功明细表
-- 用户登录认证相关信息(简化为手机号)
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:已付款 2:已发货',
  `create_time` TIMESTAMP NOT NULL COMMENT '创建时间',
  PRIMARY KEY(seckill_id,user_phone),/*联合主键*/
  KEY idx_create_time(create_time)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='秒杀成功明细表'

5.实体编写

这里写图片描述
在entity下写对应的实体Seckill，SuccessKilled

在dao层写对应接口SeckillDao，SuccessKilledDao
SeckillDao

package org.seckill.dao;

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

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

public interface SeckillDao {
    //减库存
 int reduceNumber(@Param("seckillId")long seckillId,@Param("killTime")Date killTime);
 //根据id查询秒杀对象
 Seckill queryById(long seckillId);
 //根据偏移量查询秒杀商品列表
 List<Seckill> queryAll(@Param("offset")int offset,@Param("limit")int limit);

 /**
  * 使用存储过程执行秒杀
  * @param parammap
  */
  void  killByProcedure(Map<String,Object> paramMap);
}

SuccessKilledDao

package org.seckill.dao;

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

public interface SuccessKilledDao {
//插入购买明细，可过滤重复
    int insertSuccessKilled(@Param("seckillId")long seckilled,@Param("userPhone")long userPhone);
//根据id查询successkiled 并携带秒杀产品对象实体
    SuccessKilled queryByIdWithSeckill(@Param("seckillId")long seckilled,@Param("userPhone")long userPhone);
}

mybatis 数据库和java对象做映射
有两种实现一种写在xml中，另一种写在注解中
实现接口一种是mapper实现，一种是api实现

在resource下写mybatis的配置文件mybatis-config.xml
在官网的入门中找配置和mapper的配置

<?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>
  <!-- p配置全局属性 -->
  <settings>
  <!-- 使用jdbc的getGeneratedKeys  获取数据库自增主键值 -->
    <setting name="useGeneratedKeys" value="true"/>
    <!-- 使用列别名 替换列名 -->
    <setting name="useColumnLabel" value="true"/>
    <!--开启驼峰命名转换  -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
  </settings>
  </configuration>

mapper配置

<!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语句配置
    即针对dao接口中的方法编写我们的sql语句-->


</mapper>

SeckillDao.xml

<!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语句配置
    即针对dao接口中的方法编写我们的sql语句-->


    <update id="reduceNumber">
        UPDATE seckill
        SET number = number-1
        WHERE seckill_id=#{seckillId}
        AND start_time <![CDATA[ <= ]]> #{killTime}
        AND end_time >= #{killTime}
        AND number > 0;
    </update>

    <select id="queryById" resultType="Seckill" parameterType="long">
        SELECT *
        FROM seckill
        WHERE seckill_id=#{0}
    </select>

    <select id="queryAll" resultType="Seckill" >
        SELECT *
        FROM seckill
        ORDER BY create_time DESC
        limit #{offset},#{limit}
    </select>

    <select id="killByProcedure" statementType="CALLABLE">
        call execute_seckill(
        #{seckillId,jdbcType=BIGINT,mode=IN},
        #{phone,jdbcType=BIGINT,mode=IN},
        #{killTime,jdbcType=TIMESTAMP,mode=IN},
        #{result,jdbcType=INTEGER,mode=OUT}
        )
    </select>
</mapper>

<= 用<![CDATA[ <= ]]>替换，xml验证不报错
SuccessKilledDao.xml

<!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">
        <!--当出现主键冲突时(即重复秒杀时)，会报错;不想让程序报错，加入ignore-->
        INSERT ignore INTO success_killed(seckill_id,user_phone,state)
        VALUES (#{seckillId},#{userPhone},0)
    </insert>

    <select id="queryByIdWithSeckill" resultType="SuccessKilled">

        <!--根据seckillId查询SuccessKilled对象，并携带Seckill对象-->
        <!--如何告诉mybatis把结果映射到SuccessKill属性同时映射到Seckill属性-->
        <!--可以自由控制SQL语句-->

        SELECT
            sk.seckill_id,
            sk.user_phone,
            sk.create_time,
            sk.state,
            s.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>

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行

6.mybatis整合spring

使用spring 更少的类扫描和mapper扫描，自动实现dao接口，自动注入spring容器中

coding
resource下创建spring目录
创建spring-dao.xml

7.单元测试

SeckillDaoTest

package org.seckill.dao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.seckill.entity.Seckill;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

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


/**
 *
 * 配置spring和junit整合，这样junit在启动时就会加载spring容器
 */
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit spring的配置文件
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SeckillDaoTest {

    //注入Dao实现类依赖
    @Autowired
    private SeckillDao seckillDao;



    @Test
    public void queryById() throws Exception {
        try{
        long seckillId=1000;
        Seckill seckill=seckillDao.queryById(seckillId);
        System.out.println("alaala");
        System.out.println(seckill.getName());
        System.out.println(seckill);
        }catch(Exception e){
            e.printStackTrace();
        }
    }



    @Test
    public void queryAll() throws Exception {
        try{
        List<Seckill> seckills=seckillDao.queryAll(0,100);
        for (Seckill seckill : seckills)
        {
            System.out.println(seckill);
        }
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    @Test
    public void reduceNumber() throws Exception {
        try{
        long seckillId=1000;
        Date date=new Date();
        int updateCount=seckillDao.reduceNumber(seckillId,date);
        System.out.println(updateCount);
        }catch(Exception e){
            e.printStackTrace();
        }
    }


}

SuccessKilledDaoTest

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;

import static org.junit.Assert.*;

/**
 * 
 */
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit spring的配置文件
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SuccessKilledDaoTest {

    @Resource
    private SuccessKilledDao successKilledDao;

    @Test
    public void testInsertSuccessKilled() throws Exception {

        long seckillId=1000L;
        long userPhone=13476191877L;
        int insertCount=successKilledDao.insertSuccessKilled(seckillId,userPhone);
        System.out.println("insertCount="+insertCount);
    }

    @Test
    public void TestqueryByIdWithSeckill() throws Exception {
        try{
        long seckillId=1000L;
        long userPhone=13476191877L;                    
        SuccessKilled successKilled=successKilledDao.queryByIdWithSeckill(seckillId,userPhone);
        System.out.println(successKilled);
        System.out.println(successKilled.getSeckill());
        }catch (Exception e){
            e.printStackTrace();
        }

    }

}