productmysqldao.java

一个可以作为毕业设计的网上商城项目

package com.fang.shopping.dao;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.fang.shopping.Category;
import com.fang.shopping.Product;
import com.fang.shopping.User;
import com.fang.shppping.util.DB;

public class ProductMySQLDAO implements ProductDAO {
	
	public List<Product> getProducts(){
		List<Product> list = new ArrayList<Product>();
		Connection conn = null;
		ResultSet rs = null;
		
		try {
			conn = DB.getConn();
			String sql = "select * from product order by id desc";
			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.getTimestamp("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.getTimestamp("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				
				list.add(p);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}
	
	

	/*
	 * (non-Javadoc)
	 * @see com.fang.shopping.dao.ProductDAO#getProducts(java.util.List, int, int)
	 * *return pageCount
	 */
	
	public int getProducts(List<Product> products, int pageNo, int pageSize) {

		Connection conn = null;
		ResultSet rs = null;
		ResultSet rsCount = null;
		int pageCount = 0;
		
		try {
			conn = DB.getConn();
			rsCount = DB.executeQuery(conn, "select count(*) from product");
			rsCount.next();
			pageCount = (rsCount.getInt(1) + pageSize - 1) / pageSize;
			
			String sql = "select product.id,product.name,product.descr,product.normalprice,product.memberprice,product.pdate,product.categoryid "+
						", category.id cid,category.name cname,category.descr cdescr,category.pid cpid,category.isleaf,category.grade from product join category on(product.categoryid = category.id) 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.getTimestamp("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				
				Category c = new Category();
				c.setId(rs.getInt("cid"));
				c.setName(rs.getString("cname"));
				c.setDescr(rs.getString("cdescr"));
				c.setPid(rs.getInt("cpid"));
				c.setIslesf(rs.getInt("isleaf") == 0 ? true:false);
				c.setGrade(rs.getInt("grade"));
				
				p.setCategory(c);
				products.add(p);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DB.close(rs);
			DB.close(conn);
		}
		return pageCount;
	}

	
	public int findProducts(List<Product> list,int[] categoryId,
									  String keyword,
									  double lowNormalPrice,
									  double highNormalPrice,
									  double lowMemberPrice,
									  double highMemberPrice,
									  Date startDate,
									  Date endDate,
									  int pageNo,
									  int pageSize){

		Connection conn = null;
		ResultSet rs = null;
		int pageCount = 0;
		ResultSet rsCount = null;
		try {
			conn = DB.getConn();
			String sql = "select * from product where 1=1 ";
			
			String strId = "";
			
			if (categoryId != null && categoryId.length > 0) {
				strId += "(";
				for(int i=0;i<categoryId.length;i++){
					if(i<categoryId.length - 1){
						strId += categoryId[i] + ",";
					}else{
						strId += categoryId[i];
					}
				}
				
				strId += ")";
				sql += " and categoryid in " + strId;
			}

			if (keyword != null && !keyword.trim().equals("")) {

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

			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;
			}
			
			if(startDate != null){
				sql += " and pdate >= '"+ new SimpleDateFormat("yyyy-MM-DD").format(startDate)+"'";
			}
			
			if(endDate != null){
				sql += " and pdate <= '"+ new SimpleDateFormat("yyyy-MM-DD").format(endDate)+"'";
			}
			
			String sqlCount = sql.replaceFirst("select \\*", "select count(*)");
			
			sql += " limit " + (pageNo-1)*pageSize + "," + pageSize;
			//System.out.println(sql);
			
			
			rsCount = DB.executeQuery(conn, sqlCount);
			rsCount.next();
			pageCount = (rsCount.getInt(1) + pageSize - 1) / 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.getTimestamp("pdate"));
				p.setCategoryid(rs.getInt("categoryid"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return pageCount;
		
	}
	
	public List<Product> findProducts(String name){
		return null;
	}
	
	public boolean delProductByCategoryId(int categoryId){
		return false;
	}
	
	public boolean delProductById(int[] id){
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try{
				conn = DB.getConn();
				sql = "delete from product where id = id";
			
				pstmt =  DB.getPStmt(conn, sql);

				
				System.out.println(sql);
				
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
			DB.close(pstmt);
			DB.close(conn);
		}
			return false;
	
	}
	
	public boolean updateProduct(Product p){
		return false;
	}

	public boolean addProduct(Product p) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		try{
				conn = DB.getConn();
				sql = "insert into product values(null,?,?,?,?,?,?)";
			
				pstmt =  DB.getPStmt(conn, sql);

				pstmt.setString(1, p.getName());
				pstmt.setString(2, p.getDescr());
				pstmt.setDouble(3, p.getNormalprice());
				pstmt.setDouble(4, p.getMemberprice());
				pstmt.setTimestamp(5, p.getPdate());
				pstmt.setInt(6, p.getCategoryid());
				
				System.out.println(sql);
				
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
			DB.close(pstmt);
			DB.close(conn);
		}
		
		return true;
	}



}