productmanagedao.java

这是我们做的一个网上购物系统,包手用户和管理员两大功能模块,采用jsp+servlet+java bean技术.

package com.qyg.shop.product;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import com.qyg.database.DbOperatorHandle;

public class ProductManageDAO implements ProductDAO {
	DbOperatorHandle doh=null;
	public ProductManageDAO(){
		doh=new DbOperatorHandle();
	}
	
	/**
	 * 往数据库中添加商品
	 */
	public void add(Product p) {
		try {
			PreparedStatement ps=doh.getCon().prepareStatement("insert product values(null,?,?,?,?,?)");
			ps.setString(1, p.getProduct_name());
			ps.setDouble(2, p.getProduct_price());
			ps.setString(3, p.getProduct_descr());
			ps.setInt(4, p.getProduct_cId());
			ps.setString(5, p.getProduct_image());
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("添加商品错误");
			e.printStackTrace();
		}
		finally
		{
			doh.closeCon();
		}
	}

	/**
	 * 根据产品ID删除商品
	 */
	public void delete(int pid) {
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("delete from product where product_id=?");
			ps.setInt(1, pid);
			ps.executeUpdate();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("delete wrong!");
		}
		finally{
			doh.closeCon();
		}

	}
	
	public void update(Product p) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("Update product set product_name=?,product_price=?," +
					"product_descr=?,product_cid=?,product_image=? where product_id=?");
			ps.setString(1, p.getProduct_name());
			ps.setDouble(2, p.getProduct_price());
			ps.setString(3, p.getProduct_descr());
			ps.setInt(4, p.getProduct_cId());
			ps.setString(5, p.getProduct_image());
			ps.setInt(6, p.getProduct_id());
			ps.executeUpdate();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("update product wrong!");
		}
		finally{
			doh.closeCon();
		}
	}
	
	/**
	 * 返回所有产品
	 */
	public ArrayList<Product> getAllProducts(String orderby,boolean flag) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=new ArrayList<Product>();//新建一个List对象,来存放商品
		try {
			Statement st=doh.getCon().createStatement();
			String SEL_SQL = "select * from product  order by  " + orderby;
			if (flag) {
				SEL_SQL += " desc";
			} else {
				SEL_SQL += " asc";
			}
			ResultSet rs=st.executeQuery(SEL_SQL);
			while(rs.next()){
				Product product=getProductFromRs(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return products;
	}
	/**
	 * 根据商品id得到商品信息
	 */
	public Product getProductById(int pid) {
		// TODO Auto-generated method stub
		Product product=null;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select * from product where product_id="+pid;
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				product=this.getProductFromRs(rs);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("根据商品id得到商品信息wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return product;
	}
	/**
	 * 更新产品信息
	 */


	public ArrayList<Product> getProdutsByCid(int id) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=new ArrayList<Product>();//新建一个List对象,来存放商品
		try {
			Statement st=doh.getCon().createStatement();
			ResultSet rs=st.executeQuery("select * from product where product_cId="+id);
			while(rs.next()){
				Product product=getProductFromRs(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return products;
	}

	public ArrayList<Product> getProdutsByName(String name) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=null;
		try{
			products=new ArrayList<Product>();
			Statement st=doh.getCon().createStatement();
			String sql="select * from product where product_name like '%"+name+"%'";
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				Product product=this.getProductFromRs(rs);
				products.add(product);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据品名查询商品总数wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return products;
	}
	
	public ArrayList<Product> getProdutsByName(String name, String orderby) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=null;
		try{
			products=new ArrayList<Product>();
			Statement st=doh.getCon().createStatement();
			String sql="select * from product where product_name like '%"+name+"%' order by "+orderby;
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				Product product=this.getProductFromRs(rs);
				products.add(product);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据品名查询商品总数wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return products;
	}


	/**
	 * 从rs中得到商品
	 */
	public Product getProductFromRs(ResultSet rs) {
		// TODO Auto-generated method stub
		Product p=null;
		try{
			p=new Product();
			p.setProduct_id(rs.getInt(1));
			p.setProduct_name(rs.getString(2));
			p.setProduct_price(rs.getDouble(3));
			p.setProduct_descr(rs.getString(4));
			p.setProduct_cId(rs.getInt(5));
			p.setProduct_image(rs.getString(6));
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("");
		}
		return p;
	}

	/**
	 * 得到商品总数
	 */
	public int getTotalProducts() {
		// TODO Auto-generated method stub
		int total=0;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select count(*) from product";
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			}
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("得到商品总数");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return total;
	}
	/**
	 * 根据品名查询商品总数
	 */
	public int getTotalProductsByName(String name) {
		// TODO Auto-generated method stub
		int total=0;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select count(*) from product where product_name like '%"+name+"%'";
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据品名查询商品总数wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return total;
	}
	
	public ArrayList idBetween(int firstID, int lastID, String orderby,
			boolean flag) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=new ArrayList<Product>();//新建一个List对象,来存放商品
		try {
			Statement st=doh.getCon().createStatement();
			String SEL_SQL = "select * from product where product_id between " + 
			firstID + "  and  " + lastID + "  order by  " + orderby;
			if (flag) {
				SEL_SQL += " desc";
			} else {
				SEL_SQL += " asc";
			}
			ResultSet rs=st.executeQuery(SEL_SQL);
			while(rs.next()){
				Product product=getProductFromRs(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return products;
	}

	public ArrayList priceBetween(double lowprice, double topprice,
			String orderby, boolean flag) {
		// TODO Auto-generated method stub
		ArrayList<Product> products=new ArrayList<Product>();//新建一个List对象,来存放商品
		try {
			Statement st=doh.getCon().createStatement();
			String SEL_SQL = "select * from product where product_price between " + 
			lowprice + "  and  " + topprice + "  order by  " + orderby;
			if (flag) {
				SEL_SQL += " desc";
			} else {
				SEL_SQL += " asc";
			}
			ResultSet rs=st.executeQuery(SEL_SQL);
			while(rs.next()){
				Product product=getProductFromRs(rs);
				products.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return products;
	}



	public static void main(String[] args){
		ProductDAO dao = new ProductManageDAO();
//		System.out.println(dao.idBetween(30, 31, "product_id", false));
//		System.out.println(dao.priceBetween(2500, 3000, "product_id", false));
		System.out.println(dao.getProdutsByName("惠普", "product_id"));
	}

		
}