高并发秒杀API之业务分析与DAO
来源:互联网 发布:sybase数据库导出mdb 编辑:程序博客网 时间:2024/05/22 07:45
1.秒杀业务的分析
一般的秒杀系统会存在商家,库存,用户三个实体,商家添加调整库存,库存用于发货和核账,库存用户秒杀或者预售,用户的付款,退货也会影响到库存集体如下图:
也就是秒杀业务的核心就是库存的处理。
库存业务分析:首先用户秒杀成功要相应的减去库存已经记录购买的明细,这两项操作组成了一个完整的事务。如下图:
2.难点分析的分析
主要的难点问题就是竞争多个用户同时秒杀一种商品。对于mysql 来说竞争反应到背后的技术就是事务和行级锁。
1.事务工作机制
首先是 开启事务 start transaction
update 库存数量 (竞争出现的地方)
insert 购买明细
commit 事务提交
2 行级锁
当一个用户执行减库存的操作时,其他用户执行该项操作时为等待状态如下图
秒杀的难点在于如何高效的处理竞争具体的解决方法会在单写一遍博客进行解释。接下来通过一个项目主要实现一下如下的秒杀功能。
3.设计数据库
因为主要只实现秒杀相关的功能这里只设置两张表。
1.秒杀库存表下面给出建表语句。
-- 创建秒杀库存表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元秒杀iphone6', 100, '2015-11-01 00:00:00', '2018-11-02 00:00:00'), ('500元秒杀ipad2', 200, '2015-11-01 00:00:00', '2018-11-02 00:00:00'), ('300元秒杀小米4', 300, '2015-11-01 00:00:00', '2018-11-02 00:00:00'), ('200元秒杀红米note', 400, '2015-11-01 00:00:00', '2018-11-02 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已付款,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='秒杀库存表'
4.DAO编码
1.创建工程
首先创建一个maven工程seckill工程目录如下
2.添加依赖
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.wen</groupId> <artifactId>seckill</artifactId> <version>0.0.1-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.5.2.RELEASE</version> <relativePath/> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <java.version>1.8</java.version> <thymeleaf.version> 3.0.2.RELEASE </thymeleaf.version> <thymeleaf-layout-dialect.version> 2.1.1 </thymeleaf-layout-dialect.version> <tomcat.version>7.0.69</tomcat.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> <exclusions> <exclusion> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-logging</artifactId> </exclusion> </exclusions> </dependency> <!-- log4j2 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> </dependency> <!-- web组件支持 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- thymeleaf模板支持 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> <!-- mybatis支持 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency> <!--pagehelper --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <!-- mysql连接池 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.35</version> </dependency> <!-- Apache公共类库 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-lang3</artifactId> <version>3.4</version> </dependency> <!-- google guava公共类库 --> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> <version>21.0</version> </dependency> <!--热部署--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> <!-- 测试依赖 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> <!--添加切面支持--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.31</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <!--开发中使用devtools 打包忽略--> <excludeDevtools>false</excludeDevtools> <fork>true</fork> </configuration> </plugin> </plugins> <finalName>seckill</finalName> </build></project>
3工程配置
#数据库连接配置spring: datasource: url: jdbc:mysql://localhost:3306/seckill username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver thymeleaf: mode: HTML5 #字符集和json格式工具 http: encoding: charset: utf-8 converters: preferred-json-mapper: fastjson multipart: max-file-size: 10MB application: name: seckill#mynatis配置mybatis: type-aliases-package: com.wen.seckill.model #mapper加载路径 mapper-locations: classpath:mapper/*.xml #myatbis配置文件 config-location: classpath:mybatis-conf.xml#加载log4j2logging: config: classpath:log4j2.xml level: debug file:server: session-timeout : 3600 port: 80
日志配置文件
<?xml version="1.0" encoding="utf-8"?><configuration> <properties> <!-- 文件输出格式 --> <property name="PATTERN">%d{yyyy-MM-dd HH:mm:ss.SSS} |-%-5level [%thread] %c [%L] -| %msg%n</property> </properties> <appenders> <Console name="Console" target="system_out"> <PatternLayout pattern="${PATTERN}" /> </Console> </appenders> <!--配置mybatis日志--> <loggers> <logger name="log4j.logger.org.mybatis" level="debug" additivity="false"> <appender-ref ref="Console"/> </logger> <logger name="log4j.logger.java.sql" level="debug" additivity="false"> <appender-ref ref="Console"/> </logger> <logger name="com.wen.seckill.dao" level="debug" /> <root level="info"> <appenderref ref="Console" /> </root> </loggers></configuration>
mybatis 一些功能的配置文件
<?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> <!--设置mybatis日志类型--> <settings> <setting name="logImpl" value="LOG4J2"/> <!--配置的缓存的全局开关。--> <setting name="cacheEnabled" value="true"/> <!--延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。--> <setting name="lazyLoadingEnabled" value="true"/> <!--当没有为参数提供特定的 JDBC 类型时,为空值指定 JDBC 类型。 某些驱动需要指定列的 JDBC 类型,多数情况直接用一般类型即可,比如 NULL、VARCHAR 或 OTHER。--> <setting name="jdbcTypeForNull" value="NULL"/> <setting name="useGeneratedKeys" value="true"/> <setting name="useColumnLabel" value="true"/> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> </configuration>
4 dao层实体编写
根据表结构创建实体
库存表
import java.util.Date;/** * 秒杀库存实体 */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 + "]"; }}
秒杀记录表
import java.util.Date;/** * 成功秒杀实体 * */public class SuccessKilled { private long seckillId; private long userPhone; private short state; private Date creteTime; // 多对一的复合属性 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 getCreteTime() { return creteTime; } public void setCreteTime(Date creteTime) { this.creteTime = creteTime; } 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 + ", creteTime=" + creteTime + "]"; }}
4 dao层借口编写
实体类接口
主要需要的功能有减库存,秒杀列表,根据id 检索商品信息
import java.util.Date;import java.util.List;import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.Param;import com.wen.seckill.model.Seckill;@Mapperpublic 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); /** * 获取秒杀列表 */ List<Seckill> queryAll(); }
为接口编写相应的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="com.wen.seckill.dao.SeckillDao"> <!-- 减少库存操作 --> <update id="reduceNumber"> update seckill set number=number-1 where seckill_id=#{seckillId} AND start_time <=#{killTime} and end_time>=#{killTime} and number>0 </update> <!-- 根据id 查询 --> <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> <!-- 根据id 查询 --> <select id="queryAll" resultType="Seckill" > select seckill_id,name,number,start_time,end_time,create_time from seckill </select></mapper>
秒杀接口主要需要两个功能 1插入秒杀记录 2秒杀记录检索
import java.util.Date;/** * 成功秒杀实体 * */public class SuccessKilled { private long seckillId; private long userPhone; private short state; private Date creteTime; // 多对一的复合属性 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 getCreteTime() { return creteTime; } public void setCreteTime(Date creteTime) { this.creteTime = creteTime; } 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 + ", creteTime=" + creteTime + "]"; }}
为接口编写相应的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="com.wen.seckill.dao.SuccessKilledDao"> <!-- 秒杀成功插入 --> <insert id="insertSuccessKilled"> <!-- 主键冲突报错 --> insert ignore into success_killed(seckill_id,user_phone) values(#{seckillId},#{userPhone}) </insert> <select id="queryByIdWithSeckill" resultType="SuccessKilled"> <!-- 根据id 查询 successkidded 并携带Seckill 实体 --> <!-- 根据 mybatis 将结果映射到SuccessKilled 同时映射 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 as sk inner join seckill as s on sk.seckill_id=s.seckill_id where sk.seckill_id=#{seckillId} and sk.user_phone=#{userPhone} </select></mapper>
5.单元测试
编写完相应的代码后自然要编写单元测试,测试相应的代码的正确性。
首先编写一个公用的单元测试类引入相应的测试注解配置
@RunWith(SpringJUnit4ClassRunner.class)@SpringBootTest(classes=App.class)@WebAppConfigurationpublic class BaseTest {}
编写秒杀库存dao的单元测试给出测试数据测试秒杀库存dao中的三个方法。
public class SeckillDaoTest extends BaseTest { //注入Dao实现类依赖 @Resource private SeckillDao seckillDao; @Test public void testQueryById() { long id = 1000; try { Seckill seckill = seckillDao.queryById(id); System.out.println(seckill.getName()); System.out.println(seckill); }catch(Exception e) { e.printStackTrace(); } } @Test public void testReduceNumber() throws Exception { Date killTime = new Date(); int updateCount = seckillDao.reduceNumber(1000L, killTime); System.out.println("updateCount=" + updateCount); } @Test public void testQueryAll() throws Exception { List<Seckill> seckills = seckillDao.queryAll(); for (Seckill seckill : seckills) { System.out.println(seckill); } }}
启动junit 查看测试结果。
编写秒杀记录dao的单元测试给出测试数据测试秒杀记录dao中的二个方法。
public class SuccessKilledDaoTest extends BaseTest { @Resource private SuccessKilledDao successKilledDao; @Test public void testInsertSuccessKilled() throws Exception { long id = 1001; long phone = 13631231234L; int insertCount = successKilledDao.insertSuccessKilled(id, phone); System.out.println("insertCount=" + insertCount); } @Test public void testQueryByIdWithSeckill() throws Exception { long id = 1001; long phone = 13631231234L; SuccessKilled successKilled = successKilledDao.queryByIdWithSeckill(id, phone); System.out.println(successKilled); System.out.println(successKilled.getSeckill()); }}
启动junit 查看测试结果。到此dao 层就算完成了 下一遍将接受service 层实现以及测试。
源码地址 :https://github.com/haha174/seckill.git
文章地址: http://www.haha174.top/article/details/256198
教程地址:http://www.imooc.com/learn/587
- 高并发秒杀API之业务分析与DAO
- Java高并发秒杀API之业务分析与DAO层
- Java高并发秒杀API之业务分析与DAO层
- Java高并发秒杀API(一)之业务分析与DAO层
- JAVA高并发秒杀系统构建之——业务分析与Dao层搭建
- 一、高并发秒杀API简介与业务分析
- 二、高并发秒杀API之Dao层设计与实现
- 【JAVA高并发秒杀API之DAO层】课程笔记
- Java高并发秒杀API之DAO层实现(一)
- 高并发秒杀API之Service
- 三、高并发秒杀API之Service层设计与实现
- 四、高并发秒杀API之Web层设计与实现
- 高并发秒杀系统API之Web层
- Java高并发秒杀API(二)之Service层
- Java高并发秒杀API(三)之Web层
- 慕课网-java高并发秒杀api之高并发优化-总结
- Java高并发秒杀API(四)之高并发优化
- Java高并发秒杀API之高并发优化(四)
- PAT-1135 Is It A Red-Black Tree(二叉查找树的创建和遍历)
- 双连通分量
- 《Linux内核设计与实现》读书笔记(二)--进程调度
- python下的opencv环境搭建
- 习题 7.17 用递归法将一个整数n转换成字符串。例如,输入483,应输出字符串“483”。n的位数不确定,可以是任意位数的整数。
- 高并发秒杀API之业务分析与DAO
- Sklearn 学习(一)
- Spring 计划任务 Schedule
- hdu 4081
- synchronized与lock有什么异同
- K-近邻算法(kNN)
- 从时间戳计算日历
- java位运算
- Tomcat 管理员,用户名,密码的配置