StrutsCart:ProductsDBDAO

package daos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import pojos.Product;
import exceptions.DBIntegrityException;

/**DAO that handles interaction with the Products Database.
 *

USE merwebappdb;
CREATE TABLE products(
    product_name VARCHAR(255) NOT NULL,
    product_id INT UNIQUE NOT NULL,
    price DOUBLE NOT NULL,
    modified_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY(product_id)
    );
 *
 * @author Meredith Allen
 * TODO: modify this to run on server with JNDI data source after completion of local
 * testing with basic JDBC Connection methods.
 *
 */
public class ProductsDBDao {

    private static final String DB_CONN_STRING = "jdbc:mysql://localhost:3306/merwebappdb";
    private static final String DB_USER="root";
    private static final String DB_PASSWORD="censored";

    private static final String ADD_PRODUCT_SQL="INSERT INTO products(product_name,product_id,price) VALUES(?,?,?)";
    private static final String DROP_ALL_PRODUCTS_SQL= "DELETE FROM products"; //shouldn't be used outside of testing.
    private static final String SELECT_ALL_PRODUCTS_SQL="SELECT * FROM products";
    private static final String SELECT_PRODUCT_BY_ID_SQL="SELECT FROM products WHERE product_id=?";


    static{
    try {
        Class.forName("com.mysql.jdbc.Driver");
    } catch (ClassNotFoundException e) {
         e.printStackTrace();
    }
    }

    public ProductsDBDao() {

    }

    /**Will fail on attempt to insert duplicate product ids.*/
    public synchronized void insertProduct(String productName, int productId, double productPrice)
    {
    try(Connection con = DriverManager.getConnection(DB_CONN_STRING,DB_USER,DB_PASSWORD);
        PreparedStatement ps = con.prepareStatement(ADD_PRODUCT_SQL)){
        ps.setString(1, productName);
        ps.setInt(2, productId);
        ps.setDouble(3, productPrice);
        ps.execute();

    } catch (SQLException e) {
        System.err.printf("UPDATE FAILED (%s,%d,%f).%n",productName,productId,productPrice);
        e.printStackTrace();
    }
    }

    /**Overloaded. Calls base insertProduct method.
     * Will fail on attempt to insert duplicate product ids.*/
    public synchronized void insertProduct(Product p)
    {
    insertProduct(p.getProductName(),p.getProductId(),p.getPrice());
    }

    public synchronized void insertProducts(List<Product> products)
    {
    for(Product p:products)
    {
        insertProduct(p);
    }

    }

    /**Drop all products (not a normal circumstance of operation.  May cause
     * problems if the system was relying on ids corresponding to specific products
     * and those are changed on next update.
     *
     */
    public void clearProductsTable(){
    try(Connection conn=DriverManager.getConnection(DB_CONN_STRING, DB_USER, DB_PASSWORD);
        PreparedStatement ps = conn.prepareStatement(DROP_ALL_PRODUCTS_SQL)){
       ps.execute();

    } catch (SQLException e) {
        System.err.println("FAILED ON ATTEMPT TO CLEAR PRODUCTS TABLE.");
        e.printStackTrace();
    }
    }

    /**Get a full set of Products listed in the database as Product objects.
     *
     * @return List of products in database
     */
    public List<Product> getAllProducts(){
    List<Product> products = new ArrayList<Product>();
    try(Connection conn=DriverManager.getConnection(DB_CONN_STRING, DB_USER, DB_PASSWORD);
        PreparedStatement ps = conn.prepareStatement(SELECT_ALL_PRODUCTS_SQL)){
       if(ps.execute()) //true indicates that ResultSet was stored in PreparedStatement object.
       {
           ResultSet rs = ps.getResultSet();
           while(rs.next())
           {
           String name="";
           int id=0;
           double price=0;
             name= rs.getString("product_name");
               id=rs.getInt("product_id");
               price= rs.getDouble("price");
           Product p = new Product(name,id,price);
           products.add(p);

         }


       }


    } catch (SQLException e) {
        System.err.println("FAILED ON ATTEMPT TO SELECT ALL PRODUCTS FROM TABLE.");
        e.printStackTrace();
    }
    return products;
    }
    public Product getProductById(int id)
    {
    Product product = null;
    try(Connection conn=DriverManager.getConnection(DB_CONN_STRING, DB_USER, DB_PASSWORD);
        PreparedStatement ps = conn.prepareStatement(SELECT_PRODUCT_BY_ID_SQL)){
            if(ps.execute()) //should return true (indicating ps contains ResultSet
            {
                ResultSet rs = ps.getResultSet();
                if(rs.next()){  //verify at least one result


                if(rs.next())
                {
                    throw new DBIntegrityException("Retrieved more than one product for a given id.  This should not have happened.");
                }

                }else{  //if no results, item was not found.


                }
            }
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (DBIntegrityException e) {
        e.printStackTrace();
    }
    return null;

    }

    public static void main(String[] args) {

    ProductsDBDao pdbd = new ProductsDBDao();
    pdbd.clearProductsTable();
    pdbd.insertProduct("TESTPRODUCT", 5, 3.00);
    pdbd.insertProduct("TESTPRODUCT2", 6, 6.00);
    pdbd.insertProducts(Arrays.asList(new Product("TESTPRODUCT3",7,8.00),
        new Product("TESTPRODUCT4",8,9.00)));
    //This one has a duplicate and should fail after the first add.
    pdbd.insertProducts(Arrays.asList(new Product("TESTPRODUCT5",9,11.00),
        new Product("TESTPRODUCT6SHOULDFAIL",8,9.00),
        new Product("TESTPRODUCT7WILLSTILLBEADDED",10,9.00)
        ));

    for(Product p:pdbd.getAllProducts())
        System.out.printf("PRODUCT:\t%s\t%d\t%f%n",p.getProductName(),p.getProductId(),p.getPrice());


    }
}

/*Sample output (note the rejection of the duplicate id 8.):
 * UPDATE FAILED (TESTPRODUCT6SHOULDFAIL,8,9.000000).
com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Duplicate entry '8' for key 'P
RIMARY'
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:931)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:882)
    at daos.ProductsDBDao.insertProduct(ProductsDBDao.java:64)
    at daos.ProductsDBDao.insertProduct(ProductsDBDao.java:76)
    at daos.ProductsDBDao.insertProducts(ProductsDBDao.java:83)
    at daos.ProductsDBDao.main(ProductsDBDao.java:177)
PRODUCT:    TESTPRODUCT 5   3.000000
PRODUCT:    TESTPRODUCT2    6   6.000000
PRODUCT:    TESTPRODUCT3    7   8.000000
PRODUCT:    TESTPRODUCT4    8   9.000000
PRODUCT:    TESTPRODUCT5    9   11.000000
PRODUCT:    TESTPRODUCT7WILLSTILLBEADDED    10  9.000000
 */