Example of Java Swing Application with Oracle Database - 3

Today, we will develop a class library that will consist in a software layer that maps to concepts of the problem domain, and bridges the gap between the user interface, whose development will be addressed in the next article, and data persistence layer, treated in the first article of this series.

This layer, with concepts and operations of the problem domain, is commonly called the middle-tier or business logic layer.
 
We may consider, then, that our small application system will have a 3-layered (or 3-tier) software architecture (we'll discuss the topic of software architecture in a future article).


There are several ways to develop this middle tier, which may itself comprise one or more layers. We will develop a class for each domain concept, which, within this small example, corresponds more or less to develop a class for each table in the database.
 
There are various ways to do this. In particular, we will look at two possible ways, each with its own group of supporters, and with its own advantages and disadvantages.


1 – Some authors advocate the development of only one layer of classes with attributes and getters and setters (eg class Book below) separated from a layer with classes containing operations with meaning for the business (eg class BookBLL below), being this latter layer the one with knowledge for interfacing with the database.

1.1 classe Book:
public class Book {
    private int idBook;
    private String title;
    private String editor;
    private java.sql.Date date;
    private String ISBN;
    private String edition;
    //
    private List<Author> authors = new ArrayList<Author>();

   
    public Book() {    }

    public int getID() {  return this.idBook;    }

    public void setTitle(String title) {  this.title = title;    }

    public String getTitle() {  return title;    }

    public void setEditor(String editor) { this.editor = editor;    }

    public String getEditor() {  return editor;    }

    public void setDate(java.sql.Date date) {  this.date = date;    }

    public java.sql.Date getDate() {  return date;    }

    public void setISBN(String ISBN) {  this.ISBN = ISBN;    }

    public String getISBN() {  return ISBN;    }

    public void setEdition(String edition) {  this.edition = edition;    }

    public String getEdition() {  return edition;    }

    public List<Author> getAuthors() {  return this.authors;    }
}

1.2 classe BookBLL:

public class BookBLL {
    private DBAccessObj dbo;

    public BookBLL() {
        this.dbo = new DBAccessObj();
        this.dbo.openConnection();
    }

    public BookBLL(DBAccessObj dbo) {
        this.dbo = dbo;
        this.dbo.openConnection();
    }

     public void create(Book b) throws SQLException{

            String sqlCommand =
                         "INSERT INTO Book (ISBN, Title, Edition, Editor) VALUES('" +
                                                                b.getISBN() + "', '" + b.getTitle() + "', '" +
                                                                b. getEdition() + "', '" + b.getEditor() + "')";

            this.dbo.executeSQL(sqlCommand);
     }

…
// remaining methods
…
}

This first approach does not explore the encapsulation of data and operations on the data (fundamental concept of the object-oriented programming paradigm).

2 – Another way is to develop:

• a layer of classes with attributes and getters and setters, plus CRUD operations (Create, Retrieve, Update, Delete) (eg: class BookDAL below) separated from,

• a layer of classes with operations with meaning for the business, and that do not depend directly on the database and can be obtained by composition of primitive CRUD operations (eg class BookBLL below).

In this approach, the layer with knowledge for interfacing with the database is the first one (the data access layer, DAL).

2.1 classe BookDAL:

public class BookDAL {
    private int idBook;
    private String title;
    private String editor;
    private java.sql.Date date;
    private String ISBN;
    private String edition;
    //
    private List<Author> authors = new ArrayList<Author>();
   
    private DBAccessObj dbo;

    public BookDAL() {
        this.dbo = new DBAccessObj();
        this.dbo.openConnection();
    }

    public BookDAL(DBAccessObj dbo) {
        this.dbo = dbo;
        this.dbo.openConnection();
    }

    public int getID() {  return this.idBook;    }

    public void setTitle(String title) {  this.title = title;    }

    public String getTitle() {  return title;    }

    public void setEditor(String editor) { this.editor = editor;    }

    public String getEditor() {  return editor;    }

    public void setDate(java.sql.Date date) {  this.date = date;    }

    public java.sql.Date getDate() {  return date;    }

    public void setISBN(String ISBN) {  this.ISBN = ISBN;    }

    public String getISBN() {  return ISBN;    }

    public void setEdition(String edition) {  this.edition = edition;    }

    public String getEdition() {  return edition;    }

    public List<Author> getAuthors() {  return this.authors;    }

    public void create() throws SQLException{
              String sqlCommand =
                         "INSERT INTO Book (ISBN, Title, Edition, Editor) VALUES('" +
                         this.ISBN + "', '" + this.title + "', '" + this.edition + "', '" + this.editor + "')";
              this.dbo.executeSQL(sqlCommand);
    }

    public void retrieve(int id) throws SQLException{
         String sqlCommand =
            "SELECT ISBN, Title, DateEdition, Edition, Editor FROM Book WHERE ID = '" + id + "'";

         ResultSet book;

         book = this.dbo.executeQuery(sqlCommand);
       
         if (book.next()){
                    this.idBook = id;
                    this.ISBN = book.getString("ISBN");
                    this.title = book.getString("Title");
                    this.date = book.getDate("DateEdition");
                    this.edition = book.getString("Edition");
                    this.editor = book.getString("Editor");
         }
    }

    public void retrieveByISBN(String isbn) throws SQLException{
        String sqlCommand =
         "SELECT ID, Title, DateEdition, Edition, Editor FROM Book WHERE ISBN = '" + isbn + "'";

        ResultSet book;

        book = this.dbo.executeQuery(sqlCommand);
        
        if (book.next()){
             this.idBook = book.getInt("ID");
             this.title = book.getString("Title");
             this.date = book.getDate("DateEdition");
             this.edition = book.getString("Edition");
             this.editor = book.getString("Editor");
             this.ISBN = isbn;
        }
    }

    public void update() {
        String sqlCommand =
             "UPDATE Book SET ISBN = '" + this.ISBN + "', Title = '" + this.title + "', Edition = '" +
                   this.edition + "', Editor = '" + this.editor + "' WHERE ID = '" + this.idBook + "'";

        this.dbo.executeSQL(sqlCommand);
    }

    public void delete() {
        String sqlCommand = "DELETE FROM Book WHERE ID = '" + this.idBook + "'";
        this.dbo.executeSQL(sqlCommand);
    }


    public ResultSet retrieveThisBookAuthors(DBAccessObj dbo) throws SQLException{
        String sqlCommand =
              "SELECT B.ID, B.ISBN, B.Title, A.ID, A.Name, A.Surname FROM BookAuthor BA, Author A, Book B WHERE BA.BookID = B.ID AND BA.AuthorID = A.ID AND B.ID = " + this.getID() + " ORDER BY 3, 2";

        ResultSet books;

        dbo.openConnection();
        books = dbo.executeQuery(sqlCommand);
        return books;
    }

    public void fillThisBookAuthors() throws SQLException{
        String sqlCommand = "SELECT A.ID, A.Name, A.Surname FROM BookAuthor BA, Author A, Book B WHERE BA.BookID = B.ID AND BA.AuthorID = A.ID AND B.ID = " + this.getID() + " ORDER BY 3, 2";
        ResultSet booksA;

        this.dbo.openConnection();
        booksA = this.dbo.executeQuery(sqlCommand);

        while (booksA.next()){
            Author au = new Author();
            au.setName(booksA.getString("Name"));
            au.setSurname(booksA.getString("Surname"));
            this.authors.add(au);
        }

    }

    public static ResultSet retrieveAllBooks(DBAccessObj dbo) throws SQLException{
        String sqlCommand = "SELECT ISBN, Title, DateEdition, Edition, Editor FROM Book";
        ResultSet books;

        dbo.openConnection();
        books = dbo.executeQuery(sqlCommand);
        return books;
    }

    public static ResultSet retrieveAllBooksAuthors(DBAccessObj dbo) throws SQLException{

        String sqlCommand = "SELECT B.ID, B.ISBN, B.Title, A.ID, A.Name, A.Surname FROM BookAuthor BA, Author A, Book B WHERE BA.BookID = B.ID AND BA.AuthorID = A.ID ORDER BY 1, 6, 5";
        ResultSet books;

        dbo.openConnection();
        books = dbo.executeQuery(sqlCommand);
        return books;
    }


2.2 classe ManageBooksBLL:

public class ManageBooksBLL {
    public ManageBooksBLL () {    }

    public int borrowBook (Book book, Borrower leitor)  throws  ManageBookException {
          …
    }

    public int borrowBook (int bookID, int leitorID)  throws  ManageBookException {
          …
    }

    public int returnBook (Book book)  throws  ManageBookException{
          …
    }

    public int returnBook (int bookID)  throws  ManageBookException {
          …
    }
}



In this approach, the encapsulation of data and operations appears, oriented to the problem domain entities (persistent classes or Entity), in the data access layer (DAL).

Business Logic Layer (BLL) contains operations oriented to the use cases to be implemented in the software application, and can therefore be seen as a layer providing services to the layer above, the User Interface (UI) or Presentation layer.

In practical terms, depending on the amount of complex operations in a program (operations other than CRUD), we can still mix the DAL and BLL of the 2nd approach, by adding in classes DAL the few BLL methods that we'd have, eliminating the BLL classes.

Note, in both approaches, the use of class DBAccessObj, whose development has been addressed in the previous article, to access the database.

The zip with the end result may, as usual, be asked for as a comment to this article.


Other related articles:
     - Example of Java Swing Application with Oracle Database - 1
     - Example of Java Swing Application with Oracle Database - 2
     - Example of Java Swing Application with Oracle Database – 4