import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import s2jsp.bysj.entity.Product;
import java.io.*;
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;// 定义产品列表页面显示产品个数
/**
* 查询所有产品信息
*
* @return 产品信息列表
*/
public List findAllProduct() {
List list = new ArrayList();
String sql = "select * from product ";
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 (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return list;
}
/**
* 根据产品ID查找相关产品信息
*
* @return 产品对象
*/
public Product findProductByID(String productID) {
Product product = null;
String sql = "select * from 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 (Exception e) {
e.printStackTrace();
} finally {
this.closeAll(conn, pstmt, rs);
}
return product;
}
/**
* 将图片拷贝到image文件夹下
* @param dir
* @param newDir
*/
public void copyPicture(String dir){
try {
File file=new File(dir);
File file2=new File("..//..//eclipse//workspace//example//WebRoot//image//"+file.getName());
FileInputStream input=new FileInputStream(dir);
FileOutputStream out=new FileOutputStream(file2);
byte b[]=new byte[1024];
int cont=0;
while((cont=input.read(b))>0){
out.write(b, 0, cont);
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 实现产品分页显示
*
* @param page
* 当前页数
* @return 显示产品的列表
*/
public List showProductForPage(int page) {
List list = new ArrayList();
int number = 0;
if (page > 1) {
number = PAGE_NUM * (page - 1);
}
String sql="select * from ( "
+ " select productid,serialnumber,name,brand,model,price,picture,description,rownumber() over(order by productid desc) as rn from product"
+ ") as A1 where A1.rn between "+number+" and "+(PAGE_NUM+number);
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.setBrand(rs.getString("brand"));
product.setModel(rs.getString("model"));
product.setPrice(rs.getDouble("price"));
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;
}
/**
* 根据产品ID删除相关产品信息
*
* @param productID
* 产品ID
* @return 执行SQL语句影响数据库的行数
*/
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;
}
/**
* 添加产品信息
*
* @param product
* 封装了信息的产品对象
* @return 执行SQL语句所影响数据库的行数
*改写该方法。将图片拷贝到一个目录,然后将新目标存到数据库中。
*/
public int insertProduct(Product product) {
String sql = "insert into product(serialnumber,name,brand,model,price,picture,description) values(?,?,?,?," + product.getPrice()
+ ",?,?)";
System.out.println(product.getPrice());
System.out.println(product.getPicture());
String[] param = new String[] { product.getSerialNumber(),
product.getName(), product.getBrand(), product.getModel(),
product.getPicture(), product.getDescription() };
return this.executeSQL(sql, param);
}
/**
* 根据商品ID和对象参数修改商品信息
* @param product封装了数据的商品对象
* @return 返回执行SQL语句,数据库影响行数
*/
public int updateProduct(Product product) {
String sql = "update product set serialnumber=?,name=?,brand=?,model=?,price="
+ product.getPrice()
+ ",picture=?,description=? where productID="
+ product.getProductID();
String[] param = new String[] { product.getSerialNumber(),
product.getName(), product.getBrand(), product.getModel(),
product.getPicture(), product.getDescription() };
return this.executeSQL(sql, param);
}
}