public void deleteProductByProductName(String productName) throws Exception {
    String sql = ProductHelper.DELETE_PRODUCT_SQL;
    Connection connection = null;
    PreparedStatement pstmt = null;
    try {
      connection = ConnectionUtil.getConnection();
      pstmt = connection.prepareStatement(sql);
      pstmt.setString(1, productName);
      pstmt.executeUpdate();

    } catch (Exception ex) {

      throw ex;

    } finally {
      ConnectionUtil.closeConnection(connection, pstmt);
    }
  }
  public void updateProduct(Product product) throws Exception {
    String sql = ProductHelper.UPDATE_PRODUCT_SQL;
    Connection connection = null;
    PreparedStatement pstmt = null;
    try {
      connection = ConnectionUtil.getConnection();
      pstmt = connection.prepareStatement(sql);
      productHelper.setProduct(pstmt, product);
      pstmt.executeUpdate();

    } catch (Exception ex) {

      throw ex;

    } finally {
      ConnectionUtil.closeConnection(connection, pstmt);
    }
  }
  public Long addProduct(Product product) throws Exception {
    String sql = ProductHelper.INSERT_PRODUCT_SQL;
    Connection connection = null;
    PreparedStatement pstmt = null;
    try {
      product.setProductId(DBUtil.getId(ProductHelper.SEQ_KEY));
      connection = ConnectionUtil.getConnection();
      pstmt = connection.prepareStatement(sql);
      productHelper.setProduct(pstmt, product);
      pstmt.executeUpdate();

    } catch (Exception ex) {

      throw ex;

    } finally {
      ConnectionUtil.closeConnection(connection, pstmt);
    }
    return product.getProductId();
  }
  public List<Product> loadAllProducts() throws Exception {
    String sql = " SELECT * FROM AS_PRODUCT ";
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    List<Product> productList = new ArrayList<Product>();
    try {
      connection = ConnectionUtil.getConnection();
      pstmt = connection.prepareStatement(sql);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        productList.add(productHelper.getProduct(rs));
      }
    } catch (Exception ex) {

      throw ex;

    } finally {
      ConnectionUtil.closeConnection(connection, rs, pstmt);
    }
    return productList;
  }
  public Product loadProductByProductName(String productName) throws Exception {
    String sql = " SELECT * FROM AS_PRODUCT WHERE PRODUCT_NAME = ?  ";
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    Product product = null;
    try {
      connection = ConnectionUtil.getConnection();
      pstmt = connection.prepareStatement(sql);
      pstmt.setString(1, productName);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        product = productHelper.getProduct(rs);
      }
    } catch (Exception ex) {

      throw ex;

    } finally {
      ConnectionUtil.closeConnection(connection, rs, pstmt);
    }
    return product;
  }