H2 Database Example
In this H2 Database Example, we are going to create a simple 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. As due to Embedded database it is not used for production deployment, but mostly used for development & testing
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
Required Libraries
For testing H2 database, You need to download
Project Structure
H2 SQL Statement AND Prepared Statement Examples
Here we are showing implementation for SQL Statement and PreparedStatement using H2 Database.
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 Database Example
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();
}
}
// H2 SQL Prepared Statement Example
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();
}
}
// H2 SQL Statement Example
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;
}
}
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 Database Example
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();
}
}
// H2 SQL Prepared Statement Example
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();
}
}
// H2 SQL Statement Example
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
6 Responses to "H2 Database Example"