效果图示例
二话不说,还是老规矩,先看下系统的截图:
列表页:
详情页:
编程环境
- intellj idea
- spring、springMVC
- Mybatis
- mysql
- bootstrap
- jQuery、 jQuery-countdown、jQuery-cookie
结构示意图
pom.xml
由于本文注释十分详细,所以不再累述:
<?xml version="1.0" encoding="UTF-8"?>
<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>
<packaging>war</packaging>
<name>miaosha.seckill</name>
<groupId>org.tianjun.miaosha</groupId>
<artifactId>miaosha.seckill</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<!-- 使用junit4 -->
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- 不全项目依赖 -->
<!-- 1:日志 Java日志:slfj,log4j,logback,common-logging
sl4j:规范/接口
日志实现:log4j,logback,common-logging
使用:sl4j+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.0.13</version>
</dependency>
<!-- 实现sl4j接口并合并 -->
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.0.13</version>
</dependency>
<!-- 2: 数据库相关依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<!-- DAO框架:myBatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<!-- mybatis自身实现的spring整合 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</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.2</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.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- 2): spring dao 层依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- 3): spring web 依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.2.4.RELEASE</version>
</dependency>
<!-- 4): spring test 依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.2.4.RELEASE</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
mysql DDL (schema.sql)
--链接数据库控制台
mysql -uroot -p
-- 初始化数据库脚本
-- 创建数据库
CREATE DATABASE seckill;
-- 使用数据库
use seckill;
-- 创建秒杀库存表
CREATE TABLE seckill(
`seckill_id` bigint NOT NULL AUTO_INCREMENT COMMENT '商品库存表',
`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-11-01 00:00:00','2016-11-02 00:00:00'),
('500元秒杀iPad2',200,'2016-11-01 00:00:00','2016-11-02 00:00:00'),
('300元秒杀小米4',300,'2016-11-01 00:00:00','2016-11-02 00:00:00'),
('200元秒杀红米note2',400,'2016-11-01 00:00:00','2016-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
-- 记录每次上线DDL修改
-- 上线v1.1
ALTER TABLE seckill
DROP INDEX idx_create_time,
ADD index idx_c_s(start_time,create_time);
-- 上线v1.2
-- DDL
Dao层
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1"
metadata-complete="true">
<!-- 修改servlet版本为3.1 -->
<!-- 配置DispatcherServlet -->
<servlet>
<servlet-name>seckill-dispatch</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<!-- 配置springMVC需要加载的配置文件
spring-dao.xml,spring-service.xml,spring-web.xml
Mybatis -> Spring -> SpringMVC
-->
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring/spring-*.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>seckill-dispatch</servlet-name>
<!-- 默认匹配所有的请求-->
<url-pattern>/</url-pattern>
</servlet-mapping>
</web-app>
spring-dao.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">
<!-- 配置整合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"/>
<property name="minPoolSize" value="10"/>
<!-- 关闭连接后不自动commit -->
<property name="autoCommitOnClose" value="false"/>
<!-- 获取连接超时时间 -->
<property name="checkoutTimeout" value="1000"/>
<!-- 当获取连接失败重试次数 -->
<property name="acquireRetryAttempts" value="2"/>
</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包 使用别名 -->
<property name="typeAliasesPackage" value="org.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="org.seckill.dao"/>
</bean>
</beans>
jdbc.properties:
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=123
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>
org.seckill.dao.SeckillDao:
package org.seckill.dao;
import org.apache.ibatis.annotations.Param;
import org.seckill.entity.Seckill;
import java.util.Date;
import java.util.List;
/**
* Created by tianjun on 2016/8/3.
*/
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 offet
* @param limit
* @return
*/
List<Seckill> queryAll(@Param("offset")int offet,@Param("limit")int limit);
}
org.seckill.dao.SuccessKilledDao:
package org.seckill.dao;
import org.apache.ibatis.annotations.Param;
import org.seckill.entity.SuccessKilled;
/**
* Created by tianjun on 2016/8/3.
*/
public interface SuccessKilledDao {
/**
* 插入 购买明细,可过滤重复
* @param seckillId
* @param userPhone
* @return
* 插入的行数
*/
int insertSuccessKilled(@Param("seckillId") long seckillId,@Param("userPhone") long userPhone);
/**
* 根据id查询SuccessKilled并携带秒杀产品对象实体
* @param seckillId
* @param userPhone
* @return
*/
SuccessKilled queryByIdWithSeckill(@Param("seckillId")long seckillId,@Param("userPhone") long userPhone);
}
与这两个Dao接口文档对应的mapper:
mapper/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="org.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,number,start_time,end_time,create_time
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>
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="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">
<!-- 根据id查询successkilled并携带秒杀产品对象实体 -->
<!-- 如何告诉mybatis把结果映射到successkilled同时映射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>
测试:
org.seckill.dao.SeckillDaoTest:
package org.seckill.dao;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.seckill.entity.Seckill;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.Date;
import java.util.List;
import static org.junit.Assert.*;
/**
*
* Created by tianjun on 2016/8/3.
*/
/**
* 配置spring和junit整合,junit启动是加载srpingIOC容器
* spring-test,junit
*/
@RunWith(SpringJUnit4ClassRunner.class)
//告诉junit spring配置文件
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SeckillDaoTest {
//注入Dao实现类依赖
@Autowired
private SeckillDao seckillDao;
@Test
public void testQueryById() throws Exception {
long id = 1000;
Seckill seckill = seckillDao.queryById(id);
System.out.println(seckill.getName());
System.out.println(seckill);
}
@Test
public void testQueryAll() throws Exception {
//java没有保存形参的记录:queryAll(int offset,int limit)-> queryAll(arg0,arg1),
// 因此需要用到mybatis提供的@Param("名称")
List<Seckill> seckills = seckillDao.queryAll(0, 100);
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);
}
}
org.seckill.dao.SuccessKilledDaoTest:
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 tianjun on 2016/8/4.
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SuccessKilledDaoTest {
@Resource
private SuccessKilledDao successKilledDao;
@Test
public void testInsertSuccessKilled() throws Exception {
long id =1001L;
long phone = 18867103573L;
int insertCount = successKilledDao.insertSuccessKilled(id,phone);
System.out.println("insertCount="+insertCount);
}
@Test
public void testQueryByIdWithSeckill() throws Exception {
long id =1001L;
long phone = 18867103573L;
SuccessKilled successKilled = successKilledDao.queryByIdWithSeckill(id, phone);
System.out.println(successKilled);
System.out.println(successKilled.getSeckill());
}
}
org.seckill.entity.Seckill:
package org.seckill.entity;
import java.util.Date;
/**
* Created by tianjun on 2016/8/3.
*/
public class Seckill {
private long seckillId;
private String name;
private int number;
private Date startTime;
private Date endTime;
private Date createTime;
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;
}
public int getNumber() {
return number;
}
public void setNumber(int number) {
this.number = number;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public long getSeckillId() {
return seckillId;
}
public void setSeckillId(long seckillId) {
this.seckillId = seckillId;
}
@Override
public String toString() {
return "Seckill{" +
"seckillId=" + seckillId +
", name='" + name + '\'' +
", number=" + number +
", startTime=" + startTime +
", endTime=" + endTime +
", createTime=" + createTime +
'}';
}
}
org.seckill.entity.SuccessKilled:
package org.seckill.entity;
import java.util.Date;
/**
* Created by tianjun on 2016/8/3.
*/
public class SuccessKilled {
private long seckillId;
private long userPhone;
private short state;
private Date createTime;
//变通
//多对一
private Seckill seckill;
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{" +
"seckillId=" + seckillId +
", userPhone=" + userPhone +
", state=" + state +
", createTime=" + createTime +
'}';
}
}
如上测试通过该,即可完成Dao层,进入下一章节Service的编码。