全局id:globalId生成方法

曾经终败给现在 2022-03-12 05:47 263阅读 0赞

项目中有的表建自增会出现些问题，如水平分表，有可能分出的几张表存在相同的id，查询，删除等操作就会出现错误。自动生成全局id还是有必要的。

**注意：下面提供思路。当并发量大的时候会存在问题。可考虑用redis或者触发器**

下面介绍下全局id生成方法：

工具类：

package com.wdq.utils;
    
    import com.wdq.service.IGlobalIdGeneratorService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.PostConstruct;
    
    @Component
    public class GloabalIdGenerator {
    
        private static GloabalIdGenerator idGenerator;
        @Autowired
        IGlobalIdGeneratorService globalIdGeneratorService;
    
    
        @PostConstruct
        public void init() {
            idGenerator = this;
            idGenerator.globalIdGeneratorService = this.globalIdGeneratorService;
        }
    
        /**
         * 生成单个id
         * @param tableName 表名
         * @return
         */
        public static Long generatId(String tableName){
            return idGenerator.globalIdGeneratorService.generateId(tableName);
        }
    
        /**
         * 生成连续id
         * @param tableName 表名
         * @param size 生成个数
         * @return
         */
        public static Long[] generatIdArea(String tableName,int size){
            return idGenerator.globalIdGeneratorService.generateIdArea(tableName,size);
        }
    
    }

service接口

package com.wdq.service;
    
    public interface IGlobalIdGeneratorService {
    
        /**
         * 获取表主键
         * @param tableName 要获取主键的表的名字
         * @return
         */
        Long generateId(String tableName);
    
        /**
         * 获一段主键
         * @param tableName 需要获取主键的名字
         * @param size 要获取的主键的个数
         * @return
         */
        Long[] generateIdArea(String tableName, int size);
    
    }

service实现类

package com.wdq.service.impl;
    
    import com.wdq.dao.IGlobalIdDao;
    import com.wdq.service.IGlobalIdGeneratorService;
    import com.wdq.vo.GlobalIdVo;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.TransactionDefinition;
    import org.springframework.transaction.TransactionStatus;
    import org.springframework.transaction.annotation.Transactional;
    import org.springframework.transaction.support.DefaultTransactionDefinition;
    import utils.DateUtil;
    
    import javax.annotation.Resource;
    
    @Service("globalIdGeneratorService")
    public class GlobalIdGeneratorServiceImpl implements IGlobalIdGeneratorService {
    
        @Autowired
        IGlobalIdDao globalIdDao;
    
        @Resource(name="transactionManager")
        private DataSourceTransactionManager transactionManager;
    
        @Override
        public synchronized Long generateId(String tableName) {
            //使用手动控制事物
            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 事物隔离级别，开启新事务，这样会比较安全些。
            TransactionStatus status = transactionManager.getTransaction(def); // 获得事务状态
            Long id= null;
            try {
                tableName=tableName.trim();
                id = globalIdDao.getSerNo(tableName);
                if(id==null){
                    //该表尚未生成过id的情况下，新增一条
                    int count=globalIdDao.getRecordTotal();
                    GlobalIdVo globalIdVo=new GlobalIdVo();
                    globalIdVo.setId(count+1);
                    globalIdVo.setBusTableName(tableName);
                    globalIdVo.setSerno(0L);
                    globalIdVo.setUpdateTime(DateUtil.currentTimestamp());
                    globalIdDao.insert(globalIdVo);
                    id=0L;
                }else{
                    id=id+1;
                    GlobalIdVo globalIdVo=new GlobalIdVo();
                    globalIdVo.setUpdateTime(DateUtil.currentTimestamp());
                    globalIdVo.setSerno(id);
                    globalIdVo.setBusTableName(tableName);
                    globalIdDao.updateSerNoByTableName(globalIdVo);
                }
                transactionManager.commit(status);
            } catch (Exception e) {
                e.printStackTrace();
                transactionManager.rollback(status);
            }
            return id;
        }
    
        @Override
        public synchronized Long[] generateIdArea(String tableName, int size) {
            System.out.println("=======================>"+tableName);
            //使用手动控制事物
            DefaultTransactionDefinition def = new DefaultTransactionDefinition();
            def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW); // 事物隔离级别，开启新事务，这样会比较安全些。
            TransactionStatus status = transactionManager.getTransaction(def); // 获得事务状态
            Long idArray[]= new Long[0];
            try {
                idArray = new Long[size];
    
                tableName=tableName.trim();
                Long id=globalIdDao.getSerNo(tableName);
                if(id==null){
                    //该表尚未生成过id的情况下，新增一条
                    int count=globalIdDao.getRecordTotal();
    
                    for(int i=0;i<size;i++){
                        idArray[i]=(long)i;
                    }
    
                    GlobalIdVo globalIdVo=new GlobalIdVo();
                    globalIdVo.setId(count+1);
                    globalIdVo.setBusTableName(tableName);
                    globalIdVo.setSerno((long)size-1);
                    globalIdVo.setUpdateTime(DateUtil.currentTimestamp());
                    globalIdDao.insert(globalIdVo);
    
                }else{
    
                    for(int i=0;i<size;i++){
                        idArray[i]=id+i+1;
                    }
    
                    GlobalIdVo globalIdVo=new GlobalIdVo();
                    globalIdVo.setUpdateTime(DateUtil.currentTimestamp());
                    globalIdVo.setSerno(id+size);
                    globalIdVo.setBusTableName(tableName);
                    globalIdDao.updateSerNoByTableName(globalIdVo);
                }
                transactionManager.commit(status);
            } catch (Exception e) {
                e.printStackTrace();
                transactionManager.rollback(status);
            }
            return idArray;
        }
    }

dao：

package com.wdq.dao;
    
    import com.wdq.vo.GlobalIdVo;
    import org.springframework.stereotype.Repository;
    
    @Repository("globalIdDao")
    public interface IGlobalIdDao {
    
        int deleteByPrimaryKey(Integer id);
    
        int insert(GlobalIdVo record);
    
        int insertSelective(GlobalIdVo record);
    
        GlobalIdVo selectByPrimaryKey(Integer id);
    
        int updateByPrimaryKeySelective(GlobalIdVo record);
    
        int updateByPrimaryKey(GlobalIdVo record);
    
        int updateSerNoByTableName(GlobalIdVo globalIdVo);
    
        Long getSerNo(String tableName);
    
        int getRecordTotal();
    
    }

mapper：

<?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.wdq.dao.IGlobalIdDao" >
      <resultMap id="BaseResultMap" type="com.wdq.vo.GlobalIdVo" >
        
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="bus_table_name" property="busTableName" jdbcType="VARCHAR" />
        <result column="serno" property="serno" jdbcType="INTEGER" />
        <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
      </resultMap>
      <sql id="Base_Column_List" >
        
        id, bus_table_name, serno, update_time
      </sql>
      <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        
        select 
        <include refid="Base_Column_List" />
        from global_id
        where id = #{id,jdbcType=INTEGER}
      </select>
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
        
        delete from global_id
        where id = #{id,jdbcType=INTEGER}
      </delete>
      <insert id="insert" parameterType="com.wdq.vo.GlobalIdVo" >
        
        insert into global_id (id, bus_table_name, serno, 
          update_time)
        values (#{id,jdbcType=INTEGER}, #{busTableName,jdbcType=VARCHAR}, #{serno,jdbcType=INTEGER}, 
          #{updateTime,jdbcType=TIMESTAMP})
      </insert>
      <insert id="insertSelective" parameterType="com.wdq.vo.GlobalIdVo" >
        
        insert into global_id
        <trim prefix="(" suffix=")" suffixOverrides="," >
          <if test="id != null" >
            id,
          </if>
          <if test="busTableName != null" >
            bus_table_name,
          </if>
          <if test="serno != null" >
            serno,
          </if>
          <if test="updateTime != null" >
            update_time,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
          <if test="id != null" >
            #{id,jdbcType=INTEGER},
          </if>
          <if test="busTableName != null" >
            #{busTableName,jdbcType=VARCHAR},
          </if>
          <if test="serno != null" >
            #{serno,jdbcType=INTEGER},
          </if>
          <if test="updateTime != null" >
            #{updateTime,jdbcType=TIMESTAMP},
          </if>
        </trim>
      </insert>
      <update id="updateByPrimaryKeySelective" parameterType="com.wdq.vo.GlobalIdVo" >
        
        update global_id
        <set >
          <if test="busTableName != null" >
            bus_table_name = #{busTableName,jdbcType=VARCHAR},
          </if>
          <if test="serno != null" >
            serno = #{serno,jdbcType=INTEGER},
          </if>
          <if test="updateTime != null" >
            update_time = #{updateTime,jdbcType=TIMESTAMP},
          </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
      </update>
      <update id="updateByPrimaryKey" parameterType="com.wdq.vo.GlobalIdVo" >
        
        update global_id
        set bus_table_name = #{busTableName,jdbcType=VARCHAR},
          serno = #{serno,jdbcType=INTEGER},
          update_time = #{updateTime,jdbcType=TIMESTAMP}
        where id = #{id,jdbcType=INTEGER}
      </update>
    
      <update id="updateSerNoByTableName" parameterType="com.wdq.vo.GlobalIdVo" >
        
        update global_id
        set  serno = #{serno,jdbcType=INTEGER},
        update_time = #{updateTime,jdbcType=TIMESTAMP}
        where bus_table_name = #{busTableName,jdbcType=VARCHAR}
      </update>
    
      <select id="getSerNo" resultType="java.lang.Long">
        SELECT serno FROM global_id WHERE bus_table_name=#{tableName}
      </select>
    
      <select id="getRecordTotal" resultType="java.lang.Integer">
        select COUNT(0) FROM global_id
      </select>
    
    </mapper>

vo：

package com.wdq.vo;
    
    import java.io.Serializable;
    import java.util.Date;
    
    public class GlobalIdVo implements Serializable {
        /**
         * This field was generated by MyBatis Generator.
         * This field corresponds to the database column global_id.id
         *
         * @mbggenerated
         */
        private Integer id;
    
        /**
         * This field was generated by MyBatis Generator.
         * This field corresponds to the database column global_id.bus_table_name
         *
         * @mbggenerated
         */
        private String busTableName;
    
        /**
         * This field was generated by MyBatis Generator.
         * This field corresponds to the database column global_id.serno
         *
         * @mbggenerated
         */
        private Long serno;
    
        /**
         * This field was generated by MyBatis Generator.
         * This field corresponds to the database column global_id.update_time
         *
         * @mbggenerated
         */
        private Date updateTime;
    
        /**
         * This field was generated by MyBatis Generator.
         * This field corresponds to the database table global_id
         *
         * @mbggenerated
         */
        private static final long serialVersionUID = 1L;
    
        /**
         * This method was generated by MyBatis Generator.
         * This method returns the value of the database column global_id.id
         *
         * @return the value of global_id.id
         *
         * @mbggenerated
         */
        public Integer getId() {
            return id;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method sets the value of the database column global_id.id
         *
         * @param id the value for global_id.id
         *
         * @mbggenerated
         */
        public void setId(Integer id) {
            this.id = id;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method returns the value of the database column global_id.bus_table_name
         *
         * @return the value of global_id.bus_table_name
         *
         * @mbggenerated
         */
        public String getBusTableName() {
            return busTableName;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method sets the value of the database column global_id.bus_table_name
         *
         * @param busTableName the value for global_id.bus_table_name
         *
         * @mbggenerated
         */
        public void setBusTableName(String busTableName) {
            this.busTableName = busTableName == null ? null : busTableName.trim();
        }
    
        public Long getSerno() {
            return serno;
        }
    
        public void setSerno(Long serno) {
            this.serno = serno;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method returns the value of the database column global_id.update_time
         *
         * @return the value of global_id.update_time
         *
         * @mbggenerated
         */
        public Date getUpdateTime() {
            return updateTime;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method sets the value of the database column global_id.update_time
         *
         * @param updateTime the value for global_id.update_time
         *
         * @mbggenerated
         */
        public void setUpdateTime(Date updateTime) {
            this.updateTime = updateTime;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table global_id
         *
         * @mbggenerated
         */
        @Override
        public boolean equals(Object that) {
            if (this == that) {
                return true;
            }
            if (that == null) {
                return false;
            }
            if (getClass() != that.getClass()) {
                return false;
            }
            GlobalIdVo other = (GlobalIdVo) that;
            return (this.getId() == null ? other.getId() == null : this.getId().equals(other.getId()))
                && (this.getBusTableName() == null ? other.getBusTableName() == null : this.getBusTableName().equals(other.getBusTableName()))
                && (this.getSerno() == null ? other.getSerno() == null : this.getSerno().equals(other.getSerno()))
                && (this.getUpdateTime() == null ? other.getUpdateTime() == null : this.getUpdateTime().equals(other.getUpdateTime()));
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table global_id
         *
         * @mbggenerated
         */
        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((getId() == null) ? 0 : getId().hashCode());
            result = prime * result + ((getBusTableName() == null) ? 0 : getBusTableName().hashCode());
            result = prime * result + ((getSerno() == null) ? 0 : getSerno().hashCode());
            result = prime * result + ((getUpdateTime() == null) ? 0 : getUpdateTime().hashCode());
            return result;
        }
    
        /**
         * This method was generated by MyBatis Generator.
         * This method corresponds to the database table global_id
         *
         * @mbggenerated
         */
        @Override
        public String toString() {
            StringBuilder sb = new StringBuilder();
            sb.append(getClass().getSimpleName());
            sb.append(" [");
            sb.append("Hash = ").append(hashCode());
            sb.append(", id=").append(id);
            sb.append(", busTableName=").append(busTableName);
            sb.append(", serno=").append(serno);
            sb.append(", updateTime=").append(updateTime);
            sb.append(", serialVersionUID=").append(serialVersionUID);
            sb.append("]");
            return sb.toString();
        }
    }

mybatis配置：

<?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:p="http://www.springframework.org/schema/p"  
        xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:context="http://www.springframework.org/schema/context"  
        xmlns:mvc="http://www.springframework.org/schema/mvc"
    	xmlns:aop="http://www.springframework.org/schema/aop"
        xmlns:util="http://www.springframework.org/schema/util"
        xsi:schemaLocation="http://www.springframework.org/schema/beans    
                            http://www.springframework.org/schema/beans/spring-beans-4.0.xsd    
                            http://www.springframework.org/schema/context    
                            http://www.springframework.org/schema/context/spring-context-4.0.xsd   
                            http://www.springframework.org/schema/tx 
                            http://www.springframework.org/schema/tx/spring-tx-4.0.xsd 
                            http://www.springframework.org/schema/mvc    
                            http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd
                            http://www.springframework.org/schema/util 
                           	http://www.springframework.org/schema/util/spring-util-4.0.xsd
                           	http://www.springframework.org/schema/aop
    							http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
                            ">
         
        
       <context:annotation-config />
    	<context:component-scan base-package="com.wdq" >
    		<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
    	</context:component-scan>
    
        
        <context:property-placeholder location="classpath:jdbc.properties" />
    
    	
    	<tx:annotation-driven transaction-manager="transactionManager" />
    
    	<tx:advice id="txAdvice" transaction-manager="transactionManager">
    		<tx:attributes>
    			<tx:method name="add*" propagation="REQUIRED" />
    			<tx:method name="insert*" propagation="REQUIRED" />
    			<tx:method name="save*" propagation="REQUIRED" />
    			<tx:method name="update*" propagation="REQUIRED" />
    			<tx:method name="del*" propagation="REQUIRED" />
    		</tx:attributes>
    	</tx:advice>
    
    	<aop:config>
    		<aop:pointcut expression="execution(* com.wdq.service.impl.*.*(..))"
    					  id="interceptorpointcut" />
    		<aop:advisor advice-ref="txAdvice" pointcut-ref="interceptorpointcut" order="2"/>
    		
    		
    	</aop:config>
    
    	<bean id="transactionManager"
    		  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource"></property>
    	</bean>
    
    	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    		
    		<property name="driverClassName" value="${db.driver}" />
    		<property name="url" value="${db.url}" />
    		<property name="username" value="${db.username}" />
    		<property name="password" value="${db.password}" />
    
    		
    		<property name="initialSize" value="${db.initialSize}" />
    		<property name="minIdle" value="${db.maxIdle}" />
    		<property name="maxActive" value="${db.maxActive}" />
    
    		
    		<property name="maxWait" value="${db.maxWait}" />
    
    		
    		<property name="timeBetweenEvictionRunsMillis" value="60000" />
    
    		
    		<property name="minEvictableIdleTimeMillis" value="300000" />
    
    		<property name="validationQuery" value="SELECT 'x'" />
    		<property name="testWhileIdle" value="false" />
    		<property name="testOnBorrow" value="false" />
    		<property name="testOnReturn" value="false" />
    
    		
    		<property name="poolPreparedStatements" value="false" />
    		<property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
    
    		
    		<property name="filters" value="slf4j" />
    		<property name="proxyFilters">
    			<list>
    				<ref bean="stat-filter" />
    			</list>
    		</property>
    	</bean>
    
    	
    	<bean id="stat-filter" class="com.alibaba.druid.filter.stat.StatFilter">
    		<property name="slowSqlMillis" value="1000" />
    		<property name="logSlowSql" value="false" />
    	</bean>
    
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
            <property name="dataSource" ref="dataSource" />  
            
            <property name="mapperLocations">
            	<array>
    				<value>classpath:com/wdq/dao/mapper/*.xml</value>
    			</array>
            </property>
            <property name="configLocation" value="classpath:mybatis-config.xml"/>
    		<property name="typeAliasesPackage" value="com.wdq.vo" />
    	</bean>
      
        
        <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
            <property name="basePackage" value="com.wdq.dao" />
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>  
        </bean>  
    
    </beans>

globalId表的创建：

/*
    SQLyog Ultimate v12.09 (64 bit)
    MySQL - 8.0.13 : Database - wdq
    *********************************************************************
    */
    
    
    /*!40101 SET NAMES utf8 */;
    
    /*!40101 SET SQL_MODE=''*/;
    
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    CREATE DATABASE /*!32312 IF NOT EXISTS*/`wdq` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;
    
    USE `wdq`;
    
    /*Table structure for table `global_id` */
    
    DROP TABLE IF EXISTS `global_id`;
    
    CREATE TABLE `global_id` (
      `id` int(11) NOT NULL,
      `bus_table_name` varchar(255) NOT NULL COMMENT '表名（为哪张表生成的id）',
      `serno` bigint(255) NOT NULL COMMENT '全局id',
      `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '全局id生成器表',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

jdbc相关配置：

db.driver=com.mysql.cj.jdbc.Driver
    
    #\u672C\u5730
    db.url=jdbc:mysql://192.168.2.97:3306/wdq?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&&allowMultiQueries=true&&serverTimezone=Hongkong&&useSSL=true
    db.username=root
    db.password=root
    
    #\u521D\u59CB\u8FDE\u63A5\u6570
    db.initialSize=1  
    #\u6700\u5927\u8FDE\u63A5\u6570
    db.maxActive=20  
    #\u6700\u5927\u7A7A\u95F2
    db.maxIdle=20  
    #\u6700\u5C0F\u7A7A\u95F2 
    db.minIdle=1  
    #\u6700\u5927\u7B49\u5F85\u65F6\u95F4
    db.maxWait=60000
    
    #redis\u914D\u7F6E
    redis.host=192.168.100.189
    redis.port=6379
    redis.pass=
      
    redis.maxIdle=300  
    redis.maxActive=600  
    redis.maxWait=1000  
    redis.testOnBorrow=true

mybatis-config配置：

<?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>
            
            <setting name="logImpl" value="LOG4J" />
        </settings>
    
    
        <plugins>
            
            <plugin interceptor="com.github.pagehelper.PageInterceptor">
            </plugin>
        </plugins>
    
    
       
    
    </configuration>

更详细解释参考：[https://blog.csdn.net/u010398771/article/details/79765836][https_blog.csdn.net_u010398771_article_details_79765836]

[https_blog.csdn.net_u010398771_article_details_79765836]: https://blog.csdn.net/u010398771/article/details/79765836