Add new comment
Test Driving the h2 Embedded Java Database
I'm starting work on some DB tuning utilities written in Java. I am running the utilities on my local machine against remote servers and I needed a way to hold some data locally. The volume of data I plan on storing is more than I would feel comfortable keeping in local properties or XML files and I didn't want to deal with setting up an Oracle XE instance just to service my lightweight utilities so I checked into a bunch of embedded databases. Embedded databases are databases that I can wrap into my application.
While H2 and other embedded databases (hsqldb, Apache Derby, BerkeleyDB) are worlds away from the capabilities of Oracle, they fill an important niche. If I am writing an application I can easily store data locally using standard jdbc and SQL constructs. Once they are incorporated into the application they are zero configuration as far as the user is concerned.
H2 can be fired up as a standalone DB server but for my purposes I want to hide all of that complexity and wrap it all into my application. The embedded mode for H2 is pretty snazzy. After including the H2 jar file in my classpath the DB starts up when I initiate a connection via jdbc. Once the last connection is released the H2 engine shuts down. You can even set up an embedded instance of H2 to accept connections from other clients.
I found some code snippets for Embedded mode but wanted to share my proof of concept class to fire up a H2 instance, create a table, populate it with some data and then use a SELECT query to get the data back out. H2test.java follows along with a copy of the console output.
H2test.java: for Version 1.4.195 (2017-04-23) :
import java.sql.*;
public class H2test {
static Connection conn;
public static void main(String[] args) {
createConnection();
runStatement("create table Employees ("
+ "Employee_ID INTEGER, "
+ "Name VARCHAR(30))");
runStatement("insert into employees values (1,'Tom')");
runStatement("insert into employees values (2,'Carol')");
runStatement("insert into employees values (3,'Dick')");
runStatement("insert into employees values (4,'Alice')");
doQuery("SELECT * FROM employees");
runStatement("drop table employees");
}
public static void createConnection() {
try {
Class.forName("org.h2.Driver");
} catch (Exception e) {
System.err.println("Caught IOException: " + e.getMessage());
}
try {
conn = conn = DriverManager.getConnection("jdbc:h2:./test", "sa", "");
} catch (Exception e) {
System.err.println("Caught IOException: " + e.getMessage());
}
}
public static void runStatement(String sqlstmt) {
System.out.println(sqlstmt);
Statement stmt;
try {
stmt = conn.createStatement();
stmt.executeUpdate(sqlstmt);
stmt.close();
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
public static void doQuery(String sqlstmt) {
try {
Statement select = conn.createStatement();
ResultSet result = select.executeQuery(sqlstmt);
ResultSetMetaData resultMetaData = result.getMetaData();
int numberOfColumns = resultMetaData.getColumnCount();
int rownum = 0;
System.out.println(sqlstmt);
System.out.println("Got results:");
while (result.next()) { // process results one row at a time
rownum++;
System.out.print(" Row " + rownum + " | ");
for (int i = 1; i <= numberOfColumns; i++) {
System.out.print( resultMetaData.getColumnName(i) + " : " +
result.getString(i) );
if (i < numberOfColumns) {
System.out.print(", ");
}
}
System.out.println("");
}
} catch (Exception e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
H2test.java: (for older versions of H2) :
package h2test;
import java.sql.*;
import org.h2.jdbcx.JdbcDataSource;
public class H2test {
static Connection conn;
public static void main(String[] args) {
createConnection();
runStatement("create table Employees ("
+ "Employee_ID INTEGER, "
+ "Name VARCHAR(30))");
runStatement("insert into employees values (1,'Tom')");
runStatement("insert into employees values (2,'Carol')");
runStatement("insert into employees values (3,'Dick')");
runStatement("insert into employees values (4,'Alice')");
doQuery("SELECT * FROM employees");
runStatement("drop table employees");
}
public static void createConnection() {
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:˜/test");
ds.setUser("sa");
ds.setPassword("sa");
try {
conn = ds.getConnection();
} catch (Exception e) {
System.err.println("Caught IOException: " + e.getMessage());
} finally {
}
}
public static void runStatement(String sqlstmt) {
System.out.println(sqlstmt);
Statement stmt;
try {
stmt = conn.createStatement();
stmt.executeUpdate(sqlstmt);
stmt.close();
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
public static void doQuery(String sqlstmt) {
try {
Statement select = conn.createStatement();
ResultSet result = select.executeQuery(sqlstmt);
ResultSetMetaData resultMetaData = result.getMetaData();
int numberOfColumns = resultMetaData.getColumnCount();
int rownum = 0;
System.out.println(sqlstmt);
System.out.println("Got results:");
while (result.next()) { // process results one row at a time
rownum++;
System.out.print(" Row " + rownum + " | ");
for (int i = 1; i <= numberOfColumns; i++) {
System.out.print( resultMetaData.getColumnName(i) + " : " +
result.getString(i) );
if (i < numberOfColumns) {
System.out.print(", ");
}
}
System.out.println("");
}
} catch (Exception e) {
System.err.println("SQLException: " + e.getMessage());
}
}
}
Output:
create table Employees (Employee_ID INTEGER, Name VARCHAR(30)) insert into employees values (1,'Tom') insert into employees values (2,'Carol') insert into employees values (3,'Dick') insert into employees values (4,'Alice') SELECT * FROM employees Got results: Row 1 | EMPLOYEE_ID : 1, NAME : Tom Row 2 | EMPLOYEE_ID : 2, NAME : Carol Row 3 | EMPLOYEE_ID : 3, NAME : Dick Row 4 | EMPLOYEE_ID : 4, NAME : Alice drop table employees