12.2 Example: Storing Objects in a Relational Database Using JDBC

To put theory into practice, a complete JDBC mini-application example is outlined here. It uses a domain model around books and authors similar to elsewhere in this book, and creates, modifies and displays books and authors.

The complete source code and running application are available from the book's Web site; only relevant methods of interest are shown and briefly discussed here. The physical data model used is similar to one shown in the next section and is created by the following DDL:

 CREATE TABLE BOOK(       pk BIGINT NOT NULL PRIMARY KEY,       name VARCHAR(60), isbn VARCHAR(14), published DATE ); CREATE TABLE AUTHOR (       pk BIGINT NOT NULL PRIMARY KEY,       name VARCHAR(60) ); CREATE TABLE AUTHOR_BOOK (       book BIGINT NOT NULL,       author BIGINT NOT NULL ); 

First, let's write a method to insert some records into the BOOK table:

 protected long insertBook(String name, String isbn,        int year, int month, int day) throws SQLException {      Connection con = null;      try {          con = this.getConnection();          long pk = this.getNewPK();          PreparedStatement createBookStmt;          String s = "INSERT INTO BOOK VALUES (?, ?, ?, ?)";          createBookStmt = con.prepareStatement(s);          createBookStmt.setLong(1, pk);          createBookStmt.setString(2, name);          createBookStmt.setString(3, isbn);          createBookStmt.setDate(4,                           this.newDate(year, month, day));          createBookStmt.executeUpdate();          createBookStmt.close();          con.commit();          return pk;      } catch (SQLException ex) {          if (con != null) {              try {                  con.rollback();              } catch (SQLException inEx) {                  throw new Error("Rollback failure", inEx);              }          }          throw ex;      } finally {          if (con != null) {              try {                  con.setAutoCommit(true);                  con.close();              } catch (SQLException inEx) {                  throw new Error("Rollback failure", inEx);              }          }      }  } 

Following is a method that would insert a record into the AUTHOR table and associate (join) the author with a book via the AUTHOR_BOOK table:

 protected long insertAuthorForBook(String name,                          long bookPK) throws SQLException {   Connection con = null;   try {      con = this.getConnection();      long authorPK = this.getNewPK();      PreparedStatement authorStmt;      String authorSQL = "INSERT INTO AUTHOR VALUES (?, ?)";      authorStmt = con.prepareStatement(authorSQL);      authorStmt.setLong(1, authorPK);      authorStmt.setString(2, name);      authorStmt.executeUpdate();      authorStmt.close();      PreparedStatement joinStmt;      String jSQL = "INSERT INTO AUTHOR_BOOK VALUES (?, ?)";      joinStmt = con.prepareStatement(jSQL);      joinStmt.setLong(1, bookPK);      joinStmt.setLong(2, authorPK);      joinStmt.executeUpdate();      authorStmt.close();      con.commit();      return authorPK;   } catch (SQLException ex) {      if (con != null) {         try {            con.rollback();         } catch (SQLException innerEx) {             throw new Error("rollback failed", innerEx);         }      }      throw ex;   } finally {      if (con != null) {         try {             con.setAutoCommit(true);             con.close();         } catch (SQLException inEx) {             throw new Error("rollback failed", inEx);         }      }   } } 

Last but not least, here is how we would retrieve and print a list of all books, with their respective authors below them. There are several ways to achieve this in SQL, including inner joins, nested queries, or group by clauses. This method uses an inner join:

 protected void displayBooksWithResultSet()                                  throws SQLException {     Connection con = this.getConnection();     Statement stmt = con.createStatement();     String sql = "SELECT * FROM BOOK";     ResultSet rs = stmt.executeQuery(sql);     while (rs.next()) {         String name = rs.getString("name");         Date published = rs.getDate("published");         String isbn = rs.getString("isbn");         long bookPK = rs.getLong("pk");         System.out.print(name);         System.out.print(" (" + isbn + "), ");         System.out.println(published.toString());         Statement authorStmt = con.createStatement();         sql = "SELECT NAME FROM AUTHOR A, AUTHOR_BOOK AB" +            "WHERE A.PK = AB.AUTHOR AND AB.BOOK=" + bookPK;         ResultSet autorRs = authorStmt.executeQuery(sql);         while (autorRs.next()) {             String author = autorRs.getString("name");             System.out.println("\t" + author);         }         autorRs.close();         authorStmt.close();     }     rs.close();     stmt.close();     con.close(); } 

At this point, you may wish to compare this JDBC example to JDO code achieving the same purpose shown earlier in the book. The following section discusses some of the differences in more detail.