title: How to simulate a DB for testing (Java)?
body: <p>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.<br>
  What DB tests are all about? For me, they should supply two simple requirements:  </p>
  
  <ol>
  <li>Verify SQL syntax.</li>
  <li>More importantly, check that the data is selected/updated/inserted correctly, according to a given situation.  </li>
  </ol>
  
  <p>Well then, it seems that all I need is a DB.<br>
  But actually, I prefer not, as there are few difficulties using a DB for a test:  </p>
  
  <ul>
  <li>"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.</li>
  <li>"These tests sure ain't fast..." - DB tests tend to be slower than usual tests. It's really not ideal to have slow tests.</li>
  <li>"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.</li>
  <li>"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.</li>
  </ul>
  
  <p>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?  </p>
  
  <p>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).  </p>
  
  <p>Are you familiar with this kind of tool?</p>
  
  <hr>
  
  <p><strong>Edit:</strong> 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.</p>
  
  <p>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:</p>
  
  <pre><code>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 &lt; 20; i++) {
             HashMap&lt;MockColumn, Object&gt; fields = new HashMap&lt;MockColumn, Object&gt;();
             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;
  }
  </code></pre>
  
  <p>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).  </p>
  
  <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.  </p>