使用文件创建

在src下建个sql文件夹，下面存放schema.sql文件

在这里有个坑：前面字段不是引号，后面的注释是单引号，这样在命令行执行才不会报错。

CREATE TABLE killone(
  `kill_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 (kill_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 killone(name,number,start_time,end_time) VALUES
  ('一元秒杀鸡蛋10个',100,'2017-05-21 00:00:00','2017-05-22 00:00:00'),
  ('100元秒杀鸡iphone8',200,'2017-05-21 00:00:00','2017-05-22 00:00:00'),
  ('1000元秒杀小明',100,'2017-05-21 00:00:00','2017-05-22 00:00:00'),
  ('一元秒杀洗发露',1000,'2017-05-21 00:00:00','2017-05-22 00:00:00')

--秒杀成功明细记录
CREATE TABLE success_killed (
  `kill_id` bigint not NULL comment '秒杀商品id',
  `user_phone` bigint NOT NULL comment '用户手机号',
  `state` tinyint NOT NULL DEFAULT -1 comment '状态：-1:无效，0：成功，1：已付款',
  `create_time` TIMESTAMP NOT NULL comment '创建时间',
  PRIMARY KEY (kill_id,user_phone) /*联合主键*/,
  KEY idx_create_time(create_time)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '秒杀成功明细表'

然后查看一下：

MariaDB [killone]> show create table success_killed\G;
*************************** 1. row ***************************
       Table: success_killed
Create Table: CREATE TABLE `success_killed` (
  `kill_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：已付款',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`kill_id`,`user_phone`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='秒杀成功明细表'

相应的实体对象

package com.jimo.entity;

import java.util.Date;

/**
 * Created by root on 17-5-21.
 */
public class KillOne {
    private long killId;
    private String name;
    private int number;
    private Date startTime;
    private Date endTime;
    private Date createTime;

    public long getKillId() {
        return killId;
    }

    public void setKillId(long killId) {
        this.killId = killId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    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;
    }

    @Override
    public String toString() {
        return "KillOne{" +
                "killId=" + killId +
                ", name='" + name + '\'' +
                ", number=" + number +
                ", startTime=" + startTime +
                ", endTime=" + endTime +
                ", createTime=" + createTime +
                '}';
    }
}

package com.jimo.entity;

import java.util.Date;

/**
 * Created by root on 17-5-21.
 */
public class SuccessKilled {
    private long killId;
    private long userPhone;
    private short state;
    private Date createTime;
    /*多对一关系*/
    private KillOne killOne;

    @Override
    public String toString() {
        return "SuccessKilled{" +
                "killId=" + killId +
                ", userPhone=" + userPhone +
                ", state=" + state +
                ", createTime=" + createTime +
                ", killOne=" + killOne +
                '}';
    }

    public long getKillId() {
        return killId;
    }

    public void setKillId(long killId) {
        this.killId = killId;
    }

    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 KillOne getKillOne() {
        return killOne;
    }

    public void setKillOne(KillOne killOne) {
        this.killOne = killOne;
    }
}

dao层接口

package com.jimo.dao;

import com.jimo.entity.KillOne;

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

/**
 * Created by root on 17-5-21.
 */
public interface KillOneDao {

    /**
     * 减库存
     *
     * @param killId
     * @param killTime
     * @return
     */
    public int reduceNumber(long killId, Date killTime);

    /**
     * 查询秒杀对象
     *
     * @param killId
     * @return
     */
    public KillOne queryById(long killId);

    /**
     * 根据偏移量查询列表
     *
     * @param offset
     * @param limit
     * @return
     */
    public List<KillOne> queryAll(int offset, int limit);
}

package com.jimo.dao;

import com.jimo.entity.SuccessKilled;

/**
 * Created by root on 17-5-21.
 */
public interface SuccessKilledDao {

    /**
     * 插入成功明细，可过滤重复
     *
     * @param killId
     * @param userPhone
     * @return
     */
    public int insertSuccessKilled(long killId, long userPhone);

    /**
     * 根据id查询成功明细并携带商品对象
     *
     * @param killId
     * @return
     */
    public SuccessKilled queryByIdWithKillOne(long killId);
}