Java H2
last modified July 6, 2020
Java H2 tutorial shows how to do database programming in H2 with Java.
H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode. It can be used also in a memory mode.
H2 has a very small footprint. It somes with a browser based management application called H2 Console.
Downloading H2
From the H2's home page, we download the database in a ZIP file.
$ unzip h2-2019-03-13.zip
We unzip the archive.
$ mv h2 ~/bin/
We move the installation directory to a destination of our choice.
Java H2 memory example
In the first example, we connect to an in-memory H2 database. The H2 server does not need to run for this example.
package com.zetcode;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaSeH2Memory {
public static void main(String[] args) {
var url = "jdbc:h2:mem:";
try (var con = DriverManager.getConnection(url);
var stm = con.createStatement();
var rs = stm.executeQuery("SELECT 1+1")) {
if (rs.next()) {
System.out.println(rs.getInt(1));
}
} catch (SQLException ex) {
var lgr = Logger.getLogger(JavaSeH2Memory.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
The example connects to an H2 in-memory database and executes a query. An in-memory private database for one connection only is created. The database is closed when the connection to the database is closed.
var url = "jdbc:h2:mem:";
This URL is for H2 database in memory mode.
Creating a database
In the older versions of H2, a database was automatically created if it did not exist. Due to security reasons this is not possible anymore. We need to create a database before connecting to it.
$ java -cp bin/h2-1.4.199.jar org.h2.tools.Shell Welcome to H2 Shell 1.4.199 (2019-03-13) Exit with Ctrl+C [Enter] jdbc:h2:mem:testdb URL jdbc:h2:~/tmp/h2dbs/testdb [Enter] org.h2.Driver Driver [Enter] sa User Password
A new database called testdb can be created with
the shell tool.
Starting H2 server
Now we are going to start H2 server.
$ java -jar bin/h2-1.4.199.jar -baseDir ~/tmp/h2dbs Web Console server running at http://127.0.1.1:8082 (only local connections) TCP server running at tcp://127.0.1.1:9092 (only local connections) PG server running at pg://127.0.1.1:5435 (only local connections)
We move to the installation directory and run H2 in server mode. The command
starts a web console application and two local connections; the PG server
is a PostgreSQL compatibility mode with PostgreSQL protocol. The directory
where the database files are generated is set to ~/tmp/h2dbs, where
~ stands for home directory.
We go to the web console and connect to the testdb database with the
jdbc:h2:~/tmp/h2dbs/testdb URL. The database is generated
in ~/tmp/h2dbs directory. The default user is sa with
no password set.
ALTER USER sa SET PASSWORD 's$cret'
While in the console, we set a password for user sa with the
ALTER USER statement.
CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), price INT);
INSERT INTO cars(name, price) VALUES('Audi', 52642);
INSERT INTO cars(name, price) VALUES('Mercedes', 57127);
INSERT INTO cars(name, price) VALUES('Skoda', 9000);
INSERT INTO cars(name, price) VALUES('Volvo', 29000);
INSERT INTO cars(name, price) VALUES('Bentley', 350000);
INSERT INTO cars(name, price) VALUES('Citroen', 21000);
INSERT INTO cars(name, price) VALUES('Hummer', 41400);
INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);
This is the SQL to create the cars table. We use this table in
one example.
H2 Maven dependency
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.199</version>
</dependency>
</dependencies>
This is the Maven dependency for H2.
Java H2 Server example
For this example, we start the H2 server with:
$ java -jar bin/h2-1.4.199.jar -baseDir ~/tmp/h2dbs
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zetcode</groupId>
<artifactId>javaseh2server</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>11</maven.compiler.source>
<maven.compiler.target>11</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.199</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.6.0</version>
<configuration>
<mainClass>com.zetcode.JavaSeH2Server</mainClass>
</configuration>
</plugin>
</plugins>
</build>
</project>
The POM file contains the H2 Database Engine and the exec-maven-plugin
for executing Java classes with Maven.
package com.zetcode;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaSeH2Server {
public static void main(String[] args) {
var url = "jdbc:h2:tcp://localhost:9092/~/tmp/h2dbs/testdb";
var user = "sa";
var passwd = "s$cret";
var query = "SELECT * FROM cars";
try (var con = DriverManager.getConnection(url, user, passwd);
var st = con.createStatement();
var rs = st.executeQuery(query)) {
while (rs.next()) {
System.out.printf("%d %s %d%n", rs.getInt(1),
rs.getString(2), rs.getInt(3));
}
} catch (SQLException ex) {
var lgr = Logger.getLogger(JavaSeH2Server.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
The example connects to the H2 server and executes a query. It returns all rows
from the cars table.
var url = "jdbc:h2:tcp://localhost:9092/~/tmp/h2dbs/testdb";
This is the URL for connecting to the H2 Server's testdb database.
$ mvn compile $ mvn -q exec:java 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
We compile and run the program.
Java H2 tutorial showed how to program H2 database in Java.
List all Java tutorials.