H2数据库创建自定义函数
H2数据库创建自定义函数
一书生 发表于2年前
H2数据库创建自定义函数
  • 发表于 2年前
  • 阅读 115
  • 收藏 1
  • 点赞 0
  • 评论 0

标题:腾讯云 新注册用户域名抢购1元起>>>   

摘要: 对比一下MySQL创建自定义函数和H2 database创建自定义函数

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

  1. 新建一张表,用于保存各个表的主键自增值:
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='主键序列';
  1. 编写一个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作为持久层)。

标签: H2
共有 人打赏支持
粉丝 3
博文 13
码字总数 5030
×
一书生
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: