[如何从数据库提取序列号]数据库序列号生成

本文主要讲解了如何在分布式环境下高效获取唯一序列号（ID）

设计思路

整体思路

具体代码实现

数据库DDL

CREATE TABLE `t_sequence_generator` ( `f_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID号段起始因子', `f_stub` varchar(100) NOT NULL COMMENT '服务器IP地址', PRIMARY KEY (`f_id`), UNIQUE KEY `f_stub_idx` (`f_stub`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分布式下每台服务器的ID增量因子';

数据库操作类

package com.uuid; import java.sql.*; /** * \* Created with IntelliJ IDEA. * \* @author: yanghua * \* @Date: 2017/12/6 * \* @Description:(数据库操作) * \ */ public class DBUtil { private String url = "jdbc:mysql://localhost:3306/plate?user=root&password=12345&useUnicode=true&" + "characterEncoding=UTF8&useSSL=false&serverTimezone=GMT"; private Connection connection = null; private Statement stmt = null; public DBUtil() { try { Cla("com.my;); connection = DriverManager.getConnection(url); stmt = connec(); } catch (Exception e) { e.printStackTrace(); } } /** * 查询ID * @param ip * @return */ public Long getId(String ip) { //REPLACE需要执行成功：REPLACE INTO t_sequence_generator 影响的结果为1行 if(incrId(ip)) { String sql = "SELECT f_id FROM t_sequence_generator WHERE f_stub = \"" + ip + "\""; try { ResultSet resultSet = (sql); re(); String id = re("f_id"); return Long.valueOf(id); } catch (SQLException e) { e.printStackTrace(); } finally { try { (); connec(); } catch (SQLException e) { e.printStackTrace(); } } } return 0L; } /** * 自增ID * @param ip * @return */ private boolean incrId(String ip) { String sql = "REPLACE INTO t_sequence_generator(f_stub) VALUES('"+ip+"')"; try { int i = (sql); return i > 0 ? true : false; } catch (SQLException e) { e.printStackTrace(); } return false; } }

ID生产逻辑类

package com.uuid; import java.net.InetAddress; import java.net.UnknownHostException; import java.u; /** * \* Created with IntelliJ IDEA. * \* @author: JOBS * \* @Date: 2017/12/24 * \* @Description:(全局分布式ID获取) * \ */ public class GeneratorUUID { /** * ID号段步长 */ private static final Long STEP = 10000L; /** * 实例化 原子Long */ private static AtomicLong atomicLong = new AtomicLong(); /** * 某段ID的ID上限 */ private static volatile Long currentMaxId = 0L; private GeneratorUUID(){ //设置初始值:从数据库中取 Long currentId = getId(); currentMaxId = (currentId + 1L) * STEP; currentId = currentId * STEP; a(currentId); } /** * 静态内部类实现单例 */ private static class GeneratorUUIDInner { private final static GeneratorUUID GENERATORUUID = new GeneratorUUID(); } /** * 获取唯一实例 * @return */ public static GeneratorUUID getInstance() { return Genera; } /** * 获取ID * @return */ public Long getUUID() { //判断是否达到此号段最大值，没有则直接取号，否则取下一号段 Long id; i() < currentMaxId) { id = a(); } else { //双重锁校验：并发情况下有可能多个线程进入到这里 synchronized (this){ i() < currentMaxId) { id = a(); } else { Long id1 = getId(); //重置该段ID的ID上限 currentMaxId = (id1 + 1) * STEP; //设置新的初始值 a(id1 * STEP); //再次进行取号 id = a(); } } } return id; } /** *查询IP地址对应的ID * @return */ private Long getId() { /*1、如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据，否则，直接插入新数据。 要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。 使用此SQL查询ID REPLACE INTO t_sequence_generator(f_stub) VALUES('192.168.1.1'); SELECT f_id FROM t_sequence_generator WHERE f_stub = "192.168.1.1"; */ Long id = new DBUtil().getId(getIp()); return id; } /** * 获取本机IP * @return */ private static String getIp() { try { InetAddress localHost = Ine(); String ip = localHo(); return ip; } catch (UnknownHostException e) { e.printStackTrace(); } return null; } }

模拟并发测试类

package com.uuid; import java.u; import java.u; import java.u; /** * \* Created with IntelliJ IDEA. * \* @author: JOBS * \* @Date: 2017/12/24 * \* @Description:(并发测试：全局分布式ID获取) * \ */ public class ConcurrentThreadTest { private static int thread_num = 20000;//线程数,设置同时并发线程数 private static int client_num = 20000; public static void main(String[] args) { long l = Sy(); ExecutorService exec = Execu(); final Semaphore semp = new Semaphore(thread_num); for (int index = 0; index < client_num; index++) { final int NO = index; Runnable run = new Runnable() { public void run() { try { (); //业务逻辑 long l = Genera().getUUID(); Sy().getName() + "===" + l); (); } catch (Exception e) { e.printStackTrace(); } } }; exec.execute(run); } exec.shutdown(); } }

今天就写到这里，觉得可以烦请点赞关注！！！