How to do a Database dump of H2 DB

Using the H2 Database is a common option when developing application prototypes. Once challenge is that, if you are using it as in-memory embedded H2 database , you cannot access the database from outside the JVM. A simple trick is to create a dump of the database, to check the data in all tables In this tutorial we will learn several ways to do that in a standard JPA application and in a Spring Boot application.

Dumping with the SCRIPT command

Firstly, we need to discuss the standard option for dumping an H2 database, which is to use the SCRIPT command. This command which generates a script that contains all the data for the database tables. To use the SCRIPT command, connect to the H2 database using the H2 Console and execute the following statement:

SCRIPT TO 'dump.sql'

This will generate a file called “dump.sql” that contains a series of INSERT statements that can be used to recreate the database tables and their data.

Besides, you can also perform a dump from the Command Line as follows:

 java -cp h2.jar org.h2.tools.Script -url "jdbc:h2:file:./foo" -user sa -password ""

Next, we will learn how to implement the SCRIPT command from inside the application’s JVM.

Dumping H2 DB from a JPA Application

Next, we will show how to perform a dump of H2 Database tables with Jakarta Persistence API (JPA). Check the following dumpDB method:

@PersistenceContext
private EntityManager entityManager;


public void dumpDB() {
    try (Connection conn = entityManager.unwrap(Connection.class)) {
        Script.process(conn, "dump.sql", null, "TABLES *");
    } catch (Exception e) {
        // Handle exception
    }
}

Two things to notice:

  1. The unwrap method is a generic method that is available in the EntityManager interface. You can use it to retrieve the underlying implementation of a given interface. In our case, we will use it to retrieve the Connection object.
  2. The org.h2.tools.Script class is the utility class in the H2 database that allows you to run a script file that contains SQL statements. You can use the Script class to execute a script file from the command line or from within a Java application.

When you execute the above method, a dump.sql file will be created in the current directory of the JVM Process. By inspecting the file, you will see both the DDL Statements and the content of the Tables:

SET DB_CLOSE_DELAY -1;         
;              
CREATE USER IF NOT EXISTS "SA" SALT '7aa37e3866384d0c' HASH 'a623a90dcf8a55c43f2fed87a553f227f0ec47647bfef5a3a6be82a28893ce58' ADMIN;          
CREATE SEQUENCE "PUBLIC"."CUSTOMERID_SEQ" START WITH 1 RESTART WITH 3;         
CREATE MEMORY TABLE "PUBLIC"."CUSTOMER"(
    "ID" BIGINT NOT NULL,
    "NAME" CHARACTER VARYING(40),
    "SURNAME" CHARACTER VARYING(40)
);    
ALTER TABLE "PUBLIC"."CUSTOMER" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_5" PRIMARY KEY("ID");      

-- 2 +/- SELECT COUNT(*) FROM PUBLIC.CUSTOMER; 
INSERT INTO "PUBLIC"."CUSTOMER" VALUES
(1, 'Homer', 'Simpson'),
(2, 'Bart', 'Simpson');        

Running the Dump from a Spring Boot application

If you are running a Spring Boot application you can still use the JPA Example from the previous section. To do that, you need to include the spring-boot-starter-data-jpa in your application.

On the other hand, if you are using a JdbcTemplate to access your Databases, there’s an even simpler option. You can run the “SCRIPT” command through the execute method of the JdbcTemplate interface. For example:

@Service
public class CustomerService implements CustomerRepository {
    private final JdbcTemplate jdbcTemplate;

    public CustomerService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void dumpTables() throws Exception {
            // Execute the SCRIPT TO 'dump.sql' command
            jdbcTemplate.execute("SCRIPT TO 'dump.sql'");
    }

}

Alternatively, you can also iterate through the single Tables as follows:

@Autowired
private JdbcTemplate jdbcTemplate;

public void dumpTables() throws IOException {
    // Get a list of all table names
    List<String> tableNames = jdbcTemplate.queryForList("SHOW TABLES", String.class);

    // Iterate over the table names
    for (String tableName : tableNames) {
        // Execute a SELECT * FROM <tableName> query
        List<Map<String, Object>> rows = jdbcTemplate.queryForList("SELECT * FROM " + tableName);

        // Print the results to the console
        for (Map<String, Object> row : rows) {
            System.out.println(row);
        }
    }
}

Finally, to build the above example, you will need to add the spring-boot-starter-data-jdbc dependency to your application:

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

To learn more about Spring Boot JDBC Template check this article : Using the JDBC Template with Spring Boot applications

Conclusion

In this tutorial, we have discussed some options for dumping an H2 database using the SCRIPT command. Dumping an H2 Database can be an handy option if you want to output all the tables data in a development environment.

Found the article helpful? if so please follow us on Socials