Hi,
Here a very useful library H2, the Java SQL database http://www.h2database.com/html/main.html.
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
Features:
- Pure Java
- Memory Mode
- Encrypted Database
- ODBC Driver
- Fulltext Search
- Multi Version Concurrency
Examples:
- http://www.javatips.net/blog/h2-database-example
- https://www.mkyong.com/spring/spring-embedded-database-examples/
Examples with the h2-1.4.192.jar and JDK 1.70_72
H2FileDatabaseExample
package com.huo.javablog.h2.tests;
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;
}
}
H2MemoryDatabaseExample
package com.huo.javablog.h2.tests;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
// H2 In-Memory Database Example shows about storing the database contents into memory.
public class H2MemoryDatabaseExample {
private static final String DB_DRIVER = "org.h2.Driver";
private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
private static final String DB_USER = "";
private static final String DB_PASSWORD = "";
public static void main(String[] args) throws Exception {
try {
insertWithStatement();
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 In-Memory 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 In-Memory Database inserted through Statement");
while (rs.next()) {
System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name"));
}
stmt.execute("DROP TABLE PERSON");
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;
}
}
H2CsvMemoryDatabaseExample
package com.huo.javablog.h2.tests;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//H2 Csv In-Memory Database Example shows about storing the database contents into memory.
public class H2CsvMemoryDatabaseExample {
private static final String DB_DRIVER = "org.h2.Driver";
private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
private static final String DB_USER = "";
private static final String DB_PASSWORD = "";
public static void main(String[] args) throws Exception {
try {
loadAndInsertWithPreparedStatement();
writeToCsvWithNewConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void loadAndInsertWithPreparedStatement() throws SQLException {
Connection connection = getDBConnection();
PreparedStatement createAndLoadPreparedStatement = null;
PreparedStatement insertPreparedStatement = null;
PreparedStatement selectPreparedStatement = null;
String CreateAndLoadQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255), surname varchar(255)) "
+ " AS SELECT * FROM CSVREAD('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv')";
//String CreateAndLoadQuery = "CREATE TABLE PERSON "
// + " AS SELECT * FROM CSVREAD('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv')";
String InsertQuery = "INSERT INTO PERSON" + "(id, name, surname) values" + "(?,?,?)";
String SelectQuery = "select * from PERSON";
try {
connection.setAutoCommit(false);
createAndLoadPreparedStatement = connection.prepareStatement(CreateAndLoadQuery);
createAndLoadPreparedStatement.executeUpdate();
createAndLoadPreparedStatement.close();
insertPreparedStatement = connection.prepareStatement(InsertQuery);
insertPreparedStatement.setInt(1, 12);
insertPreparedStatement.setString(2, "Huseyin");
insertPreparedStatement.setString(3, "OZVEREN");
insertPreparedStatement.executeUpdate();
insertPreparedStatement.close();
selectPreparedStatement = connection.prepareStatement(SelectQuery);
ResultSet rs = selectPreparedStatement.executeQuery();
System.out.println("H2 CSV In-Memory Database inserted through PreparedStatement");
while (rs.next()) {
System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name") + " - Surname=" + rs.getString("surname"));
}
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 writeToCsvWithNewConnection() throws SQLException {
Connection connection = getDBConnection();
PreparedStatement writePreparedStatement = null;
PreparedStatement selectPreparedStatement = null;
String WriteCsvQuery = "CALL CSVWRITE('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/peopleWritten.csv', 'SELECT * FROM PERSON');";
String SelectQuery = "select * from PERSON";
try {
connection.setAutoCommit(false);
writePreparedStatement = connection.prepareStatement(WriteCsvQuery);
writePreparedStatement.executeUpdate();
writePreparedStatement.close();
selectPreparedStatement = connection.prepareStatement(SelectQuery);
ResultSet rs = selectPreparedStatement.executeQuery();
System.out.println("H2 CSV In-Memory Database inserted through PreparedStatement");
while (rs.next()) {
System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name") + " - Surname=" + rs.getString("surname"));
}
selectPreparedStatement.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;
}
}
people.csv
id,name,surname
1,Anju,Johnson
2,Sonia,Paulson
3,Asha,Huseyinson
peopleWritten.csv
"ID","NAME","SURNAME"
"1","Anju","Johnson"
"2","Sonia","Paulson"
"3","Asha","Huseyinson"
"12","Huseyin","OZVEREN"
H2CsvMemoryNoDatabaseExample
package com.huo.javablog.h2.tests;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import org.h2.tools.Csv;
import org.h2.tools.SimpleResultSet;
//H2 Csv In-Memory Without Database Example
public class H2CsvMemoryNoDatabaseExample {
public static void main(String[] args) throws Exception {
try {
loadAndWriteCsvWithoutDB();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void loadAndWriteCsvWithoutDB() throws SQLException {
try {
{
// Reading a CSV File from a Java Application
ResultSet rs = new Csv().read("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv", null, null);
ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
for (int i = 0; i < meta.getColumnCount(); i++) {
System.out.println( meta.getColumnLabel(i + 1) + ": " + rs.getString(i + 1));
}
System.out.println();
}
rs.close();
}
System.out.println("----------------");
{
// Writing a CSV File from a Java Application
SimpleResultSet rsIns = new SimpleResultSet();
rsIns.addColumn("id", Types.INTEGER, 10, 0);
rsIns.addColumn("name", Types.VARCHAR, 255, 0);
rsIns.addColumn("surname", Types.VARCHAR, 255, 0);
rsIns.addRow("13", "Huseyin13", "OZVEREN13");
rsIns.addRow("14", "Huseyin14", "OZVEREN14");
new Csv().write("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people3.csv", rsIns, null);
// Reading a CSV File from a Java Application
ResultSet rs = new Csv().read("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people3.csv", null, null);
ResultSetMetaData meta = rs.getMetaData();
while (rs.next()) {
for (int i = 0; i < meta.getColumnCount(); i++) {
System.out.println( meta.getColumnLabel(i + 1) + ": " + rs.getString(i + 1));
}
System.out.println();
}
rs.close();
}
} catch (SQLException e) {
System.out.println("Exception Message " + e.getLocalizedMessage());
} catch (Exception e) {
e.printStackTrace();
}
}
}
people3.csv
"id","name","surname"
"13","Huseyin13","OZVEREN13"
"14","Huseyin14","OZVEREN14"
That’s all!!!
Huseyin OZVEREN