H2 File Database Example

In this H2 File Database Example, we are going to store the data in file system. Here persistence happens on file system. In this example we are creating a java class that shows how to load the driver, create a database, create table and insert some values into table

H2 is an open source software implementation of Java SQL database. The main features of H2 are.

  • Very fast, open source, JDBC API
  • Embedded and server modes; in-memory databases
  • Browser based Console application
  • Small footprint: around 1.5 MB jar file size

Reference -> http://www.h2database.com/html/main.html

Note

Other H2 examples

1)    H2 Database Example
2)    H2 File Database Example
3)    H2 In-Memory Database Example
4)    H2 Database Connection Pool Example
5)    How To view Content Of H2 In-memory & File Database
6)    H2 Batch Update Example With SQL Statement & PreparedStatment
Required Libraries

For using H2 database, You need to download

  1. h2-1.4.182.jar

Project Structure

H2 File Database Example

H2 Database Using File System

Here we are showing implementation for SQL Statement and PreparedStatement using H2 Embedded Database with disk/file system.

In below code you can see that we are using following H2 JDBC URL jdbc:h2:~/test for connecting the database, here 'test' is in the user home directory

By following way, H2 database can be embedded in Java applications

package com.h2.examples;

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

import org.h2.tools.DeleteDbFiles;

// H2 File Database Example shows about storing the database contents into file system.


public class H2FileDatabaseExample {

   
private static final String DB_DRIVER = "org.h2.Driver";
   
private static final String DB_CONNECTION = "jdbc:h2:~/test";
   
private static final String DB_USER = "";
   
private static final String DB_PASSWORD = "";

   
public static void main(String[] args) throws Exception {
       
try {
           
// delete the H2 database named 'test' in the user home directory
           
DeleteDbFiles.execute("~", "test", true);
            insertWithStatement
();
            DeleteDbFiles.execute
("~", "test", true);
            insertWithPreparedStatement
();

       
} catch (SQLException e) {
           
e.printStackTrace();
       
}
    }

   
private static void insertWithPreparedStatement() throws SQLException {
       
Connection connection = getDBConnection();
        PreparedStatement createPreparedStatement =
null;
        PreparedStatement insertPreparedStatement =
null;
        PreparedStatement selectPreparedStatement =
null;

        String CreateQuery =
"CREATE TABLE PERSON(id int primary key, name varchar(255))";
        String InsertQuery =
"INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
        String SelectQuery =
"select * from PERSON";
       
try {
           
connection.setAutoCommit(false);
           
            createPreparedStatement = connection.prepareStatement
(CreateQuery);
            createPreparedStatement.executeUpdate
();
            createPreparedStatement.close
();
           
            insertPreparedStatement = connection.prepareStatement
(InsertQuery);
            insertPreparedStatement.setInt
(1, 1);
            insertPreparedStatement.setString
(2, "Jose");
            insertPreparedStatement.executeUpdate
();
            insertPreparedStatement.close
();
           
            selectPreparedStatement = connection.prepareStatement
(SelectQuery);
            ResultSet rs = selectPreparedStatement.executeQuery
();
            System.out.println
("H2 Database inserted through PreparedStatement");
           
while (rs.next()) {
               
System.out.println("Id "+rs.getInt("id")+" Name "+rs.getString("name"));
           
}
           
selectPreparedStatement.close();
           
            connection.commit
();
       
} catch (SQLException e) {
           
System.out.println("Exception Message " + e.getLocalizedMessage());
       
} catch (Exception e) {
           
e.printStackTrace();
       
} finally {
           
connection.close();
       
}
    }

   
private static void insertWithStatement() throws SQLException {
       
Connection connection = getDBConnection();
        Statement stmt =
null;
       
try {
           
connection.setAutoCommit(false);
            stmt = connection.createStatement
();
            stmt.execute
("CREATE TABLE PERSON(id int primary key, name varchar(255))");
            stmt.execute
("INSERT INTO PERSON(id, name) VALUES(1, 'Anju')");
            stmt.execute
("INSERT INTO PERSON(id, name) VALUES(2, 'Sonia')");
            stmt.execute
("INSERT INTO PERSON(id, name) VALUES(3, 'Asha')");

            ResultSet rs = stmt.executeQuery
("select * from PERSON");
            System.out.println
("H2 Database inserted through Statement");
           
while (rs.next()) {
               
System.out.println("Id "+rs.getInt("id")+" Name "+rs.getString("name"));
           
}
           
stmt.close();
            connection.commit
();
       
} catch (SQLException e) {
           
System.out.println("Exception Message " + e.getLocalizedMessage());
       
} catch (Exception e) {
           
e.printStackTrace();
       
} finally {
           
connection.close();
       
}
    }

   
private static Connection getDBConnection() {
       
Connection dbConnection = null;
       
try {
           
Class.forName(DB_DRIVER);
       
} catch (ClassNotFoundException e) {
           
System.out.println(e.getMessage());
       
}
       
try {
           
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
                    DB_PASSWORD
);
           
return dbConnection;
       
} catch (SQLException e) {
           
System.out.println(e.getMessage());
       
}
       
return dbConnection;
   
}
}
Output
H2 Database inserted through Statement
Id 1 Name Anju
Id 2 Name Sonia
Id 3 Name Asha
H2 Database inserted through PreparedStatement
Id 1 Name Jose

 






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