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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
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:

?
1
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"

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

b. DML to insert a few records

?
1
2
3
4
5
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');
?
1
 


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

?
1
2
3
4
5
6
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.

Comments

Post a Comment

Popular posts from this blog

SQL Tutorial with HSQLDB

Image
This assumes that you have Java setup on your machine as illustrated in the "simple" Java tutorial. Q.  Why use  HSQLB ? A.  It can be useful in a number of ways. Easy to get started and if you are a beginner, you can acquire or sharpen your SQL and database skills. It is open-sourced, hence you can use it for your self-taught projects as a database. An alternative to MySQL  database. It is used in commercial projects to write unit tests for your data access layer. Apache Derby database is another alternative. It is used in the ensuing tutorials on  JDBC  and  Hibernate  as the database. Step 1 : Download the latest version of HSQLB from   http://hsqldb.org/ . I downloaded  hsqldb-2.2.9.zip  at the time of writing and extracted zip files. The HSQLDB is unpacked under  c:\Tools  as as hown below. Step 2 : You can start the server with the following command. ? 1 c:\Tools\hsqldb-2.2.9>java -cp ./h...
Read more

Hibernate tutorial with HSQLDB

Image
This tutorial is basically an extension to the  "simple" Java  and  JDBC tutorials . Hibernate is an ORM (Object to Relational Mapping) tool. Step 1 :  First step is to bring in the relevant  Hibernate  framework jar files like  hibernate-xxx.jar ,  hibernate-annotations-xxxx.jar , and other dependent jar files that are transitively brought in from hibernate framework's pom.xml file. Here is the pom.xml file for simple Java project extended for hibernate. ? 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 <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...
Read more

Java Interview Questions & Answers: user defined key class

Image
This is one of my favorite core Java interview questions as not knowing this can cause subtle and intermittent issues in Java. The issues that arise from not understanding equals( ) and hashCode( ) contract can be hard to debug.    Q.  When providing a user defined key class for storing objects in a  Map (e.g. HashMap) , what methods do you have to provide or override (i.e. method overriding)? A.  You should override the  equals( ... )  and  hashCode( .. )  methods from the  Object  class. The default implementation of the  equals( ... )  and  hashCode( ... )  methods are inherited from the  java.lang.Object  class  uses an object instance’s memory location (e.g. MyObject@6c60f2ea). This can cause problems when two instances of car objects have the same color but the inherited  equals( .. )  will return false because it uses the memory location as oppose to the color, which is different ...
Read more