In memory Database to test mysql database operation API using in memory database with maven

Question

I have to test some api using JUnit test cases. Actually I have some simple JDBC database connection with API code to retrieve data from MYSQL database. But I need one inmemory databases to test the correctness of code. I am using maven. Can any body give me proper suggestion with steps. If possible please give some sample JUnit code to test that.

Thanks, RK

If your SQL code is generic and could be run across other databases, you can checkout the derby-maven-plugin and my answer to this SO post: http://stackoverflow.com/questions/14731178/creating-temporary-database-that-works-across-maven-test-phases/15089028#15089028. — carlspring, Feb 20 '14 at 17:54

TrueDub · Answer 1 · 2013-08-20T12:08:24.510

Have a look at DBUnit - their how-to is at http://dbunit.sourceforge.net/howto.html

It does exactly what you're looking for.

The DBunit site has lots of examples. What I do is the following:

set up the d/b in the @Before method, so each test gets a clean fixture. An example of the @Before method is:

Connection conn = dataSource.getConnection();
        try {
            IDatabaseConnection connection = new DatabaseConnection(conn);
            DatabaseConfig config = connection.getConfig();
            config.setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
                    new HsqldbDataTypeFactory());
            DatabaseOperation.CLEAN_INSERT.execute(connection, loadDutyData());
            DatabaseOperation.CLEAN_INSERT
                    .execute(connection, loadHelperData());
        } finally {
            DataSourceUtils.releaseConnection(conn, dataSource);
        }

where the loadHelperData() method does the folllowing:

DataFileLoader loader = new FlatXmlDataFileLoader();
        IDataSet ds = loader.load(TEST_DATA_FILE_HELPER);
        return ds;

The load method simply takes an xml file representing the database. See the DBUnit documentation for much more information.

Updated the answer with an example – TrueDub Aug 20 '13 at 12:03 — TrueDub, Aug 20 '13 at 12:03

Shamim Ahmmed · Answer 2 · 2013-08-20T09:15:34.160

1

You could used HSQLDB which is in memory database 100% JDBC API compatible.

To make JSON String from object you could use Jacson. So from jdbc resultset make your object then JSON string.

edited Aug 20 '13 at 09:15

answered Aug 19 '13 at 19:07

Shamim Ahmmed

8,265
6
25
36

public JSONObject getResultById(int id) { Connection conn = null; Statement stmt = null; String sqlQuery = "select * from " + table + " where id='" + id + "'"; try { //get the connection conn = DBConnection.getConnection(); stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sqlQuery); //return result set in JSON format return getJsonString(rs); } catch (Exception ex) { return null; } finally { try { if (conn != null) conn.close(); if (stmt != null) stmt.close(); } catch (Exception ex) { return null; } } } – user1321939 Aug 20 '13 at 09:08
@user1321939, I did not get your comment. any problem? – Shamim Ahmmed Aug 20 '13 at 09:11
mysql grouped query doesn work with HSQL db – Derrick Jun 06 '23 at 15:09

score 0 · Answer 3 · answered Aug 20 '13 at 09:10

I like to add some code here:

    public JSONObject getResultById(int id) {
    Connection conn = null;
    Statement stmt = null;
    String sqlQuery = "select * from " + table + " where id='"
            + id + "'";
    try {
        //get the connection
        conn = DBConnection.getConnection();
        stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sqlQuery);
        //return result set in JSON format
        return getJsonString(rs);

    } catch (Exception ex) {
        return null;
    } finally {
        try {
            if (conn != null)
                conn.close();
            if (stmt != null)
                stmt.close();
        } catch (Exception ex) {
            return null;
        }
    }
}

To test this what will be best approach to test it. I some one give some sample JUnit test case then it will be very help full

In memory Database to test mysql database operation API using in memory database with maven

3 Answers3