Java H2 Embedded Database Example

JDBC Examples

---

• JDBC Create a Table Example
• JDBC Insert Multiple Rows Example
• JDBC Update Query Example
• JDBC Select Query Example
• JDBC Delete Query Example
• JDBC Transaction Management Example
• JDBC Connection to Oracle Database Example
• JDBC Connection to PostgreSQL Example
• JDBC Connection to H2 Database Example
• Java JDBC Connection to HSQLDB Database
• Java PostgreSQL Example
• Java H2 Create Table Example
• Java H2 Insert Record Example
• Java H2 Embedded Database Example

H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, the H2 database can be configured to run as an in-memory database, which means that data will not persist on the disk. Because of an embedded database, it is not used for production development but mostly used for development and testing.

In this post, we will create a simple java program to create a table in the H2 database.

H2 Database Setup and Configuration

1. Download H2 database dependency or jar file from official website http://www.h2database.com/html/download.html or from https://mvnrepository.com/artifact/com.h2database/h2/1.4.199.
2. Add H2 Jar file to your project classpath.
3. By default the Java application to connect to an H2 in-memory store with the username sa and an empty password.

 private static String jdbcURL = "jdbc:h2:~/test";
 private static String jdbcUsername = "sa";
 private static String jdbcPassword = "";

JDBC Util Class

Let's first create an H2JDBCUtils.java file with all JDBC common methods like:

package net.javaguides.jdbc.h2.crud;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class H2JDBCUtils {

    private static String jdbcURL = "jdbc:h2:~/test";
    private static String jdbcUsername = "sa";
    private static String jdbcPassword = "";

    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return connection;
    }

    public static void printSQLException(SQLException ex) {
        for (Throwable e: ex) {
            if (e instanceof SQLException) {
                e.printStackTrace(System.err);
                System.err.println("SQLState: " + ((SQLException) e).getSQLState());
                System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
                System.err.println("Message: " + e.getMessage());
                Throwable t = ex.getCause();
                while (t != null) {
                    System.out.println("Cause: " + t);
                    t = t.getCause();
                }
            }
        }
    }
}

This class we will use in the next Java programs.

Create a Table with the H2 Database

This Java program creates a users table into the H2 database.

package net.javaguides.jdbc.h2.crud;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Create Statement JDBC Example
 * @author Ramesh Fadatare
 *
 */
public class H2CreateExample {

    private static final String createTableSQL = "create table users (\r\n" + "  id  int(3) primary key,\r\n" +
        "  name varchar(20),\r\n" + "  email varchar(20),\r\n" + "  country varchar(20),\r\n" +
        "  password varchar(20)\r\n" + "  );";

    public static void main(String[] argv) throws SQLException {
        H2CreateExample createTableExample = new H2CreateExample();
        createTableExample.createTable();
    }

    public void createTable() throws SQLException {

        System.out.println(createTableSQL);
        // Step 1: Establishing a Connection
        try (Connection connection = H2JDBCUtils.getConnection();
            // Step 2:Create a statement using connection object
            Statement statement = connection.createStatement();) {

            // Step 3: Execute the query or update query
            statement.execute(createTableSQL);

        } catch (SQLException e) {
            // print SQL exception information
            H2JDBCUtils.printSQLException(e);
        }
    }
}

Output:

create table users (
  id  int(3) primary key,
  name varchar(20),
  email varchar(20),
  country varchar(20),
  password varchar(20)
  );