A minimum runnable example of my project is above:
Shopping Cart and its Products; I need to save this aggregation in pure JDBC, without ORM, in the Database. Is it the proper way to save the product list to DB, passing to the shopping_cart_fk_id to each product and its DAOs?
MainClass
package ShoppingCartDAO;
import java.util.Scanner;
public class MainClass {
public static void main(String[] args)
{
ShoppingCart shoppingCart = new ShoppingCart();
Integer choice = 0;
Scanner input = new Scanner(System.in);
do {
choice = 0;
System.out.print("Choose one option: \n");
System.out.print("1 - Register New Product \n");
System.out.print("0 - Exit And Save");
choice = input.nextInt();
switch (choice)
{
case 1:
new ShoppingCart().InsertInto(new ProductRegister().RegisterNewProduct());
break;
default:
System.out.print(" Invalid Option \n");
break;
}
} while (choice != 0);
new ShoppingCartDao().add(shoppingCart);
input.close();
}
}
ConnectionFactory
package ShoppingCartDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
class ShoppingCartDao{
public void add(ShoppingCart shoppingCart)
{
try
{
PreparedStatement pstmt = ConnectionFactory.getConnection().prepareStatement("Insert into shoppingCart (date) values (?)");
pstmt.setDate(1, new java.sql.Date(System.currentTimeMillis()));
pstmt.execute();
pstmt.close();
} catch(SQLException e)
{
throw new RuntimeException(e);
}
ProductDao productDAO = new ProductDao();
for(Product product : shoppingCart.getProduct()){
productDAO.add(product);
}
}
public Integer getCount()
{
Integer count;
try
{
Statement stmt = ConnectionFactory.getConnection().createStatement();
ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS recordCount FROM shoppingcart");
rs.next();
count = rs.getInt(1);
rs.close();
stmt.close();
} catch(SQLException e)
{
throw new RuntimeException(e);
}
return count;
}
}
ShoppingCart Entity
public class ShoppingCart
{
Long id;
Date date;
ArrayList<Product>products = new ArrayList<Product>();
public ShoppingCart(){
this.date = new Date();
}
public void InsertInto(Product product){
products.add(product);
}
public Date getDate(){
return this.buyDate;
}
public ArrayList<Product>getProduct(){
return this.products;
}
}
Product Entity
public class Product
{
String name = new String();
Integer quantity = new Integer();
public void setName(String name){
this.name = name;
}
public String getName(){
return this.name;
}
public void setQuantity(Integer quantity){
this.quantity = quantity;
}
public Integer getQuantity(){
return this.quantity;
}
}
Shopping Cart DAO
package ShoppingCartDAO;
import java.util.ArrayList;
import java.util.Date;
public class ShoppingCart {
Long id;
Date date;
ArrayList<Product>products = new ArrayList<Product>();
public ShoppingCart(){
this.date = new Date();
}
public void InsertInto(Product product){
products.add(product);
}
public Date getDate(){
return this.date;
}
public ArrayList<Product>getProduct(){
return this.products;
}
public void addProduct(Product product) {
products.add(product);
}
}
ProductDAO
package ShoppingCartDAO;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class ProductDao {
public void add(Product product)
{
try
{
String query = "Insert into product (fkShoppingCartId, name, quantity) values (?, ?, ?)";
PreparedStatement pstmt = ConnectionFactory.getConnection().prepareStatement(query);
pstmt.setLong(1, new ShoppingCartDao().getCount() + 1);
pstmt.setString(2, product.getName());
pstmt.setInt(3, product.getQuantity());
pstmt.execute();
pstmt.close();
}
catch(SQLException e)
{
throw new RuntimeException(e);
}
}
You have a Java model for your data, what database schema do you need to store it?
It's pretty obvious for the simple types like numbers and strings; for the List of products in your ShoppingCart, I assume you want to conserve the order of the Products in you ShoppingCart. I also assume that a Product is in fact an item in a ShoppingCart since it has a quantity field; so a Product has no existence of it's own: it's a part of a ShoppingCard.
With these assumptions, I can represent the Java model in the database with two tables:
and then define class ShoppingCartPersistence:
public class ShoppingCardPersistence {
private final Connection connection;
private final ProductPersistence productPersistence;
public ShoppingCardPersistence(Connection connection) {
this.connection = connection;
this.productPersistence = new ProductPersistence(connection);
}
public void create(ShoppingCart cart) throws SQLException {
try (PreparedStatement stmt = connection.prepareStatement(
"INSERT INTO SHOPPING_CART(ID, DATE) VALUES(?, ?)")) {
stmt.setLong(1, cart.getId());
stmt.setTimestamp(2, new Timestamp(cart.getDate().getTime()));
stmt.executeUpdate();
}
productPersistence.create(cart.getId(), cart.getProducts());
}
public ShoppingCart read(long id) throws SQLException {
ShoppingCart cart;
try (PreparedStatement stmt = connection.prepareStatement(
"SELECT DATE FROM SHOPPING_CART WHERE ID=?")) {
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
if (!rs.next()) {
return null;
}
cart = new ShoppingCart();
cart.setId(id);
cart.setDate(new Date(rs.getTimestamp(1).getTime()));
}
cart.setProducts(productPersistence.read(cart.getId()));
return cart;
}
public void update(ShoppingCart cart) throws SQLException {
productPersistence.delete(cart.getId());
try (PreparedStatement stmt = connection.prepareStatement(
"UPDATE SHOPPING_CART SET DATE=? WHERE ID=?")) {
stmt.setLong(2, cart.getId());
stmt.setTimestamp(1, new Timestamp(cart.getDate().getTime()));
stmt.executeUpdate();
}
productPersistence.create(cart.getId(), cart.getProducts());
}
public void delete(ShoppingCart cart) throws SQLException {
productPersistence.delete(cart.getId());
try (PreparedStatement stmt = connection.prepareStatement(
"DELETE SHOPPING_CART WHERE ID=?")) {
stmt.setLong(1, cart.getId());
stmt.executeUpdate();
}
}
}
and ProductPersistence:
public class ProductPersistence {
private final Connection connection;
public ProductPersistence(Connection connection) {
this.connection = connection;
}
public void create(long id, List<Product> products) throws SQLException {
if (!products.isEmpty()) {
try (PreparedStatement stmt = connection.prepareStatement(
"INSERT INTO PRODUCT(SHOPPING_CART_ID, POS, NAME, QUANTITY)"
+ " VALUES(?,?,?,?)")) {
for (int i = 0; i < products.size(); ++i) {
Product prod = products.get(i);
stmt.setLong(1, id);
stmt.setInt(2, i);
stmt.setString(3, prod.getName());
stmt.setInt(4, prod.getQuantity());
stmt.addBatch();
}
stmt.executeBatch();
}
}
}
public List<Product> read(long id) throws SQLException {
try (PreparedStatement stmt = connection.prepareStatement(
"SELECT POS, NAME, QUANTITY FROM PRODUCT"
+ " WHERE SHOPPING_CART_ID=?"
+ " ORDER BY POS")) {
List<Product> result = new ArrayList<>();
stmt.setLong(1, id);
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
Product prod = new Product();
prod.setQuantity(rs.getInt(2));
prod.setName(rs.getString(3));
result.add(prod);
}
return result;
}
}
public void delete(long id) throws SQLException {
try (PreparedStatement stmt = connection.prepareStatement(
"DELETE PRODUCT WHERE SHOPPING_CART_ID=?")) {
stmt.setLong(1, id);
stmt.executeUpdate();
}
}
}
You would typically use these classes with a piece of code like this:
try (Connection connection = ConnectionFactory.getConnection()) {
ShoppingCartPersistence pm
= new ShoppingCartPersistence(connection);
ShoppingCart cart = pm.read(shoppingCardId);
// do a lot of useful thing with the cart
pm.update(cart);
connection.commit();
} catch (SQLException ex) {
ex.printStackTrace();
}
Note that in your ConnectionFactory you should disable autocommit.
The ID of a shopping card has to be assigned before a call to ShoppingCardPersistence.create but databases offer mechanisms to automatically generate them, either with an autoincremented column or with a sequence.