《Java高并发秒杀API之业务分析与DAO层》学习总结
时间:2017年08月22日星期二
说明:本文部分内容均来自慕课网。@慕课网:http://www.imooc.com
教学源码:无
学习源码:https://github.com/zccodere/study-imooc
第一章:课程介绍
1-1 课程介绍
基于SpringMVC+Spring+MyBatis实现高并发秒杀API
主要内容
SpringMVC+Spring+MyBatis使用与整合
秒杀类系统需求理解和实现
常用技术解决高并发问题
为什么是这三个框架
框架易于使用和轻量级
低代码侵入性
成熟的社区和用户群
为什么用秒杀类系统来讲解
秒杀业务场景具有典型“事务”特性
秒杀/红包类需求越来越常见
面试常问问题
能学到什么
刚初学者:框架的使用与整合技巧
有经验者:秒杀分析过程和优化思路
秒杀系列将分为四门课程进行
Java高并发秒杀API之业务分析与DAO层
Java高并发秒杀API之Service
Java高并发秒杀API之web
Java高并发秒杀API之高并发优化
1-2 效果演示
效果图
相关技术介绍
MySQL:表设计、SQL技巧、事务和行级锁
MyBatis:DAO层设计与开发、MyBatis合理使用、MyBatis与Spring整合
Spring:Spirng IOC整合Service、声明式事务运用
SpringMVC:Restful接口设计和使用、框架运作流程、Controller开发技巧
前端:交互设计、Bootstrap、jQuery
高并发:高并发点和高并发分析、优化思路并实现
2-2 创建项目
说明
从零开始创建
从官网获取相关配置:文档更全面权威、避免过时或错误
使用Maven创建项目
创建名为seckill的maven工程pom文件如下
<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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.myimooc</groupId>
<artifactId>seckill</artifactId>
<packaging>war</packaging>
<version>0.0.1-SNAPSHOT</version>
<name>seckill Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<!-- 使用junit4 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- 补全项目依赖 -->
<!-- 1:日志 java日志:sfl4j,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.1.11</version>
</dependency>
<!-- 实现slf4j接口并整合 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.1.11</version>
</dependency>
<!-- 2:数据库相关依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.42</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0 </artifactId>
<version>0.9.1.2</version>
</dependency>
<!-- 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>
<!-- Servlet web相关依赖 -->
<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.8.8</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.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- 2)spring dao层依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- 3)spring web相关依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- 4)spring test相关依赖 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.3.9.RELEASE</version>
</dependency>
<!-- redis客户端:jedis -->
<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.1</version>
</dependency>
</dependencies>
<build>
<finalName>seckill</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
第三章:业务分析
3-1 业务分析
秒杀系统业务流程
秒杀业务的核心 --> 库存的处理
用户针对库存业务分析
减库存
记录购买明细
完整事务
数据落地
记录秒杀成功信息
谁购买成功了
成功的时候或有效期
付款和发货信息
为什么需要事务
情景说明:减库存没有记购买明细、记了明细没有减库存、出现超卖或少卖
负责责任是谁?程序员:背黑锅,很悲催
关于数据落地
MySQL VS NoSQL
事务机制依然是目前最可靠的落地方案
3-2 难点分析
难点问题:竞争
竞争对于MySQL来说是事务和行级锁
事务工作机制
Start Transaction:开启事务
Update:修改库存数量(存在竞争的环节)
Insert:新增购买明细
Commit:提交事务
行级锁
秒杀的难点是如何高效的处理竞争
如何解决
后续揭晓
3-3 功能分析
天猫的秒杀库存系统参考
秒杀功能
秒杀接口暴露
执行秒杀
相关查询
代码开发阶段
DAO设计编码
Service设计编码
Web设计编码
第四章:DAO层设计与开发
4-1 数据库设计与编码
数据库脚本如下
-- 数据库初始化脚本
-- 创建数据库
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元秒杀iphone7',100,'2017-08-22 00:00:00','2017-08-23 00:00:00'),
('500元秒杀ipad2',200,'2017-08-22 00:00:00','2017-08-23 00:00:00'),
('300元秒杀小米4',300,'2017-08-22 00:00:00','2017-08-23 00:00:00'),
('200元秒杀红米note',400,'2017-08-22 00:00:00','2017-08-23 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(seckil_id,user_phone),/* 联合主键 */
key idx_create_time(create_time)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='秒杀成功明细表'
-- 为什么手写DDL
-- 记录每次上线的DDL修改
-- 上线V1.1
ALTER TABLE seckill
DROP INDEX idx_create_time,
add index_idx_c_s(start_time,create_time);
-- 上线V1.2
-- DDL
4-2 实体和接口
代码编写
1.编写Seckill类
package com.myimooc.seckill.entity;
import java.util.Date;
/**
* @describe 商品库存表
* @author zc
* @version 1.0 2017-08-22
*/
public class Seckill {
private long seckillId;
private String name;
private long number;
private Date startTime;
private Date endTime;
private Date createTime;
// 省略getter and setter
2.编写SuccessSeckilled类
package com.myimooc.seckill.entity;
import java.util.Date;
/**
* @describe 成功秒杀明细表
* @author zc
* @version 1.0 2017-08-22
*/
public class SuccessSeckilled {
private long seckillId;
private Long userPhone;
private short state;
private Date createTime;
// 多对一
private Seckill seckill;
// 省略getter and setter
3.编写SeckillDao类
package com.myimooc.seckill.dao;
import com.myimooc.seckill.entity.Seckill;
import org.apache.ibatis.annotations.Param;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* @describe 商品库存dao
* @author zc
* @version 1.0 2017-08-22
*/
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(long seckillId);
/**
* 根据偏移量查询秒杀商品列表
* @param offset
* @param limit
* @return
*/
List<Seckill> queryAll(@Param("offset") int offset, @Param("limit") int limit);
/**
* 使用存储过程执行秒杀
* @param paramMap
*/
void killByProcedure(Map<String,Object> paramMap);
}
4.编写SuccessSeckilledDao类
package com.myimooc.seckill.dao;
import com.myimooc.seckill.entity.SuccessSeckilled;
import org.apache.ibatis.annotations.Param;
/**
* @describe 成功秒杀明细dao
* @author zc
* @version 1.0 2017-08-22
*/
public interface SuccessKilledDao {
/**
* 新增购买明细,可过滤重复
* @param seckillId
* @param userPhone
* @return 插入的行数
*/
int insertSuccessKilled(@Param("seckillId")long seckillId,@Param("userPhone") long userPhone);
/**
* 根据id查询SuccessKilled并携带秒杀产品对象实体
* @param seckillId
* @return
*/
SuccessSeckilled queryByIdWithSeckill(@Param("seckillId")long seckillId,@Param("userPhone") long userPhone);
}
4-3 MyBatis实现DAO理论
MyBatis简介
特点:
参数 + SQL = Entity/List
SQL写在哪:
XML提供SQL(推荐使用)、注解提供SQL
如何实现DAO接口:
Mapper自动实现DAO接口(推荐使用)、API编程方式实现DAO接口
4-4 MyBatis实现DAO编程(上)
代码编写
1.编写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
-->
<setting name="useColumnLabel" value="true"/>
<!-- 开启驼峰命名转换:Table(create_time) -> Entity(createTime) -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
2.编写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="com.myimooc.seckill.dao.SeckillDao">
<!-- 目的:为DAO接口方法提供sql语句配置 -->
<update id="reduceNumber">
<!-- 具体sql -->
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 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>
<!-- mybatis调用存存储过程 -->
<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>
4-5 MyBatis实现DAO编程(下)
代码编写
1.编写SuccessSeckilledDao.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.myimooc.seckill.dao.SuccessKilledDao">
<insert id="insertSuccessKilled">
<!-- 忽略主键冲突,报错 -->
insert ignore into success_killed(seckill_id,user_phone,state)
values (#{seckillId},#{userPhone},0)
</insert>
<select id="queryByIdWithSeckill" resultType="SuccessSeckilled">
<!-- 根据id查询SuccessKilled并携带秒杀产品对象实体 -->
<!-- 如何告诉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.seckill_id",
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-6 MyBatis整合Spring理论
整合目标
更少的编码:只写接口,不写实现
更少的配置:别名、配置扫描、dao实现
足够的灵活性:自己定制SQL、自由传参、结果集自动赋值
XML提供SQL、DAO接口Mapper
4-7 MyBatis整合Spring编码
代码编写
1.编写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.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="${jdbc.drver}" />
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.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="acquireRetryAttempts" value="2"/>
</bean>
<!-- 3:配置SqlSessionFactory对象 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource" />
<!-- 配置MyBatis全局配置文件:mybatis-config.xml -->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
<!-- 扫描entity包 使用别名 -->
<property name="typeAliasesPackage" value="com.myimooc.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"/>
<!-- 给出需要扫描Dao接口包 -->
<property name="basePackage" value="com.myimooc.seckill.dao"/>
</bean>
<bean id="redisDao" class="com.myimooc.seckill.dao.cache.RedisDao">
<constructor-arg index="0" value="localhost"/>
<constructor-arg index="1" value="6379"/>
</bean>
</beans>
2.编写jdbc.properties
jdbc.drver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/seckill?userUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=root
4-8 单元测试(上)
代码编写
1.编写SeckillDaoTest类
package com.myimooc.seckill.dao;
import com.myimooc.seckill.entity.Seckill;
import org.junit.Test;
import org.junit.runner.RunWith;
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;
/**
* SeckillDao单元测试类
* 配置spring和junit整合,junt启动时加载springIOC容器
* spring-test,junit
*/
@RunWith(SpringJUnit4ClassRunner.class)
// 告诉junit spring配置文件
@ContextConfiguration("classpath:spring/spring-dao.xml")
public class SeckillDaoTest {
// 注入Dao实现依赖
@Resource
private SeckillDao seckillDao;
@Test
public void queryById() throws Exception {
long id = 1000;
Seckill seckill = seckillDao.queryById(id);
System.out.println(seckill.getName());
System.out.println(seckill);
}
@Test
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);
}
}
4-9 单元测试(下)
代码编写
1.编写SuccessSeckilledDaoTest类
package com.myimooc.seckill.dao;
import com.myimooc.seckill.entity.SuccessSeckilled;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* SuccessKilledDao单元测试类
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-dao.xml")
public class SuccessKilledDaoTest {
@Autowired
private SuccessKilledDao successKilledDao;
@Test
public void insertSuccessKilled() throws Exception {
long id = 1001L;
long phone = 13502123541L;
int insertCount = successKilledDao.insertSuccessKilled(id,phone);
System.out.println(insertCount);
}
@Test
public void queryByIdWithSeckill() throws Exception {
long id = 1001L;
long phone = 13502123541L;
SuccessSeckilled successSeckilled = successKilledDao.queryByIdWithSeckill(id,phone);
System.out.println(successSeckilled);
System.out.println(successSeckilled.getSeckill());
}
}
点击查看更多内容
7人点赞
评论
共同学习,写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦