spring秒杀

– 创建数据库
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,‘2016-01-01 00:00:00’,‘2016-01-02 00:00:00’),
(‘800元秒杀ipad’,200,‘2016-01-01 00:00:00’,‘2016-01-02 00:00:00’),
(‘6600元秒杀mac book pro’,300,‘2016-01-01 00:00:00’,‘2016-01-02 00:00:00’),
(‘7000元秒杀iMac’,400,‘2016-01-01 00:00:00’,‘2016-01-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 ‘状态标识👎无效 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=‘秒杀成功明细表’;
2、实现MyBatis配置文件并且交由Spring托管
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"/>
       <!--开启驼峰命名法 字段名seckill_Id 对应属性名seckillId -->
       <setting name="mapUnderscoreToCamelCase" value="true"/>
   </settings>
</configuration>

spring-dao.xml
   这里关键是sqlsessionfactory的配置，需要指定mybatis全局配置文件、mapper.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">

    <!--1、配置数据库相关参数-->
    <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}"/>

        <!--数据库连接池最大连接数-->
        <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="3"/>
    </bean>

    <!--3、配置SqlSessionFactory-->
    <bean id="sessionfactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!--mybatis全局配置文件-->
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <!--扫描entity包-->
        <property name="typeAliasesPackage" value="com.seckill.entity"/>
        <!--扫描sql xml文件-->
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    </bean>

    <!--4、配置扫描Dao接口包，动态实现dao接口注入到spring容器-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--需要扫描的dao接口-->
        <property name="basePackage" value="com.seckill.dao"/>
        <!--注入sqlsessionfactory-->
        <property name="sqlSessionFactoryBeanName" value="sessionfactory"/>
    </bean>
</beans>

1、实现接口。这里注意Param注解，当方法只有一个参数时不用指定，如果你需要给mapper里传多个参数则指定，也可以用HashMap传参。

public interface SeckillDao {  
   /**减库存**/    
  int reduceNumber(@Param("seckillId") long seckillId,@Param("killTime") Date killTime);     
   /**查询秒杀商品详情**/  
  Seckill queryById(long seckillId);  
        /**查询所有秒杀商品**/  
  List<Seckill> queryAll(@Param("offset") int offset,@Param("limit") int limit); 
  }
  public interface SuccessKillDao { 
      /**     * 插入秒杀商品明细     * **/ 
     int insertSuccessKilled(@Param("seckillId") long seckillId,@Param("userPhone")  long userPhone);     
     /**     * 根据商品id查询商品秒杀明细，并且同时返回商品明细     * **/  
       SuccessKilled queryByIdWithSeckill(@Param("seckillId") long seckillId,@Param("userPhone")  long userPhone);
        }

2、秒杀实现。也就是mapper目录下的*.xml文件。
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.seckill.dao.SeckillDao">
<update id="reduceNumber">
        update seckill set number=number-1
        where seckill_Id=#{seckillId}
        and start_time <![CDATA[ <= ]]> #{killTime}
        and end_time <![CDATA[ >= ]]> #{killTime}
        and number>0
</update>

<select id="queryById" resultType="Seckill">
        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
        limit #{offset},#{limit}
</select>
</mapper>

SuccessKillDao.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.SuccessKillDao">


<!--主键重复时不再插入数据-->
<insert id="insertSuccessKilled">
insert ignore into  success_killed (seckill_id,user_Phone,state) values (#{seckillId},#{userPhone},0)
</insert>

<select id="queryByIdWithSeckill" resultType="SuccessKilled">
        select
        sk.seckill_id,
        sk.user_Phone,
        sk.state,
        sk.create_time,
        s.seckill_id "seckill.seckill_id",
        s.name "seckill.name",
        s.number "seckill.number",
        s.create_time "seckill.create_time",
        s.start_time "seckill.start_time",
        s.end_time "seckill.end_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>