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

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.

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.

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