Java Books Application

Java Books Application
— print (last updated: Nov 13, 2009) — print

This project illustrates a simple non-graphical database application where the database backend is presented via a model. It presents the model-controller portion of the MVC paradigm in that the exact details of the database are not used by the actual driver program. The comparison we want to make is between two different versions of the model:

the "homegrown" version where the data is presented through member functions specific to the needs of the application
a version using the Hibernate Object Relational Model (ORM)

Both versions rely on a MySQL database which is ultimately accessed from the JDBC via the MySQL Connector/J driver JAR file. The second version requires additional JAR files specific to Hibernate usage. Fortunately, both the MySQL driver and the Hibernate JAR files are available as NetBeans libraries.

This project consists of two independent parts which could be treated as two separate projects; they are merged into a single project to emphasize the commonalities and differences between these two versions of the model.

Homegrown version execution

Download the BooksApp.zip archive.

Database Preparation

Extract the zip file BooksApp.zip as the folder BooksApp (and move if necessary) into the default NetBeans application folder. For Windows XP, this folder is

My Documents\NetBeansProjects\

for Linux/UNIX it is

~/NetBeansProjects/

Create the database & access (if necessary)

This application assumes the existence of the test database is accessible to the user guest with no password. Creating this is a matter of executing the following:

mysql -u root
mysql> create database if not exists test;
mysql> create user guest@localhost;
mysql> grant all on test.* to guest@localhost;

If the "create user" statement fails, it means the guest user already exists; just go on to the last statement.

Create/initialize the books table

The easiest way to create the books table is to open a terminal shell, navigate to the BooksApp folder and execute the mysql command-line client as guest, running the table-creation script:

mysql -u guest test < table.sql

The script is this:




table.sql


drop table if exists books;

create table books (
id INTEGER AUTO_INCREMENT NOT NULL, 
title VARCHAR(80) NOT NULL,
type ENUM('paper','cloth'),
qty INT,
PRIMARY KEY(id),
UNIQUE(title)
);

insert into books (title,type,qty) values
('Java in a "Nutshell"', "paper", 20 ),
("Programming 'Perl'", "paper", 14),
("Multimedia Systems", "cloth", 35),
("Data Structures in Java", "cloth", 27),
("Java Foundation Classes", "paper", 51)
;

It creates the table from scratch and populates it with initial content.

NetBeans Installation/Execution

Open a New Project and select Java Project with Existing Sources, click Next. Give it the name BooksApp (it must be the name of the project folder) and click Next.
In the Existing Sources window, click the Add Folder... button. It should open to the BooksApp folder with the src folder visible. Select src. Then OK and, back in previous window, Finish.
Add the MySQL driver JAR file as a project library:
- In the Projects window for the BooksApp project, right-click on Libraries (not Test Libraries) and select Add Library.
- Select MySQL JDBC Driver and click the Add Library button.
Then run by selecting Run Run Main Project. The first time you will be asked to identify the main class, which is correctly determined to be homegrown.Main.

Try running multiple times because the statements modify the table content and the output will thus be different.

Homegrown code

As indicated initially, the precise data representation is hidden from the Main program (the controller) via the Books class, representing the MySQL books table. The class Book, quite naturally represents a single entry in the books table. Note that the setId operation, unlike the other setters is not public, and so is available only to other classes within the homegrownmodels package, in particular, to the Books class.




homegrownmodels/Book.java


package homegrownmodels;

public class Book {

  private String title;
  private String type;
  private int qty;
  private int id = 0;

  public Book() { }

  public Book(String title, String type, int qty) {
    this.title = title;
    this.type = type;
    this.qty = qty;
  }

  public String getTitle() { return title; }

  public String getType() { return type; }

  public int getQty() { return qty; }

  public void setTitle(String string) { title = string; }

  public void setType(String string) { type = string; }

  public void setQty(int qty) { this.qty = qty; }

  public int getId() { return id; }

  void setId(int id) { this.id = id; }

  @Override
  public String toString() {
    return "[" + id + ", " + title + ", " + type + ", " + qty + "]";
  }
}

In contrast to the Book class, the Books class is written to fit with the needs of this specific application. The database.properties file is a resource file which acts like a config file in the sense that it can be replaced without recompiling the source files.




homegrownmodels/database.properties


url=jdbc:mysql://localhost/test
user=guest
password=





homegrownmodels/Books.java


package homegrownmodels;

import java.sql.*;
import java.util.*;

public class Books {

  private String table_name = "books";
  private DB db = new DB();

  public Book fetch(int id) throws Exception {
    Connection cx = db.connect();
    PreparedStatement st = cx.prepareStatement(
            "SELECT * FROM " + table_name + " WHERE id=?");
    st.setInt(1, id);
    ResultSet rs = st.executeQuery();
    if (!rs.next()) {
      return null;
    }
    String title = rs.getString("title");
    String type = rs.getString("type");
    int qty = rs.getInt("qty");
    Book book = new Book(title, type, qty);
    book.setId(id);
    return book;
  }

  public int insert(Book book) throws Exception {
    Connection cx = db.connect();

    PreparedStatement st = cx.prepareStatement(
      "INSERT INTO " + table_name + "(title,type,qty)" + " VALUES (?, ?, ?)");

    st.setString(1, book.getTitle());
    st.setString(2, book.getType());
    st.setInt(3, book.getQty());
    st.executeUpdate();

    ResultSet rs = st.executeQuery("select last_insert_id()");
    rs.next();
    return rs.getInt(1);
  }

  public boolean remove(int id) throws Exception {
    Connection cx = db.connect();
    PreparedStatement st = cx.prepareStatement(
            "DELETE FROM " + table_name + " WHERE id=?");
    st.setInt(1, id);
    int num = st.executeUpdate();
    return (num != 0);
  }

  public List<Book> fetchAll() throws Exception {
    Connection cx = db.connect();

    String sql_op = "SELECT * FROM " + table_name;

    Statement st = cx.createStatement();

    ResultSet rs = st.executeQuery(sql_op);

    List<Book> L = new LinkedList<Book>();
    while (rs.next()) {
      int id = rs.getInt("id");
      String title = rs.getString("title");
      String type = rs.getString("type");
      int qty = rs.getInt("qty");
      Book book = new Book(title, type, qty);
      book.setId(id);
      L.add(book);
    }
    return L;
  }

  public void modify(Book book) throws Exception {
    Connection cx = db.connect();

    PreparedStatement st = cx.prepareStatement(
      "UPDATE " + table_name + " SET title=?, type=?, qty=?" + " WHERE id=?");

    st.setString(1, book.getTitle());
    st.setString(2, book.getType());
    st.setInt(3, book.getQty());
    st.setInt(4, book.getId());
    st.executeUpdate();
  }
}

Although many of the operations could be considered as "general", the Books class would certainly have to be expanded to suit the needs of other applications.




homegrown/Main.java


package homegrown;

import java.util.*;
import homegrownmodels.*;

public class Main {

  public static void tableDump(Books books) throws Exception {
    List<Book> list = books.fetchAll();
    for (Book b : list) {
      System.out.println(b);
    }
  }

  public static void main(String[] args) {
    try {
      Books books = new Books();
      Book book;

      System.out.println("-------------------- dump");
      tableDump(books);

      System.out.println("-------------------- fetch of id=5");
      book = books.fetch(5);
      System.out.println(book);

      System.out.println("-------------------- remove of id=2");
      boolean rem = books.remove(2);

      System.out.println("-------------------- dump after remove (" + rem + ")");
      tableDump(books);

      System.out.println("\n-------------------- add a book");
      book = new Book("Yet another Java Book", "paper", 55);
      try {
        int newId = books.insert(book);
        System.out.println("new id = " + newId);
      } catch (Exception x) {
        System.out.println(x.getMessage());
      }
      System.out.println("-------------------- dump after add");
      tableDump(books);

      System.out.println("\n-------------------- fetch of id=4 & modify");
      book = books.fetch(4);
      System.out.println(book);
      book.setQty(book.getQty() + 10);
      book.setType("paper");
      books.modify(book);

      System.out.println("-------------------- dump afer modifications");
      tableDump(books);
      
    } catch (Exception x) {
      x.printStackTrace();
      return;
    }
    System.out.println("The End");
  }
}

Hibernate version execution

Download the src.ziparchive. Move/copy these files and folders:

book.hbm.xml  hibernate.cfg.xml  hibernatemodels/  hibernate/

contained in the src folder into:

NetBeansProjects/BooksApp/src/

Prior to execution, reinitialize the table, as above, by running the mysql command-line client:

mysql -u guest test < table.sql

The hibernate folder contains an alternative Main class. Execute this as follows:

Add the Hibernate libraries to the project:
- In the Projects window for the BooksApp project, right-click on Libraries and select Add Library.
- Select Hibernate and click the Add Library button.
Change the Main class to be executed. Click the drop-down indicated by:
```
<default config>
```
and choose Customize. In the dialog window, change the Main Class field by activating the Browse button on the right side and selecting hibernate.Main
Run as before. The effects should basically be the same. Run multiple times as well.

Hibernate code

The Hibernate version is significantly more complex due to the fact that it is intended to offer a general way to access a variety of relational databases. The package hibernatemodels replaces homegrownmodels, and, within it, the Book class is exactly the same.

The key configuration file is




hibernate.cfg.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC 
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN" 
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
    <property name="hibernate.connection.username">guest</property>
    <mapping resource="book.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

which specifies the DBMS, the database and all relevant access information. It also indicates how to "bring in" the desired table via the file book.hbm.xml:




book.hbm.xml


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
          "-//Hibernate/Hibernate Mapping DTD 3.0//EN" 
          "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <class name="hibernatemodels.Book" table="books">
    <id column="id" name="id" type="int">
      <generator class="increment"/>
    </id>
    <property name="title" unique="true" />
    <property name="type"/>
    <property name="qty"/>
  </class>
</hibernate-mapping>





hibernatemodels/Book.java



package hibernatemodels;

public class Book { /* same as homegrownmodels.Book */ }

The XML configuration file indicates the structure of the table in Hibernate's terms. Its simplicity is indicative of the simple field types of a single record. Hibernate can obtain most of the necessary information from the class itself which is given by the name attribute. The table attribute, of course, specifies the actual MySQL table name.

Finally we build the structure through which Hibernate operates in the DB class. The key accessor is the Hibernate session which corresponds roughly to the JDBC connection, although Hibernate's session is much more sophisticated.




hibernatemodels/DB.java


package hibernatemodels;

import org.hibernate.*;
import org.hibernate.cfg.Configuration;

public class DB {
  public Session getSession() {
    return sessionFactory.openSession();
  }

  private static final SessionFactory sessionFactory;

  static {
    try {
      // Create the SessionFactory from hibernate.cfg.xml
      sessionFactory = new Configuration().configure().buildSessionFactory();
    } catch (HibernateException x) {
      System.err.println("Initial SessionFactory creation failed." + x);
      throw new ExceptionInInitializerError(x);
    }
  }

  public static SessionFactory getSessionFactory() {
    return sessionFactory;
  }
}

Finally, the Main controller uses Hibernate through the session objects. The load method corresponds quite closely to the homegrown fetch method where the additional parameter Book.class is effectively telling Hibernate to use the books table. Other selections are based on Hibernate's so-called Criteria queries which mimick, in an object-oriented fashion, the components of where clauses in SQL queries.




hibernate/Main.java


package hibernate;

import org.hibernate.*;
import org.hibernate.criterion.*;
import java.util.*;

import hibernatemodels.*;

public class Main {
  public static void tableDump(Session cx) throws Exception {
    Criteria crit = cx.createCriteria(Book.class);
    List<Book> list = crit.list();
    for (Book book : list) {
      System.out.println(book);
    }
  }

  public static void main(String[] args) {
    System.err.close();

    DB db = new DB();
    Session cx = db.getSession();
    Transaction tx = cx.beginTransaction();
    try {
      Book book;

      System.out.println("\n-------------------- dump");
      tableDump(cx);

      System.out.println("\n-------------------- criteria dump");
      Criteria crit = cx.createCriteria(Book.class);
      crit.add(Restrictions.ge("qty", 30));
      crit.addOrder(Order.asc("title"));
      List<Book> list = crit.list();
      for (Book b : list) {
        System.out.println(b);
      }

      System.out.println("\n-------------------- fetch of id=5");
      book = (Book) cx.load(Book.class, 5);
      System.out.println(book);

      System.out.println("\n-------------------- remove of id=2");
      book = (Book) cx.load(Book.class, 2);
      try {
        cx.delete(book);
        cx.flush();
      } catch (Exception x) {
        System.out.println(x.getMessage());
      }
      System.out.println("-------------------- dump after remove");
      tableDump(cx);

      System.out.println("\n-------------------- add a book");
      book = new Book("Yet another Java Book", "paper", 55);
      try {
        Integer newId = (Integer) cx.save(book);
        System.out.println("new id = " + newId);
        cx.flush();
      } catch (Exception x) {
        System.out.println(x.getCause().toString().replaceFirst(".*: ", ""));
      }
      System.out.println("-------------------- dump after add");
      tableDump(cx);

      System.out.println("\n-------------------- fetch of id=4 & modify");
      book = (Book) cx.load(Book.class, 4);
      System.out.println(book);
      book.setQty(book.getQty() + 10);
      book.setType("paper");
      cx.flush();
      System.out.println("-------------------- dump afer modifications");
      cx.flush();

    } catch (Exception x) {
      x.printStackTrace();
      return;
    } finally {
      tx.commit();
    }
    System.out.println("The End");
  }
}

Note the usage of

System.err.close();

at the beginning of execution. This is done to avoid the voluminous standard error output of a typical Hibernate execution.