Springmvc+spring+mybatis-based implementation of "Database Interface" for second-kill system

Objective

This tutorial focuses on mybatis implementation of the underlying interface, the MyBatis to spring to host. The c3p0 used by the database connection pool. MySQL for the database. There are 2 major categories: Seconds to kill the product query, seconds to kill the details of the insertion.

Preparatory work

1, database script. First initialize the database, there are 2 main tables: Seckill "seconds to kill the commodity table", success_killed "second kill record schedule." Success_killed adopts double primary key seckill_id, User_phone. The same product with a mobile phone number can only be killed once, if through the illegal means through the business interface, the repeated insertion of the second kill record details will return 0.

--Create DATABASE seckill;--using database use Seckill;  CREATE TABLE seckill (' seckill_id ' BIGINT not NULL auto_increment COMMENT ' commodity stock id ', ' name ' VARCHAR) NOT null COMMENT ' Commodity name ', ' number ' int not null COMMENT ' inventory quantity ', ' start_time ' TIMESTAMP not null COMMENT ' seconds kill start time ', ' End_time ' Timestam P NOT null COMMENT ' seconds kill end Time ', ' create_time ' TIMESTAMP not null DEFAULT current_timestamp COMMENT ' creation time ', PRIMARY KEY (s eckill_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 inventory table ';--initialize data insert into Seckill (Name,number,start_time, End_time) VALUES (' $1000 seconds to kill Iphone6 ', 100, ' 2016-01-01 00:00:00 ', ' 2016-01-02 00:00:00 '), (' 800 Yuan seconds to kill ipad ', 200, ' 2016-01-01 00:00:00 ', ' 2016-01-02 00:00:00 '), (' $6600 seconds to kill Mac Book Pro ', 300, ' 2016-01-01 00:00:00 ', ' 2016-01-02 00:00:00 '), (' 7000 yuan seconds to kill imac ', 400, ' 2016-01-01 00:00:00 ', ' 2016-01-02 00:00:00 ');--second Kill success List--User login authentication related information (simplified to mobile phone number) CREATE TABLE Success_ Killed (' seckill_id ' BIGINT not null COMMENT ' seconds kill commodity id ', ' user_phone ' BIGINT not null COMMENT ' user phone number ', ' state ' TINYINT not NULL DEFA ULT-1 COMMENT ' status ID:-1: Invalid 0: Success 1: Paid 2: Shipped ', ' create_time ' TIMESTAMP not NULL COMMENT ' creation time ', PRIMARY KEY (seckill_id,us Er_phone),/* Federated PRIMARY KEY */Key Idx_create_time (create_time)) Engine=innodb DEFAULT Charset=utf8 comment= ' second kill success List ';

2. Implement the MyBatis configuration file and submit it to spring hosting

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-conf Ig.dtd "><Configuration>   <Settings>       <!--get the self-increment primary key value using the Getgeneratedkeys of JDBC -       <settingname= "Usegeneratedkeys"value= "true"/>       <!--Replace column name with column alias, default value True -       <settingname= "Usecolumnlabel"value= "true"/>       <!--Open Hump nomenclature field name seckill_id corresponding attribute name Seckillid -       <settingname= "Mapunderscoretocamelcase"value= "true"/>   </Settings></Configuration>

Spring-dao.xml

The key here is the Sqlsessionfactory configuration, which requires specifying the MyBatis global profile, mapper.xml file location.

<?XML version= "1.0" encoding= "UTF-8"?><Beansxmlns= "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 " >    <!--1. Configuration Database Related parameters -    <Context:property-placeholder Location= "Classpath:jdbc.properties"/>    <!--2. Configure the database connection pool -    <BeanID= "DataSource"class= "Com.mchange.v2.c3p0.ComboPooledDataSource">        < Propertyname= "Driverclass"value= "${jdbc.driver}"/>        < Propertyname= "Jdbcurl"value= "${jdbc.url}"/>        < Propertyname= "User"value= "${jdbc.username}"/>        < Propertyname= "Password"value= "${jdbc.password}"/>        <!--database connection pool Maximum number of connections -        < Propertyname= "Maxpoolsize"value= "+" />        <!--database Connection pool minimum number of connections -        < Propertyname= "Minpoolsize"value= "Ten"/>        <!--do not automatically commit after closing a connection -        < Propertyname= "Autocommitonclose"value= "false"/>        <!--get connection time-out -        < Propertyname= "Checkouttimeout"value= "+"/>        <!--retry attempts when getting a connection failure -        < Propertyname= "Acquireretryattempts"value= "3"/>    </Bean>    <!--3, configuration sqlsessionfactory -    <BeanID= "Sessionfactory"class= "Org.mybatis.spring.SqlSessionFactoryBean">        < Propertyname= "DataSource"ref= "DataSource"/>        <!--mybatis Global configuration file -        < Propertyname= "Configlocation"value= "Classpath:mybatis-config.xml"/>        <!--Scan the entity package -        < Propertyname= "Typealiasespackage"value= "Com.seckill.entity"/>        <!--Scan SQL XML files -        < Propertyname= "Mapperlocations"value= "Classpath:mapper/*.xml"/>    </Bean>    <!--4, Configuration Scan DAO interface package, dynamic implementation of the DAO interface injected into the spring container -    <Beanclass= "Org.mybatis.spring.mapper.MapperScannerConfigurer">        <!--DAO interfaces that need to be scanned -        < Propertyname= "Basepackage"value= "Com.seckill.dao"/>        <!--Inject Sqlsessionfactory -        < Propertyname= "Sqlsessionfactorybeanname"value= "Sessionfactory"/>    </Bean></Beans>

Second kill the Bottom interface implementation

1, realize the interface. Note here that the Param annotation is not specified when the method has only one parameter, and if you need to assign multiple parameters to mapper, you can also use HashMap to pass the arguments.

Public interface Seckilldao {    /** minus inventory **/    int Reducenumber (@Param ("Seckillid") long Seckillid, @Param ("Killtime ") Date killtime);    /** query seconds kill commodity details **/    seckill Querybyid (long seckillid);    /** query all seconds kill commodity **/    list<seckill> Queryall (@Param ("offset") int offset, @Param ("limit") int limit);} Public interface Successkilldao {    /**     * insert seconds to kill product Details     * **/    int insertsuccesskilled (@Param ("Seckillid") Long Seckillid, @Param ("Userphone")  long Userphone);    /**     * Product ID to query commodity seconds to kill details, and also return the product details     * **/    successkilled Querybyidwithseckill (@Param ("Seckillid") long Seckillid, @Param ("Userphone")  long Userphone);}

2, seconds to kill the implementation. This is the *.xml file in the Mapper directory.

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 <! [cdata[>=]]> #{killtime} and number>0 </update> <select id= "Querybyid" resulttype= "Se Ckill "> select Seckill_id,name,number,start_time,end_time,create_time from Seckill where seckill_i D=#{seckillid} </select> <select id= "Queryall" resulttype= "Seckill" > select Seckill_id,name,n Umber,start_time,end_time,create_time from Seckill ORDER by Create_time limit #{offset},#{limit} & Lt;/select></mapper> 

Successkilldao.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.SuccessKillDao" > <!--primary key Repeat, no longer insert data--<insert id= " Insertsuccesskilled "> Insert ignore into success_killed (seckill_id,user_phone,state) VALUES (#{seckillid},#{us        erphone},0) </insert> <select id= "Querybyidwithseckill" resulttype= "successkilled" > select        sk.seckill_id, Sk.user_phone, Sk.state, Sk.create_time, s.seckill_id "seckill.seckill_id", S.name "Seckill.name", S.number "Seckill.number", S.create_time "Seckill.create_time", s.start_t IME "Seckill.start_time", S.end_time "Seckill.end_time" from success_killed SK inner JOIN Seckill s O n sk.seckill_id = s.seckill_id where Sk.seckill_id=#{seckillid} and Sk.user_phone=#{userphone} </seleCt></mapper> 

3, OK, ready to work, you can achieve the method of unit testing.

　

Springmvc+spring+mybatis-based implementation of "Database Interface" for second-kill system