文档章节

H2数据库创建自定义函数

一书生
 一书生
发布于 2016/06/21 23:15
字数 1049
阅读 139
收藏 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
浦东
高级程序员
私信 提问
WEB框架之---Django初级篇

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

AltBoy
06/26
0
0
H2数据库使用

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

郭恩洲_OSC博客
2015/10/10
383
1
SpringBoot默认的H2数据库如何查看?

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

chancein007
2017/01/11
0
0
H2 script STRINGDECODE

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

wonder365
2013/02/18
0
0
使用 Eclipse BIRT 给科学数据绘图

BIRT 是为商业报告设计的,但您同样可以使用它创建科学数据图。通过创建以下两个图,我们将学习如何将 BIRT 用于科学研究:变星的亮度图;每年的太阳黑子数量图。 顾名思义,Eclipse Busines...

红薯
2009/10/11
1K
1

没有更多内容

加载失败,请刷新页面

加载更多

当程序员有了中年危机 你会发现你就是个屁

前言 程序员是一个怎样的存在?引用一句鸡汤的名言来说:你以为你用双手改变了世界,实际上是苍老了自己。为什么我今天会抛出这个话题,其实我也是一个懵懂的少年,我也曾经为了成为一名程序...

架构师springboot
5分钟前
0
0
大型网站B2C商城项目实战+MongoDB+Redis+zookeeper+MySQL

本文列出了当今计算机软件开发和应用领域最关键部分,如果你想保证你现在以及未来的几年不失业,那么你最好跟上这些技术的发展。虽然你不必对这十种技术样样精通,但至少应该对它们非常熟悉。...

java知识分子
5分钟前
0
0
大型企业网络系统集成方案如何设计?

网络系统集成是企业实现无纸化办公和即时通讯办公的基础建设,在以生产效率为核心竞争力的市场中,企业想要快速获取信息并有效提高企业工作效率及业务能力,企业网络系统集成是必不可少的,由...

Java干货分享
7分钟前
0
0
Spring应用学习——IOC

1. Spring简介 1. Spring的出现是为了取代EJB(Enterprise JavaBean)的臃肿、低效、脱离现实的缺点。Spring致力于J2EE应用的各层(表现层、业务层、持久层)的解决方案,Spring是企业应用开...

江左煤郎
7分钟前
0
0
用Redis轻松实现秒杀系统

导论 曾经被问过好多次怎样实现秒杀系统的问题。昨天又在CSDN架构师微信群被问到了。因此这里把我设想的实现秒杀系统的价格设计分享出来。供大家参考。 秒杀系统的架构设计 秒杀系统,是典型...

James-
15分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部