CODEWITHSUNDEEP
javadatabaseembeddingh2
Georgi
Georgi

Reputation: 4411

Embedding the Java h2 database programmatically

At the moment we use HSQLDB as an embedded database, but we search for a database with less memory footprint as the data volume grows.

Derby / JavaDB is not an option at the moment because it stores properties globally in the system properties. So we thought of h2.

While we used HSQLDB we created a Server-object, set the parameters and started it. This is described here (and given as example in the class org.hsqldb.test.TestBase).

The question is: Can this be done analogous with the h2 database, too? Do you have any code samples for that? Scanning the h2-page, I did not find an example.

Upvotes: 33

Views: 70205

Answers (4)

Basil Bourque
Basil Bourque

Reputation: 338171

Example app

The Answer by Alex Miller is correct. I will add a complete example Java app as a demonstration.

Embedded mode

To clarify: "Embedded mode" means running the database engine within a Java app. If embedded, the database is not accessible from outside that app.

So the Comments on the Question about port numbers makes no sense for an embedded database. When embedded, no network is involved, and therefore no port numbers are involved. (If you want to make connections over the network from other apps, then run H2 in Server mode rather than Embedded mode.)

Embedded-mode database URL

Let’s analyze the embedded-mode database URL seen in code below: "jdbc:h2:mem:ExampleDb;DB_CLOSE_DELAY=-1".

  • The first two parts say that we want to use JDBC to access an H2 Database Engine database.
  • The third-part indicates we want an in-memory database named with the fourth part, ExampleDb.
  • By default, the in-memory database is closed and discarded after our database connection ends. If you want to keep the database intact for more connections, add the fifth part, DB_CLOSE_DELAY=-1. By adding that fifth part, the in-memory database will continue to exist, until our app exits.

Persistent storage versus In-memory

If you want to persist your database to storage, rather than in-memory, drop the mem part. Use an embedded database URL as seen on the Cheat Sheet. For example, a database named InvoicesDb in the user’s Home folder would have an embedded database URL of "jdbc:h2:~/InvoicesDb".

java.sql.DataSource

Also notice the use of a DataSource implementation. Using that interface allows you to externalize the connection info outside your source code. Then a system administrator can make changes without you needing to edit your Java code and recompile the app.

Example code

package work.basil.example.jdbc;

import org.h2.jdbcx.JdbcDataSource;
import org.intellij.lang.annotations.Language;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.OffsetDateTime;

public class ExampleNow
{
    public static void main ( String[] args )
    {
        ExampleNow app = new ExampleNow( );
        app.demo( );
    }

    private void demo ( )
    {
        DataSource dataSource = this.obtainDataSource( );
        // @Language ( "SQL" )  // Or comment: language=HTML — If using IntelliLang plugin in IntelliJ, for language injections. https://www.jetbrains.com/help/idea/using-language-injections.html
        String sql = """
                SELECT CURRENT_TIMESTAMP ;
                """;
        try 
        (
                Connection connection = dataSource.getConnection( ) ;
                PreparedStatement preparedStatement = connection.prepareStatement( sql ) ;
                ResultSet resultSet = preparedStatement.executeQuery( );
        )
        {
            if ( resultSet.next( ) )
            {
                OffsetDateTime offsetDateTime = resultSet.getObject( 1 , OffsetDateTime.class );
                System.out.println( "offsetDateTime.toString() = " + offsetDateTime );
            }
        } catch ( SQLException e )
        {
            throw new RuntimeException( e );
        }
    }

    private DataSource obtainDataSource ( )
    {
        org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource( );  // Implementation of `javax.sql.DataSource`, bundled with H2.
        ds.setURL( "jdbc:h2:mem:ExampleDb;DB_CLOSE_DELAY=-1" ); // To not auto-close (and delete) the in-memory database, pass `DB_CLOSE_DELAY=-1`.
        ds.setUser( "scott" );
        ds.setPassword( "tiger" );
        ds.setDescription( "An example database to capture the current moment." );
        return ds;
    }
}

Where run:

offsetDateTime.toString() = 2024-08-09T21:04:20.395959Z

Upvotes: 0

Alex Miller
Alex Miller

Reputation: 70201

H2 embedded mode

Yes, you can run H2 in embedded mode. You just use the bundled JDBC driver and connect to an embedded URL like this (their example):

This database can be used in embedded mode, or in server mode. To use it in embedded mode, you need to:

* Add h2.jar to the classpath
* Use the JDBC driver class: org.h2.Driver
* The database URL jdbc:h2:~/test opens the database 'test' in your user home directory

Example of connecting with JDBC to an embedded H2 database (adapted from http://www.h2database.com/javadoc/org/h2/jdbcx/JdbcDataSource.html ):

import org.h2.jdbcx.JdbcDataSource;
// ...
JdbcDataSource ds = new JdbcDataSource();
ds.setURL("jdbc:h2:˜/test");
ds.setUser("sa");
ds.setPassword("sa");
Connection conn = ds.getConnection();

See Cheat Sheet for examples of database URLs for embedded mode.

If you're looking to use H2 in a purely in-memory / embedded mode, you can do that too. See this link for more:

You just need to use a special URL in normal JDBC code like "jdbc:h2:mem:db1".

Upvotes: 73

javydreamercsw
javydreamercsw

Reputation: 5089

If for some reason you need an embedded H2 database in server mode you can do it either manually using the API at http://www.h2database.com/javadoc/org/h2/tools/Server.html - or by appending ;AUTO_SERVER=TRUE to the database URL.

Upvotes: 5

anjanb
anjanb

Reputation: 13847

From the download, I see that the file tutorial.html has this 

import org.h2.tools.Server;
...
// start the TCP Server
Server server = Server.createTcpServer(args).start();
...
// stop the TCP Server
server.stop();

Upvotes: 25

Related Questions