Java秒杀简单设计二：数据库表Dao层设计
上一篇中搭建springboot项目环境和设计数据库表 https://www.cnblogs.com/taiguyiba/p/9791431.html
在此基础上设计数据库表Dao层代码
1.数据库表设计
项目涉及到两张表，seckill:秒杀库存表，success_killed:秒杀成功明细表
seckill:秒杀库存表
CREATE TABLE `seckill` ( `seckill_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商品库存ID', `name` varchar(120) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商品名称', `number` int(11) NOT NULL COMMENT '库存数量', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '秒杀开始时间', `end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '秒杀结束时间', PRIMARY KEY (`seckill_id`) USING BTREE, INDEX `idx_start_time`(`start_time`) USING BTREE, INDEX `idx_end_time`(`end_time`) USING BTREE, INDEX `idx_create_time`(`create_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1000 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '秒杀库存表';
插入数据：
INSERT INTO `seckill` VALUES (1000, '1000元秒杀iphone6', 100, '2018-10-14 22:36:57', '2016-01-01 00:00:00', '2016-01-02 00:00:00'); INSERT INTO `seckill` VALUES (1001, '800元秒杀ipad', 200, '2018-10-14 22:36:57', '2016-01-01 00:00:00', '2016-01-02 00:00:00'); INSERT INTO `seckill` VALUES (1002, '6600元秒杀mac book pro', 300, '2018-10-14 22:36:57', '2016-01-01 00:00:00', '2016-01-02 00:00:00'); INSERT INTO `seckill` VALUES (1003, '7000元秒杀iMac', 400, '2018-10-14 22:36:57', '2016-01-01 00:00:00', '2016-01-02 00:00:00');
success_killed:秒杀成功明细表
CREATE TABLE `success_killed` ( `seckill_id` bigint(20) NOT NULL COMMENT '秒杀商品ID', `user_phone` bigint(20) NOT NULL COMMENT '用户手机号', `state` tinyint(4) NOT NULL DEFAULT -1 COMMENT '状态标识:-1:无效 0:成功 1:已付款 2:已发货', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`seckill_id`, `user_phone`) USING BTREE, INDEX `idx_create_time`(`create_time`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '秒杀成功明细表';
2.Dao设计
首先在 SeckillApplication.java文件中添加Mapper扫描注解@MapperScan("com.seckill.dao")
package com.seckill;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.seckill.dao")
public class SeckillApplication {
public static void main(String[] args) {
SpringApplication.run(SeckillApplication.class, args);
}
}
创建SeckillDao.java文件：
包括减库存、查询、使用存储过程秒杀等方法
package com.seckill.dao;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.annotations.Param;
import com.seckill.entity.Seckill;
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(@Param("seckillId") long seckillId);
/**
* 根据偏移量查询秒杀商品列表
* @param offset
* @param limit
* @return
*/
List<Seckill> queryAll(@Param("offset") int offset,@Param("limit")int limit);
/**
* 使用存储过程执行秒杀
* @param paramMap
*/
void killByProcedure(Map<String,Object> paramMap);
}
注意：Java没有保存形参的记录，queryAll(int offset,int limit) ==> queryAll(arg0,arg1),所以在多个参数的时候，需要使用注解：@Param
创建 SuccessKilledDao.java文件
package com.seckill.dao;
import org.apache.ibatis.annotations.Param;
import com.seckill.entity.SuccessKilled;
public interface SuccessKilledDao {
/**
* 插入购买明细，可过滤重复
* @param seckillId
* @param userPhone
* @return 插入的行数
*/
int insertSuccessKilled(@Param("seckillId") long seckillId,@Param("userPhone") long userPhone);
/**
* 根据秒杀商品ID查询明细SuccessKilled对象， 携带了Seckill秒杀产品对象
* @param seckillId
* @param userPhone
* @return
*/
SuccessKilled queryByIdWithSeckill(@Param("seckillId") long seckillId,@Param("userPhone") long userPhone);
}
创建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 >= #{killTime}
AND number > 0
</update>
<select id="queryById" resultType="Seckill" parameterType="long">
SELECT *
FROM seckill
WHERE seckill_id = #{seckillId}
</select>
<select id="queryAll" resultType="Seckill">
SELECT *
FROM seckill
ORDER BY create_time DESC
limit #{offset},#{limit}
</select>
<!-- 使用mybatis调用存储过程 -->
<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文件
<?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.SuccessKilledDao">
<insert id="insertSuccessKilled">
<!--当出现主键冲突时(即重复秒杀时)，会报错;不想让程序报错，加入ignore-->
INSERT ignore INTO success_killed(seckill_id,user_phone,state)
VALUES (#{seckillId},#{userPhone},0)
</insert>
<select id="queryByIdWithSeckill" resultType="SuccessKilled">
<!--根据seckillId查询SuccessKilled对象，并携带Seckill对象-->
<!--如何告诉mybatis把结果映射到SuccessKill属性同时映射到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>