从零开发高并发秒杀系统（一）DAO层
建议从官网获取配置，项目用到的官方文档地址：
logback配置：http://logback.qos.ch/manual/configuration.html
spring配置：http://docs.spring.io/spring/docs/
mybatis配置：http://mybatis.github.io/mybatis-3/zh/index.html
项目使用环境：eclipse+maven+mysql+spring+springmvc+mybatis+redis
一、项目依赖
1、单元测试相关依赖：建议Junit依赖的版本使用4.0以上，因为Junit3.0默认使用编程的方式运行Junit，而Junit4.0默认使用注解的方式运行Junit，如下
<dependency>
<!-- 使用junit4 -->
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
2、日志相关依赖：使用slf4j+logback。java日志包有slf4j、logback、log4j、commons-logging，slf4j是接口/规范，针对我们编程，即我们的应用程序中import的类都是slf4j的类，logback、log4j、commons-logging则是slf4j是接口/规范的具体日志实现。
<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>
<!-- 实现slf4j接口并整合 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.1.1</version>
</dependency>
3、数据库相关依赖：采用c3p0数据库连接池。因为mysql数据库驱动只有在运行时才会用到，所以把范围设置为runtime
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
<scope>runtime</scope>
</dependency>
<!-- 数据库连接池 -->
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
4、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>
5、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.5.4</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
6、spring相关依赖：使用4.1.7.RELEASE版本
(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)spring web层依赖
<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>
7、jsp页面相关依赖(如果报错：Multiple annotations found at this line:- javax.servlet.jsp cannot be resolved to a type添加此依赖)
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
二、秒杀业务分析
秒杀业务的核心：库存的处理
秒杀业务： 1、减库存 2、记录购买明细 构成一个完整事务，最后进行数据落地，即所有相关数据都更新或插入到mysql中，因为事务机制依然是目前最可靠的数据落地方案。
秒杀业务的难点：热点商品的高并发竞争，反映到技术上就是 Mysql的事务+行级锁
秒杀的事务分析：start Transaction -> update 库存数量 -> insert 购买明细 -> commit/rollback
竞争出现在 update 库存数量
秒杀的难点：如何高效的处理竞争
三、数据库表设计
--数据库初始化脚本
--创建数据库
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元秒杀iphone8',100,'2018-05-01 00:00:00','2018-05-02 00:00:00',
'500元秒杀ipad',200,'2018-05-01 00:00:00','2018-05-02 00:00:00',
'100000元秒杀奔驰',10,'2018-05-01 00:00:00','2018-05-02 00:00:00',
'200元秒杀红米note',300,'2018-05-01 00:00:00','2018-05-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 comment '状态标识 -1：无效 0：成功 1：已付款',
`create_time` timestamp not null '创建时间',
primary key(seckill_id,user_phone),/*联合主键:防止重复秒杀*/
key idx_create_time(create_time),
)engine=InnoDB default charset=utf8 comment='秒杀成功明细表';
四、DAO层实体和接口编码
实体类
package com.jiangwangxiang.entity;
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 + "]";
}
}
package com.jiangwangxiang.entity;
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 + "]";
}
}
dao接口
注意：java没有保存形参的记录，所以像 int reduceNumber(long seckillId, Date killTime); 这样的方法中的seckillId会被arg0代替，killTime会被arg1代替(只有一个方法的参数不会出现这样的问题)，依次类推下去，这样会
导致mybatis的mapper文件中的sql语句中无法绑定seckillId、killTime等这样的参数从而执行sql出错
解决方法：使用param注解，并在注解中设置形参，像这样
是使用
package com.jiangwangxiang.dao;
import java.util.Date;
import java.util.List;
import com.jiangwangxiang.entity.Seckill;
//秒杀库存DAO接口
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);
}
package com.jiangwangxiang.dao;
import com.jiangwangxiang.entity.SuccessKilled;
public interface SuccessKilledDao {
//插入购买明细，可过滤重复
int insertSuccessKilled(@Param("seckillId") long seckillId, @Param("userPhone") long userPhone);
//根据id查询SuccessKilled并携带秒杀产品对象实体
SuccessKilled queryByIdWithSeckill(@Param("seckillId") long seckillId, @Param("userPhone") long userPhone);
}
基于mybatis实现DAO
mybatis配置文件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 -->
<setting name="useColumnLabel" value="true" />
<!-- 开启驼峰命名转换:Table{create_time} -> Entity{createTime} -->
<setting name="mapUnderscoreToCamelCase" value="true" />
</settings>
</configuration>
DAO接口对应的mapper文件
SeckillDao.xml 注意：xml中不允许直接写<=，必须进行转换，方法为： <![CDATA[ <= ]]>
<?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.jiangwangxiang.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>
</mapper>
SuccessKilledDao.xml 注意：insert语句如果主键冲突会报错，所以使用 INSERT ignore ，这样的话即使主键冲突也不会报错，只是插入不会成功而已
<?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.jiangwangxiang.dao.SuccessKilledDao">
<insert id="insertSuccessKilled">
<!-- 主键冲突，报错 -->
INSERT ignore INTO success_killed (seckill_id, user_phone, state)
VALUES (#{seckillId}, #{userPhone}, 0)
</insert>
<select id="queryByIdWithSeckill" resultType="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.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>
mybatis整合spring XML提供SQL+DAO接口Mapper
spring-dao.xml 注意：src/main/java 和 src/main/resources目录都是classpath
<?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.driver}" />
<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="10000" />
<!-- 当获取连接失败重试次数 -->
<property name="acquireRetryAttempts" value="2" />
</bean>
<!-- 3.配置SqlSessionFactory对象 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据库连接池 -->
<property name="dataSource" ref="dataSource" />
<!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
<property name="configLocation" value="classpath:mybatis-config.xml" />
<!-- 扫描entity包 使用别名 -->
<property name="typeAliasesPackage" value="com.jiangwangxiang.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.jiangwangxiang.dao" />
</bean>
</beans>
五、DAO层单元测试
@RunWith(SpringJUnit4ClassRunner.class)的意思是junit启动时加载springIOC容器
@ContextConfiguration({ "classpath:spring/spring-dao.xml", "classpath:spring/spring-service.xml" })的意思是告诉junit spring配置文件的位置
package com.jiangwangxiang.seckill;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* 配置spring和junit整合，junit启动时加载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 testQueryById() throws Exception {
long id = 1000;
Seckill seckill = seckillDao.queryById(id);
System.out.println(seckill.getName());
System.out.println(seckill);
}
@Test
public void testQueryAll() throws Exception {
List<Seckill> seckills = seckillDao.queryAll(0, 100);
for (Seckill seckill : seckills) {
System.out.println(seckill);
}
}
@Test
public void testReduceNumber() throws Exception {
Date killTime = new Date();
int updateCount = seckillDao.reduceNumber(1000L, killTime);
System.out.println("updateCount=" + updateCount);
}
}
package com.jiangwangxiang.seckill;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* 配置spring和junit整合，junit启动时加载springIOC容器 spring-test,junit
*/
@RunWith(SpringJUnit4ClassRunner.class)
// 告诉junit spring配置文件
@ContextConfiguration({ "classpath:spring/spring-dao.xml" })
public class SuccessKilledDaoTest {
@Resource
private SuccessKilledDao successKilledDao;
@Test
public void testInsertSuccessKilled() throws Exception {
/**
* 第一次:insertCount=1
* 第二次:insertCount=0
*/
long id = 1001;
ong 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());
}
}
浙公网安备 33010602011771号