在进行单元测试时，支持H2数据库中的DB2功能 [英] Support DB2 functions in H2 database while unit testing

问题描述

我们有一个生产中的DB2数据库，并且我们正在建立一个H2内存数据库以进行测试.我了解即使在DB2模式下配置了H2，也不支持所有DB2功能.我们如何对包含数据库特定功能但H2中尚不支持的SQL进行单元测试?

We have a DB2 database in production, and we are setting up a H2 in-memory database for tests. I understand that not all DB2 functions are supported in H2 even when we configured it in DB2 mode. How do we unit test SQLs that contains database specific functions but are not yet supported in H2?

如果我们开始编写特定于H2的数据库服务，那么我们将结束编写功能代码的不同层.

If we start writing H2 specific DB services then we will end writing different layer of functional code.

不支持的功能

VARCHAR_FORMAT

VARCHAR_FORMAT

H2配置

jdbc:h2:mem:test; MODE = DB2

jdbc:h2:mem:test;MODE=DB2

H2版本

1.4.188

1.4.188

Java堆栈跟踪

Caused by: org.h2.jdbc.JdbcSQLException: Function "VARCHAR_FORMAT" not found; SQL statement:
select S.SCHEDULE_ID scheduleId, VARCHAR_FORMAT(S.START_DATE, 'DD-Mon-YYYY') startDate
from ScheduleSubscription S WITH UR  [90022-182]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.message.DbException.get(DbException.java:179) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.message.DbException.get(DbException.java:155) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readJavaFunction(Parser.java:2333) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readFunction(Parser.java:2385) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readTerm(Parser.java:2719) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readFactor(Parser.java:2251) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readSum(Parser.java:2238) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readConcat(Parser.java:2208) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readCondition(Parser.java:2058) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readAnd(Parser.java:2030) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.readExpression(Parser.java:2022) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1934) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1966) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parseSelectSub(Parser.java:1860) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1681) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parseSelect(Parser.java:1669) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parsePrepared(Parser.java:433) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parse(Parser.java:305) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.parse(Parser.java:277) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.command.Parser.prepareCommand(Parser.java:242) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.engine.Session.prepareLocal(Session.java:446) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.engine.Session.prepareCommand(Session.java:388) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1190) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72) ~[h2-1.4.182.jar:1.4.182]
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:666) ~[h2-1.4.182.jar:1.4.182]
    at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:295) ~[commons-dbcp-1.4.jar:1.4]
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:318) ~[commons-dbcp-1.4.jar:1.4]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_35]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_35]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_35]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_35]
    at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:54) ~[mybatis-3.2.8.jar:3.2.8]
    at $Proxy35.prepareStatement(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:73) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:85) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:57) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:73) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108) ~[mybatis-3.2.8.jar:3.2.8]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102) ~[mybatis-3.2.8.jar:3.2.8]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.6.0_35]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) ~[na:1.6.0_35]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) ~[na:1.6.0_35]
    at java.lang.reflect.Method.invoke(Method.java:597) ~[na:1.6.0_35]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ~[mybatis-spring-1.2.2.jar:1.2.2]
    ... 47 common frames omitted

推荐答案

创建一个名为 VARCHAR_FORMAT 的H2函数，并确保至少返回测试数据的正确值.

Create a H2 function called VARCHAR_FORMAT and make sure it returns proper values at least for the test data.

这当然需要为H2中尚不存在的每个功能付出努力，但是这是相当可行的.测试也保持清洁.

This of course requires effort for every function that doesn't already exist in H2, but is quite feasible. Tests stay clean too.

这篇关于在进行单元测试时，支持H2数据库中的DB2功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！