productmysqldao.java

基于mysql5.0完成的shopping网站后台

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.shopping.dao;

import com.shopping.model.Category;
import com.shopping.model.Product;
import com.shopping.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ruirui
 */
public class ProductMysqlDAO implements ProductDAO {

    public ProductMysqlDAO() {
    }

    public List<Product> getProducts() {
        List<Product> list = new ArrayList<Product>();

        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            String sql = "select * from product";
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
                Product p = new Product();
                p.initRs(rs);
                list.add(p);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductMysqlDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.close(conn, stmt, rs);
        }

        return list;

    }

    /**
     * pageCount   int
     * @param list
     * @param pageNo
     * @param pageSize
     * @return
     */
    public int getProducts(List<Product> list, int pageNo, int pageSize) {

        Connection conn = null;
        Statement stmt1 = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSet rs1 = null;
        int pageCount = -1;
        try {
            conn = DBUtil.getConnection();
            //...............
            //��ѯ��¼�ĸ���...
            String sql1 = "select count(*) from product";
            stmt1 = DBUtil.getStatement(conn);
            rs1 = DBUtil.query(stmt1, sql1);
            rs1.next();
            pageCount = (rs1.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, category.cno,category.grade" +
                    " from product join category on (product.categoryid = category.id) limit " + (pageNo - 1) * pageSize + " , " + pageSize;

            System.out.println(sql);
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
                Product u = new Product();
                u.initRs(rs);

                Category c = new Category();
                c.setId(rs.getInt("cid"));
                c.setName(rs.getString("cname"));
                c.setDescr(rs.getString("cdescr"));
                c.setPid(rs.getInt("pid"));
                c.setIsleaf(rs.getInt("cno") == 0 ? true : false);
                c.setGrade(rs.getInt("grade"));

                u.setCategory(c);
                list.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductMysqlDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.close(null, stmt, rs);
            DBUtil.close(conn, stmt1, rs1);
        }

        return pageCount;



    }

    public List<Product> searchProduts(int[] categoryId, String name, String descr, double lowNoremalPrice, double highNoremalPrice, double lowMemberPrice, double highMemberPrice, Date startDate, Date endDate, int pageNo, int pageSize) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public List<Product> searchProduts(int categoryId, String keyword) {
        List<Product> list = new ArrayList<Product>();

        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        String sql = "select * from product where categoryid = " + categoryId + " and name like '%" + keyword + "%'";

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
                Product p = new Product();
                p.initRs(rs);
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.close(conn, stmt, rs);
        }
        return list;
    }

    public List<Product> searchProduts(int categoryId,
            String keyword,
            double lowNormalPrice,
            double highNormalPrice) {



        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        List<Product> list = new ArrayList<Product>();


        String sql = "select * from product where 1 = 1";
        //һ�������⣬���where�������ж�������ǰ��˳���ϻ��������Ӱ����
        //��
        //����f�ȴ�ķ���ǰ��,,,
        // id 3 4 5  ������һ��ƥ����ݼ�������ȥ
        //����  price > 0


        //��������һ��һ��4����������
        String 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;
        }

        System.out.println(sql);

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
                Product u = new Product();
                u.initRs(rs);
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }

        return list;



    }

    public boolean deleteProductByCategoryId(int id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean deleteProductByCategoryId(int[] idArray) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean updateProduct(Product p) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * idΪ-1????Ϊʲô��һֵ...
     * @param p
     * @return
     */
    public boolean addProduct(Product p) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "insert into product values(" + p.getId() + ",?,?,?,?,?,?)";
        if (p.getId() == -1) {
            sql = "insert into product values(null,?,?,?,?,?,?)";
        }
        int result = -1;

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getPStatement(conn, sql);
            stmt.setString(1, p.getName());
            stmt.setString(2, p.getDescr());
            stmt.setDouble(3, p.getNormalPrice());
            stmt.setDouble(4, p.getMemberPrice());
            stmt.setTimestamp(5, p.getPdate());
            stmt.setInt(6, p.getCategoryId());
            result = stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, stmt, null);

        }

        if (result == 0) {
            return false;
        }
        return true;

    }

    public List<Product> getProducts(int pageNo, int pageSize) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public Product loadById(int id) {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt = null;
        String sql = "select * from product where id = " + id;
        Product p = new Product();

        try {
            conn = DBUtil.getConnection();
            stmt = DBUtil.getPStatement(conn, sql);
            rs = DBUtil.query(stmt, sql);
            rs.next();
            p.initRs(rs);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, stmt, rs);
        }


        return p;

    }
}