productmysqldao.java

这是尚学堂马士兵老师讲解的网上购物系统

package cn.edu.chu.product;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cn.edu.chu.category.Category;
import cn.edu.chu.util.DB;

public class ProductMySQLDAO implements ProductDAO {

	public Product getProduct(int id){
		Product p=null;
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = DB.getConn();
			String sql = "select * from product where id="+id;
//System.out.println(sql);
			rs = DB.executeQuery(conn, sql);
			while(rs.next()){
				p = new Product();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setDescr(rs.getString("descr"));
				p.setNormalprice(rs.getDouble("normalprice"));
				p.setMemberprice(rs.getDouble("memberprice"));
				p.setPdate(rs.getDate("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		
		return p;
	}
	
	public List<Product> getProducts() {
		List<Product> list = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		// System.out.println("DataBase is starting!");
		try {
			conn = DB.getConn();
			String sql = "select * from product";
			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setDescr(rs.getString("descr"));
				p.setNormalprice(rs.getDouble("normalprice"));
				p.setMemberprice(rs.getDouble("memberprice"));
				p.setPdate(rs.getDate("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public List<Product> getProducts(int pageNo, int pageSize) {
		List<Product> list = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from product limit " + (pageNo - 1)
					* pageSize + "," + pageSize;
			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setDescr(rs.getString("descr"));
				p.setNormalprice(rs.getDouble("normalprice"));
				p.setMemberprice(rs.getDouble("memberprice"));
				p.setPdate(rs.getDate("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public List<Product> findProducts(int categoryId, String keyWords,
			double lowNormalPrice, double highNormalPrice,
			double lowMemberPrice, double highMemberPrice, int pageNo,
			int pageSize) {
		/*
		 * System.out.println("categoryId:"+categoryId);
		 * System.out.println("keyWords:"+keyWords);
		 * System.out.println("lowNormalPrice:"+lowNormalPrice);
		 * System.out.println("highNormalPrice:"+highNormalPrice);
		 * System.out.println("lowMemberPrice:"+lowMemberPrice);
		 * System.out.println("highMemberPrice:"+highMemberPrice);
		 */

		List<Product> list = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from product where 1=1 ";
			
			if (categoryId != 0) {
				
				sql += "and categoryid=" + categoryId;
			}

			if (keyWords.trim().length()!=0) {

				sql += " and name like '%" + keyWords + "%' or descr like '%"
						+ keyWords + "%'";
			}

			if (lowNormalPrice >= 0) {
				sql += "and normalprice > " + lowNormalPrice;
			}

			if (highNormalPrice > 0) {
				sql += "and normalprice < " + highNormalPrice;
			}

			if (lowMemberPrice >= 0) {
				sql += "and normalprice > " + lowMemberPrice;
			}

			if (highMemberPrice > 0) {
				sql += "and normalprice < " + highMemberPrice;
			}

			sql += " limit " + (pageNo - 1) * pageSize + "," + pageSize;
System.out.println(sql);

			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setDescr(rs.getString("descr"));
				p.setNormalprice(rs.getDouble("normalprice"));
				p.setMemberprice(rs.getDouble("memberprice"));
				p.setPdate(rs.getDate("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public List<Product> findProducts(int categoryId, String keyWords,
			double lowNormalPrice, double highNormalPrice,
			double lowMemberPrice, double highMemberPrice) {
		/*
		 * System.out.println("categoryId:"+categoryId);
		 * System.out.println("keyWords:"+keyWords);
		 * System.out.println("lowNormalPrice:"+lowNormalPrice);
		 * System.out.println("highNormalPrice:"+highNormalPrice);
		 * System.out.println("lowMemberPrice:"+lowMemberPrice);
		 * System.out.println("highMemberPrice:"+highMemberPrice);
		 */

		List<Product> list = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from product where 1=1 ";
			
			if (categoryId != 0) {
				
				sql += "and categoryid=" + categoryId;
			}

			if (keyWords.trim().length()!=0) {

				sql += " and name like '%" + keyWords + "%' or descr like '%"
						+ keyWords + "%'";
			}

			if (lowNormalPrice >= 0) {
				sql += "and normalprice > " + lowNormalPrice;
			}

			if (highNormalPrice > 0) {
				sql += "and normalprice < " + highNormalPrice;
			}

			if (lowMemberPrice >= 0) {
				sql += "and normalprice > " + lowMemberPrice;
			}

			if (highMemberPrice > 0) {
				sql += "and normalprice < " + highMemberPrice;
			}

			//sql += " limit " + (pageNo - 1) * pageSize + "," + pageSize;
System.out.println(sql);

			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				Product p = new Product();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setDescr(rs.getString("descr"));
				p.setNormalprice(rs.getDouble("normalprice"));
				p.setMemberprice(rs.getDouble("memberprice"));
				p.setPdate(rs.getDate("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public boolean deleteProductsByCategoryId(int CategoryId) {
		return false;
	}

	public boolean deleteFromProductsById(int[] idArray) {
		return false;
	}

	public boolean updateProduct(Product p) {
		return false;
	}

	public boolean addProduct(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "insert into product values(null,?,?,?,?,?,?)";
			pstmt = DB.getStmt(conn, sql);
			pstmt.setString(1, p.getName());
			pstmt.setString(2, p.getDescr());
			pstmt.setDouble(3, p.getNormalprice());
			pstmt.setDouble(4, p.getMemberprice());
			pstmt.setDate(5, p.getPdate());
			pstmt.setInt(6, p.getCategoryid());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}

		return false;
	}

	public boolean deleteProduct(int id) {
		Connection conn=null;
		Statement stmt=null;
		String sql="delete from product where id="+id;
		try {
			conn=DB.getConn();
			stmt=DB.getStmt(conn);
			stmt.executeUpdate(sql);			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.close(stmt);
			DB.close(conn);
		}	
		return true;
	}

}