Implementing Databases and JPA in a Java Application

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 27

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 2 of 27

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"));
p.setPrice(rs.getDouble("productprice"));
System.out.println("Product "+p.getCode());
Page 3 of 27

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);
pool.freeConnection(connection);
}
}
}
Page 4 of 27

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;
}
}
public void freeConnection(Connection c) {
try {
c.close();
} catch (SQLException sqle) {
System.err.println(sqle);
Page 5 of 27