Writing a Test Class with In-memory H2 Database

While I was implementing unit tests for one java component, I had a requirement to write @Before method inside which I needed to initialize a database. Further I had to create a DataSource using it and bind it to the InitialContext.

According to the requirement, the database had to be populated with some initial data.

This post is about how I've used an in-memory H2 database inside the test classes.

    public static final String DRIVER_CLASS = "org.h2.Driver"; 
    public static final String CONNECTION_URL = "jdbc:h2:mem:WSO2ML_DB"; 
    public static final String USERNAME = "wso2carbon";
    public static final String PASSWORD = "wso2carbon";

    @Before
    public void createTestDB() throws SQLException, URISyntaxException, 
                                      NamingException, ClassNotFoundException, FileNotFoundException {

        // Create data source
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL(CONNECTION_URL);
        ds.setUser(USERNAME);
        ds.setPassword(PASSWORD);

        // Create initial context
        System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
        System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");

        // Bind data source -> 
        // DataSource can be accessed by : (DataSource) initContext.lookup("jdbc/WSO2ML_DB");
        InitialContext ic = new InitialContext();
        ic.createSubcontext("jdbc");
        ic.bind("jdbc/WSO2ML_DB", ds);

        Class.forName(DRIVER_CLASS);
        Connection connection = DriverManager.getConnection("jdbc:h2:mem:WSO2ML_DB", USERNAME, PASSWORD);

        // TO dump -> SCRIPT TO '<path-to-directory>/ml-db-dump.sql'
        // TO populate DB -> RUNSCRIPT

        // Populate DB with the given DB-dump
        URL resource = TestDB.class.getResource("/ml-db-dump.sql");
        String filePath = new File(resource.toURI()).getAbsolutePath();
        RunScript.execute(connection, new FileReader(filePath));
    }

References :
[1] http://stackoverflow.com/questions/3461310/how-can-i-bind-a-datasource-to-an-initialcontext-for-junit-testing
[2] http://www.journaldev.com/2509/jdbc-datasource-example-oracle-mysql-and-apache-dbcp-tutorial
[3] http://java.dzone.com/articles/presentation-and-use-h2
[4] http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcDataSource.html
[5] http://stackoverflow.com/questions/3256694/how-in-h2db-get-sql-dump-like-in-mysql
[6] http://h2database.com/html/grammar.html#runscript
[7] http://stackoverflow.com/questions/10675768/executing-script-file-in-h2-database