ProductDao.java

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);

}

 

}