Implementing Databases and JPA in a Java Application

This article discusses the implementation of databases and JPA in a Java application. It covers adding, editing, and removing products from a shopping cart using JDBC and connection pooling. It also covers the conversion to JPA and the use of annotations and the EclipseLink JPA provider. The article also discusses the implementation of secure connections and restricted access to certain pages.

MITS5502
(Research/CaseStudy Title)
(Student Full Name)
(Student ID)

Contents
Part 3 Solution............................................................................................................................................2
Add product to shopping cart..................................................................................................................2
Edit product in cart..................................................................................................................................2
Remove product from cart......................................................................................................................3
Sample codes...........................................................................................................................................3
The ProductDB class............................................................................................................................3
The ConnectionPool class....................................................................................................................5
The updated CartServlet class.............................................................................................................6
Part 4 Solution............................................................................................................................................9
Add product to shopping cart..................................................................................................................9
Edit product in cart..................................................................................................................................9
Remove product from cart....................................................................................................................10
The project structure.............................................................................................................................10
The ProductDB class..........................................................................................................................10
The Product class...............................................................................................................................12
The DBUtil class.................................................................................................................................14
Part 5 Solution..........................................................................................................................................16
Index page.............................................................................................................................................16
Authenticated products page using https protocol...............................................................................16
On successful authentication.................................................................................................................17
Project Structure...................................................................................................................................17
Content.xml file for DB configurations..............................................................................................18
Web.xml file for Security configurations............................................................................................18
Stage 6 Solution........................................................................................................................................20
Migrated jsp files...................................................................................................................................20
Migrated Controller Servlet...................................................................................................................21
Modified index.jsp.................................................................................................................................21
Fortmatted jsp with css.........................................................................................................................22
AdminController class with getRequestUri method..............................................................................22
References................................................................................................................................................26
Page 1 of 28

Page 2 of 28

Part 3 Solution
This part of the project has implemented the use of databases in storing products data. Java
Database Connectivity is an application programming interface for the programming language
Java, which characterizes how a customer may get to a database (Chatterjee, Juvekar and Sen,
2009). JDBC has been used together with connection pool. ProductDB class is used in adding,
updating and deleting products. Below are sample screenshots and code samples that shows
this implementation.
Add product to shopping cart
Edit product in cart
Page 3 of 28

Remove product from cart
Sample codes
The ProductDB class
package murach.data;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import murach.business.Product;
public class ProductDB {
//This method returns null if a product isn't found.
public static Product selectProduct(String productCode) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM product "
+ "WHERE productcode = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, productCode);
rs = ps.executeQuery();
if (rs.next()) {
Product p = new Product();
p.setCode(rs.getString("productcode"));
p.setDescription(rs.getString("productdescription"));
Page 4 of 28

p.setPrice(rs.getDouble("productprice"));
System.out.println("Product "+p.getCode());
return p;
} else {
return null;
}
} catch (SQLException e) {
System.err.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
//This method returns null if a product isn't found.
public static List<Product> selectProducts() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM product";
try {
ps = connection.prepareStatement(query);
rs = ps.executeQuery();
ArrayList<Product> products = new ArrayList();
while (rs.next()) {
Product p = new Product();
p.setCode(rs.getString("productpode"));
p.setDescription(rs.getString("productpescription"));
p.setPrice(rs.getDouble("productprice"));
products.add(p);
}
return products;
} catch (SQLException e) {
System.err.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
Page 5 of 28

pool.freeConnection(connection);
}
}
}
The ConnectionPool class
package murach.data;
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class ConnectionPool {
private static ConnectionPool pool = null;
private static DataSource dataSource = null;
public synchronized static ConnectionPool getInstance() {
if (pool == null) {
pool = new ConnectionPool();
}
return pool;
}
private ConnectionPool() {
try {
InitialContext ic = new InitialContext();
dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/musicDB");
} catch (NamingException e) {
System.err.println(e);
}
}
public Connection getConnection() {
try {
return dataSource.getConnection();
} catch (SQLException sqle) {
System.err.println(sqle);
return null;
}
}
Page 6 of 28

public void freeConnection(Connection c) {
try {
c.close();
} catch (SQLException sqle) {
System.err.println(sqle);
}
}
}
The updated CartServlet class
package murach.cart;
//import murach.data.ProductIO;
import murach.business.LineItem;
import murach.business.Cart;
import murach.business.Product;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import murach.data.ProductDB;
public class CartServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
String url = "/index.jsp";
ServletContext sc = getServletContext();
// get current action
String action = request.getParameter("action");
if (action == null) {
action = "cart"; // default action
}
// perform action and set URL to appropriate page
if (action.equals("shop")) {
url = "/index.jsp"; // the "index" page
}
else if (action.equals("cart")) {
Page 7 of 28

String productCode = request.getParameter("productCode");
String quantityString = request.getParameter("quantity");
HttpSession session = request.getSession();
Cart cart = (Cart) session.getAttribute("cart");
if (cart == null) {
cart = new Cart();
}
//if the user enters a negative or invalid quantity,
//the quantity is automatically reset to 1.
int quantity;
try {
quantity = Integer.parseInt(quantityString);
if (quantity < 0) {
quantity = 1;
}
} catch (NumberFormatException nfe) {
quantity = 1;
}
// String path = sc.getRealPath("/WEB-INF/products.txt");
// Product product = ProductIO.getProduct(productCode, path);
//introduce new db implementation
Product product = ProductDB.selectProduct(productCode);
LineItem lineItem = new LineItem();
lineItem.setProduct(product);
lineItem.setQuantity(quantity);
// System.out.println("Line item "+lineItem.getProduct().getCode());
if (quantity > 0) {
cart.addItem(lineItem);
} else if (quantity == 0) {
cart.removeItem(lineItem);
}
session.setAttribute("cart", cart);
url = "/cart.jsp";
}
else if (action.equals("checkout")) {
url = "/checkout.jsp";
}
sc.getRequestDispatcher(url)
Page 8 of 28

.forward(request, response);
}
@Override
protected void doGet(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
}
Page 9 of 28

Part 4 Solution
For this part, the application has been converted to use JPA while working with the database.
The Java Persistence API is a Java application programming interface particular that depicts the
administration of social information in applications utilizing the likes of Java Platform and
Standard Edition (Lai and ZhongZhi, 2010). JPA annotations have been added to class Product
and EclipseLink JPA provider used in ProductDB class. Below are sample screenshots and code
samples that shows this implementation.
Add product to shopping cart
Edit product in cart
Page 10 of 28

Remove product from cart
The project structure
The ProductDB class
package murach.data;
import java.util.List;
Page 11 of 28

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import murach.business.Product;
public class ProductDB {
public static Product selectProduct(long productId) {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
try {
Product product = em.find(Product.class, productId);
return product;
} finally {
em.close();
}
}
public static Product selectProduct(String productCode) {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
String query = "SELECT p FROM Product p "
+ "WHERE p.code = :productcode";
TypedQuery<Product> typedquery = em.createQuery(query, Product.class);
typedquery.setParameter("productcode", productCode);
Product product = null;
try {
product = typedquery.getSingleResult();
} catch (Exception ex) {
System.err.println(ex);
} finally {
em.close();
}
return product;
}
//This method returns null if a product isn't found.
public static List<Product> selectProducts() {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
String query = "SELECT p FROM Product p";
TypedQuery<Product> typedquery = em.createQuery(query, Product.class);
List<Product> products = null;
try {
products = typedquery.getResultList();
} catch (Exception ex) {
System.err.println(ex);
Page 12 of 28

} finally {
em.close();
}
return products;
} }
//This method returns null if a product isn't found.
public static List<Product> selectProducts() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM product";
try {
ps = connection.prepareStatement(query);
rs = ps.executeQuery();
ArrayList<Product> products = new ArrayList();
while (rs.next()) {
Product p = new Product();
p.setCode(rs.getString("productpode"));
p.setDescription(rs.getString("productpescription"));
p.setPrice(rs.getDouble("productprice"));
products.add(p);
}
return products;
} catch (SQLException e) {
System.err.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
}
The Product class
package murach.business;
Page 13 of 28

import java.io.Serializable;
import java.text.NumberFormat;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
@Entity
public class Product implements Serializable {
@Id
@GeneratedValue
@Column(name = "ProductID")
private Long productid;
@Column(name = "ProductCode")
private String code;
@Column(name = "ProductDescription")
private String description;
@Column(name = "ProductPrice")
private double price;
public Product() {
code = "";
description = "";
price = 0;
}
public Product(Long productid, String code, String description, double price) {
this.productid = productid;
this.code = code;
this.description = description;
this.price = price;
}
public Long getProductid() {
return productid;
}
public void setProductid(Long productid) {
this.productid = productid;
}
Page 14 of 28

public void setCode(String code) {
this.code = code;
}
public String getCode() {
return code;
}
public void setDescription(String description) {
this.description = description;
}
public String getDescription() {
return description;
}
public void setPrice(double price) {
this.price = price;
}
public double getPrice() {
return price;
}
public String getPriceCurrencyFormat() {
NumberFormat currency = NumberFormat.getCurrencyInstance();
return currency.format(price);
}
}
The DBUtil class
package murach.data;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
public class DBUtil {
private static final EntityManagerFactory emf =
Persistence.createEntityManagerFactory("productMaint4PU");
Page 15 of 28

public static EntityManagerFactory getEmFactory(){
return emf;
}
}
Page 16 of 28

Part 5 Solution
For this Part, the application has been enhanced to implement secure connections. Access to
pages in exception of the Index page is restricted and can only be accessed once a user is
authorized and in accordance to the roles assigned. Below are sample screenshots and code
samples that shows this implementation.
Index page
Authenticated products page using https protocol
Page 17 of 28

On successful authentication
Project Structure
Page 18 of 28

Content.xml file for DB configurations
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/ProductMaint5">
<!-- the music database that contains the Music Store data -->
<Resource auth="Container" driverClassName="com.mysql.cj.jdbc.Driver"
logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000"
name="jdbc/musicDB" password="1qaz2wsx3edc." removeAbandoned="true"
removeAbandonedTimeout="60" type="javax.sql.DataSource"
url="jdbc:mysql://localhost:3306/music?autoReconnect=true" username="root"/>
<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"
logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000"
name="jdbc/murachDB" password="1qaz2wsx3edc." removeAbandoned="true"
removeAbandonedTimeout="60" type="javax.sql.DataSource"
url="jdbc:mysql://localhost:3306/murach?autoReconnect=true" username="root"/>
<Realm className="org.apache.catalina.realm.DataSourceRealm"
dataSourceName="jdbc/murachDB" debug="99" localDataSource="true"
roleNameCol="Rolename" userCredCol="Password" userNameCol="Username"
userRoleTable="UserRole" userTable="UserPass"/>
</Context>
Web.xml file for Security configurations
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
<servlet-name>CartServlet</servlet-name>
<servlet-class>murach.cart.CartServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>CartServlet</servlet-name>
<url-pattern>/cart</url-pattern>
</servlet-mapping>
<session-config>
<session-timeout>
30
</session-timeout>
</session-config>
<welcome-file-list>
<welcome-file>
Page 19 of 28

index.jsp
</welcome-file>
</welcome-file-list>
<!-- Define two security roles -->
<security-role>
<description>customer service employees</description>
<role-name>service</role-name>
</security-role>
<security-role>
<description>programmers</description>
<role-name>programmer</role-name>
</security-role>
<security-constraint>
<!-- Restrict access to the URLs in the admin directory -->
<web-resource-collection>
<web-resource-name>Products</web-resource-name>
<url-pattern>/products/*</url-pattern>
</web-resource-collection>
<!-- Authorize the service and programmer roles -->
<auth-constraint>
<role-name>service</role-name>
<role-name>programmer</role-name>
</auth-constraint>
<user-data-constraint>
<transport-guarantee>CONFIDENTIAL</transport-guarantee>
</user-data-constraint>
</security-constraint>
<!-- Use form-based authentication to provide access -->
<login-config>
<auth-method>FORM</auth-method>
<form-login-config>
<form-login-page>/login.jsp</form-login-page>
<form-error-page>/login_error.jsp</form-error-page>
</form-login-config>
</login-config>
</web-app>
Page 20 of 28

Stage 6 Solution
For this part, the application has been added to Music store admin section. The JSP files have
been added to admin directory and all the necessary CSS, JSP, XML and Java files modified.
Below are sample screenshots and code samples that shows this implementation.
Migrated jsp files
Page 21 of 28

Migrated Controller Servlet
Modified index.jsp
Page 22 of 28

Fortmatted jsp with css
AdminController class with getRequestUri method
package music.controllers;
import music.data.ReportDB;
import music.data.InvoiceDB;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import music.business.*;
public class AdminController extends HttpServlet {
@Override
public void doPost(HttpServletRequest request,
Page 23 of 28

HttpServletResponse response)
throws IOException, ServletException {
String requestURI = request.getRequestURI();
String url = "/admin";
if (requestURI.endsWith("/displayInvoices")) {
url = displayInvoices(request, response);
} else if (requestURI.endsWith("/processInvoice")) {
url = processInvoice(request, response);
} else if (requestURI.endsWith("/displayReport")) {
displayReport(request, response);
}
else if (requestURI.endsWith("/cart")) {
url = maintainProducts(request, response);
}
getServletContext()
.getRequestDispatcher(url)
.forward(request, response);
}
@Override
public void doGet(HttpServletRequest request,
HttpServletResponse response)
throws IOException, ServletException {
String requestURI = request.getRequestURI();
String url = "/admin";
if (requestURI.endsWith("/displayInvoice")) {
url = displayInvoice(request, response);
} else if (requestURI.endsWith("/displayInvoices")) {
url = displayInvoices(request, response);
}
else if (requestURI.endsWith("/cart")) {
url = maintainProducts(request, response);
}
getServletContext()
.getRequestDispatcher(url)
.forward(request, response);
}
private String displayInvoices(HttpServletRequest request,
Page 24 of 28

HttpServletResponse response) throws IOException {
List<Invoice> unprocessedInvoices
= InvoiceDB.selectUnprocessedInvoices();
String url;
if (unprocessedInvoices != null) {
if (unprocessedInvoices.size() <= 0) {
unprocessedInvoices = null;
}
}
HttpSession session = request.getSession();
session.setAttribute("unprocessedInvoices", unprocessedInvoices);
url = "/admin/invoices.jsp";
return url;
}
private String displayInvoice(HttpServletRequest request,
HttpServletResponse response) {
HttpSession session = request.getSession();
String invoiceNumberString = request.getParameter("invoiceNumber");
int invoiceNumber = Integer.parseInt(invoiceNumberString);
List<Invoice> unprocessedInvoices = (List<Invoice>)
session.getAttribute("unprocessedInvoices");
Invoice invoice = null;
for (Invoice unprocessedInvoice : unprocessedInvoices) {
invoice = unprocessedInvoice;
if (invoice.getInvoiceNumber() == invoiceNumber) {
break;
}
}
session.setAttribute("invoice", invoice);
return "/admin/invoice.jsp";
}
private String processInvoice(HttpServletRequest request,
HttpServletResponse response) throws IOException {
HttpSession session = request.getSession();
Page 25 of 28

Invoice invoice = (Invoice) session.getAttribute("invoice");
InvoiceDB.update(invoice);
return "/adminController/displayInvoices";
}
private String maintainProducts(HttpServletRequest request,
HttpServletResponse response) throws IOException {
// HttpSession session = request.getSession();
return "/cart";
}
private void displayReport(HttpServletRequest request,
HttpServletResponse response) throws IOException {
String reportName = request.getParameter("reportName");
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
Workbook workbook;
if (reportName.equalsIgnoreCase("userEmail")) {
workbook = ReportDB.getUserEmail();
} else if (reportName.equalsIgnoreCase("downloadDetail")) {
workbook = ReportDB.getDownloadDetail(startDate, endDate);
} else {
workbook = new HSSFWorkbook();
}
response.setHeader("content-disposition",
"attachment; filename=" + reportName + ".xls");
try (OutputStream out = response.getOutputStream()) {
workbook.write(out);
}
}
}
Page 26 of 28