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(0, 6);
ps.setString(1, "Lewis");
ps.setString(2, "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.