JavaBlog.fr / Java.lu Database,Java,Libray,Tools Java : Library : API : H2, the Java SQL database

Java : Library : API : H2, the Java SQL database

September 29, 2016September 29, 2016 | Huseyin OZVERENHuseyin OZVEREN | 0 Comment | 10:01 AM

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

view source
print?
001package com.huo.javablog.h2.tests;
002 
003 
004import java.sql.Connection;
005import java.sql.DriverManager;
006import java.sql.PreparedStatement;
007import java.sql.ResultSet;
008import java.sql.SQLException;
009import java.sql.Statement;
010 
011 
012import org.h2.tools.DeleteDbFiles;
013 
014// H2 Database Example
015 
016public class H2FileDatabaseExample {
017 
018    private static final String DB_DRIVER = "org.h2.Driver";
019    private static final String DB_CONNECTION = "jdbc:h2:~/test";
020    private static final String DB_USER = "";
021    private static final String DB_PASSWORD = "";
022 
023    public static void main(String[] args) throws Exception {
024        try {
025            // delete the H2 database named 'test' in the user home directory
026            DeleteDbFiles.execute("~", "test", true);
027            insertWithStatement();
028            DeleteDbFiles.execute("~", "test", true);
029            insertWithPreparedStatement();
030 
031        } catch (SQLException e) {
032            e.printStackTrace();
033        }
034    }
035 
036    // H2 SQL Prepared Statement Example
037    private static void insertWithPreparedStatement() throws SQLException {
038        Connection connection = getDBConnection();
039        PreparedStatement createPreparedStatement = null;
040        PreparedStatement insertPreparedStatement = null;
041        PreparedStatement selectPreparedStatement = null;
042 
043        String CreateQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255))";
044        String InsertQuery = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
045        String SelectQuery = "select * from PERSON";
046        try {
047            connection.setAutoCommit(false);
048            
049            createPreparedStatement = connection.prepareStatement(CreateQuery);
050            createPreparedStatement.executeUpdate();
051            createPreparedStatement.close();
052            
053            insertPreparedStatement = connection.prepareStatement(InsertQuery);
054            insertPreparedStatement.setInt(1, 1);
055            insertPreparedStatement.setString(2, "Jose");
056            insertPreparedStatement.executeUpdate();
057            insertPreparedStatement.close();
058            
059            selectPreparedStatement = connection.prepareStatement(SelectQuery);
060            ResultSet rs = selectPreparedStatement.executeQuery();
061            System.out.println("H2 Database inserted through PreparedStatement");
062            while (rs.next()) {
063                System.out.println("Id="+rs.getInt("id")+" - Name="+rs.getString("name"));
064            }
065            selectPreparedStatement.close();
066            
067            connection.commit();
068        } catch (SQLException e) {
069            System.out.println("Exception Message " + e.getLocalizedMessage());
070        } catch (Exception e) {
071            e.printStackTrace();
072        } finally {
073            connection.close();
074        }
075    }
076 
077    // H2 SQL Statement Example
078    private static void insertWithStatement() throws SQLException {
079        Connection connection = getDBConnection();
080        Statement stmt = null;
081        try {
082            connection.setAutoCommit(false);
083            stmt = connection.createStatement();
084            stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
085            stmt.execute("INSERT INTO PERSON(id, name) VALUES(1, 'Anju')");
086            stmt.execute("INSERT INTO PERSON(id, name) VALUES(2, 'Sonia')");
087            stmt.execute("INSERT INTO PERSON(id, name) VALUES(3, 'Asha')");
088 
089            ResultSet rs = stmt.executeQuery("select * from PERSON");
090            System.out.println("H2 Database inserted through Statement");
091            while (rs.next()) {
092                System.out.println("Id="+rs.getInt("id")+" - Name="+rs.getString("name"));
093            }
094            stmt.close();
095            connection.commit();
096        } catch (SQLException e) {
097            System.out.println("Exception Message " + e.getLocalizedMessage());
098        } catch (Exception e) {
099            e.printStackTrace();
100        } finally {
101            connection.close();
102        }
103    }
104 
105    private static Connection getDBConnection() {
106        Connection dbConnection = null;
107        try {
108            Class.forName(DB_DRIVER);
109        } catch (ClassNotFoundException e) {
110            System.out.println(e.getMessage());
111        }
112        try {
113            dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
114                    DB_PASSWORD);
115            return dbConnection;
116        } catch (SQLException e) {
117            System.out.println(e.getMessage());
118        }
119        return dbConnection;
120    }
121}

H2MemoryDatabaseExample

view source
print?
001package com.huo.javablog.h2.tests;
002 
003 
004import java.sql.Connection;
005import java.sql.DriverManager;
006import java.sql.PreparedStatement;
007import java.sql.ResultSet;
008import java.sql.SQLException;
009import java.sql.Statement;
010 
011// H2 In-Memory Database Example shows about storing the database contents into memory.
012 
013public class H2MemoryDatabaseExample {
014 
015    private static final String DB_DRIVER = "org.h2.Driver";
016    private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
017    private static final String DB_USER = "";
018    private static final String DB_PASSWORD = "";
019 
020    public static void main(String[] args) throws Exception {
021        try {
022            insertWithStatement();
023            insertWithPreparedStatement();
024        } catch (SQLException e) {
025            e.printStackTrace();
026        }
027    }
028 
029    private static void insertWithPreparedStatement() throws SQLException {
030        Connection connection = getDBConnection();
031        PreparedStatement createPreparedStatement = null;
032        PreparedStatement insertPreparedStatement = null;
033        PreparedStatement selectPreparedStatement = null;
034 
035        String CreateQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255))";
036        String InsertQuery = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
037        String SelectQuery = "select * from PERSON";
038 
039        try {
040            connection.setAutoCommit(false);
041 
042            createPreparedStatement = connection.prepareStatement(CreateQuery);
043            createPreparedStatement.executeUpdate();
044            createPreparedStatement.close();
045 
046            insertPreparedStatement = connection.prepareStatement(InsertQuery);
047            insertPreparedStatement.setInt(1, 1);
048            insertPreparedStatement.setString(2, "Jose");
049            insertPreparedStatement.executeUpdate();
050            insertPreparedStatement.close();
051 
052            selectPreparedStatement = connection.prepareStatement(SelectQuery);
053            ResultSet rs = selectPreparedStatement.executeQuery();
054            System.out.println("H2 In-Memory Database inserted through PreparedStatement");
055            while (rs.next()) {
056                System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name"));
057            }
058            selectPreparedStatement.close();
059 
060            connection.commit();
061        } catch (SQLException e) {
062            System.out.println("Exception Message " + e.getLocalizedMessage());
063        } catch (Exception e) {
064            e.printStackTrace();
065        } finally {
066            connection.close();
067        }
068    }
069 
070    private static void insertWithStatement() throws SQLException {
071        Connection connection = getDBConnection();
072        Statement stmt = null;
073        try {
074            connection.setAutoCommit(false);
075            stmt = connection.createStatement();
076            stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
077            stmt.execute("INSERT INTO PERSON(id, name) VALUES(1, 'Anju')");
078            stmt.execute("INSERT INTO PERSON(id, name) VALUES(2, 'Sonia')");
079            stmt.execute("INSERT INTO PERSON(id, name) VALUES(3, 'Asha')");
080 
081            ResultSet rs = stmt.executeQuery("select * from PERSON");
082            System.out.println("H2 In-Memory Database inserted through Statement");
083            while (rs.next()) {
084                System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name"));
085            }
086 
087            stmt.execute("DROP TABLE PERSON");
088            stmt.close();
089            connection.commit();
090        } catch (SQLException e) {
091            System.out.println("Exception Message " + e.getLocalizedMessage());
092        } catch (Exception e) {
093            e.printStackTrace();
094        } finally {
095            connection.close();
096        }
097    }
098 
099    private static Connection getDBConnection() {
100        Connection dbConnection = null;
101        try {
102            Class.forName(DB_DRIVER);
103        } catch (ClassNotFoundException e) {
104            System.out.println(e.getMessage());
105        }
106        try {
107            dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
108            return dbConnection;
109        } catch (SQLException e) {
110            System.out.println(e.getMessage());
111        }
112        return dbConnection;
113    }
114}

H2CsvMemoryDatabaseExample

view source
print?
001package com.huo.javablog.h2.tests;
002 
003 
004 
005import java.sql.Connection;
006import java.sql.DriverManager;
007import java.sql.PreparedStatement;
008import java.sql.ResultSet;
009import java.sql.SQLException;
010 
011//H2 Csv In-Memory Database Example shows about storing the database contents into memory.
012 
013public class H2CsvMemoryDatabaseExample {
014 
015 private static final String DB_DRIVER = "org.h2.Driver";
016 private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
017 private static final String DB_USER = "";
018 private static final String DB_PASSWORD = "";
019 
020 public static void main(String[] args) throws Exception {
021     try {
022         loadAndInsertWithPreparedStatement();
023         writeToCsvWithNewConnection();
024     } catch (SQLException e) {
025         e.printStackTrace();
026     }
027 }
028 
029 private static void loadAndInsertWithPreparedStatement() throws SQLException {
030     Connection connection = getDBConnection();
031     PreparedStatement createAndLoadPreparedStatement = null;
032     PreparedStatement insertPreparedStatement = null;
033     PreparedStatement selectPreparedStatement = null;
034 
035     String CreateAndLoadQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255), surname varchar(255)) "
036            + " AS SELECT * FROM CSVREAD('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv')";
037     //String CreateAndLoadQuery = "CREATE TABLE PERSON "
038     //         + " AS SELECT * FROM CSVREAD('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv')";
039 
040     String InsertQuery = "INSERT INTO PERSON" + "(id, name, surname) values" + "(?,?,?)";
041     String SelectQuery = "select * from PERSON";
042 
043     try {
044         connection.setAutoCommit(false);
045 
046         createAndLoadPreparedStatement = connection.prepareStatement(CreateAndLoadQuery);
047         createAndLoadPreparedStatement.executeUpdate();
048         createAndLoadPreparedStatement.close();
049 
050         insertPreparedStatement = connection.prepareStatement(InsertQuery);
051         insertPreparedStatement.setInt(1, 12);
052         insertPreparedStatement.setString(2, "Huseyin");
053         insertPreparedStatement.setString(3, "OZVEREN");
054         insertPreparedStatement.executeUpdate();
055         insertPreparedStatement.close();
056 
057         selectPreparedStatement = connection.prepareStatement(SelectQuery);
058         ResultSet rs = selectPreparedStatement.executeQuery();
059         System.out.println("H2 CSV In-Memory Database inserted through PreparedStatement");
060         while (rs.next()) {
061             System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name") + " - Surname=" + rs.getString("surname"));
062         }
063         selectPreparedStatement.close();
064 
065         connection.commit();
066     } catch (SQLException e) {
067         System.out.println("Exception Message " + e.getLocalizedMessage());
068     } catch (Exception e) {
069         e.printStackTrace();
070     } finally {
071         connection.close();
072     }
073 }
074 
075 
076 private static void writeToCsvWithNewConnection() throws SQLException {
077     Connection connection = getDBConnection();
078     PreparedStatement writePreparedStatement = null;
079     PreparedStatement selectPreparedStatement = null;
080 
081     String WriteCsvQuery = "CALL CSVWRITE('C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/peopleWritten.csv', 'SELECT * FROM PERSON');";
082     String SelectQuery = "select * from PERSON";
083 
084     try {
085         connection.setAutoCommit(false);
086 
087         writePreparedStatement = connection.prepareStatement(WriteCsvQuery);
088         writePreparedStatement.executeUpdate();
089         writePreparedStatement.close();
090 
091         selectPreparedStatement = connection.prepareStatement(SelectQuery);
092         ResultSet rs = selectPreparedStatement.executeQuery();
093         System.out.println("H2 CSV In-Memory Database inserted through PreparedStatement");
094         while (rs.next()) {
095             System.out.println("Id=" + rs.getInt("id") + " - Name=" + rs.getString("name") + " - Surname=" + rs.getString("surname"));
096         }
097         selectPreparedStatement.close();
098 
099         connection.commit();
100     } catch (SQLException e) {
101         System.out.println("Exception Message " + e.getLocalizedMessage());
102     } catch (Exception e) {
103         e.printStackTrace();
104     } finally {
105         connection.close();
106     }
107 }
108  
109 private static Connection getDBConnection() {
110     Connection dbConnection = null;
111     try {
112         Class.forName(DB_DRIVER);
113     } catch (ClassNotFoundException e) {
114         System.out.println(e.getMessage());
115     }
116     try {
117         dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
118         return dbConnection;
119     } catch (SQLException e) {
120         System.out.println(e.getMessage());
121     }
122     return dbConnection;
123 }
124}

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

view source
print?
01package com.huo.javablog.h2.tests;
02 
03 
04import java.sql.ResultSet;
05import java.sql.ResultSetMetaData;
06import java.sql.SQLException;
07import java.sql.Types;
08 
09import org.h2.tools.Csv;
10import org.h2.tools.SimpleResultSet;
11 
12//H2 Csv In-Memory Without Database Example
13 
14public class H2CsvMemoryNoDatabaseExample {
15 
16 public static void main(String[] args) throws Exception {
17     try {
18         loadAndWriteCsvWithoutDB();
19     } catch (SQLException e) {
20         e.printStackTrace();
21     }
22 }
23 
24 private static void loadAndWriteCsvWithoutDB() throws SQLException {
25     try {
26         {
27             // Reading a CSV File from a Java Application
28             ResultSet rs = new Csv().read("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people.csv", null, null);
29             ResultSetMetaData meta = rs.getMetaData();
30             while (rs.next()) {
31                 for (int i = 0; i < meta.getColumnCount(); i++) {
32                     System.out.println( meta.getColumnLabel(i + 1) + ": " + rs.getString(i + 1));
33                 }
34                 System.out.println();
35             }
36             rs.close();
37         }
38         System.out.println("----------------");
39         {
40             // Writing a CSV File from a Java Application
41             SimpleResultSet rsIns = new SimpleResultSet();
42             rsIns.addColumn("id", Types.INTEGER, 10, 0);
43             rsIns.addColumn("name", Types.VARCHAR, 255, 0);
44             rsIns.addColumn("surname", Types.VARCHAR, 255, 0);
45             rsIns.addRow("13", "Huseyin13", "OZVEREN13");
46             rsIns.addRow("14", "Huseyin14", "OZVEREN14");
47             new Csv().write("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people3.csv", rsIns, null);
48          
49             // Reading a CSV File from a Java Application
50             ResultSet rs = new Csv().read("C:/HOMEWARE/projects/MyFirstProjects/H2Test/src/com/huo/javablog/h2/tests/people3.csv", null, null);
51             ResultSetMetaData meta = rs.getMetaData();
52             while (rs.next()) {
53                 for (int i = 0; i < meta.getColumnCount(); i++) {
54                     System.out.println( meta.getColumnLabel(i + 1) + ": " + rs.getString(i + 1));
55                 }
56                 System.out.println();
57             }
58             rs.close();
59         }
60 
61     } catch (SQLException e) {
62         System.out.println("Exception Message " + e.getLocalizedMessage());
63     } catch (Exception e) {
64         e.printStackTrace();
65     }
66 }
67 
68}

people3.csv

"id","name","surname"
"13","Huseyin13","OZVEREN13"
"14","Huseyin14","OZVEREN14"

That’s all!!!

Huseyin OZVEREN

Tags: , , , , ,

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.

Related Post