本人邮箱: kco1989@qq.com
欢迎转载,转载请注明网址 http://blog.csdn.net/tianshi_kco
github: https://github.com/kco1989/kco
代码已经全部托管[github]((https://github.com/kco1989/kco/tree/master/primaryKeyBuild)有需要的同学自行下载
前言
在项目开发中,我遇到一个需求.就是要生成自定义的主键.
主键的格式为:前缀 + 当天日期 + 自增长序列号
自增长序列号
: 这个序列号是每天从0或1开始自增长的
在了解完需要之后,我做了简单的分析之后就马上进入编码模式,这里我使用的是mysql数据,全部代码已经上传到github,有需要的同学自行下载
目录结构(项目管理工具用的maven)
- src
- main
- java
- com.kco.bean.SequenceNumberBean
- com.kco.dao.SequenceNumberDao
- com.kco.Enum.SequenceNumberEnum
- com.kco.service.SequenceNumberService
- com.kco.service.SequenceNumberServiceImpl
- resources
- META-INF/mybatis/mapper/SequenceNumberDao.xml
- META-INF/mybatis/sql-map-config.xml
- META-INF/spring/spring-base-jdbc.xml
- log4j.properties
- java
- test
- java
- com.kco.TestSequenceNumberService
- java
- main
- pom.xml
编码
1, 首先创建一个maven工程,配置一下jar依赖 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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.kco</groupId>
<artifactId>mytestcode</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.3</version>
</dependency>
<!-- test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<scope>test</scope>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.12</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.7</version>
</dependency>
<!-- spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.2.3.RELEASE</version>
</dependency>
<!-- aop -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.8.8</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjtools</artifactId>
<version>1.8.8</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.8</version>
</dependency>
<!--spring mybatis 整合-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.13</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.13</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jcl-over-slf4j</artifactId>
<scope>runtime</scope>
<version>1.7.13</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.5.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<version>2.6</version>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>tbl</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
</plugins>
</build>
</project>
2., 初始化数据库
CREATE TABLE PUB_SEQUENCE_NUMBER(
prefix VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '前缀(主键)',
NAME VARCHAR(30) NOT NULL COMMENT '描述',
toda
y char(8) NOT NULL COMMENT '当天日期',
minNum INTEGER NOT NULL DEFAULT 0 COMMENT '序列号最小号码',
currentNum INTEGER NOT NULL DEFAULT 0 COMMENT '当前序列号',
numLength INTEGER NOT NULL DEFAULT 8 COMMENT '序列号长度'
);
3, 整合spring和mybatis,创建线程池和事务切面等 spring-base-jdbc.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"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
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/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<context:annotation-config/>
<context:component-scan base-package="com.kco" />
<!-- 配置config的数据库 -->
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///test?useUnicode=yes&characterEncoding=utf8&allowMultiQueries=true"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
<property name="minIdle" value="10"/>
<property name="maxIdle" value="10"/>
<property name="maxWaitMillis" value="-1"/>
<property name="maxTotal" value="10"/>
</bean>
<!-- spring和MyBatis完美整合-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" >
<property name="dataSource" ref="dataSource" />
<property name="configLocation"
value="classpath:META-INF/mybatis/sql-map-config.xml" />
<property name="mapperLocations" value="classpath*:META-INF/mybatis/mapper/*Dao.xml" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" id="mapperScannerConfigurer">
<property name="annotationClass" value="org.springframework.stereotype.Repository"/>
<property name="basePackage" value="com.kco.dao"/>
</bean>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:advice id="txAdvice">
<tx:attributes>
<tx:method name="*" propagation="REQUIRED" rollback-for="java.lang.Throwable"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:advisor advice-ref="txAdvice" pointcut="execution(* *..*ServiceImpl*.*(..))"/>
</aop:config>
</beans>
4, 配置mybatis sql-map-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
<properties>
<property name="dialect" value="mysql"/>
<property name="pageSqlId" value=".*Page$"/>
</properties>
<settings>
<setting name="cacheEnabled" value="false"/>
<setting name="lazyLoadingEnabled" value="false"/>
</settings>
</configuration>
5, 因为要生成自定义主键一般在开发阶段就已经能确定,所以我这里使用了一个枚举类来管理所有的自定义主键 com.kco.Enum.SequenceNumberEnum
package com.kco.Enum;
import com.kco.bean.SequenceNumberBean;
/**
* com.kco.Enum
* Created by swlv on 2016/10/25.
*/
public enum SequenceNumberEnum {
GD(new SequenceNumberBean("GD","工单主键生成策略", 1, 1, 8));
private SequenceNumberBean sequenceNumberBean;
SequenceNumberEnum(SequenceNumberBean sequenceNumberBean){
this.sequenceNumberBean = sequenceNumberBean;
}
public SequenceNumberBean getSequenceNumberBean() {
return sequenceNumberBean;
}
}
6, 数据库PUB_SEQUENCE_NUMBER
对应的bean类 com.kco.bean.SequenceNumberBean
package com.kco.bean;
/**
* 主键生成策略的bean
* com.kco.bean
* Created by swlv on 2016/10/25.
*/
public class SequenceNumberBean {
private String prefix;
private String name;
private String today;
private int minNum;
private int currentNum;
private int numLength;
public String getPrefix() {
return prefix;
}
public void setPrefix(String prefix) {
this.prefix = prefix;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getToday() {
return today;
}
public void setToday(String today) {
this.today = today;
}
public int getMinNum() {
return minNum;
}
public void setMinNum(int minNum) {
this.minNum = minNum;
}
public int getCurrentNum() {
return currentNum;
}
public void setCurrentNum(int currentNum) {
this.currentNum = currentNum;
}
public int getNumLength() {
return numLength;
}
public void setNumLength(int numLength) {
this.numLength = numLength;
}
public SequenceNumberBean() {
}
public SequenceNumberBean(String prefix, String name, int minNum, int currentNum, int numLength) {
this.prefix = prefix;
this.name = name;
this.minNum = minNum;
this.currentNum = currentNum;
this.numLength = numLength;
}
@Override
public String toString() {
return "SequenceNumberBean{" +
"prefix='" + prefix + '\'' +
", name='" + name + '\'' +
", today='" + today + '\'' +
", minNum=" + minNum +
", currentNum=" + currentNum +
", numLength=" + numLength +
'}';
}
}
7, 服务层的接口类以及实现 com.kco.service.SequenceNumberService
package com.kco.service;
import com.kco.Enum.SequenceNumberEnum;
/**
* com.kco.service
* Created by swlv on 2016/10/25.
*/
public interface SequenceNumberService {
/**
* 生成一个主键
* @param sequenceNumberEnum 主键生成类型
* @return 返回一个生成的主键
*/
String newSequenceNumber(SequenceNumberEnum sequenceNumberEnum);
}
com.kco.service.SequenceNumberServiceImpl
package com.kco.service;
import com.kco.Enum.SequenceNumberEnum;
import com.kco.bean.SequenceNumberBean;
import com.kco.dao.SequenceNumberDao;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* com.kco.service
* Created by swlv on 2016/10/25.
*/
@Service
public class SequenceNumberServiceImpl implements SequenceNumberService{
@Resource
private SequenceNumberDao sequenceNumberDao;
@Override
public synchronized String newSequenceNumber(SequenceNumberEnum sequenceNumberEnum) {
if (sequenceNumberEnum == null){
return null;
}
try {
SequenceNumberBean sequenceNumberBean = sequenceNumberDao.newSequenceNumber(sequenceNumberEnum.getSequenceNumberBean().getPrefix());
Thread.sleep(100);
if (sequenceNumberBean == null){
sequenceNumberBean = sequenceNumberEnum.getSequenceNumberBean();
sequenceNumberBean.setToday(sequenceNumberDao.getToday());
}
Thread.sleep(100);
sequenceNumberDao.updateSequenceNumber(sequenceNumberBean);
Thread.sleep(100);
return String.format("%s%6s%08d",
sequenceNumberBean.getPrefix(), sequenceNumberBean.getToday(), sequenceNumberBean.getCurrentNum());
} catch (InterruptedException e) {
e.printStackTrace();
return "";
}
}
}
8, 数据库dao层接口 com.kco.dao.SequenceNumberDao
package com.kco.dao;
import com.kco.bean.SequenceNumberBean;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
/**
* com.kco.dao
* Created by swlv on 2016/10/25.
*/
@Repository
public interface SequenceNumberDao {
/**
* 根据前缀生成一个序列号信息
* @param prefix 前缀
* @return 新的序列号信息
*/
SequenceNumberBean newSequenceNumber(@Param("prefix") String prefix);
/**
* 将生成的序列号信息更新到数据库中
* @param sequenceNumberBean 需要更新信息
*/
void updateSequenceNumber(@Param("bean") SequenceNumberBean sequenceNumberBean);
/**
* 获取数据库当天日期
* @return
*/
String getToday();
}
9., dao层mybatis的实现 SequenceNumberDao.xml
<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.kco.dao.SequenceNumberDao">
<resultMap id="sequenceNumberResultMap" type="com.kco.bean.SequenceNumberBean">
<id property="prefix" column="prefix"/>
<result property="name" column="name"/>
<result property="today" column="today"/>
<result property="minNum" column="minNum"/>
<result property="currentNum" column="currentNum"/>
<result property="numLength" column="numLength"/>
</resultMap>
<select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
FROM PUB_SEQUENCE_NUMBER
WHERE prefix = #{prefix}
AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
</select>
<update id="updateSequenceNumber" parameterType="com.kco.bean.SequenceNumberBean">
REPLACE INTO PUB_SEQUENCE_NUMBER(prefix,name,today,minNum,currentNum,numLength)
VALUES(#{bean.prefix}, #{bean.name}, DATE_FORMAT(CURRENT_DATE(),'%Y%m%d'), #{bean.minNum}, #{bean.currentNum},#{bean.numLength})
</update>
<select id="getToday" resultType="string">
SELECT DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
</select>
</mapper>
10, 编写测试代码(模拟并发测试) com.kco.TestSequenceNumberService
package com.kco;
import com.kco.Enum.SequenceNumberEnum;
import com.kco.service.SequenceNumberService;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Queue;
import java.util.concurrent.ArrayBlockingQueue;
/**
* com.cmbchina.base.service
* Created by swlv on 2016/10/25.
*/
public class TestSequenceNumberService {
public static void main(String[] args) throws InterruptedException {
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("META-INF/spring/spring-base-jdbc.xml");
SequenceNumberService sequenceNumberService = applicationContext.getBean(SequenceNumberService.class);
Queue<String> queue = new ArrayBlockingQueue<String>(200);
List<Thread> list = new ArrayList<>();
for (int i = 0;i < 200; i ++){
list.add(new Thread(()->{
String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GD);
queue.add(key);
}));
}
for (Thread thread : list){
thread.start();
}
while (queue.size() != 200);
System.out.println(queue);
}
}
11, 至此整个项目就已经搭建完成
细节解析
在
com.kco.service.SequenceNumberServiceImpl#newSequenceNumber
的实现中在更新当天日期字符串时,使用的是sequenceNumberBean.setToday(sequenceNumberDao.getToday());
这是因为服务器的时间跟数据库的时间有可能不一致,这里以数据库的时间为准
测试
1, 查询数据库的数据
SELECT count(1) FROM PUB_SEQUENCE_NUMBER
结果:
count(1) |
---|
0 |
2, 清空数据后,再运行com.kco.TestSequenceNumberService#main
3, 然后查一下数据库
SELECT * FROM PUB_SEQUENCE_NUMBER
prefix | NAME | today | minNum | currentNum | numLength |
---|---|---|---|---|---|
GD | 工单主键生成策略 | 20161026 | 1 | 176 | 8 |
问题出现了,明明我是启动了200个线程在跑,怎么才生成176个主键,检查代码也没有问题啊,
newSequenceNumber
也加了关键字synchronized
.先把这个问题放一下,来回忆一下
synchronized
的用法,
synchronized
是在线程池在访问共同变量时使其加锁,达到互斥的效果
共同变量?共同变量?咦,好像有什么不对劲的地方.newSequenceNumber
使用的都是局部变量,没有共同变量啊!不对?是有共同变量,那个共同变量就是数据库的记录
除此之外使用synchronized
还有一个问题,这个项目是web项目的一部分,一般在部署web项目都是有几个单边,即在不同的PC部署几个一模一样的代码,再通过集群访问的方式随机访问其中一台.那么这几个应用其实就是多进程程序,都不再一个JVM中执行,加synchronized
来保证互斥,是不够的.
4, 既然知道是数据库同步出现了问题,那么怎么保证数据库同步呢?
上网搜索一下,发现数据库有悲观锁
和乐观锁
解决方法就是在Dao层的实现
<select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
FROM PUB_SEQUENCE_NUMBER
WHERE prefix = #{prefix}
AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
</select>
改为
<select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
FROM PUB_SEQUENCE_NUMBER
WHERE prefix = #{prefix}
AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') for UPDATE
</select>
5, 然后再重新查一下数据库
SELECT * FROM PUB_SEQUENCE_NUMBER
prefix | NAME | today | minNum | currentNum | numLength |
---|---|---|---|---|---|
GD | 工单主键生成策略 | 20161026 | 1 | 200 | 8 |
发现问题解决了,这里是用到了数据库锁表的机制来实现同步.
以后有机会再学一下数据库同步,
悲观锁
和乐观锁
.因为这个我还不太熟悉,这里就不卖弄了.
6., 修改一下 com.kco.Enum.SequenceNumberEnum
再增加一个自定义主键GDD(new SequenceNumberBean("GDD","工单主键生成策略", 1, 1, 8))
7., 清空数据,然后运行com.kco.TestSequenceNumberService#main
8, 修改com.kco.TestSequenceNumberService2#main
将String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GD);
改为String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GDD);
再次运行com.kco.TestSequenceNumberService#main
, 这是用来模拟两个程序同时访问数据的情况
9, 查询数据
SELECT * FROM PUB_SEQUENCE_NUMBER
prefix | NAME | today | minNum | currentNum | numLength |
---|---|---|---|---|---|
GD | 工单主键生成策略 | 20161026 | 1 | 200 | 8 |
GDD | 工单主键生成策略 | 20161026 | 1 | 200 | 8 |
数据正常
10., 清空数据,连续运行两次com.kco.TestSequenceNumberService2#main
模拟多进程对生成同一个自定义主键
11, 查询结果
SELECT * FROM PUB_SEQUENCE_NUMBER
prefix | NAME | today | minNum | currentNum | numLength |
---|---|---|---|---|---|
GDD | 工单主键生成策略 | 20161026 | 1 | 400 | 8 |
至此, 全部搞定
遗留问题
- 数据库
悲观锁
和乐观锁
具体是怎么样的.有什么区别? for update
锁表对性能有没有什么影响,除了性能,还有其他隐藏的隐患?
这几个问题就留给读者(还有我自己)自行查资料研究了
打赏
如果觉得我的文章写的还过得去的话,有钱就捧个钱场,没钱给我捧个人场(帮我点赞或推荐一下)