【SpringBoot+MySQL】后台管理系统——基础工具:主键生成

一、 前言

在正式开发之前，需要给大家介绍一些常用的工具，提前给大家介绍以下，方便大家后期看代码。
本篇先介绍第主键生成工具。

二、 主键生成

一般数据库表 主键都采用int 自增长的字段。在这里给大家介绍一种生成方式。

1. sql2o

先给大家上一个工具类

package com.base.infrastructure.util.sql;

import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.sql2o.Sql2o;

import lombok.var;

@Component
public class Sql2oUtil {
    @Value("${spring.datasource.username}")
    private String dbUsername;

    @Value("${spring.datasource.password}")
    private String dbPassword;

    @Value("${spring.datasource.url}")
    private String dbUrl;

    private static Sql2o sql2o;


    private Sql2o getSql2o() {
        if (sql2o == null) {
            sql2o = new Sql2o(dbUrl, dbUsername, dbPassword);
        }
        return sql2o;
    }

    public <T> List<T> executeAndFetch(String sql, Map<String, Object> params, Class<T> clazz) throws Exception {
        try {
            var con = getSql2o().open();
            var query = con.createQuery(sql);
            if (params != null && !params.isEmpty()) {
                for (var paramKey : params.keySet()) {
                    query.addParameter(paramKey, params.get(paramKey));
                }
            }
            return query.executeAndFetch(clazz);
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }
    }

    public void executeUpdate(String sql, Map<String, Object> params) throws Exception {
        try {
            var con = getSql2o().open();

            var query = con.createQuery(sql);
            if (params != null && !params.isEmpty()) {
                for (var paramKey : params.keySet()) {
                    query.addParameter(paramKey, params.get(paramKey));
                }
            }
            query.executeUpdate();
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        }

    }
}

2. 主键Prefix枚举

package com.management.common.enums;

/**
 * 数据库表主键ID的前缀
 */
public enum PrefixEnum {

    t_organization("OR");


    private String priFix;

    PrefixEnum(String priFix) {
        this.priFix = priFix;
    }

    public String getPriFix() {
        return priFix;
    }

}

3. 主键生成器

package com.management.common.primaryKey;

import cn.hutool.core.collection.CollectionUtil;
import com.management.common.enums.PrefixEnum;
import com.management.util.sql.Sql2oUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
@Slf4j
public class PrimaryKeyGenerator {
    @Autowired
    private Sql2oUtil sql2oUtil;

    private static final Long maxKey = 9999999999999999L;

    public String generateKey(String keyPrefix, PrefixEnum table) throws Exception {
        try {
            log.info("[生成主键]- keyPrefix：{}, table:{}", keyPrefix, table);
            String sql = "SELECT id from " + table + " ORDER BY id LIMIT 1;";
            log.info("[生成主键]- sql：{}", sql);

            List<String> list = sql2oUtil.executeAndFetch(sql, null, String.class);
            String key;
            if (!CollectionUtil.isEmpty(list)) {
                key = keyPrefix + (Long.valueOf(list.get(0).substring(2)) - 1);
            } else {
                key = keyPrefix + maxKey;
            }

            log.info("[生成主键]- key：{}", key);
            return key;

        } catch (Exception e) {
            e.printStackTrace();
            log.error("生成主键error:{}", e.getMessage());
            throw e;
        }
    }

}

4. 测试

新建一个表，也是我们后期要用的

sql语句如下：

CREATE TABLE `t_organization`  (
  `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
  `parentId` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上级部门Id',
  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门编号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `levelCode` int(0) NULL DEFAULT NULL COMMENT '部门等级',
  `createdAt` date NULL DEFAULT NULL COMMENT '创建时间',
  `createdById` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建操作人',
  `deleted` tinyint(0) NULL DEFAULT NULL COMMENT '是否删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 22 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

新建接口里调用PrimaryKeyGenerator 的 generateKey方法。

/**
 * (Organization)表控制层
 */
@Api(value = "机构相关接口")
@RestController
@Slf4j
@RequestMapping("organization")
public class OrganizationController {

        @Autowired
        private PrimaryKeyGenerator primaryKeyGenerator;


        @ApiOperation(value = "测试生成主键")
        @GetMapping("/generateKey")
        @ResponseBody
        public ServiceApiResult<String> generateKey(@RequestParam String keyPrefix, @RequestParam PrefixEnum table) {
            try {
                String primaryKey = primaryKeyGenerator.generateKey(keyPrefix, table);
                return ServiceApiResult.success(primaryKey);
            } catch (Exception e) {
                e.printStackTrace();
                return ServiceApiResult.error(e.getMessage());
            }
        }

目录结构如下图：

运行结果如下图：