Java H2 Database Create Table

I recently found the Java H2 SQL Database that is a great low footprint option for implementing a database into your Java application. The H2 Database is very fast, open source , and comes with the JDBC API. The steps below are me starting from scratch using the H2 Java Database.

  1. Downloaded H2 Database Engine
  2. Installed H2 Database on Windows 10
  3. Change the default ‘sa’ user password
  4. Added the H2 dependency to my pom.xml file
  5. Started the H2 Database in Server Mode


Add H2 Dependency

I prefer to develop my Java programs using Eclipse as a maven project so I added the H2 dependency to my pom.xml file that is available in Maven Central Repository. Currently 1.4.196 version is the most current in Maven Central.

<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <version>1.4.196</version>
      <scope>runtime</scope>
    </dependency>


Launch H2 Console

After installing H2 Database Server, you will see the default Admin user is sa, which I believe is set to an empty password. Go to [Start] and search for H2 and then click on H2 Console to launch the H2 Console. Click on [Connect] button to launch H2 Console in browser.


Set Admin Password

The H2 Console will launch in your browser and look like below. The default database you will notice is called test. The default Admin user is sa. You will want to set the password to one of your choosing. In the blank command box in the console, type the SQL command below (first box) and then click [RUN] button. After the SQL command gets executed, H2 will display the result like below (second box).

ALTER USER sa SET PASSWORD 'YOURNEWPASSWORD';
ALTER USER sa SET PASSWORD 'YOURNEWPASSWORD';
Update count: 0
(3 ms)


Define H2 Database Parameters

Create a new Java Class to create a new table in our H2 Database. The first code in your class should be the following static final variables that are needed to connect to our new H2 Database. The DATABASE_DRIVER is standard for an H2 Database. The DATABASE_CONNECTION is using tcp to our localhost and connecting to the test database. We finish by defining the sa username and password that we set earlier.

private static final String DATABASE_DRIVER = "org.h2.Driver";
private static final String DATABASE_CONNECTION = "jdbc:h2:tcp://localhost/~/test";
private static final String DATABASE_USER = "sa";
private static final String DATABASE_PASSWORD = "YOURNEWPASSWORD";


Define New Database Table

We are creating a new DB table called WORKERS that has 5 fields. The first field is the primary key called employeeid that is auto incrementd so we don’t actaully have to set it when adding a new record. That is followed by first name, last name, department, and location.

//Create our H2 SQL Statement
		String CreateSQLQuery = "CREATE TABLE WORKERS(employeeid int auto_increment primary key, "
				+ "								   firstname varchar(100), "
				+ "								   lastname varchar(100), "
				+ "								   department varchar(100),"
				+ "								   location varchar(10))";


Connect to H2 Database

We created a new method called getDBConnection() that takes care of getting a connection object to our H2 Database on local server called test. Having this connection functionality in a separate method is good coding because most likely you would be calling it frequently in a real world Java program.

//Make a connection to the H2 Database
	private static Connection getDBConnection() 
	{
    
		Connection H2DBConnection = null;
		
        try 
        {
            Class.forName(DATABASE_DRIVER);
        } 
        catch (ClassNotFoundException ex) 
        {
            System.out.println(ex.toString());
        }
        try 
        {
        	H2DBConnection = DriverManager.getConnection(DATABASE_CONNECTION, DATABASE_USER, DATABASE_PASSWORD);
            
            return H2DBConnection;
        } 
        catch (SQLException ex) 
        {
        	System.out.println(ex.toString());
        }
        
        return H2DBConnection;
    }


Complete Java Code Create New Table

Here is the full Java source code that is a good working example of how to create a new table within your H2 database server.

package com.jcd.java.tutorials.MavenProject;

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

public class JavaH2Database 
{
	 private static final String DATABASE_DRIVER = "org.h2.Driver";
	 private static final String DATABASE_CONNECTION = "jdbc:h2:tcp://localhost/~/test";
	 private static final String DATABASE_USER = "sa";
	 private static final String DATABASE_PASSWORD = "YOURNEWPASSWORD";
	    
	public static void main(String[] args) 
	{
		
		//Let's create a new worker h2 database table
		createDBTable();
	}
	
	//Create new H2 Database table
	public static void createDBTable() 
	{
		
		//Create our H2 SQL Statement
		String CreateSQLQuery = "CREATE TABLE WORKERS(employeeid int auto_increment primary key, "
				+ "								   firstname varchar(100), "
				+ "								   lastname varchar(100), "
				+ "								   department varchar(100),"
				+ "								   location varchar(10))";
		
		//Create H2 DB Connection Object
		Connection connection = getDBConnection();
		
		try 
		{
			//Set auto commit to false	
			connection.setAutoCommit(false);
		
			//Create a Statement Object
			Statement statement = connection.createStatement();
			
			//Execute the statement
			statement.execute(CreateSQLQuery);
			
			
			//Close the Statement Object
			statement.close();
			
			//Close the Connection Object
            connection.commit();
			
		}
		catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
		
		System.out.println("Successfully Created WORKERS Table!");
		
	}
	
	//Make a connection to the H2 Database
	private static Connection getDBConnection() 
	{
    
		Connection H2DBConnection = null;
		
        try 
        {
            Class.forName(DATABASE_DRIVER);
        } 
        catch (ClassNotFoundException ex) 
        {
            System.out.println(ex.toString());
        }
        try 
        {
        	H2DBConnection = DriverManager.getConnection(DATABASE_CONNECTION, DATABASE_USER, DATABASE_PASSWORD);
            
            return H2DBConnection;
        } 
        catch (SQLException ex) 
        {
        	System.out.println(ex.toString());
        }
        
        return H2DBConnection;
    }
}
Successfully Created WORKERS Table!


After successfully creating the new WORKERS database table in Java, you can go back into the H2 Console and see the new table as well as each field or column.


Concluding Comments

The password I defined here in this Java code was for example only. Please define a high strength password to your Admin ID. The Java H2 Database is a great low resource option to implement as a database in your Java application. The H2 DB is written in Java and as you can see adding a new table is quite simple using the Java programming language.



References:

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *