easybuy项目一个jsp+servlet+javabean的一个实例

package com.quicklyteam.easybuy.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.quicklyteam.easybuy.dao.BaseDao; import com.quicklyteam.easybuy.dao.ProductDao; import com.quicklyteam.easybuy.entity.Product; /** * 商品数据访问层实现类 * @author Yzw * @version 2013-5-10 */ public class ProductDaoImpl extends BaseDao implements ProductDao { /** * 通过商品状态获取部分商品 */ public List<Product> getProductsByStatus(int status, int number) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; List<Product> productList = new ArrayList<Product>(); String sql = "select * from easybuy_product where ep_status = ? and rownum <= ?"; try { ps = conn.prepareStatement(sql); ps.setInt(1, status); ps.setInt(2, number); rs = ps.executeQuery(); while (rs.next()) { Product product = new Product(rs.getInt("ep_id"), rs.getString("ep_name"), rs.getString("ep_description"), rs.getDouble("ep_price"), rs.getInt("ep_stock"), rs.getInt("ep_status"), rs.getInt("epc_id"), rs.getInt("epc_child_id"), rs.getString("ep_file_name"), 1); productList.add(product); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, rs); } return productList; } /** * 通过类别获取商品，并限制数据范围 */ public List<Product> getProductsByCategory(int id, int level, int pageSize, int pageIndex) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; List<Product> productList = new ArrayList<Product>(); String sql = "select *\n" + " from (select rownum r, t.*\n" + " from (select * from easybuy_product where epc_id = ? and ep_status != -1) t\n" + " where rownum <= ? * ?)\n" + " where r > (? - 1) * ?"; if (level == 2) { sql = "select *\n" + " from (select rownum r, t.*\n" + " from (select * from easybuy_product where epc_child_id = ? and ep_status != -1) t\n" + " where rownum <= ? * ?)\n" + " where r > (? - 1) * ?"; } if (id == -1 && level == -1) { sql = "select *\n" + " from (select rownum r, t.*\n" + " from (select * from easybuy_product where ep_status != -1) t\n" + " where rownum <= ? * ?)\n" + " where r > (? - 1) * ?"; } try { ps = conn.prepareStatement(sql); if (id == -1 && level == -1) { ps.setInt(1, pageIndex); ps.setInt(2, pageSize); ps.setInt(3, pageIndex); ps.setInt(4, pageSize); } else { ps.setInt(1, id); ps.setInt(2, pageIndex); ps.setInt(3, pageSize); ps.setInt(4, pageIndex); ps.setInt(5, pageSize); } rs = ps.executeQuery(); while (rs.next()) { Product product = new Product(rs.getInt("ep_id"), rs.getString("ep_name"), rs.getString("ep_description"), rs.getDouble("ep_price"), rs.getInt("ep_stock"), rs.getInt("ep_status"), rs.getInt("epc_id"), rs.getInt("epc_child_id"), rs.getString("ep_file_name"), 1); productList.add(product); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, rs); } return productList; } /** * 获取指定商品类别ID的商品数量 */ public int getCountByCategory(int id, int level) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; String sql = "select count(*) from easybuy_product where epc_id = ? and ep_status != -1"; if (level == 2) { sql = "select count(*) from easybuy_product where epc_child_id = ? and ep_status != -1"; } if (id == -1 && level == -1) { sql = "select count(*) from easybuy_product where ep_status != -1"; } try { ps = conn.prepareStatement(sql); if (id != -1 && level != -1) { ps.setInt(1, id); } rs = ps.executeQuery(); if (rs.next()) { return rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, rs); } return 0; } /** * 获取可以显示的总页数 */ public int getTotalPages(int count, int pageSize) { // TODO Auto-generated method stub return count % pageSize == 0 ? count / pageSize : count / pageSize + 1; } /** * 通过ID获取商品信息 */ public Product getProductById(int id) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; ResultSet rs = null; String sql = "select * from easybuy_product where ep_id = ?"; try { ps = conn.prepareStatement(sql); ps.setInt(1, id); rs = ps.executeQuery(); if (rs.next()) { Product product = new Product(rs.getInt("ep_id"), rs.getString("ep_name"), rs.getString("ep_description"), rs.getDouble("ep_price"), rs.getInt("ep_stock"), rs.getInt("ep_status"), rs.getInt("epc_id"), rs.getInt("epc_child_id"), rs.getString("ep_file_name"), 1); return product; } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, rs); } return null; } /** * 通过ID删除商品 */ public int deleteById(int id, int columnType) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; String sql = "delete easybuy_product where"; if (columnType == 1) { sql += " ep_id = ?"; } else if (columnType == 2) { sql += " epc_id = ?"; } else if (columnType == 3) { sql += " epc_child_id = ?"; } else { return 0; } try { ps = conn.prepareStatement(sql); ps.setInt(1, id); return ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, null); } return 0; } /** * 添加一条商品 */ public int insert(Product product) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; String sql = "insert into easybuy_product values(easybuy_product_seq.nextval,?,?,?,?,?,?,?,?)"; try { ps = conn.prepareStatement(sql); ps.setString(1, product.getName()); ps.setString(2, product.getDescription()); ps.setDouble(3, product.getPrice()); ps.setInt(4, product.getStock()); ps.setInt(5, product.getStatus()); ps.setInt(6, product.getEpcId()); ps.setInt(7, product.getEpcChildId()); ps.setString(8, product.getFileName()); return ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, null); } return 0; } /** * 更新一条商品 */ public int update(Product product) { // TODO Auto-generated method stub Connection conn = getConnection(); PreparedStatement ps = null; String sql = "update easybuy_product set ep_name=?,ep_description=?,ep_price=?,ep_stock=?,ep_status=?,epc_id=?,epc_child_id=?,ep_file_name=? where ep_id=?"; try { ps = conn.prepareStatement(sql); ps.setString(1, product.getName()); ps.setString(2, product.getDescription()); ps.setDouble(3, product.getPrice()); ps.setInt(4, product.getStock()); ps.setInt(5, product.getStatus()); ps.setInt(6, product.getEpcId()); ps.setInt(7, product.getEpcChildId()); ps.setString(8, product.getFileName()); ps.setInt(9, product.getId()); return ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { closeAll(conn, ps, null); } return 0; } /** * 禁