文档章节

H2数据库创建自定义函数

一书生
 一书生
发布于 2016/06/21 23:15
字数 1049
阅读 119
收藏 1
点赞 0
评论 0
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 ⋅ 2017/06/27 ⋅ 0

MYCP开发指南系列之:开发第一个C++ Servlet

1.1 开发第一个 C++ Servlet C++ Servlet 跟CSP 一样,是MYCP 的web 层组件,用于输出动态HTML 页面,实现web 应用的界面显示。关于CSP 的开发规范请看《CSP11.pdf 》文档,本节主要简单描述...

红薯 ⋅ 2011/01/05 ⋅ 7

使用 Eclipse BIRT 给科学数据绘图

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

红薯 ⋅ 2009/10/11 ⋅ 1

H2 script STRINGDECODE

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

wonder365 ⋅ 2013/02/18 ⋅ 0

SpringBoot默认的H2数据库如何查看?

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

chancein007 ⋅ 2017/01/11 ⋅ 0

H2数据库使用

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

郭恩洲_OSC博客 ⋅ 2015/10/10 ⋅ 1

H2数据库使用

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

Mr&Cheng ⋅ 2013/01/20 ⋅ 0

分布式SQL引擎--Lealone

Lealone 为 HBase 提供一个分布式SQL引擎,尝试将BigTable(HBase)和 RDBMS (H2数据库) 结合的项目。 Lealone 发音 ['li:ləʊn] 这是我新造的英文单词,灵感来自于在淘宝工作期间办公桌上那些...

匿名 ⋅ 2013/03/20 ⋅ 1

AngularJS 学习笔记---AngularJS 指令

AngularJS 指令 AngularJS 通过被称为 指令 的新属性来扩展 HTML。 AngularJS 通过内置的指令来为应用添加功能。 AngularJS 允许你自定义指令。 AngularJS 指令 AngularJS 指令是扩展的 HTML...

恋空御月 ⋅ 2016/05/27 ⋅ 0

H2 数据库 小结

1.数据库连接的jdbc url h2分嵌入模式和服务模式,根据不同的模式和情况。连接数据库jdbc url的写法不同 连接内嵌模式的数据库 "jdbc:h2:file:D:/test" 连接自定目录下的指定数据库 "jdbc:h2...

神勇小白鼠 ⋅ 2011/01/18 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

JavaScript零基础入门——(八)JavaScript的数组

JavaScript零基础入门——(八)JavaScript的数组 欢迎大家回到我们的JavaScript零基础入门,上一节课我们讲了有关JavaScript正则表达式的相关知识点,便于大家更好的对字符串进行处理。这一...

JandenMa ⋅ 今天 ⋅ 0

sbt网络问题解决方案

转自:http://dblab.xmu.edu.cn/blog/maven-network-problem/ cd ~/.sbt/launchers/0.13.9unzip -q ./sbt-launch.jar 修改 vi sbt/sbt.boot.properties 增加一个oschina库地址: [reposit......

狐狸老侠 ⋅ 今天 ⋅ 0

大数据,必须掌握的10项顶级安全技术

我们看到越来越多的数据泄漏事故、勒索软件和其他类型的网络攻击,这使得安全成为一个热门话题。 去年,企业IT面临的威胁仍然处于非常高的水平,每天都会看到媒体报道大量数据泄漏事故和攻击...

p柯西 ⋅ 今天 ⋅ 0

Linux下安装配置Hadoop2.7.6

前提 安装jdk 下载 wget http://mirrors.hust.edu.cn/apache/hadoop/common/hadoop-2.7.6/hadoop-2.7.6.tar.gz 解压 配置 vim /etc/profile # 配置java环境变量 export JAVA_HOME=/opt/jdk1......

晨猫 ⋅ 今天 ⋅ 0

crontab工具介绍

crontab crontab 是一个用于设置周期性被执行的任务工具。 周期性执行的任务列表称为Cron Table crontab(选项)(参数) -e:编辑该用户的计时器设置; -l:列出该用户的计时器设置; -r:删除该...

Linux学习笔记 ⋅ 今天 ⋅ 0

深入Java多线程——Java内存模型深入(2)

5. final域的内存语义 5.1 final域的重排序规则 1.对于final域,编译器和处理器要遵守两个重排序规则: (1)在构造函数内对一个final域的写入,与随后把这个被构造对象的引用赋值给一个引用...

江左煤郎 ⋅ 今天 ⋅ 0

面试-正向代理和反向代理

面试-正向代理和反向代理 Nginx 是一个高性能的反向代理服务器,但同时也支持正向代理方式的配置。

秋日芒草 ⋅ 今天 ⋅ 0

Spring 依赖注入(DI)

1、Setter方法注入: 通过设置方法注入依赖。这种方法既简单又常用。 类中定义set()方法: public class HelloWorldOutput{ HelloWorld helloWorld; public void setHelloWorld...

霍淇滨 ⋅ 昨天 ⋅ 0

马氏距离与欧氏距离

马氏距离 马氏距离也可以定义为两个服从同一分布并且其协方差矩阵为Σ的随机变量之间的差异程度。 如果协方差矩阵为单位矩阵,那么马氏距离就简化为欧氏距离,如果协方差矩阵为对角阵,则其也...

漫步当下 ⋅ 昨天 ⋅ 0

聊聊spring cloud的RequestRateLimiterGatewayFilter

序 本文主要研究一下spring cloud的RequestRateLimiterGatewayFilter GatewayAutoConfiguration @Configuration@ConditionalOnProperty(name = "spring.cloud.gateway.enabled", matchIfMi......

go4it ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部