Loading...

Excecute SQL on Connection with H2

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

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;
}