Take the 2-minute tour ×

I have a lot of DAO tests which access the database. I tried to mock them using Java in memory database like H2. But the problem is the ddl scripts contain partition statements which does not seem to be supported by H2. I basically want to use the same ddl scripts as in production and do not want to modify them in any manner for my "unit" tests. I also tried HSQL, but found H2 more closely resembles oracle, but even then it doesnt support partition. Is there a workaround for this ?

I did look at the following Create an in-memory database structure from an Oracle instance It talks about using H2 but I have already mentioned its shortcomings. Looking for some alternatives.

share|improve this question
You can't mock an Oracle database (or any DBMS that is) if you want to have meaningful tests. Setup a test-instance against which you run the tests. Btw: you should mention if you are actually speaking about partitioning or window functions (that use over(partition by ..) –  a_horse_with_no_name Feb 12 '13 at 21:44
+1 for @a_horse_with_no_name's comment. Each database has a slightly different syntax, you will do well to set up a test instance of oracle –  Miserable Variable Feb 12 '13 at 22:04
@MiserableVariable It depends on the complexity of the DAOs and on what you want to test. If the DAOs just do simple CRUD operations, H2 is by far sufficient for unit tests. But I agree there should also be tests (i.e. integration tests) running on Oracle. –  Stefan Ferstl Feb 12 '13 at 22:50
Correct, in general it is ok to use H2 if the SQL capabilities used by your program is supported by H2 and Oracle in the same manner, i.e. without any subtle differences. It may also ok if you are using an OR mapping library/framework. But in practice I have found it necessary to use the actual db server to be used in production –  Miserable Variable Feb 12 '13 at 23:06
add comment

2 Answers

If you want meaningful tests, you'll need to run them against an instance of the same database version as you will run in production.

share|improve this answer
add comment

You could use Oracle's data dictionary to generate a H2 schema containing tables, their relationships, indices, constraints, etc. I am using such a generated schema for tests on an in-memory H2 database.

For example to get all tables and their columns, you can use this query:

  FROM user_tables ut JOIN user_tab_columns utc
    ON ut.table_name = utc.table_name;

Similar queries can be made for foreign keys, other constraints, indices, sequences and all other things you need for your tests. What you further need is a templating engine to transform the results of the queries into an H2 DDL script.

share|improve this answer
But that still won't "implement" support for windowing functions or Oracle specific SQL extensions. –  a_horse_with_no_name Feb 12 '13 at 22:31
@a_horse_with_no_name nope, but at least it solves the problem with Oracle-specific DDLs. –  Stefan Ferstl Feb 12 '13 at 22:37
add comment

Your Answer


