DAO(Data Access Object) 数据访问对象
首先需要创建秒杀库存表和秒杀成功明细表，如下所示：
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,'2017-04-30 00:00:00','2017-06-02 00:00:00'),
('500元秒杀ipad2',200,'2017-04-30 00:00:00','2017-06-02 00:00:00'),
('300元秒杀小米4',300,'2017-04-30 00:00:00','2017-06-02 00:00:00'),
('200元秒杀红米note',400,'2017-05-30 00:00:00','2017-06-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='秒杀成功明细表';
dao包需要的相关类名和函数名如表6-9所示。
SeckillDao.java:
public interface SeckillDao {
int reduceNumber(@Param("seckillId") long seckillId,@Param("killTime") Date killTime);
Seckill queryById(long seckillId);
List<Seckill> queryAll(@Param("offset") int offet, @Param("limit") int limit);
void killByProcedure(Map<String,Object> paramMap);
}
SuccessKilledDao.java:
public interface SuccessKilledDao {
int insertSuccessKilled(@Param("seckillId") long seckillId ,@Param("userPhone") long userPhone);
SuccessKilled queryByIdWithSeckill(@Param("seckillId") long seckillId, @Param("userPhone") long userPhone);
}
基于MyBatis来实现我们设计的Dao层接口。首先需要配置我们的MyBatis，在resources包下创建MyBatis全局配置文件mybatis-config.xml文件。
<configuration>
<settings>
<setting name="useGeneratedKeys" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
</configuration>
配置文件创建好后我们需要关注的是Dao接口该如何实现，mybatis为我们提供了mapper动态代理开发的方式为我们自动实现Dao的接口。在mapper包下创建对应Dao接口的xml映射文件，里面用于编写我们操作数据库的sql语句，SeckillDao.xml和SuccessKilledDao.xml。
SeckillDao.xml:
<mapper namespace="org.seckill.dao.SeckillDao">
<update id="reduceNumber">
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>
<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>
SuccessKilledDao.xml:
<mapper namespace="org.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="SuccessKilled">
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的整合，整合目标:
（1）更少的编码:只写接口，不写实现类。
（2）更少的配置:别名、配置扫描映射xml文件、dao实现。
（3）足够的灵活性:自由定制SQL语句、自由传结果集自动赋值。
在spring包下创建一个spring-dao.xml，用于配置dao层对象的配置文件，在resources包下创建jdbc.properties.xml，用于配置数据库的连接信息，配置如下。
spring-dao.xml：
<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">
<context:property-placeholder location="classpath:jdbc.properties"/>
<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"/>
<property name="autoCommitOnClose" value="false"/>
<property name="checkoutTimeout" value="1000"/>
<property name="acquireRetryAttempts" value="2"/>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
<property name="typeAliasesPackage" value="org.seckill.entity"/>
<property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
<property name="basePackage" value="org.seckill.dao"/>
</bean>
<bean id="redisDao" class="org.seckill.dao.cache.RedisDao">
<constructor-arg index="0" value="localhost"/>
<constructor-arg index="1" value="6379"/>
</bean>
</beans>
jdbc.properties.xml:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/seckill?useUnicode=true&characterEncoding=utf8
jdbc.username=root
jdbc.password=123456
