JDBC Tutorial with HSQLDB

This assumes that you have gone through the basic SQL tutorial with HSQLDB. This tutorial extends the SQL tutorial by using JDBC (Java Data Base Connectivity) to programmatically connect to the database,  write data to the database, and read data from the database.  This is also known as the CRUD operations, which stands for Create, Read, Update, and Delete.

Step 1: You need the relevant driver jar for your database. In this tutorial I am using the HSQLDB, hence require the hsqldb-x.x.x.jar. Go to the maven repository (http://search.maven.org) and note down the GroupId, ArtifactId, and the Version.

Step 2: Open the "Simple" Java tutorial within eclipse and add this dependency jar  to your pom.xml file as shown below.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.mytutorial</groupId>
  <artifactId>simple</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>simple</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    
     <dependency>
      <groupId>org.hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>2.2.8</version>
    </dependency>    
    
  </dependencies>
</project>

Once you have updated the pom.xml file, right-mouse-click on "simple" project to bring up the context menu, and then select "Maven --> Update Dependencies". This will display hsqldb-2.2.8.jar under Maven Dependencies as highlighted above.

Step 3: Create the JdbcTutorial.java class file under com.mytutorial package. Chack the import statements at the top which are classes from the JDBC API.

package com.tutorial;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcTutorial {

 public Connection getConnection() {
  Connection con = null;
  try {
   //load the HSQLDB Database Driver. 
   //This gets loaded from the hsqldb-xxx.jar
   Class.forName("org.hsqldb.jdbcDriver");
  } catch (ClassNotFoundException cnfe) {
   System.out.println("ERROR: failed to load HSQLDB JDBC driver.");
   cnfe.printStackTrace();
  }
  
  try {
   //connect to the database. 
   con = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/mytestdb", "sa", ""); 
  }
  catch (SQLException e) {
   System.out.println("ERROR: failed to connect to the databse");
   e.printStackTrace();
  }
  
  return con;
 }
 
 
 public void executeInsertQuery(Connection con) {
  PreparedStatement ps = null;

  try {
   ps = con.prepareStatement("INSERT INTO COURSE VALUES(?,?,?)");
   ps.setInt(1, 6);
   ps.setString(2, "Lewis");
   ps.setString(3, "JDBC");
   ps.executeUpdate(); // executes the insert query
   
  }
  catch (Exception e) {
   System.out.println("ERROR executing query: ");
   e.printStackTrace();
  }
  finally {
   try {
    //close the statement
    ps.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }
 
 
 public void executeSelectQuery(Connection con) {
  PreparedStatement ps = null;
  
  try {
   ps = con.prepareStatement("SELECT COURSE_ID, NAME, COURSE  FROM COURSE");
   ResultSet rs = ps.executeQuery(); // read from database
   while(rs.next()){
    Integer id = rs.getInt("COURSE_ID");
    String name = rs.getString("NAME");
    String course = rs.getString("COURSE");
    System.out.println("id:" + id + ", name:" + name + ", course:" + course);
   }
   
  }
  catch (Exception e) {
   System.out.println("ERROR executing query: ");
   e.printStackTrace();
  }
  finally{
   try {
    ps.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }


 //main method
 public static void main(String[] args) {
  
  JdbcTutorial tut = new JdbcTutorial();
  
  //1. get the connection to the database
  final Connection con = tut.getConnection();
  
  //2. Insert a record via JDBC
  tut.executeInsertQuery(con);
  
  //3. select all records from the database
  tut.executeSelectQuery(con);
  
  
  //4. close the connection to the databse
  try {
   con.close();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }

}

Step 4: Before you can execute the above code, you need to have the HSQLDB database server running and the relevant table (i.e. Course) and data inserted as illustrated in the "SQL tutorial with HSQLDB" tutorial.

Start the database server:

c:\Tools\hsqldb-2.2.9\hsqldb>java -cp ./lib/hsqldb.jar org.hsqldb.server.Server

Note: This tutorial assumes that you have created the schema and inserted the relevant data as illustrated in "SQL tutorial with HSQLDB" tutorial with "jdbc:hsqldb:hsql://localhost:9001/mytestdb", and the SQL queries executed are



a. DDL to create a table named "Course"

create table Course (course_id integer, name varchar(50), course varchar(50), PRIMARY KEY (course_id)); 

b. DML to insert a few records

insert into Course values (1,'Sam', 'Java');  
insert into Course values (2,'peter', 'J2EE');  
insert into Course values (3,'paul', 'JSF');  
insert into Course values (4,'jonathan', 'Hibernate');  
insert into Course values (5,'james', 'Spring');  

Step 5: You can now select the "JdbcTutorial.java" from eclipse and then right-mouse-click to bring up the contextual menu. Select "Run As --> Java Application". The program will start executing from the "main" method. The output will be

id:1, name:Sam, course:Java
id:2, name:peter, course:J2EE
id:3, name:paul, course:JSF
id:4, name:jonathan, course:Hibernate
id:5, name:james, course:Spring
id:6, name:Lewis, course:JDBC

Where id:6 was inserted programmatically, and the rest were inserted via the DatabaseManager as demonstrated in the SQL tutorial. The JdbcTutorial also illustrates retrieval of the data via JDBC.


JDBC, Spring, and Hibernate tutorials

• Spring and Hibernate simple JDBC example
• How to create datasources with Spring and why favor JNDI?
• Spring SimpleJdbcCall to invoke stored procedures
• Spring JDBC Template examples -- calling stored proc, simple select, and insert with returning the generated key