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:

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