H2 database example in Gradle

H2 database is a lightweight SQL database implemented in Java, it's embeddable and can run in memory. This post shows how to use it in Gradle project.

Step 1. Create an example Gradle project

Step 2. Add H2 database dependencies to Gradle script

Currently, the version of H2 database is 1.3.148

dependencies {
    compile group: 'commons-collections', name: 'commons-collections', version: '3.2'
    testCompile group: 'junit', name: 'junit', version: '4.+'
    compile group: 'com.h2database', name: 'h2', version: '1.3.148'

Note that H2 database package has already contained a JDBC driver, you don't need to add another jar to provide the JDBC driver. This is one of the advantages of lightweight database, all you need is an all-in-one jar and you are good to go. This is different from the main stream database like MySQL which needs an extra jar like mysql-connector-java-5.1.39-bin.jar. You also have to make sure the version of the mysql connector is consistent with the database server. All of these add complexities to your project.

Step 3. Connect to database

H2 database has its JDBC driver shipped with it, the class is org.h2.Driver

        Connection conn = null;
        Statement stmt = null;
        conn = DriverManager.getConnection("jdbc:h2:~/test", "", "");

The connection string means the database is a file in your user home and the name is test. Go to your user home you will find a file named test.h2.db is created.

Step 4. Use JDBC API to create table and insert data

The rest is the same as any other databases, just use the JDBC API. Let's create a table named user, it has two columns the id and the name.

        stmt.execute("drop table user if exists");
        stmt.execute("create table user(id int primary key, name varchar(100))");
        stmt.execute("insert into user values(1, 'hello')");
        stmt.execute("insert into user values(2, 'world')");
        ResultSet rs = stmt.executeQuery("select * from user");
        while (rs.next()) {
            System.out.println("id " + rs.getInt("id") + " name " + rs.getString("name"));