Java高并发秒时啊API之业务分析与Dao层2
http://blog.csdn.net/kaka0509/article/details/68951719
---4-1 数据库设计与编码---------------------------------------------------------------------------------
schema.sql:
--数据库初始化脚本 --创建db CREATE DATABASE seckill; --使用数据库 use seckill; --创建秒杀库存表 创建后可用show create table seckill \G查看创建语句 -- 表名和column不可加''会出错，不加的话show create table时会自动显示 CREATE TABLE seckill ( seckill_id bigint NOT NULL AUTO_INCREMENT COMMENT 'ITEM_ID', name varchar(120) NOT NULL COMMENT 'ITEM_NAME', number int NOT NULL COMMENT 'ITEM_amount', start_time timestamp NOT NULL COMMENT 'start_time', end_time timestamp NOT NULL COMMENT 'end_time', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time', 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 'second kill'; --初始化数据 insert into seckill(name,number,start_time,end_time) values ('1000元秒杀iPhone6',100,'2017-07-03 00:00:00','2017-07-04 00:00:00'), ('500元秒杀ipad2',200,'2017-07-03 00:00:00','2017-07-04 00:00:00'), ('300元秒杀小米4',300,'2017-07-03 00:00:00','2017-07-04 00:00:00'), ('200元秒杀魅族',400,'2017-07-03 00:00:00','2017-07-04 00:00:00'); --秒杀成功明细表 --用户登录认证 相关信息 CREATE TABLE success_killed( seckill_id bigint NOT NULL AUTO_INCREMENT COMMENT 'ITEM_ID', user_phone bigint NOT NULL COMMENT 'phone nomber', state tinyint NOT NULL DEFAULT -1 COMMENT '-1:无效 0:成功 1:已付款 2:发货', create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'create_time', PRIMARY KEY (seckill_id,user_phone),/*联合主键*/ key idx_create_time(create_time) )ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT 'success detail'; --连接数据库控制台 mysql -uroot -proot /*为什么手写DDL * 记录每次上线的DDL修改 * 上线V1.1 ALTER TABLE seckill DROP INDEX idx_create_time, ADD index idx_c_s(start_time,create_time); **************/
---4-2 DAO实体和接口编码---------------------------------------------------------------------------------
entity：
Seckill.java
package org.seckill.entity; import java.util.Date; public class Seckill { private long seckillId; private String name; private int number; private Date startTime; private Date endTime; private Date createTime; public long getSeckillId() { return seckillId; } public void setSeckillId(long seckillId) { this.seckillId = seckillId; } 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 "Seckill [seckillId=" + seckillId + ", name=" + name + ", number=" + number + ", startTime=" + startTime + ", endTime=" + endTime + ", createTime=" + createTime + "]"; } }
SuccessKilled .java:
package org.seckill.entity; import java.util.Date; 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 create) { this.createTime = create; } 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 + ", create=" + createTime + "]"; } }
Dao:
SeckillDao.java
package org.seckill.dao; import java.util.Date; import java.util.List; import org.seckill.entity.Seckill; public interface SeckillDao { /* * 减库存， * @param seckillId * @param killTime * @return 如果影响行数>1,表示更新的记录行数 * */ int reduceNumber(long seckillId,Date killTime); /* * 根据ID查询秒杀对象 * @param seckillId * @return * */ Seckill queryById(long seckillId); /* * 根据偏移量查询秒杀商品列表 * @param offet * @param limit * @return * */ List<Seckill> queryAll(int offet,int limit); }
SuccessKilledDao .java
package org.seckill.dao; import org.seckill.entity.SuccessKilled; public interface SuccessKilledDao { /* * 插入明细，可过滤重复 * @param seckillID * @param userPhone * @return 插入的行数 * */ int insertSuccessKilled(@Param("seckillId")long seckillId,@Param("userPhone")long userPhone); /* * 根据id查询SuccessKilled并携带秒杀产品对象实体 * @param seckillID * @return * */ SuccessKilled queryByIdWithSeckill(long seckillId); }
---4-3 基于myBatis实现DAO理论---------------------------------------------------------------------------------
1.Mybatis和Hibernate都是处理关系对象映射ORM这一功能的
数据库---映射---对象
2.mybatis的有两种方式提供SQL:
2.1.XML提供SQL
2.2.注解提供SQL
推荐利用XML提供SQL,利于维护.
3.mybatis特点及与其他ORM框架的区别:
1）sql写在xml文件或者注解当中，推荐写在xml文件当中(便于维护)
2）mybatis实现DAO接口，
通过Mapper自动实现DAO接口 。推荐使用Mapper
API编程方式实现DAO接口（sql写在程序里面）
---4-4 基于myBatis实现DAO编程(上) +基于myBatis实现DAO编程(下)-----------------------------------------------------------------------------
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"> <!--resultType为int 可以不用设。 parameterType多个参数可以不用给，能够自动识别出这两个属性 #{}:${}解析穿过来的参数值不带单引号，#{}解析传过来参数带单引号。 #{}这个是ognl的表达式语言 ${}是jstl表达式语言 --> <!-- 具体SQL --> update seckill set number = number - 1 where seckill_id = #{seckillId} and start_time <![CDATA[ <= ]]> #{killTime} <!-- XML中不允许有‘<=’,用<![CDATA[ <= ]]声明不是XML语法>--> and end_time >= #{killTime} and number > 0; </update> <select id="queryById" resultType="Seckill" parameterType="long"> <!-- 已经开启了使用列别名替换列和驼峰命名转换见mybatis-config.xml,就不需要这样写了 seckill_id as seckillId --> select seckill_id,name,number,start_time,end_time,create_time from seckill where seckill_id = #{seckillId} </select> <select id="queryAll" resultType="Seckill"> <!--resault是个list，通过泛型查看里面的内容，resaultType直接给出里面的类型就可以(泛型的类型) parameterType = "int"多个参数可以不用给 --> select seckill_id,name,number,start_time,end_time,create_time from seckill order by create_time desc limit #{offset},#{limit} </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="org.seckill.dao.SuccessKilledDao"> <insert id="insertSuccessKilled"> <!--sql技巧 过滤主键重复：主键冲突，报错 ， 添加ignore (忽略)不报错，但是返回结果是影响了0 rows affected --> insert ignore into seccess_killed(seckill_id,user_phone) values(#{seckillId},#{userPhone}); </insert> <select id="queryByIdWithSeckill" resultType="SuccessKilled"> <!-- 根据id查询SuccessKilled并携带Seckill实体 --> <!-- 如果告诉MyBatis把结果映射到SuccessKilled同时把seckill实体也映射 s.seckill_id "seckill.seckill_id" 。 加""是为了数据返回时MyBatis识别("seckill.seckill_id"本质上是OGNL表达式)， 已经开启了使用列别名替换列和驼峰命名转换seckill.seckill_id=seckill.seckillId --> <!-- 可以自由控制SQL --> select
sk.seckill_id,
sk.user_phone,
sk.state,
sk.create_time,
s.seckill_id "seckill.seckill_id",<!-- 通过别名 告诉mybatis把值赋到SuccessKilled的 -->
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-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="mapUnderscoreCamelCase" value="true"/> </settings> </configuration>
---4-6 myBatis整合Spring理论---------------------------------------------------------------------------------
mybatis与Spring的整合目标： 1、更少的编码 1). 只需要接口，不需要实现(Mybatis 自动完成) 2、更少的配置 1). 别名(Mybatis可以扫描对应包，因此使用一些类的时候不需要使用包名+类名) 2). 配置扫描 3). dao的实现 3、足够的灵活性 1). 自己定制SQL语句 2). 自由传参
---4-7 myBatis整合Spring编码---------------------------------------------------------------------------------
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-4.1.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd"> <!-- 配置整合MyBatis过程 properties的属性：${url}--> <!-- 1：配置数据库相关参数 --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 2：数据库连接池 --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <!-- 配置连接池属性 --> <property name="driverClass" value="${driver}" /> <property name="jdbcUrl" value="${url}" /> <property name="user" value="${jdbc.username}" /> <property name="password" value="${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.SqlSessionFactory"> <!-- 注入数据库连接池 --> <property name="dataSource" ref="dataSource"/> <!-- 配置MyBatis全局配置文件 --> <property name="configLocation" value="classpath:mybatis-config.xml"/> <!-- 扫描entity包 支持使用别名org.seckill.entity.Seckill -> Seckill 多个包时可以分开写 value="org.seckill.entity;seckill.entity2" --> <property name="typeAliasesPackage" value="org.seckill.entity"/> <!-- 扫描sql配置文件：mapper需要的xml文件 --> <property name="mapperLocations" value="classpath:mapper/*.xml"></property> </bean> <!-- 4:配置扫描Dao接口包，动态实现Dao接口，注入到Spring容器中 不设置ID因为无其他地方引用，自动运行 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <!-- 注入SqlSessionFactory sqlSessionFactoryBeanName:防止处理时属性未被加载(jdbc.properties没被加载时，dataSource就是错误的) 通过BeanName后处理，使用Mybatis时再加载 --> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"> </property> <!-- 给出需要扫描DAO接口包: 扫描特定的包，把包内的dao接口自动实现一个实现类，并注入Spring容器。 --> <property name="basePackage" value="org.seckill.dao" /> </bean> </beans>
---4-8 4-8 DAO层单元测试编码和问题排查 ---------------------------------------------------------------------------------
SeckillDao.java
package org.seckill.dao; import java.util.Date; import java.util.List; import org.apache.ibatis.annotations.Param; import org.seckill.entity.Seckill; public interface SeckillDao { /* * 减库存， * @param seckillId * @param killTime * @return 如果影响行数>1,表示更新的记录行数 * */ //int reduceNumber(long seckillId,Date killTime); 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 offset,@Param("limit") int limit); //List<Seckill> queryAll(int offset,int limit); }
SeckillDaoTest.java
原因：
java没有保留形参的的记录，(List<Seckill> queryAll(int offset,int limit);)
queryAll(int offset,int limit) --> queryAll(arg0,arg1)
limit #{offset},#{limit}就无法找到offset 和limit
在mybatis中如果要表示多个参数的传进来，要用注解@Param来指定参数名，如果是一个参数，那不用指定。
List<Seckill> queryAll(@Param("offset") int offset,@Param("limit") int limit);
package org.seckill.dao; import java.util.Date; import java.util.List; import javax.annotation.Resource; import org.apache.ibatis.annotations.Param; import org.junit.Test; import org.junit.runner.RunWith; import org.seckill.entity.Seckill; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; /* * 配置spring和junit整合： * junit启动时加载springIOC容器 * spring-test(SpringJUnit4ClassRunner.class) * junit(@RunWith) * */ @RunWith(SpringJUnit4ClassRunner.class) //告诉junit spring配置文件 @ContextConfiguration({"classpath:spring/spring-dao.xml"}) public class SeckillDaoTest { //注入Dao实现类依赖 @Resource会从spring容器中查找SeckillDao类型的实现类并注入到seckillDao @Resource 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.toString()); /* * 1000元秒杀iPhone6 Seckill [seckillId=1000, name=1000元秒杀iPhone6, number=100, startTime=Mon Jul 03 00:00:00 CST 2017 , endTime=Tue Jul 04 00:00:00 CST 2017, createTime=Tue Jul 04 10:14:41 CST 2017] * */ } @Test public void testQueryAll() throws Exception{ /* junit出错： * org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter 'offset' not found. Available parameters are [arg1, arg0, param1, param2] * Caused by: org.apache.ibatis.binding.BindingException: Parameter 'offset' not found. Available parameters are [arg1, arg0, param1, param2] * 原因：List<Seckill> queryAll(int offset,int limit); * java没有保留形参的的记录，queryAll(int offset,int limit)-》queryAll(arg0,arg1) * limit #{offset},#{limit}就无法找到offset 和limit * 在mybatis中如果要表示多个参数的传进来，要用注解@Param来指定参数名，如果是一个参数，那不用指定。 * List<Seckill> queryAll(@Param("offset") int offset,@Param("limit") int limit); * */ List<Seckill> seckills = seckillDao.queryAll(0, 100); for(Seckill kill: seckills){ System.out.println(kill.toString()); } } @Test public void testReduceNumber() throws Exception{ /* * 16:26:41.616 [main] DEBUG o.m.s.t.SpringManagedTransaction - * JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@304bb45b] will not be managed by Spring * 这个JDBC Connection没有被spring托管，是从c3p0拿到的 * update seckill set number = number - 1 where seckill_id = ? and start_time <= ? and end_time >= ? and number > 0; Parameters: 1000(Long), 2017-07-05 16:26:41.84(Timestamp), 2017-07-05 16:26:41.84(Timestamp) DEBUG o.s.dao.SeckillDao.reduceNumber - <== Updates: 0 updateCount= 0Wed Jul 05 16:26:41 CST 2017 * */ Date killTime =new Date(); int updateCount =seckillDao.reduceNumber(1000, killTime); System.out.println("updateCount= "+updateCount+killTime); } }
---4-9 DAO层单元测试编码和问题排查（下）---------------------------------------------------------------------------------
SuccessKilledDao.java
package org.seckill.dao; import org.apache.ibatis.annotations.Param; import org.seckill.entity.SuccessKilled; public interface SuccessKilledDao { /* * 插入明细，可过滤重复 * @param seckillID * @param userPhone * @return 插入的行数 * */ int insertSuccessKilled(@Param("seckillId")long seckillId,@Param("userPhone")long userPhone); /* * 根据id查询SuccessKilled并携带秒杀产品对象实体 * @param seckillID * @return * */ SuccessKilled queryByIdWithSeckill(@Param("seckillId")long seckillId,@Param("userPhone")long userPhone); }
SuccessKilledDaoTest.java
package org.seckill.dao; import javax.annotation.Resource; 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; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration({"classpath:spring/spring-dao.xml"}) public class SuccessKilledDaoTest { @Resource private SuccessKilledDao successKilledDao; @Test public void testInsertSuccessKilled() { /* * 第一次执行结果:1,插入成功 * 第二次执行结果:0,重复key * */ long seckillId = 1000; long userPhone = 23412344321L; int insertNom = successKilledDao.insertSuccessKilled(seckillId, userPhone); System.out.println("insert Nom ="+insertNom); } @Test public void testQueryByIdWithSeckill() { /* * state= -1 原因 state tinyint NOT NULL DEFAULT -1 COMMENT '-1:无效 0:成功 1:已付款 2:发货', * 创建时设定为默认-1 * 改为0有两种： * 1） NOT NULL DEFAULT 0 COMMENT '-1:无效 0:成功 1:已付款 2:发货', * 2) insert ignore into Success_killed(seckill_id,user_phone,state) values(#{seckillId},#{userPhone},0); * */ long seckillId = 1000; long userPhone = 23412344321L; SuccessKilled sk= successKilledDao.queryByIdWithSeckill(seckillId,userPhone); if(sk != null){ System.out.println("SuccessKilled="+sk.toString()); System.out.println("seckill="+sk.getSeckill().toString()); } } }
SuccessKilled.xml
注意：
select
sk.seckill_id,
sk.user_phone,
sk.state,
sk.create_time,
s.seckill_id "seckill.seckill_id",<!-- 通过别名 告诉mybatis把值赋到SuccessKilled的 -->
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"
加""是为了数据返回时MyBatis识别("seckill.seckill_id"本质上是OGNL表达式)，
已经开启了使用列别名替换列和驼峰命名转换seckill.seckill_id=seckill.seckillId
<?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"> <!--sql技巧 过滤主键重复：主键冲突，报错 ， 添加ignore (忽略)不报错，但是返回结果是影响了0 rows affected --> insert ignore into Success_killed(seckill_id,user_phone,state) values(#{seckillId},#{userPhone},0); </insert> <select id="queryByIdWithSeckill" resultType="SuccessKilled"> <!-- 根据id查询SuccessKilled并携带Seckill实体 --> <!-- 如果告诉MyBatis把结果映射到SuccessKilled同时把seckill实体也映射 s.seckill_id "seckill.seckill_id" 。 加""是为了数据返回时MyBatis识别("seckill.seckill_id"本质上是OGNL表达式)， 已经开启了使用列别名替换列和驼峰命名转换seckill.seckill_id=seckill.seckillId --> <!-- 可以自由控制SQL --> select sk.seckill_id, sk.user_phone, sk.state, sk.create_time, s.seckill_id "seckill.seckill_id",<!-- 通过别名 告诉mybatis把值赋到SuccessKilled的 --> 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>
