package s2jsp.bysj.dao;
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 s2jsp.bysj.dao.BaseDao;
import s2jsp.bysj.entity.Product;
public class ProductDao extends BaseDao {
private Connection conn = null; // 保存数据库连接
private PreparedStatement pstmt = null;// 用于执行SQL语句
private ResultSet rs = null;// 用户保存查询结果集
public static final int PAGE_NUM = 5;// 定义列表中显示的商品个数
public List findAllProduct() {
List list = new ArrayList();
String sql = "select * form product";
try {
conn = this.getConn();
Product product = new Product();
product.setProductID(rs.getInt("productID"));
product.setSerialNumber(rs.getString("serialNumber"));
product.setName(rs.getString("name"));
product.setPrice(rs.getDouble("price"));
product.setBrand(rs.getString("brand"));
product.setModel(rs.getString("model"));
product.setPicture(rs.getString("picture"));
product.setDescription(rs.getString("description"));
list.add(product);
} catch (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return list;
}
public Product findProductByID(String productID) {
Product product = null;
String sql = "select * form product where productID = ?";
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, productID);
rs = pstmt.executeQuery();
if (rs.next()) {
product = new Product();
product.setProductID(rs.getInt("productID"));
product.setSerialNumber(rs.getString("serialNumber"));
product.setName(rs.getString("name"));
product.setPrice(rs.getDouble("price"));
product.setBrand(rs.getString("brand"));
product.setModel(rs.getString("model"));
product.setPicture(rs.getString("picture"));
product.setDescription(rs.getString("description"));
}
} catch (ClassNotFoundException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (SQLException e) {
// TODO 自动生成 catch 块 用于处理未知错误
e.printStackTrace();
}
return product;
}
public int insertProduct(Product product) {
String sql = "insert into product values(" + product.getProductID()
+ "?,?,?,?," + product.getPrice() + ",?,?)";
String[] param = new String[] { product.getSerialNumber(),
product.getName(), product.getBrand(), product.getModel(),
product.getPicture(), product.getDescription() };
return this.executeSQL(sql, param);
}
public List showProductforpage(int page) {
List list = new ArrayList();
int number = 0;
if (page > 1) {
number = PAGE_NUM * (page - 1);
}
String sql = "select top"
+ PAGE_NUM
+ "* from Product where productID not in(select top"
+ number
+ "productID from product order by productID desc)order by productID desc";
try {
conn = this.getConn();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Product product = new Product();
product.setProductID(rs.getInt("productID"));
product.setSerialNumber(rs.getString("serialNumber"));
product.setName(rs.getString("name"));
product.setPrice(rs.getDouble("price"));
product.setBrand(rs.getString("brand"));
product.setModel(rs.getString("model"));
product.setPicture(rs.getString("picture"));
product.setDescription(rs.getString("description"));
list.add(product);
}
} catch (ClassNotFoundException e) {
// TODO 自动生成 catch 块
e.printStackTrace();
} catch (SQLException e) {
// TODO 自动生成 catch 块 用于处理未知错误
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);// 使用完清理内存
}
return list;
}
public int deleteProductByID(String productID) {
String sql = "delete from product where productID = ?";
String[] param = new String[] { productID };
int count = this.executeSQL(sql, param);
return count;
}
public int updateProduct(Product product) {
String sql = "update product set serialNumber=?,name=?,brand=?,model=?,price="
+ product.getPrice()
+ ",prcture=?,description=? where productID="
+ product.getProductID();
String[] param = new String[] { product.getSerialNumber(),
product.getName(), product.getBrand(), product.getModel(),
product.getPicture(), product.getDescription()};
int count = this.executeSQL(sql, param);
return count;
}
}