文档章节

H2数据库创建自定义函数

一书生
 一书生
发布于 2016/06/21 23:15
字数 1049
阅读 147
收藏 1
H2

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作为持久层)。

© 著作权归作者所有

共有 人打赏支持
一书生
粉丝 3
博文 13
码字总数 5030
作品 0
浦东
高级程序员
私信 提问
Java嵌入式数据库H2学习总结(一)——H2数据库入门

一、H2数据库介绍   常用的开源数据库有:H2,Derby,HSQLDB,MySQL,PostgreSQL。其中H2和HSQLDB类似,十分适合作为嵌入式数据库使用,而其它的数据库大部分都需要安装独立的客户端和服务...

guicaizhou
2016/07/08
0
0
WEB框架之---Django初级篇

一、下载与安装(Windows版) 下载地址:https://www.djangoproject.com/download/ 或者通过命令:pip install django==1.11.2进行安装(Python3使用pip3)推荐使用此版本 安装完成后找到dja...

AltBoy
2018/06/26
0
0
SpringBoot默认的H2数据库如何查看?

如果大家用SpringBoot的话,如果在pom.xml文件里面加入了H2 数据库的引用的话,其将会把数据持久化到H2 内存数据库中。 那么,我们如何查看其被持久化到H2数据库中的数据,且不需要安装第三发...

chancein007
2017/01/11
0
0
H2数据库使用

常用的开源数据库:H2,Derby,HSQLDB,MySQL,PostgreSQL。其中H2,HSQLDB类似,十分适合作为嵌入式数据库使用,其它的数据库大部分都需要安装独立的客户端和服务器端。 H2的优势: 1、h2采...

郭恩洲_OSC博客
2015/10/10
383
1
H2 script STRINGDECODE

MYSQL 导出 到 H2 近期想将开发中系统的数据库随身携带,以便随时学习。最好的办法当然是将数据库随身携带,所以想到了导出到H2中。H2的压缩模式很厉害,在MYSQL中1G左右的空间,压缩后占用6...

wonder365
2013/02/18
0
0

没有更多内容

加载失败,请刷新页面

加载更多

mysql 查询当天、本周,本月,上一个月的数据

今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1 近7天 SELECT * FROM 表名 wher......

BraveLN
20分钟前
2
0
Android Multimedia框架总结(六)C++中MediaPlayer的C/S架构

前面几节中,都是通过java层调用到jni中,jni向下到c++层并未介绍 看下Java层一个方法在c++层 MediaPlayer后续过程 frameworks/av/media/libmedia/MediaPlayer.cpp 找一个我们之前熟悉的setDa...

天王盖地虎626
37分钟前
2
0
【Linux】【MySQL】CentOS7安装最新版MySQL8.0.13(最新版MySQL从安装到运行)

1、前言   框框博客在线报时:2018-11-07 19:31:06   当前MySQL最新版本:8.0.13 (听说比5.7快2倍)   官方之前表示:MySQL 8.0 正式版 8.0.11 已发布,MySQL 8 要比 MySQL 5.7 快 2 ...

Code辉
38分钟前
1
0
oracle dg备库重建redolog:ora-00313,ora-00312

trace文件: Errors in file /crbank/dbs/app/product/diag/rdbms/rdbs/dbs/trace/dbs_mrp0_24445130.trc: ORA-00313: open failed for members of log group 8 of thread 1 ORA-00312: onl......

hnairdb
51分钟前
1
0
深入分析Java I/O的工作机制 (一)

1.Java的I/O类库的基本架构 先说一下什么是类库:可以说是类的集合,类库包括接口、抽象类、具体类等。 I/O是机器获取和交互信息的主要渠道。 java在I/O上也一直在做持续的优化,在1.4版开始...

java菜分享
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部