Java H2 tutorial
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.