秒杀系统练习及问题总结——Dao层

秒杀系统项目体系结构（Dao层）

pom.xml文件

<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.seckill</groupId>
  <artifactId>seckill</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>seckill Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>

    <!--单元测试-->
    <dependency>
      <!--使用Junit4-->
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <!--补全项目依赖-->
    <!--1.日志 java日志：slf4j,log4j,logback,common-logging
      slf4j是规范/接口
      日志实现：log4j,logback,common-logging
      这个项目使用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>

    <!--实现slf4j接口并整合-->
    <dependency>
      <groupId>ch.qos.logback</groupId>
      <artifactId>logback-classic</artifactId>
      <version>1.1.1</version>
    </dependency>

    <!--1.数据库相关依赖-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.37</version>
      <scope>runtime</scope>
    </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>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.3</version>
    </dependency>

    <!--3.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>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.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>

  </dependencies>
  <build>
    <finalName>seckill</finalName>
  </build>
</project>

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>

spring-dao.xml配置（主要配置的是Spring的dao层的相关配置，包括和mybatis的整合）

<?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"/><!--最大的连接数默认的是15-->
        <property name="minPoolSize" value="10"/>
        <!--关闭连接后不自动的提交-->
        <property name="autoCommitOnClose" value="false"/>
        <!--获取链接超时时间-->
        <property name="checkoutTimeout" value="1000"/>
        <!--获取链接失败重试次数-->
        <property name="acquireRetryAttempts" value="5"/>
    </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包使用别名：例如：com.seckill.entity.Seckill——》Seckill-->
        <property name="typeAliasesPackage" value="com.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.seckill.dao"/>
    </bean>

</beans>

这是一个mapper.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.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,start_time,end_time,create_time
        from seckill
        order by create_time DESC
        limit #{offset},#{limit}
    </select>

</mapper>

dao层的一个类（只写接口，在给一个方法添加注释的时候使用的快捷键是AIL+Enter,在形参是两个及两个以上的的时候要使用@Param()来进行标示，一个的使用可以不用。

package com.seckill.dao;

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

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

/**
 * Created by WangKai's Hands.
 * User:WangKai
 * Date:2018/2/21
 * Time:15:06
 * Email: 18729354508@163.com
 */
/*有关Seckill的Dao层接口*/
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);
}

jdbc.properties配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf-8
jdbc.username=root
jdbc.password=123456

必须按照官网的这个方式来！惨痛的教训惨痛的教训

下面的是一个Junit的测试类，这也是出现的最大的问题就是JDBC一直连接不上，主要的问题就是视频上面的和官网上面的配置的时候有些是不同的 ，就比如说这个jdbc.url。惨痛的教训，特此贴一个博客，谢谢这位哥们：

http://blog.csdn.net/sinat_27406925/article/details/70666380

package com.seckill.dao;

import com.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;

import static org.junit.Assert.*;

/**
 * Created by WangKai's Hands.
 * User:WangKai
 * Date:2018/2/21
 * Time:22:25
 * Email: 18729354508@163.com
 */
/*
* 配置Spring和Junit的整合，是为了能够在JUnit的启动的时候我们能够自动的加载SpringIOC的容器
* 我们有两个依赖分别是spring-test,junit
* */

//RunWith 是一Junit依赖,作用是在Junit启动的时候去加载我们这个Spring容器
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit我们的Spring的配置文件是在哪里？
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SeckillDaoTest {

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

    @Test
    
     /*输出结果以及mybatis的运行
    
        23:40:33.457 [main] DEBUG o.m.s.t.SpringManagedTransaction - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@2a551a63] will not be managed by Spring
        23:40:33.486 [main] DEBUG c.s.dao.SeckillDao.reduceNumber - ==>  Preparing: *//*具体的SQL语句*//* UPDATE seckill SET number = number - 1 WHERE seckill_id = ? and start_time <= ? and end_time >= ? and number > 0; 
        23:40:33.599 [main] DEBUG c.s.dao.SeckillDao.reduceNumber - ==> Parameters: 1000(Long), 2018-02-21 23:40:33.051(Timestamp), 2018-02-21 23:40:33.051(Timestamp)
        23:40:33.677 [main] DEBUG c.s.dao.SeckillDao.reduceNumber - <==    Updates: 0
        23:40:33.677 [main] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4c4d362a]
        updateCount+=0
       */
    public void reduceNumber() throws Exception {
        Date killTime = new Date();
        int updateCount = seckillDao.reduceNumber(1000L,killTime);
        System.out.println("updateCount+="+updateCount);
    }

    @Test
    public void queryById() throws Exception {
        long id = 1002;
        Seckill seckill = seckillDao.queryById(id);
        System.out.println(seckill.getName());
        System.out.println(seckill);
        /*
        输出结果：
        300元秒杀小米4
        Seckill{
        seckillId=1002,name='300元秒杀小米4', number=300,
         startTime=Thu Nov 01 00:00:00 CST 2018,
         endTime=Fri Nov 02 00:00:00 CST 2018,
         createTime=Wed Feb 21 14:21:02 CST 2018}*/
    }

    @Test
    /*
    * org.mybatis.spring.MyBatisSystemException: nested exception is
    * org.apache.ibatis.binding.BindingException: Parameter 'offset' not found.
     * Available parameters are [0, 1, param1, param2]
     * java没有保存形参的记录，queryAll(int offset,int limit)——》query(org0,org1)
     * */
    /*
    *运行结果：
     *  Seckill{seckillId=1000, name='1000元秒杀iphone6', number=0, startTime=Thu Nov 01 00:00:00 CST 2018, endTime=Fri Nov 02 00:00:00 CST 2018, createTime=Wed Feb 21 14:21:02 CST 2018}
    Seckill{seckillId=1001, name='500元秒杀ipad2', number=0, startTime=Thu Nov 01 00:00:00 CST 2018, endTime=Fri Nov 02 00:00:00 CST 2018, createTime=Wed Feb 21 14:21:02 CST 2018}
    Seckill{seckillId=1002, name='300元秒杀小米4', number=0, startTime=Thu Nov 01 00:00:00 CST 2018, endTime=Fri Nov 02 00:00:00 CST 2018, createTime=Wed Feb 21 14:21:02 CST 2018}
    Seckill{seckillId=1003, name='200元秒杀红米note', number=0, startTime=Thu Nov 01 00:00:00 CST 2018, endTime=Fri Nov 02 00:00:00 CST 2018, createTime=Wed Feb 21 14:21:02 CST 2018}
    * */
    public void queryAll() throws Exception {
        List<Seckill> seckills = seckillDao.queryAll(0,100);
        for (Seckill seckill:seckills) {
            System.out.println(seckill);
        }
    }

}

手写SQL脚本：

--创建数据库seckill
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元秒杀iphone6',100,'2018-11-01 00:00:00','2018-11-02 00:00:00'),
  ('500元秒杀ipad2',200,'2018-11-01 00:00:00','2018-11-02 00:00:00'),
  ('300元秒杀小米4',300,'2018-11-01 00:00:00','2018-11-02 00:00:00'),
  ('200元秒杀红米note',400,'2018-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='秒杀成功明细表';

--为什么手写DDL
--一般我们所有的项目都会有一个schema.sql 这样的一个文件来记录每次上线的DDL修改
--上线v1.1版本
ALTER TABLE seckill;
DROP INDEX idx_create_time;
ADD INDEX idx_c_s(start_time,create_time);

--上线v1.2
--dd1

备注：

https://docs.spring.io/spring/docs/

Spring的各个版本的官方文档

在IDEA中生成一个类的测试方法的快捷键是Ctrl+shift+t

一定要手写SQL脚本，多多实用Linux系统，不要使用MySQL的Client。

未完待续！