How to simulate a DB for testing (Java)?

Question

I'm programming in Java and my applications are making a lot of use of DB. Hence, it is important for me to be able to test my DB usage easily.
What DB tests are all about? For me, they should supply two simple requirements:

1. Verify SQL syntax.
2. More importantly, check that the data is selected/updated/inserted correctly, according to a given situation.

Well then, it seems that all I need is a DB.
But actually, I prefer not, as there are few difficulties using a DB for a test:

• "Just get yourself a testing DB, how hard could it be?" - Well, in my working place, to have a personal testing DB is pretty impossible. You have to use a "public" DB, which is accessible for everyone.
• "These tests sure ain't fast..." - DB tests tend to be slower than usual tests. It's really not ideal to have slow tests.
• "This program should handle any case!" - It becomes somewhat annoying and even impossible to try and simulate each and every case in a DB. For each case a certain amount of insert/update queries should be made, which is annoying and takes time.
• "Wait a second, how do you know there are 542 rows in that table?" - One of the main principles in testing, is to be able to test the functionality in a way different from that of your tested-code. When using a DB, there's usually one way to do something, therefore the test is exactly the same as the core-code.

So, you can figure out I don't like DBs when it comes to tests (of course I will have to get to this in some point, but I'd rather get there later on my testing, after I found most bugs using the rest of the test methods). But what am I looking for?

I'm looking for a way to simulate a DB, a mock DB, using the file system or just virtual memory. I thought that maybe there's a Java tool/package which allows to simply construct (using code interface) a DB mock per test, with simulated tables and rows, with SQL verification, and with a code interface for monitoring its status (rather then using SQL).

Are you familiar with this kind of tool?

---

Edit: Thanks for the answers! Although I was asking for a tool, you also provided me with some tips concerning the problem :) It will take me some time to check out your offers, so I can't say right now whether your answers were satisfying not.

Anyway, here's a better view of what I'm looking for - Imagine a class named DBMonitor, that one of its features is finding the number of rows in a table. Here is an imaginary code of how I would like to test that feature using JUnit:

public class TestDBMonitor extends TestCase {

    @Override
    public void setUp() throws Exception {

       MockConnection connection = new MockConnection();

       this.tableName = "table1";
       MockTable table = new MockTable(tableName);

       String columnName = "column1";
       ColumnType columnType = ColumnType.NUMBER;
       int columnSize = 50;
       MockColumn column = new MockColumn(columnName, columnType, columnSize);
       table.addColumn(column);

       for (int i = 0; i < 20; i++) {
           HashMap<MockColumn, Object> fields = new HashMap<MockColumn, Object>();
           fields.put(column, i);
           table.addRow(fields);
       }

       this.connection = connection;
    }

    @Test
    public void testGatherStatistics() throws Exception {

       DBMonitor monitor = new DBMonitor(connection);
       monitor.gatherStatistics();
       assertEquals(((MockConnection) connection).getNumberOfRows(tableName),
                    monitor.getNumberOfRows(tableName));
    }

    String tableName;
    Connection connection;
}

I hope this code is clear enough to understand my idea (excuse me for syntax errors, I was typing manually without my dear Eclipse :P).

By the way, I use ORM partially, and my raw SQL queries are quite simple and shouldn't differ from one platform to another.

Answer 1

Java comes with Java DB.

That said, I would advise against using a different type of DB than what you use in production unless you go through an ORM layer. Otherwise, your SQL might not be as cross-platform as you think.

Also check out DbUnit