Question

Really need help with this. This is for my Advanced Java Programming Class. If anyone is an expert in Java I would very much appreciate the help. I will provide the code I was told to open.

Exercise 13-3   Use JPA to work with the library checkout system

In this exercise, you'll convert the application from the previous exercise to use JPA instead of JDBC to access the database.

Review the project

• Start NetBeans and open the project named ch13_ex3_library that's in the more_ex_starts directory.
• Open the LibraryController class and note that all of the code is already implemented for you because it's identical to the code you wrote for the previous exercise.
• Open the CheckoutDb class. Note that the methods for inserting, getting, and deleting checkout records are already there as placeholders, but they are empty. You will need to implement these methods.
• Verify that the EclipseLink JPA library is available to your project. If not, make it available to your project.

Create the library_catalog_jpa database

• Start MySQL Workbench
• In the MySQL connections section, double-click on the “Local instance MySQL” item. If prompted, enter the password for the root user. This should connect you to the local MySQL server as the root user.
• Open a Workbench query window and use the commands you learned in chapter11 to create a database named library_catalog_jpa. You don't need to add any tables to it because JPA will do this for you automatically.

Add JPA annotations to the Checkout class

• Open the Checkout class and add the necessary annotations to turn it into a JPA entity.

Add a persistence unit

• Add a new persistence.xml file named goldenOaksPU that connects to the database named library_catalog_jpa as the root user.
• Open the DBUtil class and verify that the persistence unit for the EntityManagerFactory matches the persistence unit you defined in the persistence.xml file.

Add code to insert a checkout record

• Open the CheckoutDb class and complete the checkoutBook method.
• Run the application and verify that you can check out a book.

Add code to get the list of checked out books

• Open the CheckoutDb class and complete the selectCheckedOutBooks method.
• Run the application and click on the “Manage checked out books” link. Verify that the book you checked out earlier appears in the list.

Add code to check in a book

• Open the CheckoutDb class and complete the checkinBook method. For the purposes of this exercise, you can delete the record from the database to consider the book to be checked in.
• Run the application, click the “Manage checked out books” link, and check in the book you checked out earlier. Verify that it no longer appears in the list of checked out books.

LibraryController.java

package goldenoaks.controllers;

import goldenoaks.business.Checkout;
import goldenoaks.data.CheckoutDb;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class LibraryController extends HttpServlet {

@Override
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
String action = request.getParameter("action");
String url = "";
switch (action) {
case "checkout":
url = "/checkout.jsp";
break;
case "manage":
url = manage(request, response);
break;
}

getServletContext().getRequestDispatcher(url)
.forward(request, response);
}

@Override
public void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
String action = request.getParameter("action");
String url = "";
switch (action) {
case "doCheckout":
url = doCheckout(request, response);
break;
case "doCheckin":
url = doCheckin(request, response);
break;
}

getServletContext().getRequestDispatcher(url)
.forward(request, response);
}

private String manage(HttpServletRequest request,
HttpServletResponse response) {
List checkedOutList = CheckoutDb.selectCheckedOutBooks();
request.setAttribute("checkedOutList", checkedOutList);
return "/checkedOutList.jsp";
}

private String doCheckout(HttpServletRequest request,
HttpServletResponse response) {
Checkout checkout = new Checkout();
checkout.setFirstName(request.getParameter("firstName"));
checkout.setLastName(request.getParameter("lastName"));
checkout.setEmailAddress(request.getParameter("emailAddress"));
checkout.setBookTitle(request.getParameter("bookTitle"));

CheckoutDb.checkoutBook(checkout);
request.setAttribute("checkout", checkout);

return "/thankyou.jsp";
}

private String doCheckin(HttpServletRequest request,
HttpServletResponse response) {
long checkoutNumber =
Long.parseLong(request.getParameter("checkoutNumber"));
CheckoutDb.checkinBook(checkoutNumber);
return manage(request, response);
}
}

CheckoutDB.java

package goldenoaks.data;

import goldenoaks.business.Checkout;
import java.util.List;

public class CheckoutDb {

public static void checkoutBook(Checkout checkout) {
//TODO: Add code to check out a book.
}

public static List<Checkout> selectCheckedOutBooks() {
//TODO: Add code to select all checked out books.
  
return null;
}

public static void checkinBook(long checkoutNumber) {
//TODO: Add code to check in a book.
}
}

DBUtil.java

package goldenoaks.data;

import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

public class DBUtil {
private static final EntityManagerFactory emf =
Persistence.createEntityManagerFactory("goldenOaksPU");
  
public static EntityManagerFactory getEmFactory() {
return emf;
}
}

Please if there is any expert who can help with this I would appreciate it thank you! This is all done in Java.

Answer 1

CheckoutDb.java

package goldenoaks.data;

import goldenoaks.business.Checkout;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CheckoutDb {

    public static int checkoutBook(Checkout checkout) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;

        String query = "INSERT INTO Checkout "
                + "(FirstName, LastName, EmailAddress, BookTitle, DueDate) "
                + "VALUES (?, ?, ?, ?, ?)";
        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, checkout.getFirstName());
            ps.setString(2, checkout.getLastName());
            ps.setString(3, checkout.getEmailAddress());
            ps.setString(4, checkout.getBookTitle());
            ps.setDate(5, new java.sql.Date(checkout.getDueDate().getTime()));
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            return 0;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }

    public static List<Checkout> selectCheckedOutBooks() {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Checkout> checkouts = new ArrayList<>();
        String query = "SELECT * from Checkout";
        try {
            ps = connection.prepareStatement(query);
            rs = ps.executeQuery();
            while (rs.next()) {
                Checkout checkout = new Checkout();
                checkout.setCheckoutNumber(rs.getLong("CheckoutNumber"));
                checkout.setFirstName(rs.getString("FirstName"));
                checkout.setLastName(rs.getString("LastName"));
                checkout.setEmailAddress(rs.getString("EmailAddress"));
                checkout.setBookTitle(rs.getString("BookTitle"));
                checkout.setDueDate(rs.getDate("DueDate"));
                checkouts.add(checkout);
            }
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

        return checkouts;
    }

    public static int checkinBook(long checkoutNumber) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();
        PreparedStatement ps = null;
        String query = "DELETE FROM Checkout WHERE CheckoutNumber = ?";
        try {
            ps = connection.prepareStatement(query);
            ps.setLong(1, checkoutNumber);
             return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            return 0;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }
}

ConnectionPool.java

package goldenoaks.data;

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class ConnectionPool {

    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;

    private ConnectionPool() {
        try {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource)
                    ic.lookup("java:/comp/env/jdbc/goldenoaks");
        } catch (NamingException e) {
            System.out.println(e);
        }
    }

    public static synchronized ConnectionPool getInstance() {
        if (pool == null) {
            pool = new ConnectionPool();
        }
        return pool;
    }

    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        }
    }

    public void freeConnection(Connection c) {
        try {
            c.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

DBUtil.java

package goldenoaks.data;

import java.sql.*;

public class DBUtil {

    public static void closeStatement(Statement s) {
        try {
            if (s != null) {
                s.close();
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    public static void closePreparedStatement(Statement ps) {
        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
    }

    public static void closeResultSet(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            System.err.println(e);
        }
    }
}