HSQLDB Connection using Java

The advantage of HSQLDB is that it supports SQL-92, 2008, 2011 standards and the small DB engine fastens the process. Also it offers In-memory and disk-based tables.

Tech Stack: Java 8 ( version 6 or more will also work)
HSQLDB jar (HSQLDB 2.4.0 jar supports Java 8)

Setting up HSQLDB in local:

• Download the hsqldb-2.4.0 zip file and extract the same. The lib folder should contain the hsqldb.jar.
• Inside bin folder runManagerSwing.bat is the file which opens the DatabaseManagerSwing class and using GUI the applet front end will open.
• Create a file named server.properties inside the folder and update the file with below values:

server.database.0 = file:hsqldb/demodb

server.dbname.0 = testdb

As by default no DB is created, above content will create a database inside hsqldb folder named as demodb with database name testdb.

• Command to run the above DB (using cmd) is:

java -classpath lib/hsqldb.jar org.hsqldb.server.Server –database.0 file:hsqldb/demodb –dbname.0 testdb

• Once the above command runs, the DB will be up and running and run runManagerSwing.bat file. Provide the details as below:

JAVA PROGRAM TO CONNECT AND USE THE DB:

• Details of db.properties file:

Sample program:

package com.test.hsqldb;

import java.io.FileInputStream;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import java.util.Properties;

public class DbConnection {

       public static void main(String[] args) throws Exception{

               Connection con = null;

               Statement stmt = null;

               ResultSet result = null;

               Properties prop = loadProperties();

               String driverClass = prop.getProperty(“HSQL.driverclass”);

               String hsqlURL = prop.getProperty(“HSQL.url”);

               String username = prop.getProperty(“HSQL.username”);

               String password = prop.getProperty(“HSQL.password”);

               try{

                      Class.forName(driverClass);

                      con = DriverManager.getConnection(hsqlURL, username, password);

                      stmt = con.createStatement();

                      /*result = stmt.executeUpdate(“CREATE TABLE tutorials_table “

                                     + “(id INT NOT NULL, title VARCHAR(50) NOT NULL, author VARCHAR(20) NOT NULL, “

                                     + “submission_date DATE,PRIMARY KEY (id));”);*/

                      /*result = stmt.executeUpdate(“DROP TABLE tutorials_table”);*/

                      /*result = stmt.executeUpdate(“INSERT INTO tutorials_table VALUES (102,’Learn HSQL’, ‘Davy Jones‘, NOW())”);*/

                      result = stmt.executeQuery(“SELECT id, title, author FROM tutorials_table”);

                      while(result.next()){

                   System.out.println(result.getInt(“id”)+” || “+

                      result.getString(“title”)+” || “+

                      result.getString(“author”));

                }

               }catch(Exception e){

                      e.printStackTrace();

               }

       }

       private static Properties loadProperties() throws Exception {

               Properties prop = new Properties();

               InputStream in = new FileInputStream(“db.properties”);

               prop.load(in);

               in.close();

               return prop;

       }

}

• If we replace the hsqldb file and server.properties which is created in my system in some other’s system. Then same db with values will be reflected in the other system.

 

Copyright secured by Digiprove © 2019 Geeks 18