LibraryDB

Description

This project illustrates a non-graphical JDBC application which represents the database backend for a library which lends books to a group of registered users. The database consists of 3 tables: book, user, and borrow. The book entity has a quantity field suggesting a number of available copies. The user entities register "borrowing" a copy of the book by having an entry in the borrow table. This borrow table is referred to as a join table in that its purpose is to register the joining of the two main entities: book and user.

Many-to-many and many-to-one

The relation between books and users is referred to as a many-to-many relationship: a user can "borrow many" books, and a the book (copies of) are borrowed by many users. The borrow table joins a book to a user by the integer id of each; a unique integer id is a common component of entities in modern databases. We are primarily interested in accessing either the users of a given book, depicted like this:
or, symmetrically, the books of a given user, depicted like this:
The relationship between books and borrows, or users and borrows is referred to as many-to-one or one-to-many, depending on your perspective. In particular, Thus the borrow record should hold the book id. The same is true for the relation a user and a borrow. We are focusing on one of these two parts:
or

ORM

The JDBC programming aspects of the database are encapsulated into simplistic ORM (Object-Relational Mapping). The goal of an ORM is to present the underlying tables of a relational database in an object-oriented presentation of classes and member functions. Primarily, the tables become classes, and the records within the tables become objects within the class.

Unlike general purpose ORMs, our version is built to suit only the needs of this application; nevertheless, it illustrates the essence of an ORM in its ability to hide most of the details of the JDBC database connectivity and SQL-based operations from the application. Our ORM is held in the models package; it supports two DBMSs: MySQL and SQLite. These operational usage is modeled closely after the RedBeanPhp ORM which we will study during the second part of the course.

Preparation for MySQL

Create the database and access rights (if necessary). This application assumes the existence of the MySQL test database which is fully accessible to the guest user with empty password. The command shell test is this (typing ENTER when password is requested):
mysql -u guest test -p
If you are using MySQL from XAMPP, this is likely to succeed without further preparation. The reason is that the pre-built test database is made accessible by any user (i.e., by the so-called anonymous user in MySQL). In non-XAMPP installations you will need to create the database and/or establish the user like this (getting in as root user may differ):
mysql -u root
mysql> create database if not exists test;
mysql> create user guest@localhost;
mysql> grant all on test.* to guest@localhost;
mysql> quit
If the "create user" statement fails, it means guest already exists; just skip this statement. Check the effectiveness by running the previous mysql command.

Preparation for SQLite

SQLite uses a memory-based or file-based database with a C++-based native API. Its home page is:
www.sqlite.org/
The JDBC driver we are using is found here:
www.xerial.org/trac/Xerial/wiki/SQLiteJDBC
You can download the version used from the Computer Science site:
sqlite-jdbc-3.16.1.jar.zip
Extract the JAR file from this archive and store it in some dedicated Java JAR library (which you may need to create) folder. Some suggestions (~ stands for your home directory in MAC and Linux):
Documents\Jars     (Windows)
~/Library/Jars/    (Mac & Linux)

Install and Run

Download the source archive LibraryDB.zip. Extract it (as the LibraryDB folder) into the default NetBeans application folder:
Documents\NetBeansProjects\     (Windows)
~/NetBeansProjects/             (Mac & Linux)
Now, on to the NetBeans preparation and execution. Open NetBeans.
  1. Create a New Project and select Java Project with Existing Sources, click Next.
  2. Now the Name and Location dialog. The most important thing to get correct is:
    Project Folder: /path/to/NetBeansProjects/LibraryDB
    Secondarily you need to set the Project Name.

    There are two ways to achieve these two outcomes:
    1. Specify the Project Folder first, then Project Name:
      • Use the Browse button to locate the Project Folder.
      • Enter the Project Name, which can be anything. For definiteness, we'll use the folder basename LibraryDB.
    2. Enter the Project Name first. By doing this first, NetBeans sets the Project Folder automatically. So you should make the Project Name be exactly LibraryDB.
    Click Next.
  3. In the Existing Sources window, click the Add Folder... button. Make sure that it is open to the NetBeansProjects/LibraryDB folder with the src folder visible. Select src.

    Then OK and, back in previous window, Finish.
You can skip step #3 and add the Existing Source folder, src, after creating the project. To do so, right-click on the LibraryDB project and select Properties. From this dialog, the top list in the Sources window shows the source directories in use. If nothing is there, click the Add Folder button and select src from the popup.

Avoid External Sources

In either case, the Properties ⇾ Sources top window should hold one entry to the relative path:
src
If there is anything other than this, it means your source folder is external to the project folder. AVOID THIS!

Force full recompilation

If you get unexpected errors after making some change, the best thing to do is to force recompilation of the entire project by running:
Run ⇾ Clean and Build Main Project
Equivalently, run the button. Afterwards, re-run the intended program(s).

Run it using MySQL

Now run the MySQL part of the project. The class which controls the choice of DBMS is models.DBProps.
  1. Add the MySQL driver JAR file as a project library:
    • In the Projects window for the LibraryDB project, right-click on Libraries and select Add Library.
    • Select MySQL JDBC Driver and click the Add Library button.
  2. Open DBProps.getProps and verify that MySQL is being used:
    public enum DB { mysql, sqlite }
     
    public static final DB which = DB.mysql;
  3. Expand the setup package and run the program(s) to create and populate the tables. Use right-click ⇾ Run File from the menu. You can do it either in one step by running:
    MakeTables.java
    
    or in two steps by running:
    CreateTables.java
    PopulateTables.java
    
    The files used to define the database tables are in the setup.tables package:
    book-mysql.sql 
    user-mysql.sql 
    borrow-mysql.sql 
    
  4. Expand the demos package and run these test programs:
    ShowTest.java
    ModifyTest.java
    
    The ModifyTest.java program behaves differently on the first run compared to all subsequent runs unless you re-initialize the database.

Run it using SQLite

  1. Add the SQLite driver JAR file:
    • Right-click on Libraries and select Add JAR/Folder.
    • Navigate to the sqlite-jdbc-3.16.1.jar file (see above) and click the Choose button.
  2. Open DBProps.getProps and change make a change so that SQLite is used:
    public enum DB { mysql, sqlite }
     
    public static final DB which = DB.sqlite;
  3. Again, run
    MakeTables.java
    
    The files used to define the database tables are now:
    book-sqlite.sql 
    user-sqlite.sql 
    borrow-sqlite.sql 
    
  4. Run the test programs just like before.
If you view the project within the Files window, you'll see the SQL database file being used:
database.sqlite

View database through clients

It is a good idea to get a "second opinion" on what is happening by viewing the contents of the database table at various points. Use both the command-line clients mysql and sqlite3 as well as Navicat (or your favorite GUI client).

Using the command-line clients:
mysql -u guest test
mysql> show tables;
mysql> select * from book;
mysql> select * from user;
mysql> select * from borrow;
mysql> quit
or
cd /PATH/TO/NetBeansProjects/LibraryDB/
sqlite3 database.sqlite 
sqlite> .tables
sqlite> .headers on
sqlite> select * from book;
sqlite> select * from user;
sqlite> select * from borrow;
sqlite> .quit

You also might try seeing the database tables in some of the GUI clients, like Navicat.

SQL Tables

A book record in our database is based on three key fields:
title: a case-insensitive string of up to 255 characters, unique among all books
binding: one of two enumerated values identified by either paper or cloth
quantity: a non-negative integer representing the quantity available
An additional field commonly added is an integer id which is treated as the main identifier of the record and is maintained in a way so that unique values are generated automatically. The SQL representations in MySQL and SQLite express some of these fields identically, and some with variations. The table versions are as follows:

book-mysql.sql
create table book (
  id int auto_increment primary key not null,
  title varchar(255) unique not null, 
  binding enum ('paper','cloth') not null,
  quantity int not null
)

book-sqlite.sql
create table book (
  id integer primary key not null,
  title text unique not null collate nocase,
  binding text not null collate nocase,
  quantity integer not null
)

user-mysql.sql
create table user (
  id integer auto_increment primary key not null,
  name varchar(255) unique not null,
  email varchar(255) not null
)

user-sqlite.sql
create table user (
  id integer primary key not null,
  name text unique not null collate nocase,
  email text not null collate nocase
)

borrow-mysql.sql
create table borrow (
  id integer auto_increment primary key not null,
  book_id integer not null,
  user_id integer not null,
  borrowed_at date,
  foreign key(book_id) references book(id),
  foreign key(user_id) references user(id),
  unique(book_id,user_id)
)

borrow-sqlite.sql
create table borrow (
  id integer primary key not null,
  book_id integer not null,
  user_id integer not null,
  borrowed_at date,
  foreign key(book_id) references book(id),
  foreign key(user_id) references user(id),
  unique(book_id,user_id)
)

Quoting identifiers

Technically, we should quote identifiers to avoid potential conflicts with SQL keywords. For example, a field named "primary" is permitted if quoted. MySQL requires backquotes:
create table `book` (  
  `id`  ... ,
  `title` ... ,
  `binding`  ... ,
  `quantity` ... 
)
In SQLite you can use double, single or backquotes. It is not necessary to quote identifiers which are not SQL keywords, and so for sake of simplicity, we omit quotes and avoid SQL keywords as identifiers.

The id field

Records are usually inserted without giving an id value, in which case the id field is automatically incremented for each inserted record. The field, typically named "id," is a common feature of tables in modern relational database usage because ORMs more-or-less expect this field with this name. The id values of deleted records are never reused. The MySQL version makes this explicit by the auto_increment qualification and it is implicit in SQLite.

One other point is that the usage of int and integer are equivalent in MySQL; however, this is not so with our version of SQLite driver on the SQLite database, in which case you must use integer.

The title field in book

The MySQL varchar type is case-insensitive. With the 255 length specification, MySQL will generate an error if you try to go beyond 255 characters. In contrast, the SQLite version is much less specific. You could use exactly the same type specification, but it will be neither case-insensitive nor length-restrictive. The best we can do is achieve case-insensitivity in SQLite using the collate nocase qualifier as we have done.

The binding field in book

The MySQL version expresses exactly what we want: this field can contain only the enumerated values 'paper' and 'cloth', regarded as case-insensitive constants. SQLite has no equivalent.

The quantity field in book

Technically, we want to avoid negative values. We can specify this requirement in the MySQL version by modifying the type:
quantity int unsigned not null
However, the outcome will be that a negative value entered will simply turn into zero with no indication of error. We simply ignore this issue at the database level.

Foreign keys

The borrow table serves a specific role as the join of the book and user tables. The join records holds the primary key id, book_id and user_id of each of the two records which are joined; thus it represents a key from another table, or a foreign key.

The borrow table employs foreign key constraints on the id values representing the pair. They are typically used in any many-to-one relationship.
create table borrow (
  ...
  book_id integer not null,
  user_id integer not null,
  ...
  foreign key(book_id) references book(id),
  foreign key(user_id) references user(id),
  ...
);
The purpose of these constraints is to make sure the ids referenced in this table are "honest" in the sense that the book_id and user_id really refer to the id of an existing book and user, respectively. A consequence is that you must delete any relevant entries in the borrow table prior to deleting the actual book or user record.

Creating the tables

Information used to establish the database is maintained in the DBProps class:

models.DBProps
package models;
 
import java.util.Properties;
 
public class DBProps {
  public enum DB { mysql, sqlite }
 
  public static final DB which = DB.mysql;  // DB.mysql or DB.sqlite
 
  public static Properties getProps() {
    Properties db = new Properties();
    switch (which) {
      case mysql:
        db.put("url", "jdbc:mysql://localhost/test");
        db.put("username", "guest");
        db.put("password", "");
        break;
      case sqlite:
        db.put("url", "jdbc:sqlite:database.sqlite");
        db.put("driver", "org.sqlite.JDBC");
        break;
    }
    return db;
  }
}
Using an enumerated type to represent the choices of supported databases is more succinct than using Java strings. The enumerated type constants strictly limit the possibilities to the desired two choices and these constants can be used as strings, per se.

Here is the helper function (called in CreateTables and MakeTables) which creates the tables. We think of this as "low level" usage because it

setup.Helper
  static String getResourceContent(String filename) throws IOException {
    InputStream istr = Helper.class.getResourceAsStream(filename);
    if (istr == null) {
      throw new IOException("Missing file: " + filename);
    }
    Scanner s = new Scanner(istr).useDelimiter("\\A");  
    return s.next();
  }
 
  public static void createTables(Properties props) throws
    IOException, ClassNotFoundException, SQLException {
 
    String url = props.getProperty("url");
    String username = props.getProperty("username");
    String password = props.getProperty("password");
    String driver = props.getProperty("driver");
    if (driver != null) {
      Class.forName(driver); // load driver if necessary
    }
    Connection cx = DriverManager.getConnection(url, username, password);
 
    // the order of creation and order of dropping are reversed
 
    ArrayList<String> create_order
        = new ArrayList(Arrays.asList( "user", "book", "borrow" ));
 
    ArrayList<String> drop_order = (ArrayList<String>) create_order.clone(); 
    Collections.reverse(drop_order);    
 
    Statement stmt = cx.createStatement();
 
    System.out.format("\n---- drop tables\n");
    for (String table : drop_order) {
      String sql = String.format("drop table if exists %s", table);
      System.out.println(sql);
      stmt.execute(sql);
    }
 
    System.out.format("\n---- create tables\n");
    for (String table : create_order) {
      String filename = String.format("tables/%s-%s.sql", table, DBProps.which);
      String sql = getResourceContent(filename).trim();
 
      System.out.println(sql);
      stmt.execute(sql);
    }
  }
Due to the foreign key constraints, the join table, borrow, must be dropped first and created last.

The models package

The Book, User and Borrow classes in the models package are meant to provide object-oriented representations of records in the book, user and borrow tables, respectively. They both extend the abstract Model root class.

Bean classes

A classes is called a bean classes when its public methods express exposed properties through getter and setter methods. These methods employ a strict syntax. One or both of these methods are employed:
public JavaType getSomeProperty() { ... }
public void setSomeProperty(JavaType ...) { ... }
in order to expose the property:
JavaType someProperty
The type signatures and camel-cased names follow this precise formula in order to correctly expose the property. A variation is made for boolean properties in which the getter method is "boolean isSomeProperty" i.e., "is" replaces "get".

Classes representing the tables

The table classes are all extensions of the abstract Model class:
models.Model  
We might consider this a "starter" model extension:
models.Starter  
These are the model classes for the 3 tables in use in this application:
models.Book  
models.User  
models.Borrow  
The classes contain data members which represent the field values in each table. These field values correspond to bean properties exposed by the classes. Note that id is a read-only property because it is meant to be set only through the database operations and thereby always correspond to the id of a table record.

In addition to the bean properties the classes extend three abstract member functions from the Model class:
void load(ResultSet rs) throws SQLException { }
void insert() throws SQLException { } 
void update() throws SQLException { }
These are "low-level" operations which, through JDBC, relate the Book or User object to the corresponding table record. The "empty" access protection implies that these operations are not accessible outside the models package, but will be accessible to the crucial ORM driver class.

Field Validation

We observed above that the MySQL table definition provides most, but not all, of the desired field validations we want for a Book (e.g., title length, binding and quantity restrictions) when an object is created, either through the public constructor (which omits the id parameter) In contrast, the SQLite table definition provides virtually none of the required validations.

If we wanted to be thorough, we could make the Book and User class constructors and setters enforce the desired field validations. The advantage of doing it here is that we have much more programming control and doing so would effectively neutralize the DBMS differences between MySQL and SQLite. Nevertheless, for simplicity of exposition, we will avoid this issue and be careful to do field validation before setting the fields.

The Model class

The Model class provides, in a addition to the polymorphic root, provides common getId, equals, hashCode member functions to establish the identity of an object based entirely on its id.

Helper members in many-to-one relationship

Take a closer look at the Borrow class, in particular these member functions:

models.Borrow
public final class Borrow extends Model {
  private int user_id;
  private int book_id;
 
  ...
  public int getBookId() {
    return book_id;
  }
 
  public int getUserId() {
    return user_id;
  }
 
  public Book getBook() {
    try {
      return ORM.load(Book.class, book_id);
    }
    catch(Exception ex) {
      System.err.println(ex.getMessage());
      return null;
    }
  }
 
  public User getUser() {
    try {
      return ORM.load(User.class, user_id);
    }
    catch(Exception ex) {
      System.err.println(ex.getMessage());
      return null;
    }
  }
  ...
}
The getBook and getUser functions are convenience members to directly obtain the book or user, respectively, that "owns" the borrow record.

The ORM class

The ORM class defines (static) functions which we apply on the Model objects. The goal of the ORM is to provide most of the functionality needed for most operations. The initialization is achieved like this:
ORM.init(props);
where props is a java.util.Properties object specifying information about the data source. The init operation creates a (static) connection held in the ORM class which is used the basis for the SQL operations. A program would use the ORM member functions and Book class like this:
  1. Create a book.
    Book book = new Book();
    String title = "some title";
    String binding = "paper" or "cloth";
    int quantity = some-non-negative-integer;
    book.setTitle(title);
    book.setBinding(binding);
    book.setQuantity(quantity);
    int id = ORM.store(book);
    The save operation attempts to insert a record of the object's fields into the the book table. The id of the newly created record is returned. An alternative method is to use the 3-parameter constructor
    Book book = new Book(title,binding,quantity);
    id = ORM.store(book);
  2. Access a book by id:
    Book book = ORM.load(Book.class, id);
    The Class parameter identifies for the ORM which Model to use and the id parameter identifies the record. If there is no record for the id, we can tell the result by discovering:
    book == null
    
  3. Update a book. This is programmed to be like adding a book, except that the book starts from a table record. Both the add and update operations with the ORM.store operation. A typical update looks like this:
    Book book = ORM.load(Book.class, id);
    if (book != null) {
      book.setQuantity(new_quantity);
      ...
      ORM.store(book);
    }
  4. Find a list of books though a "SELECT *" query with optional qualifications. The general call is:
    String extra = "where, order-by, limit, etc";
    Object[] values = query-insert-values;
    Collection<Book> L = ORM.findAll(Book.class, extra, values);
    For example, find all, without and with ordering:
    Collection<Book> L = ORM.findAll(Book.class);
    // title-ordered:
    Collection<Book> L = ORM.findAll(Book.class, "order by title");
    or, with where restrictions
    Object[] values = new Object[]{10,"paper"};
    Collection<Book> L = ORM.findAll(Book.class, 
                       "where quantity > ? and binding = ?", values);
    In all cases, a common operation with the result, L, is:
    if (L.isEmpty()) {
      // none found
    } 
    else {
      // iterate through the Collection of Books, etc.
    }
    The findAll function has return type Collection<T> object which is actually a LinkedHashSet object, offering the benefits of being a Set as well as ordering correctly corresponding to the SELECT query.
  5. Find a single book by where qualifications using a "SELECT *" query. This operation is typically used on unique fields for which there can be only one record.
    Object[] values = new Object[]{"Some Title"};
    Book book = ORM.findOne(Book.class, "title = ?", values);
    if (book == null) {
      // not found
    } 
    else {
      // ...
    }
    The programmatic difference from findAll is that we only attempt to retrieve a single record, regardless of however many may match the where specification.

ORM class code

In this section we want to point out a few details about how the operations are implemented in JDBC. Here is the class for reference:
models.ORM  
For example, a new Model object is created via a constructor designed in a way so that the id is 0 and cannot be explicitly set except through the save operation calling the non-public insert method.

The store operation is this:
public static int store(Model m) throws Exception {
  if (m.getId() == 0) {
    m.insert();
  } 
  else {
    m.update();
  }
  return m.getId();
}
An existing Model object (with non-zero id field) is obtained from load, findOne or findAll. The non-id fields are modified and changes to the table record are made by the store operation calling the update method.

The second example is the load operation:
public static <T extends Model> T load(Class C, int id) throws Exception {
  String table = (String) C.getField("TABLE").get(null);
 
  cx = connection();
  String sql = String.format("select * from %s where id=?", table);
  PreparedStatement st = cx.prepareStatement(sql);
  st.setInt(1, id);
  ResultSet rs = st.executeQuery();
  if (!rs.next()) {
    return null;
  }
  @SuppressWarnings("unchecked")
  T m = (T) C.newInstance();
  m.load(rs);
  return m;
}
Our code uses reflection to obtain the table associated with the class C. The table name is a static member. We could artificially create a non-static member function which returns this value, but it seems better to avoid this artificiality with the operation:
String table = (String) C.getField("TABLE").get(null);
The "null" argument reflects the fact that the field is static. For a non-static field value, you would replace null by the object in question.

The final steps are to instantiate a T object and assign its members from the result set row using the non-public load method.

Other classes

The table creation/population main is effected by code in these classes:
setup.Helper  
setup.CreateTables  
setup.PopulateTables  
setup.MakeTables  
The remaining main classes from the demos package are used to illustrate the ORM behavior as described above using variations of operations to perform retrievals and updates.
demos.ShowTest  
demos.ModifyTest  


© Robert M. Kline