I'm new to programming web sites (and so on the relationship between program and database) and I have encountered a problem.

I'm creating an online web shop that interacts and saves every info on a database.

The problem comes up regarding the shopping cart: I want that when a user adds a product in the cart, he can logout and when he logs in again he should find the cart unaltered, so I have a product as an object Product that has a int productID that is unique in the database.

I have a package DAO that interacts with the database, and there is this method:

 * Queries the database in search of the shopping cart of the indicated user
 * @param userMail indicates the user from which take the shopping cart
 * @return the Shopping Cart of the user
 * @throws ClassNotFoundException if an error occurs with the connection to the database
public static ShoppingCart getShoppingCartFromDatabase(String userMail) throws ClassNotFoundException {

    ShoppingCart updatedCart = new ShoppingCart(userMail);


    try (Connection con = DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD)){

        try (PreparedStatement pst = con.prepareStatement(
                "SELECT * FROM  " + TABLE_NAME + " "
                + "WHERE user = ?")) {

            pst.setString(1, userMail);

            ResultSet rs = pst.executeQuery();

            while (rs.next()) {

                String userMail = rs.getString("user");
                int IDProduct = rs.getInt("product");
                int npieces = rs.getInt("npieces");

                //HERE THE PROBLEM
                updatedCart.addToCart(IDProduct, npieces);


        } catch (SQLException e) {
            System.out.println("Errorin query: " + e.getMessage());

    } catch (SQLException e){
        System.out.println("Error in connection: " + e.getMessage());

    return updatedCart;


The class ShoppingCart:

public class ShoppingCart {

    private final String userMail;
    private final List<ProductInCart> productsInCart;

     * Constructor of ShoppingCart.
     * It creates a new ProductInCart item and assigns it to the list of ShoppingCart
     * @param mail is the mail of the user, that is the owner of the shopping cart
    public ShoppingCart(String mail){

        this.userMail = mail;
        this.productsInCart = new ArrayList<ProductInCart>();


    //other methods, such as get and set etc


And the class ProductInCart:

public class ProductInCart{

    private final Product product;
    private int numberOfProducts;

     * Class constructor.
     * @param product Product to be added
     * @param num Number of that item in the cart
    public ProductInCart(Product product, int num){
        this.product = product;
        this.numberOfProducts = num;

    //Other methods such as get and set, etc


The problem is that the method public void addToCart(Product product, int num) adds a product of type Product and I don't know how to get from the ID of the product to the product itself, other than creating again locally the product in getShoppingCartFromDatabase method.

The addToCart method:

* Adds multiple items of a new product in the cart
* @param product added in cart
* @param num number of items of that product
* @throws ClassNotFoundException if an error occurs with the connection to the database
* @see ShoppingCartDAO#addOneItem(String, ProductInCart)
public void addToCart(Product product, int num) throws ClassNotFoundException{
    ProductInCart added = new ProductInCart(product, num);
    ShoppingCartDAO.addOneItem(this.userMail, added);

The method ShoppingCartDAO.addOneItem(String, Product) adds a row in the table of the cart(IDProduct, IDUser, nPieces) in the database, and PK(IDProduct, IDUser).

This is my solution (or better, the explanation of my solution because all the code would be very large).

Premise: the products stored are of three types, Product, ProfessionalProduct and ScholasticProduct, both extend Product.

My solution is that in method getShoppingCartFromDatabase(String userMail, User user) I execute a query that queries the database (table cart) with the userMail (that is the ID of the user) and puts in a ResultSet all the products (or rather their IDs) and the number of items.

When I scan the ResultSet, I first clean the cart of the user, deleting the cart of that user in the database (I have a temporary copy in ResultSet), and then I add the product in the new cart and in the database: updatedCart.addToCart(ProductDAO.getFromDatabase(productID), nPieces);

That method now works because ProductDAO.getFromDatabase(productID) returns a Product. The method ProductDAO.getFromDatabase(productID) executes a query on the database looking for the product with the indicated ID, then it returns a new object of type Product (or ProfessionalProduct or ScholasticProduct based on an int productType that discriminates between the types and allows me to call the right constructor) that is created in that method.

My question is if there is a better solution, because mine seems a little bit complicated and prone to errors. I assume that an user can have up to ten or so products in the cart, so it's futile to talk about performances.



In general the creation of the database connection is extracted as a separate responsibility, either to a Library or into a ConnectionFactory. This allows you to then change anything about the Connection implementation without having to adjust it in every single DAO method:

public static ShoppingCart getShoppingCartFromDatabase(String userMail) {
    ShoppingCart updatedCart = new ShoppingCart(userMail);
    try (Connection con = ConnectionFactory.newConnection()) {
        // ...

I like very much that you're using try-with-resources, I'd drop the inner catch block though.

I also like that you're using a PreparedStatement. I like less that it's a SELECT *. In general SELECT * is discouraged because of the performance implications and it's unwanted ignorance of the table structure. Instead the columns selected should be properly enumerated:

private static final String CART_QUERY = "SELECT user, product, npieces FROM " 
     + TABLE_NAME + " WHERE user = ?";
// [...]

    try (PreparedStatement pst = con.perpareStatement(CART_QUERY)) {
       // ...

clearing the Parameters on a freshly instantiated PreparedStatement is a waste of CPU cycles, drop that :)

Design Problem

Regarding your design question: You're probably interested in a JOIN:

SELECT user, nproducts, product.name as pname, product.type as ptype

This allows you to directly retrieve the product from the database


It's generally not a good idea to put everything in an application into the static context. That makes it hard to reduce coupling between components. Instead of that you should strive to remove the static keyword wherever possible and research Dependency Injection


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.