package flex.samples.product;
import java.util.ArrayList;
import java.util.List;
import java.sql.*;
import flex.samples.ConnectionHelper;
import flex.samples.DAOException;
public class ProductService {
public List getProducts() throws DAOException {
List list = new ArrayList();
Connection c = null;
try {
c = ConnectionHelper.getConnection();
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM product ORDER BY name");
while (rs.next()) {
list.add(new Product(rs.getInt("product_id"),
rs.getString("name"),
rs.getString("description"),
rs.getString("image"),
rs.getString("category"),
rs.getDouble("price"),
rs.getInt("qty_in_stock")));
}
} catch (SQLException e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
return list;
}
public List getProductsByName(String name) throws DAOException {
List list = new ArrayList();
Connection c = null;
try {
c = ConnectionHelper.getConnection();
PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE UPPER(name) LIKE ? ORDER BY name");
ps.setString(1, "%" + name.toUpperCase() + "%");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(new Product(rs.getInt("product_id"),
rs.getString("name"),
rs.getString("description"),
rs.getString("image"),
rs.getString("category"),
rs.getDouble("price"),
rs.getInt("qty_in_stock")));
}
} catch (SQLException e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
return list;
}
public Product getProduct(int productId) throws DAOException {
Product product = new Product();
Connection c = null;
try {
c = ConnectionHelper.getConnection();
PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE product_id=?");
ps.setInt(1, productId);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
product = new Product();
product.setProductId(rs.getInt("product_id"));
product.setName(rs.getString("name"));
product.setDescription(rs.getString("description"));
product.setImage(rs.getString("image"));
product.setCategory(rs.getString("category"));
product.setPrice(rs.getDouble("price"));
product.setQtyInStock(rs.getInt("qty_in_stock"));
}
} catch (Exception e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
return product;
}
public Product create(Product product) throws DAOException {
Connection c = null;
PreparedStatement ps = null;
try {
c = ConnectionHelper.getConnection();
ps = c.prepareStatement("INSERT INTO product (name, description, image, category, price, qty_in_stock) VALUES (?, ?, ?, ?, ?, ?)");
ps.setString(1, product.getName());
ps.setString(2, product.getDescription());
ps.setString(3, product.getImage());
ps.setString(4, product.getCategory());
ps.setDouble(5, product.getPrice());
ps.setInt(6, product.getQtyInStock());
ps.executeUpdate();
Statement s = c.createStatement();
// HSQLDB Syntax to get the identity (company_id) of inserted row
ResultSet rs = s.executeQuery("CALL IDENTITY()");
// MySQL Syntax to get the identity (product_id) of inserted row
// ResultSet rs = s.executeQuery("SELECT LAST_INSERT_ID()");
rs.next();
// Update the id in the returned object. This is important as this value must get returned to the client.
product.setProductId(rs.getInt(1));
} catch (Exception e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
return product;
}
public boolean update(Product product) throws DAOException {
Connection c = null;
try {
c = ConnectionHelper.getConnection();
PreparedStatement ps = c.prepareStatement("UPDATE product SET name=?, description=?, image=?, category=?, price=?, qty_in_stock=? WHERE product_id=?");
ps.setString(1, product.getName());
ps.setString(2, product.getDescription());
ps.setString(3, product.getImage());
ps.setString(4, product.getCategory());
ps.setDouble(5, product.getPrice());
ps.setInt(6, product.getQtyInStock());
ps.setInt(7, product.getProductId());
return (ps.executeUpdate() == 1);
} catch (SQLException e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
}
public boolean remove(Product product) throws DAOException {
Connection c = null;
try {
c = ConnectionHelper.getConnection();
PreparedStatement ps = c.prepareStatement("DELETE FROM product WHERE product_id=?");
ps.setInt(1, product.getProductId());
int count = ps.executeUpdate();
return (count == 1);
} catch (Exception e) {
e.printStackTrace();
throw new DAOException(e);
} finally {
ConnectionHelper.close(c);
}
}
public boolean delete(Product product) throws DAOException {
return remove(product);
}
}