Excecute SQL on Connection with H2

This post is older than a year. Consider some information might not be accurate anymore.

The H2 database allows to excecute SQL statement(s) within the connection. This comes handy if you are using the H2 database to test your Java persistence queries.

String url = "jdbc:h2:mem:test;INIT=runscript from '~/create.sql'\\;runscript from '~/init.sql'";

Please note the double backslash is only required in a Java or properties file. In a GUI, or in an XML file, only one backslash is required:

<property name="url" value="jdbc:h2:mem:test;INIT=create schema if not exists test\;runscript from '~/sql/init.sql'"/>

One undocumented feature is that you can also take the classpath for the file location. For instance you have in your Maven project in src/test/resources:

h2.properties
init.sql

h2.properties contain the JDBC settings

user=admin
password=4321
driver=org.h2.Driver
url=jdbc:h2:~/demo;DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;
testurl=jdbc:h2:~/test;INIT=RUNSCRIPT FROM 'classpath:init.sql';DB_CLOSE_DELAY=-1;AUTO_SERVER=TRUE;

In the testurl property, the init.sql is taken from the classpath. This can result in following example code:

static final Connection connect(final String fileName, final boolean test) throws ClassNotFoundException, SQLException, IOException {
    Properties settings = new Properties();
    //establish database connection
    settings.load(new FileInputStream(fileName));
    Class.forName(settings.getProperty("driver"));
    String url = test ? settings.getProperty("testurl") : settings.getProperty("url");
    Connection conn = DriverManager.getConnection(url, settings.getProperty("user"), settings.getProperty("password"));
    return conn;
}