H2数据库创建自定义函数

最新推荐文章于 2024-06-11 11:12:50 发布

weixin_34220179

最新推荐文章于 2024-06-11 11:12:50 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 java 测试

原文链接：https://my.oschina.net/lw900925/blog/698723

2019独角兽企业重金招聘Python工程师标准>>>

H2数据库是一个纯Java实现的内嵌数据库，功能强大，而且很轻量（运行只需要一个Jar文件）。关于H2数据库的详细介绍，可以看看H2 database的官方网站，下面记录一下我用H2数据自定义函数的过程。
项目中生产环境用的是MySQL数据库，其中表的主键生成策略如下：

新建一张表，用于保存各个表的主键自增值：

CREATE TABLE `wt_key_sequence` (
  `table_name` varchar(64) NOT NULL COMMENT '表名',
  `key_seq_no` bigint(10) DEFAULT '0' COMMENT '表主键的序列值',
  `seq_prefix` varchar(2) DEFAULT NULL COMMENT '序列前缀',
  PRIMARY KEY (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='主键序列';

编写一个MySQL函数，用来生成表的主键，主键的长度为12位。基本思路为：传入一个table_name，根据这个table_name查询wt_key_sequence表中的key_seq_no值，取出后加1，然后在更新这个值。将加1后的值转换为16进制字符串，并判断长度是否超过10位，如果未超过，前面补0。然后再拼接前缀seq_prefix的值，返回。最后生成的主键看起来可能是这样：130000003e1f。

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `WT_F_KEY_GENERATOR`(tableName VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
BEGIN
	-- 根据表自动生成主键序列，格式为：前缀（2位） + 序列值（10位）
	DECLARE m_tableName VARCHAR(255);
    DECLARE m_seqNo INT DEFAULT 0;
    DECLARE m_seqPrefix VARCHAR(2);
    DECLARE m_digit INT DEFAULT 10;
    
    DECLARE m_strSequence VARCHAR(12);
    
    -- 查询表是否注册了自动序列
	SELECT table_name INTO m_tableName FROM wt_key_sequence WHERE table_name=tableName;
    
    -- 如果没有记录，就新建一条记录
    IF m_tableName IS NULL THEN 
		INSERT INTO wt_key_sequence (table_name, key_seq_no) VALUES (tableName, 0);
    END IF;

    -- 查询序列值
    SELECT key_seq_no INTO m_seqNo FROM wt_key_sequence WHERE table_name=tableName;
    SELECT seq_prefix INTO m_seqPrefix FROM wt_key_sequence WHERE table_name=tableName;
    
    -- 序列自动加1，并更新原有值
    SET m_seqNo = m_seqNo + 1;
    UPDATE wt_key_sequence SET key_seq_no=m_seqNo WHERE table_name=tableName;
    
    -- 转换为16进制
    SET m_strSequence = HEX(m_seqNo);
    
    -- 如果长度不满10位，在前面补0
    WHILE LENGTH(m_strSequence) < m_digit DO 
		SET m_strSequence = CONCAT('0', m_strSequence);
    END WHILE;
    
    -- 拼接前缀
    SET m_strSequence = CONCAT(m_seqPrefix, m_strSequence);
    
	RETURN LOWER(m_strSequence);
END ;;
DELIMITER ;

在做单元测试的时候，我们往往使用的是H2这种内嵌型数据库，但是上面MySQL创建函数的脚本就不能直接在H2 database里用了，H2为我们提供了另一种方式，可以用Java编程的方式创建自定义的函数：

H2创建自定义函数的脚本如下：

-- ---------- H2 Database Functions ----------
CREATE ALIAS WT_F_KEY_GENERATOR FOR "org.matrixstudio.webtop.test.h2db.Functions.keyGeneratorFunction";

其中org.matrixstudio.webtop.test.h2db.Functions是一个Java类，keyGeneratorFunction是该类中的一个静态方法，该方法实现的就是上述MySQL自定义函数WT_F_KEY_GENERATOR的功能。

/**
 * Copyright 2015 (c) Matrix Studio. All Rights Reserved.
 */
package org.matrixstudio.webtop.test.h2db;

import java.sql.SQLException;

import org.apache.commons.configuration.Configuration;
import org.apache.commons.configuration.ConfigurationException;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * <p>H2数据库自定义函数。</p>
 * @author liuwei
 * @version 1.0
 */
public class Functions {
	
	private static Logger logger = LoggerFactory.getLogger(Functions.class);

	private static JdbcTemplate jdbcTemplate;
	private static DruidDataSource dataSource;
	
	static {
		try {
			// 初始化数据源
			Configuration configuration = new PropertiesConfiguration("application-test.properties");
			dataSource = new DruidDataSource();
			dataSource.setDriverClassName(configuration.getString("jdbc.dataSource.driverClassName"));
			dataSource.setUrl(configuration.getString("jdbc.dataSource.url"));
			dataSource.setUsername(configuration.getString("jdbc.dataSource.username"));
			dataSource.setPassword(configuration.getString("jdbc.dataSource.password"));
			dataSource.setInitialSize(1);
			dataSource.setMinIdle(1);
			dataSource.setMaxActive(20);
			dataSource.setMaxWait(60000L);
			dataSource.setTimeBetweenEvictionRunsMillis(60000L);
			dataSource.setMinEvictableIdleTimeMillis(300000L);
			dataSource.setValidationQuery("SELECT 1");
			dataSource.setTestWhileIdle(true);
			dataSource.setTestOnBorrow(false);
			dataSource.setTestOnReturn(false);
			dataSource.setPoolPreparedStatements(false);
			dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
			dataSource.setFilters("stat");
			
			dataSource.init();
			jdbcTemplate = new JdbcTemplate(dataSource);
			
		} catch (ConfigurationException | SQLException e) {
			logger.error(e.getMessage(), e);
		}
	}
	
	// ------------------------ H2 database functions ------------------------
    /**
	 * <p>主键生成器函数。</p>
	 * @param tableName 表名称
	 * @return 生成后的主键序列
	 */
	public static String keyGeneratorFunction(String tableName) {
		String primaryKey = null;
		int digit = 10;
		
		try {
			// 查询表是否注册了自动序列
			String strTableNameQuerySql = "SELECT table_name FROM wt_key_sequence WHERE table_name=?";
			String queryTableName = jdbcTemplate.queryForObject(strTableNameQuerySql, String.class, new Object[] {tableName});
			
			// 如果没有记录，就新建一条记录
			if (StringUtils.isBlank(queryTableName)) {
				String strKeySeqInsertSql = "INSERT INTO wt_key_sequence (table_name, key_seq_no) VALUES (?, ?)";
				jdbcTemplate.update(strKeySeqInsertSql, new Object[] {tableName, 0});
			}
			
			// 查询序列值
			String strSeqNoQuerySql = "SELECT key_seq_no FROM wt_key_sequence WHERE table_name=?";
			String strSeqPrefixQuerySql = "SELECT seq_prefix FROM wt_key_sequence WHERE table_name=?";
			int seqNo = jdbcTemplate.queryForObject(strSeqNoQuerySql, Integer.class, new Object[] {tableName});
			String seqPrefix = jdbcTemplate.queryForObject(strSeqPrefixQuerySql, String.class, new Object[] {tableName});
			
			// 序列自动加1，并更新原有值
			seqNo += 1;
			String strSeqNoUpdateSql = "UPDATE wt_key_sequence SET key_seq_no=? WHERE table_name=?";
			jdbcTemplate.update(strSeqNoUpdateSql, new Object[] {seqNo, tableName});
			
			
			// 转换为16进制
			String strSeqNo = Integer.toHexString(seqNo);

			// 如果长度不满10位，在前面补0
			while (strSeqNo.length() < digit) {
				strSeqNo = "0" + strSeqNo;
			}
			
			// 拼接前缀
			primaryKey = seqPrefix + strSeqNo;
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}		
		return primaryKey;
	}
}

这样做可以保证在切换到H2中做单元测试的时候，不用修改DAO层的查询语句（项目中使用的是Spring data JPA作为持久层）。

转载于:https://my.oschina.net/lw900925/blog/698723