Java高并发秒杀API之业务分析与DAO层

--数据库初始化

--创建数据库
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='描述库存表';

--创建索引，用于加速查询
--key idx_start_time(start_time),
--key idx_end_time(end_time),   
--key idx_create_time(create_time)

--默认mysql有多种存储引擎，但是可以支持事物的只有InnoDB,所以在这里通过显示的语法去告诉mysql这个表的ENGINE引擎是InnoDB
--由于数据库采用的是自增作为主键，给出初始的自增id:AUTO_INCREMENT=1000
--默认编码 DEFAULT CHARSET=utf-8 
--表的注释 COMMENT='秒杀库存表'

--初始化数据
insert into 
    seckill(name,number,start_time,end_time)
value
    ('1000元秒杀iphone6',100,'2012-12-12 00:00:00','2012-12-13 00:00:00'),
    ('500元秒杀ipad2',200,'2012-12-12 00:00:00','2012-12-13 00:00:00'),
    ('300元秒杀mi4',300,'2012-12-12 00:00:00','2012-12-13 00:00:00'),
    ('200元秒杀红米note',400,'2012-12-12 00:00:00','2012-12-13 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='秒杀成功明细表';

--连接数据库控制台
mysql -uroot -p123456

--查看创建表的结构
show create table seckill\G

--为什么手写DDL
--记录每次上线的DDL修改
--上线V1.1
ALTER TABLE seckill
DROP INDEX idx_create_time,
ADD INDEX idx_c_s(start_time,create_time);


--上线V1.2
--DDL

package org.seckill.entity;

import java.util.Date;
/**
 * 秒杀库存实体
 * @author Administrator
 *
 */

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 org.seckill.entity;

import java.util.Date;

/**
 * 秒杀成功明细实体
 */
public class SuccessKilled {

    /**
     * 一个秒杀seckill对应多个成功记录
     */
    private Seckill seckill;

    private long seckillId;

    private long userPhone;

    private short state;

    private Date createTime;

    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{" +
                "seckill=" + seckill +
                ", seckillId=" + seckillId +
                ", userPhone=" + userPhone +
                ", state=" + state +
                ", createTime=" + createTime +
                '}';
    }
}

package org.seckill.dao;

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

import org.seckill.entity.Seckill;

public interface SeckillDao {

    /**
     * 减库存
     * @param seckillId
     * @param killTime
     * @return 如果更新行数大于1,表示更新的行数
     */
    int reduceNumber(long seckillId,Date killTime);

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


    /**
     * 根据偏移量查询描述列表
     * @param offet
     * @param limit
     * @return
     */
    List<Seckill> queyAll(int offet,int limit);

}

package org.seckill.dao;

import org.seckill.entity.SuccessKilled;

public interface SuccessKilledDao {

    /**
     * 插入购买明细（可过滤重复）
     * @param seckillId
     * @param userPhone
     * @return
     */
    int insertSuccessKilled(long seckillId,long userPhone);

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



}

<?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的getGeneratekeys获取自增主键值，当inert一条记录时我们是不插入id的，id是通过自增去赋值的，当插入完后想得到该插入记录的id时可以调用jdbc的getGeneratekeys -->
        <setting name="useGeneratedKeys" value="true" />

        <!--使用列别名替换别名 默认true select name as title form table; -->
        <setting name="useColumnLabel" value="true" />
        <!--开启驼峰命名转换Table:create_time到 Entity(createTime) -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
</configuration>

<?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">

<!-- namespace:指定为那个接口提供配置 --> 
<mapper namespace="org.seckill.dao.SeckillDao">
    <!-- 目的：为DAO接口方法提供sql语句配置  -->  

    <!-- int reduceNumber(long seckillId, Date killTime);-->
    <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>

    <!-- parameterType:参数类型，正常情况java表示一个类型的包名+类名，这直接写类名，因为后面有一个配置可以简化写包名的过程 -->
    <!--   Seckill queryById(long seckillId);-->
    <select id="queryById" resultType="Seckill" parameterType="long">
        <!-- 可以通过别名的方式列明到java名的转换，如果开启了驼峰命名法就可以不用这么写了 
        select seckill_id as seckillId
        -->

        SELECT seckill_id,name,number,start_time,end_time,create_time
        FROM seckill
        WHERE seckill_id = #{seckillId}
    </select>

    <!-- List<Seckill> queryAll(int offset,int limit);-->
    <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>  

<?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">

    <!--通过ignore关键字将主键冲突时的报错改为返回0-->
    <!--int insertSuccessKilled(long seckilledId,long userPhone);-->
    <update id="insertSuccessKilled">
       INSERT ignore INTO success_killed(seckill_id,user_phone,state)VALUES (#{seckilledId},#{userPhone},1)
    </update>

    <!-- SuccessKilled queryByIdWithSeckill(@Param("seckilledId") long seckilledId, @Param("userPhone") long userPhone);-->
    <select id="queryByIdWithSeckill" resultType="SuccessKilled">
      SELECT
      sk.seckill_id,sk.user_phone,sk.create_time,sk.state,
      s.seckill_id "seckill.seckill_id",s.name "seckill.name", 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=#{seckilledId} and sk.user_phone=#{userPhone};
    </select>

</mapper>

<?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过程-->

    <!--１.配置数据库相关参数-->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--2.数据库连接池-->
    <!--todo java.sql.SQLException: Access denied for user ''@'localhost' (using password: NO)-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
                    <!-- 配置连接池属性 -->
                    <property name="driverClass" value="${driverClassName}"></property>
                    <property name="jdbcUrl" value="${jdbc.url}"></property>
                    <property name="user" value="${jdbc.username}"></property>
                    <property name="password" value="${jdbc.password}"></property>
<!-- 
            <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test_mysql"></property>
            <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/test_mysql"></property>
            <property name="user" value="root"></property>
            <property name="password" value="111111"></property>
 -->
            <!-- c3p0连接池的私有属性 -->
            <property name="maxPoolSize" value="30"></property>
            <property name="minPoolSize" value="10"></property>
            <!-- 关闭连接后不自动commit -->
            <property name="autoCommitOnClose" value="false"></property>
            <!--  获取连接超时时间 -->
            <property name="checkoutTimeout" value="10000"></property>
            <!--  获取连接重试次数 -->
            <property name="acquireRetryAttempts" value="3"></property>
        </bean>
<!-- 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/test_mysql"/>
        <property name="username" value="root"/>
        <property name="password" value="111111"/>
        todo
           <property name="driverClassName" value="${driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
    </bean>
 -->
    <!--３.配置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="org.seckill.entity"/>
        <!--扫描sql配置文件:mapper需要的xml文件-->
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    </bean>

    <!--４:配置扫描Dao接口包,动态实现DAO接口,注入到spring容器-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--注入SqlSessionFactory-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!-- 给出需要扫描的Dao接口-->
        <property name="basePackage" value="org.seckill.dao"/>
    </bean>

    <!--RedisDao-->
    <!-- <bean id="redisDao" class="org.seckill.dao.cache.RedisDao">
        <constructor-arg index="0" value="localhost"/>
        <constructor-arg index="1" value="6379"/>
    </bean> -->

</beans>

package org.seckill.dao;

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

import javax.annotation.Resource;

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;

/**
 * Created by wchb7 on 16-5-8.
 */

/**
 * 配置Spring和Junit整合,junit启动时加载springIOC容器
 * spring-test,junit
 */

@RunWith(SpringJUnit4ClassRunner.class)
@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);
    }


    @Test
    public void testQueryAll() throws Exception {
//      Java没有保存形参的记录:QueryAll(int offset,int limit)->QueryAll(arg0,arg1);
//      因为java形参的问题,多个基本类型参数的时候需要用@Param("seckillId")注解区分开来
        List<Seckill> seckills = seckillDao.queryAll(0, 4);
        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 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.*;

/**
 * Created by wchb7 on 16-5-9.
 */


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring/spring-dao.xml")
public class SuccessKilledDaoTest {

    @Resource
    private SuccessKilledDao successKilledDao;

    @Test
    public void testInsertSuccessKilled() throws Exception {
        /*
         第一次：iinsertCount=1
         第一次：iinsertCount=0
         */
        long id = 1000L;
        long phone = 15811112222L;
        int insertCount = successKilledDao.insertSuccessKilled(id, phone);
        System.out.println("insertCount: " + insertCount);
    }

    @Test
    public void testQueryByIdWithSeckill() throws Exception {

        long id = 1000L;
        long phone = 15811112222L;
        SuccessKilled successKilled = successKilledDao.queryByIdWithSeckill(id, phone);
        System.out.println(successKilled);
        if (successKilled != null) {
            System.out.println(successKilled.getSeckill());
        }
    }
}