Java & HSQLDB : Connecting to a DBMS (HSQLDB) from Java Program

[info]In case you don’t have the HSGLDB, You can download the DBMS : HSQLDB from here [/info]

1) Create a simple file and call it Simple.java. Here is a simple code written in Java to connect to a DBMS :

import java.io.*;
import java.sql.*;
import java.util.*;
public class Simple {
    public static void main(String[] args) {

       Connection con=null;;

        try {
            Class.forName("org.hsqldb.jdbc.JDBCDriver");
            con=DriverManager.getConnection("jdbc:hsqldb:mydbb","SA","");

            con.createStatement().executeUpdate("SET DATABASE SQL SYNTAX MYS TRUE");

            con.createStatement().executeUpdate("create table IF NOT EXISTS students (id int auto_increment primary key," +
            		"name varchar(45)," +
            		"lname varchar(45),"+
            		"age int)");

            //Code for inserting or updating or deleting		
	    con.createStatement().executeUpdate("insert into students (name,lname,age) values" +
						"('John','McHill','45')");	

	    //Code for SELECT					
	    PreparedStatement pst=con.prepareStatement("select * from students");
	    pst.clearParameters();
	    ResultSet rs=pst.executeQuery();;
	    System.out.println("ID\t\tName\t\tL.Name\t\tAge");
	    while(rs.next()){
		System.out.println(rs.getString(1)+"\t\t"+rs.getString(2)+"\t\t"+rs.getString(3));
	    }
	    Statement st = con.createStatement();
  	    st.execute("SHUTDOWN");
  	    con.close();  
	}catch(Exception e){
  		e.printStackTrace();
  	} 
    }
}

Line 7 : Connection con=null;;

This is to create a Connection object (to the database manager) initialized to null.

Line 10: Class.forName(“org.hsqldb.jdbc.JDBCDriver”);

For loading the JDBC connector for the HSQLDB

Line 11 : con=DriverManager.getConnection(“jdbc:hsqldb:mydbb”,”SA”,””);

Making the actual connection to the database manager using the username SA and empty password. The name of the database is : mydbb, you can change it.

Line 13: con.createStatement().executeUpdate(“SET DATABASE SQL SYNTAX MYS TRUE”);

we use the function executeUpdate for making queries that don’t return results. The command here is to set the syntax for the database manager to MySQL.

Line 15: con.createStatement().executeUpdate(“create table …

Making a query for creating a table.

Line 21: con.createStatement().executeUpdate(“insert …

Making a query for inserting data

Line 25: PreparedStatement pst=con.prepareStatement(“select *

As opposed to the previous queries. Here we use the object PreparedStatement along with ResultSet to execute a select query as it returns results.

3)Make sure that you save the HSQLDB.jar file within the same directory as the Simple.java file.

4) Compile your program using the command : javac Simple.java

5) Run your program using the command : java -cp .;hsqldb.jar Simple