目录
1.idea-->file--project
2.添加依赖
<!--1.添加日志依赖-->
<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.1.1</version>
</dependency>
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.1.1</version>
</dependency>
<!--2.数据库相关依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>c3p0</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.1.2</version>
</dependency>
<!--这里要用最新的否可能连接失败-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.11</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.2</version>
</dependency>
<!--3.DAO框架:mybaits依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.3.0</version>
</dependency>
<!--4.spring自身实现的spring整合依赖-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.3</version>
</dependency>
<!--5.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.4</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<!--6spring依赖-->
<!--6.1spring核心依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<!--6.2spring dao层依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<!--6.3spring web层依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
<!--6.4spring test相关依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.1.9.RELEASE</version>
</dependency>
3.数据库设计
-- 数据初始化脚本
-- 创建数据库
create database seckill;
-- 使用数据库
use seckill;
-- 创建秒杀库存表
create table seckill(
seckill_id bigint not null auto_increment comment '商品库id',
name varchar(20) 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)
)ENGINE=InnoDB auto_increment= 100 default charset=utf8 comment='秒杀库存表';
-- 初始化数据
insert into seckill(name, number,start_time,end_time, create_time)
values(
'iphone9', 100,'2020-3-12 00:00:00','2020-3-31 00:00:00','2020-03-12 00:00:00',
'iphone6', 101,'2020-3-12 00:00:00','2020-3-31 00:00:00','2020-03-12 00:00:00',
'iphone7', 10,'2020-3-12 00:00:00','2020-3-31 00:00:00','2020-03-12 00:00:00',
'iphone8', 50,'2020-3-12 00:00:00','2020-3-31 00:00:00','2020-03-12 00:00:00',
)
-- 秒杀成功明细表
create table success_killed(
seckill_id bigint not null comment '秒杀商品id'
user_phone bigintn not null comment '用户手机号',
state tinyint not null default -1 comment '已付款 已发货',
create_time timestamp not null comment '创建时间'
primary key (seckill_id ,user_phone),
key idx_create_time(create_time)
)engine=innodb default charset=utf8 comment='秒杀成功明细表';
-- 查看数据库索引
show index from seckill;
-- 删除索引
alter table seckill DROP INDEX idx_start_time;
-- 添加索引
alter table seckill add INDEX idx_start_time (start_time)
-- 修改索引
alter table seckill DROP INDEX idx_start_time,
add index idx_start_time1(start_time);
4.建包,新增实体
5.dao接口定义
5.引入mybaits 实现DAO
6. 整合spring-mybatis
spring-dao.xml
<!--配置整合mybatis过程 -->
<!--1.配置数据库相关参数 -->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--2.数据库连接池-->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClass}"/>
<property name="jdbcUrl" value="${jdbc.jdbcUrl}"/>
<property name="user" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
<!--c3p0连接池私有属性-->
<property name="maxPoolSize" value="30"/>
<property name="minPoolSize" value="10"/>
<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">
<!--3.1注入数据库连接-->
<property name="dataSource" ref="dataSource"/>
<!--3.2配置mysql全部配置文件-->
<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>
<!--5.事物控制的配置-->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!--控制数据源-->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--配置基于注解的声明式事务-->
<tx:annotation-driven transaction-manager="transactionManager"/>
<!--配置redis DAO-->
<bean id = "redisDao" class="org.seckill.dao.RedisDao">
<constructor-arg index="0" value="localhost"/>
<constructor-arg index="1" value="6379"/>
</bean>
7.创建测试类
@RunWith(SpringJUnit4ClassRunner.class)
// 告诉junit spring 配置文件位置
@ContextConfiguration({"classpath:spring/spring-dao.xml"})
public class SecKillDaoTest {
@Resource
private SecKillDao secKillDao;
@Test
public void reduceNumber() {
long id = 100;
int updateCount = secKillDao.reduceNumber(id, new Date());
System.out.println("========");
System.out.println(JSON.toJSONString(updateCount));
}
@Test
public void queryById() {
long id = 100;
SecKill secKill = secKillDao.queryById(id);
System.out.println("========");
System.out.println(JSON.toJSONString(secKill));
}
@Test
public void queryAll() {
long id = 100;
List<SecKill> secKills = secKillDao.queryAll(0, 100);
System.out.println("========");
for (SecKill secKill : secKills) {
System.out.println(secKill.getStartTime());
}
//System.out.println(JSON.toJSONString(secKills));
}
}
注意:xml中schemaLocation中要按照顺序来,否则会出现以下的错误
<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
https://www.springframework.org/schema/context/spring-context.xsd">
8.配置ioc及事务
spring-service.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-4.2.xsd">
<context:component-scan base-package="org.seckill.service">
<context:include-filter type="annotation" expression="org.springframework.stereotype.Service"/>
<context:include-filter type="annotation" expression="org.springframework.stereotype.Component"/>
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
</beans>
9.配置spring-mvc
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
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
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<context:component-scan base-package="org.seckill.service">
<!--让他只扫控制器-->
<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Service" />
</context:component-scan>
<!--事物控制的配置-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<!--控制数据源-->
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置基于注解的声明式事务-->
<tx:annotation-driven transaction-manager="transactionManager"/>
<!-- 配置视图解析器 方便页面返回-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/views/"></property>
<property name="suffix" value=".jsp"></property>
</bean>
<!--两个标准配置-->
<!--将springmvc不能处理的请求交给tomcat-->
<mvc:default-servlet-handler/>
<!--能支持spring更高级的功能 映射动态请求-->
<mvc:annotation-driven/>
</beans>
10.引入redis优化秒杀接口
分析:mysql根据主键更新锁级别为行级锁,如果update操作需要1s 而insert操作需要10s,加入有第三个线程进来,采用第一种方式则第三个线程要卡到第22秒才能执行到,三个请求加起来是33s执行完。
如果采用简单的优化先insert后update,则三个线程可同时insert,只卡update这一步,三个线程全部执行完只需要13s。大大提高了服务器的吞吐量。