You will have the following classes:

Book POJO

Class attributes: isbn, title, editionNumber, copyright

Methods: constructor, get methods, toString

Author POJO

Class attributes: authorId, firstName, and lastName

Methods: constructor, get methods, toString

Book DAOStatic Class Variables:

One for each SQL statement, also constants

Methods:public static List getAllBooks()

return a list of all books in the database

public static List getBooksForAuthor(String authorFirstName, String authorLastName)

return a list of all books in the database based on first name and last name

public static List getBooksForAuthor(Integer authorId)

return a list of all books in the database based on author id

main method to call other static methods

Author DAOStatic Class Variables:

One for each SQL statement, also constants

Methods:public static void displayAllAuthors()

print a list of all authors in the database. Note these are printed from the method and therefore, do not need to be returned.

public static List getAllAuthors()

return a list of all authors in the database

public static Integer getAuthorIdBySearch(String authorFirstName, String authorLastName)

return the author id based on running a query and then doing a search (using Java) on first name and last name. Note that the search algorithm is written in Java code.

public static Integer getAuthorIdByQuery(String authorFirstName, String authorLastName)

return the author id based on running a query and where clause on first name and last name. Note that the SQL statement WHERE clause is used to search on first name and last name.

main method to call other static methods

Steps:

1. Copy over the 4 code files provided.

2. No changes are necessary for the Book POJO and Author POJO

3. Update the main method of the Book DAO to:

Call the various methods as shown in the TODO statements in the code.

Run the Book DAO to test out your calls.

Update the main method of the Author DAO to:

Call the various methods as shown in the TODO statements in the code.

Run the Author DAO to test out your calls.

public class Author { private int authorId; private String firstName; private String lastName; public Author(int inAuthorId, String inFirstName, String inLastName) { this.authorId = inAuthorId; this.firstName = inFirstName; this.lastName = inLastName; } public int getAuthorId() { return this.authorId; } public String getFirstName() { return this.firstName; } public String getLastName() { return this.lastName; } @Override public String toString() { return new StringBuilder().append(this.getAuthorId()).append("-") .append(this.getFirstName()).append("-") .append(this.getLastName()).toString(); } }

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.Statement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;

// DAO = Data Access Object for Author

public class AuthorDAO { private static final String DATABASE_URL = "jdbc:hsqldb:hsql://localhost/books"; // SQL statements used private final static String SELECT_ALL_AUTHORS = "SELECT author_id, first_name, last_name " + "FROM authors ORDER BY last_name ASC"; private final static String SELECT_AUTHOR_ID = "SELECT author_id FROM authors WHERE first_name=? and last_name=?";

// numbers for each field private final static int AUTHOR_ID = 1; private final static int FIRST_NAME = 2; private final static int LAST_NAME = 3; public static void displayAllAuthors() { try ( Connection connection = DriverManager.getConnection(DATABASE_URL, "SA", ""); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(SELECT_ALL_AUTHORS)) { // get ResultSet's meta data ResultSetMetaData metaData = resultSet.getMetaData(); int numberOfColumns = metaData.getColumnCount(); System.out.printf("Authors Table of Books Database:%n%n"); // display the names of the columns in the ResultSet for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", metaData.getColumnName(i)); System.out.println(); // display query results while (resultSet.next()) { for (int i = 1; i <= numberOfColumns; i++) System.out.printf("%-8s\t", resultSet.getObject(i)); System.out.println(); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } } public static List getAllAuthors() { List listOfAuthors = new ArrayList<>(); try ( Connection connection = DriverManager.getConnection(DATABASE_URL, "SA", ""); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(SELECT_ALL_AUTHORS)) { Integer authorId; String firstName; String lastName; Author author; // get each object, which is an individual book while (resultSet.next()) { // option 1 // create temporary variables // and ten create the object authorId = resultSet.getInt(AUTHOR_ID); firstName = resultSet.getString(FIRST_NAME); lastName = resultSet.getString(LAST_NAME); // create an author object author = new Author(authorId, firstName, lastName);

// option 2 // as we create the object, // get items from the results set and pass to constructor /* author = new Author(resultSet.getInt(AUTHOR_ID), resultSet.getString(FIRST_NAME), resultSet.getString(LAST_NAME)); */ // add the book object to the list listOfAuthors.add(author); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } return listOfAuthors; } public static Integer getAuthorIdBySearch( String authorFirstName, String authorLastName) { List authors = getAllAuthors(); Integer authorId = null; for(Author author : authors) { if (author.getFirstName().equals(authorFirstName) && author.getLastName().equals(authorLastName)) { authorId = author.getAuthorId(); System.out.println("Author " + authorFirstName + " " + authorLastName + " found and the id is " + authorId); return authorId; } } System.out.println("Author " + authorFirstName + " " + authorLastName + " Not Found"); return null; } public static Integer getAuthorIdByQuery( String authorFirstName, String authorLastName) {

Integer authorId = null; try ( Connection connection = DriverManager.getConnection(DATABASE_URL, "SA", ""); PreparedStatement preparedStatement = connection.prepareStatement(SELECT_AUTHOR_ID)) { preparedStatement.setString(1, authorFirstName); preparedStatement.setString(2, authorLastName); ResultSet resultSet = preparedStatement.executeQuery();

while (resultSet.next()) { authorId = resultSet.getInt(AUTHOR_ID); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } return authorId; } public static void main(String args[]) {

System.out.println(" Now displaying authors with headings. . ."); displayAllAuthors(); System.out.println(" Now retrieving authors as ArrayList. . ."); List authors = getAllAuthors(); System.out.println(" ArrayList of authors returned is: " + authors); System.out.println(" Now retrieving author id by search . . ."); System.out.println(" Searching for xxx yyy . . .");

// TODO: create a local variable called authorId // call getAuthorIdBySearch, returning value to authorId // and passing in a first name and last name that exists System.out.println("authorId is " + authorId); System.out.println(" Searching for Joe Smith . . .");

// TODO: reuse authorId local variable // call getAuthorIdBySearch, returning value to authorId // and passing in a first name and last name that DOES NOT exist // such as Joe Smith

System.out.println("authorId is " + authorId); System.out.println(" Now retrieving author id by query . . ."); System.out.println(" Searching for xxx yyy . . .");

// TODO: reuse authorId local variable // call getAuthorIdByQuery, returning value to authorId // and passing in a first name and last name that exists

System.out.println("authorId is " + authorId); System.out.println(" Searching for Joe Smith . . .");

// TODO: reuse authorId local variable // call getAuthorIdByQuery, returning value to authorId // and passing in a first name and last name that DOES NOT exist // such as Joe Smith

System.out.println("authorId is " + authorId); } }

public class Book { private String isbn; private String title; private int editionNumber; private String copyright; public Book(String inIsbn, String inTitle, int inEditionNumber, String inCopyright) { this.isbn = inIsbn; this.title = inTitle; this.editionNumber = inEditionNumber; this.copyright = inCopyright; } public String getIsbn() { return this.isbn; } public String getTitle() { return this.title; } public int getEditionNumber() { return this.editionNumber; } public String getCopyright() { return this.copyright; } @Override public String toString() { return new StringBuilder().append(this.getIsbn()).append("-") .append(this.getTitle()).append("-") .append(this.getEditionNumber()).append("-") .append(this.getCopyright()).toString(); } }

import java.sql.Connection; import java.sql.Statement; import java.sql.PreparedStatement; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException;

import java.util.List; import java.util.ArrayList;

//DAO = Data Access Object for Book

public class BookDAO {

private final static String DATABASE_URL = "jdbc:hsqldb:hsql://localhost/books"; // SQL statements used private final static String SELECT_ALL_BOOKS = "SELECT isbn, title, edition_number, copyright " + "FROM titles ORDER BY title ASC"; private final static String SELECT_BOOKS_FOR_AUTHOR_ID = "SELECT isbn, title, edition_number, copyright " + "FROM titles INNER JOIN author_isbn ON " + "titles.isbn = author_isbn.isbn " + "WHERE author_id = ? ORDER BY title ASC"; // numbers for each field private final static int ISBN = 1; private final static int TITLE = 2; private final static int EDITION_NUM = 3; private final static int COPYRIGHT = 4;

public static List getAllBooks() { List listOfBooks = new ArrayList<>(); try ( Connection connection = DriverManager.getConnection(DATABASE_URL, "SA", ""); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(SELECT_ALL_BOOKS);) { String isbn; String title; Integer editionNumber; String copyright; Book book; // get each object, which is an individual book while (resultSet.next()) { // get each column, which are the attributes for each book // order is based on the order specified in the query isbn = resultSet.getString(ISBN); title = resultSet.getString(TITLE); editionNumber = resultSet.getInt(EDITION_NUM); copyright = resultSet.getString(COPYRIGHT); // create a book object book = new Book(isbn, title, editionNumber, copyright);

// add the book object to the list listOfBooks.add(book); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } return listOfBooks; } public static List getBooksForAuthor( String authorFirstName, String authorLastName) { Integer authorId = AuthorDAO.getAuthorIdByQuery(authorFirstName, authorLastName); if (authorId == null) return null; else return getBooksForAuthor(authorId); } public static List getBooksForAuthor(Integer authorId) { List listOfBooks = new ArrayList<>(); ResultSet resultSet = null; try { Connection connection = DriverManager.getConnection(DATABASE_URL, "SA", ""); PreparedStatement preparedStatement = connection.prepareStatement(SELECT_BOOKS_FOR_AUTHOR_ID); preparedStatement.setInt(1, authorId.intValue()); resultSet = preparedStatement.executeQuery(); String isbn; String title; Integer editionNumber; String copyright; Book book; // get each object, which is an individual book while (resultSet.next()) { // get each column, which are the attributes for each book // order is based on the order specified in the query isbn = resultSet.getString(ISBN); title = resultSet.getString(TITLE); editionNumber = resultSet.getInt(EDITION_NUM); copyright = resultSet.getString(COPYRIGHT); // create a book object book = new Book(isbn, title, editionNumber, copyright);

// add the book object to the list listOfBooks.add(book); } } catch (SQLException sqlException) { sqlException.printStackTrace(); } finally { try { resultSet.close(); } catch (SQLException sqlException) { sqlException.printStackTrace(); } } return listOfBooks; } public static void main(String[] args) {

// get the books from the database System.out.println(" Now retrieving all books . . ."); List allBooks = getAllBooks(); System.out.println(" ArrayList of books returned is: " + allBooks);

System.out.println(" Now retrieving books by xxx yyy. . .");

// TODO: create a list selectBooks and call // getBooksForAuthor, passing in a first name // and last name that exists System.out.println(" Now retrieving books by Sonial Mago"); List selectBooks = getBooksForAuthor("Sonia","Mago"); System.out.println(" ArrayList of books returned is: " + selectBooks);

// TODO: reuse selectBooks and call // getBooksForAuthor, passing in a first name // and last name for another author that exists System.out.println(" Now retrieving books by Sue Cue"); selectBooks = getBooksForAuthor("Sue","Cue"); System.out.println(" ArrayList of books returned is: " + selectBooks); System.out.println(" Now retrieving books by Kal Mao . . .");

// TODO: reuse selectBooks and call // getBooksForAuthor, passing in a first name // and last name for another an author // that DOES NOT exist, such as Kal Mao

System.out.println(" ArrayList of books returned is: " + selectBooks); } }