模仿天猫商城的J2EE商城网站项目后端6—dao包

最新推荐文章于 2024-04-16 09:16:51 发布

何奈之

最新推荐文章于 2024-04-16 09:16:51 发布

阅读量600

点赞数

文章标签：后端 java-ee java

本文链接：https://blog.csdn.net/qq_37154145/article/details/122478301

版权

PropertyValueDAO.java

OrderItemDAO.java

OrderDAO.java

ReviewDAO.java

模仿天猫商城的J2EE商城网站项目后端5——bean包https://blog.csdn.net/qq_37154145/article/details/122413886 模仿天猫商城的J2EE商城网站项目后端7—servlet包https://blog.csdn.net/qq_37154145/article/details/122498512UserDAO.java

package tmall.dao;

import tmall.bean.User;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
	/**
	 * 获取user总记录条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from User";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个user
	 * @param bean
	 */
	public void add(User bean) {
		String sql = "insert into user values(null ,? ,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getPassword());

			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个user
	 * @param bean
	 */
	public void update(User bean) {
		String sql = "update user set name= ? , password = ? where id = ? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getPassword());
			ps.setInt(3, bean.getId());

			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个user
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from User where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取user
	 * @param id
	 * @return bean
	 */
	public User get(int id) {
		User bean = null;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from User where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				bean = new User();
				String name = rs.getString("name");
				bean.setName(name);
				String password = rs.getString("password");
				bean.setPassword(password);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 获取所有user
	 * @return beans
	 */
	public List<User> list() {
		return list(0, Short.MAX_VALUE);
	}

	/**
	 * 分页获取user
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<User> list(int start, int count) {
		List<User> beans = new ArrayList<User>();

		String sql = "select * from User order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				User bean = new User();
				int id = rs.getInt(1);

				String name = rs.getString("name");
				bean.setName(name);
				String password = rs.getString("password");
				bean.setPassword(password);

				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 判断是否已退出登录
	 * @param name
	 * @return boolean
	 */
	public boolean isExist(String name) {
		User user = get(name);
		return user != null;
	}

	/**
	 * 据userName获取一个user
	 * @param name
	 * @return bean
	 */
	public User get(String name) {
		User bean = null;

		String sql = "select * from User where name = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				bean = new User();
				int id = rs.getInt("id");
				bean.setName(name);
				String password = rs.getString("password");
				bean.setPassword(password);
				bean.setId(id);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 据userName和password获取user
	 * @param name
	 * @param password
	 * @return bean
	 */
	public User get(String name, String password) {
		User bean = null;

		String sql = "select * from User where name = ? and password=?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, name);
			ps.setString(2, password);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				bean = new User();
				int id = rs.getInt("id");
				bean.setName(name);
				bean.setPassword(password);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

}

ProductDAO.java

package tmall.dao;

import tmall.bean.Category;
import tmall.bean.Product;
import tmall.bean.ProductImage;
import tmall.util.DBUtil;
import tmall.util.DateUtil;

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

public class ProductDAO {
	/**
	 * 获取product记录总条数
	 * @param cid
	 * @return total
	 */
	public int getTotal(int cid) {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Product where cid = " + cid;

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个product
	 * @param bean
	 */
	public void add(Product bean) {
		String sql = "insert into Product values(null,?,?,?,?,?,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getSubTitle());
			ps.setFloat(3, bean.getOriginalPrice());
			ps.setFloat(4, bean.getPromotePrice());
			ps.setInt(5, bean.getStock());
			ps.setInt(6, bean.getCategory().getId());
			ps.setTimestamp(7, DateUtil.d2t(bean.getCreateDate()));
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个product
	 * @param bean
	 */
	public void update(Product bean) {
		String sql = "update Product set name= ?, subTitle=?, originalPrice=?,promotePrice=?,stock=?, cid = ?, createDate=? where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());
			ps.setString(2, bean.getSubTitle());
			ps.setFloat(3, bean.getOriginalPrice());
			ps.setFloat(4, bean.getPromotePrice());
			ps.setInt(5, bean.getStock());
			ps.setInt(6, bean.getCategory().getId());
			ps.setTimestamp(7, DateUtil.d2t(bean.getCreateDate()));
			ps.setInt(8, bean.getId());
			ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个product
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from Product where id = " + id;

			s.execute(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一个product
	 * @param id
	 * @return bean
	 */
	public Product get(int id) {
		Product bean = new Product();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from Product where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				String name = rs.getString("name");
				String subTitle = rs.getString("subTitle");
				float originalPrice = rs.getFloat("originalPrice");
				float promotePrice = rs.getFloat("promotePrice");
				int stock = rs.getInt("stock");
				int cid = rs.getInt("cid");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				bean.setName(name);
				bean.setSubTitle(subTitle);
				bean.setOriginalPrice(originalPrice);
				bean.setPromotePrice(promotePrice);
				bean.setStock(stock);
				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				bean.setCreateDate(createDate);
				bean.setId(id);
				setFirstProductImage(bean);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 根据分类获取所有product
	 * @param cid
	 * @return beans
	 */
	public List<Product> list(int cid) {
		return list(cid, 0, Short.MAX_VALUE);
	}

	/**
	 * 根据分类分页获取product
	 * @param cid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Product> list(int cid, int start, int count) {
		List<Product> beans = new ArrayList<Product>();
		Category category = new CategoryDAO().get(cid);

		String sql = "select * from Product where cid = ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, cid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Product bean = new Product();
				int id = rs.getInt(1);
				String name = rs.getString("name");
				String subTitle = rs.getString("subTitle");
				float originalPrice = rs.getFloat("originalPrice");
				float promotePrice = rs.getFloat("promotePrice");
				int stock = rs.getInt("stock");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				bean.setName(name);
				bean.setSubTitle(subTitle);
				bean.setOriginalPrice(originalPrice);
				bean.setPromotePrice(promotePrice);
				bean.setStock(stock);
				bean.setCreateDate(createDate);
				bean.setId(id);
				bean.setCategory(category);
				setFirstProductImage(bean);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 获取所有product
	 * @return beans
	 */
	public List<Product> list() {
		return list(0, Short.MAX_VALUE);
	}

	/**
	 * 分页获取product
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Product> list(int start, int count) {
		List<Product> beans = new ArrayList<Product>();

		String sql = "select * from Product limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Product bean = new Product();
				int id = rs.getInt(1);
				int cid = rs.getInt("cid");
				String name = rs.getString("name");
				String subTitle = rs.getString("subTitle");
				float originalPrice = rs.getFloat("originalPrice");
				float promotePrice = rs.getFloat("promotePrice");
				int stock = rs.getInt("stock");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				bean.setName(name);
				bean.setSubTitle(subTitle);
				bean.setOriginalPrice(originalPrice);
				bean.setPromotePrice(promotePrice);
				bean.setStock(stock);
				bean.setCreateDate(createDate);
				bean.setId(id);

				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 将多个分类的product，各自填充到对应的category对象c中
	 * @param categories
	 */
	public void fill(List<Category> categories) {
		for (Category c : categories)
			fill(c);
	}

	/**
	 * 将同一分类的product，一次性填充到category对象c中
	 * @param c
	 */
	public void fill(Category c) {
		List<Product> ps = this.list(c.getId());
		c.setProducts(ps);
	}

	/**
	 * 将product按行填充到前端页面
	 * @param categories
	 */
	public void fillByRow(List<Category> categories) {
		int productNumberEachRow = 8;
		for (Category c : categories) {
			List<Product> products = c.getProducts();
			List<List<Product>> productsByRow = new ArrayList<>();
			for (int i = 0; i < products.size(); i += productNumberEachRow) {
				int size = i + productNumberEachRow;
				size = size > products.size() ? products.size() : size;
				List<Product> productsOfEachRow = products.subList(i, size);
				productsByRow.add(productsOfEachRow);
			}
			c.setProductsByRow(productsByRow);
		}
	}

	/**
	 * 设置封面/第一张图片
	 * @param p
	 */
	public void setFirstProductImage(Product p) {
		List<ProductImage> pis = new ProductImageDAO().list(p, ProductImageDAO.type_single);
		if (!pis.isEmpty())
			p.setFirstProductImage(pis.get(0));
	}

	/**
	 * 单个product各自设置已售和评论的数量
	 * @param p
	 */
	public void setSaleAndReviewNumber(Product p) {
		int saleCount = new OrderItemDAO().getSaleCount(p.getId());
		p.setSaleCount(saleCount);

		int reviewCount = new ReviewDAO().getCount(p.getId());
		p.setReviewCount(reviewCount);
	}

	/**
	 * 多个product分别各自设置已售和评论的数量
	 * @param products
	 */
	public void setSaleAndReviewNumber(List<Product> products) {
		for (Product p : products) {
			setSaleAndReviewNumber(p);
		}
	}

	/**
	 * 根据关键词分页查询
	 * @param keyword
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Product> search(String keyword, int start, int count) {
		List<Product> beans = new ArrayList<Product>();

		if (null == keyword || 0 == keyword.trim().length())
			return beans;

		String sql = "select * from Product where name like ? limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, "%" + keyword.trim() + "%");
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Product bean = new Product();
				int id = rs.getInt(1);
				int cid = rs.getInt("cid");
				String name = rs.getString("name");
				String subTitle = rs.getString("subTitle");
				float originalPrice = rs.getFloat("originalPrice");
				float promotePrice = rs.getFloat("promotePrice");
				int stock = rs.getInt("stock");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				bean.setName(name);
				bean.setSubTitle(subTitle);
				bean.setOriginalPrice(originalPrice);
				bean.setPromotePrice(promotePrice);
				bean.setStock(stock);
				bean.setCreateDate(createDate);
				bean.setId(id);

				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				setFirstProductImage(bean);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

CategoryDAO.java

package tmall.dao;

import tmall.bean.Category;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class CategoryDAO {
	/**
	 * 获取category记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Category";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个category
	 * @param bean
	 */
	public void add(Category bean) {
		String sql = "insert into category values(null,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());

			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个category
	 * @param bean
	 */
	public void update(Category bean) {
		String sql = "update category set name= ? where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getName());
			ps.setInt(2, bean.getId());

			ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个category
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from Category where id = " + id;

			s.execute(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一个category
	 * @param id
	 * @return bean
	 */
	public Category get(int id) {
		Category bean = null;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from Category where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				bean = new Category();
				String name = rs.getString(2);
				bean.setName(name);
				bean.setId(id);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 查询所有category
	 * @return beans
	 */
	public List<Category> list() {
		return list(0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询category
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Category> list(int start, int count) {
		List<Category> beans = new ArrayList<Category>();

		String sql = "select * from Category order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Category bean = new Category();
				int id = rs.getInt(1);
				String name = rs.getString(2);
				bean.setId(id);
				bean.setName(name);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

ProductImageDAO.java

package tmall.dao;

import tmall.bean.Product;
import tmall.bean.ProductImage;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductImageDAO {
	// 图片类型
	public static final String type_single = "type_single"; // 单张图片/展示图片
	public static final String type_detail = "type_detail"; // 详情图片

	/**
	 * 获取ProductImage记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from ProductImage";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一张图片
	 * @param bean
	 */
	public void add(ProductImage bean) {
		String sql = "insert into ProductImage values(null,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getProduct().getId());
			ps.setString(2, bean.getType());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void update(ProductImage bean) {
	}

	/**
	 * 据id删除一张图片
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from ProductImage where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一张图片
	 * @param id
	 * @return bean
	 */
	public ProductImage get(int id) {
		ProductImage bean = new ProductImage();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from ProductImage where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				int pid = rs.getInt("pid");
				String type = rs.getString("type");
				Product product = new ProductDAO().get(pid);
				bean.setProduct(product);
				bean.setType(type);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 据所属产品与图片类型，查询所有图片
	 * @param p
	 * @param type
	 * @return beans
	 */
	public List<ProductImage> list(Product p, String type) {
		return list(p, type, 0, Short.MAX_VALUE);
	}

	/**
	 * 据所属产品与图片类型，分页查询图片
	 * @param p
	 * @param type
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<ProductImage> list(Product p, String type, int start, int count) {
		List<ProductImage> beans = new ArrayList<ProductImage>();

		String sql = "select * from ProductImage where pid =? and type =? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, p.getId());
			ps.setString(2, type);

			ps.setInt(3, start);
			ps.setInt(4, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {

				ProductImage bean = new ProductImage();
				int id = rs.getInt(1);

				bean.setProduct(p);
				bean.setType(type);
				bean.setId(id);

				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

PropertyDAO.java

package tmall.dao;

import tmall.bean.Category;
import tmall.bean.Property;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PropertyDAO {
	/**
	 * 获取property记录总条数
	 * @param cid
	 * @return total
	 */
	public int getTotal(int cid) {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Property where cid =" + cid;

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个property
	 * @param bean
	 */
	public void add(Property bean) {
		String sql = "insert into Property values(null,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getCategory().getId());
			ps.setString(2, bean.getName());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个property
	 * @param bean
	 */
	public void update(Property bean) {
		String sql = "update Property set cid= ?, name=? where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getCategory().getId());
			ps.setString(2, bean.getName());
			ps.setInt(3, bean.getId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个property
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from Property where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据property的名字和category的id，获取一个property
	 * @param name
	 * @param cid
	 * @return bean
	 */
	public Property get(String name, int cid) {
		Property bean = null;

		String sql = "select * from Property where name = ? and cid = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, name);
			ps.setInt(2, cid);

			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				int id = rs.getInt("id");
				bean = new Property();
				bean.setName(name);
				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 根据id获取一个property
	 * @param id
	 * @return bean
	 */
	public Property get(int id) {
		Property bean = new Property();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from Property where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {

				String name = rs.getString("name");
				int cid = rs.getInt("cid");
				bean.setName(name);
				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 据category的id，查询所有property
	 * @param cid
	 * @return beans
	 */
	public List<Property> list(int cid) {
		return list(cid, 0, Short.MAX_VALUE);
	}

	/**
	 * 据category的id，分页查询property
	 * @param cid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Property> list(int cid, int start, int count) {
		List<Property> beans = new ArrayList<Property>();

		String sql = "select * from Property where cid = ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, cid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Property bean = new Property();
				int id = rs.getInt(1);
				String name = rs.getString("name");
				bean.setName(name);
				Category category = new CategoryDAO().get(cid);
				bean.setCategory(category);
				bean.setId(id);

				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

PropertyValueDAO.java

package tmall.dao;

import tmall.bean.Product;
import tmall.bean.Property;
import tmall.bean.PropertyValue;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PropertyValueDAO {
	/**
	 * 获取propertyValue记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from PropertyValue";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个propertyValue
	 * @param bean
	 */
	public void add(PropertyValue bean) {
		String sql = "insert into PropertyValue values(null,?,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getProduct().getId());
			ps.setInt(2, bean.getProperty().getId());
			ps.setString(3, bean.getValue());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个propertyValue
	 * @param bean
	 */
	public void update(PropertyValue bean) {
		String sql = "update PropertyValue set pid= ?, ptid=?, value=?  where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getProduct().getId());
			ps.setInt(2, bean.getProperty().getId());
			ps.setString(3, bean.getValue());
			ps.setInt(4, bean.getId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个propertyValue
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from PropertyValue where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一个propertyValue
	 * @param id
	 * @return bean
	 */
	public PropertyValue get(int id) {
		PropertyValue bean = new PropertyValue();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from PropertyValue where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				int pid = rs.getInt("pid");
				int ptid = rs.getInt("ptid");
				String value = rs.getString("value");

				Product product = new ProductDAO().get(pid);
				Property property = new PropertyDAO().get(ptid);
				bean.setProduct(product);
				bean.setProperty(property);
				bean.setValue(value);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 查询一个product某个property的值propertyValue
	 * @param ptid
	 * @param pid
	 * @return bean
	 */
	public PropertyValue get(int ptid, int pid) {
		PropertyValue bean = null;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from PropertyValue where ptid = " + ptid + " and pid = " + pid;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				bean = new PropertyValue();
				int id = rs.getInt("id");

				String value = rs.getString("value");

				Product product = new ProductDAO().get(pid);
				Property property = new PropertyDAO().get(ptid);
				bean.setProduct(product);
				bean.setProperty(property);
				bean.setValue(value);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 查询所有propertyValue
	 * @return beans
	 */
	public List<PropertyValue> list() {
		return list(0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询propertyValue
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<PropertyValue> list(int start, int count) {
		List<PropertyValue> beans = new ArrayList<PropertyValue>();

		String sql = "select * from PropertyValue order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				PropertyValue bean = new PropertyValue();
				int id = rs.getInt(1);

				int pid = rs.getInt("pid");
				int ptid = rs.getInt("ptid");
				String value = rs.getString("value");

				Product product = new ProductDAO().get(pid);
				Property property = new PropertyDAO().get(ptid);
				bean.setProduct(product);
				bean.setProperty(property);
				bean.setValue(value);
				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 初始化一个product的propertyValue
	 * @param p
	 */
	public void init(Product p) {
		List<Property> pts = new PropertyDAO().list(p.getCategory().getId());

		for (Property pt : pts) {
			PropertyValue pv = get(pt.getId(), p.getId());
			if (null == pv) {
				pv = new PropertyValue();
				pv.setProduct(p);
				pv.setProperty(pt);
				this.add(pv);
			}
		}
	}

	/**
	 * 查询一个product的所有propertyValue
	 * @param pid
	 * @return beans
	 */
	public List<PropertyValue> list(int pid) {
		List<PropertyValue> beans = new ArrayList<PropertyValue>();

		String sql = "select * from PropertyValue where pid = ? order by ptid desc";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, pid);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				PropertyValue bean = new PropertyValue();
				int id = rs.getInt(1);

				int ptid = rs.getInt("ptid");
				String value = rs.getString("value");

				Product product = new ProductDAO().get(pid);
				Property property = new PropertyDAO().get(ptid);
				bean.setProduct(product);
				bean.setProperty(property);
				bean.setValue(value);
				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

OrderItemDAO.java

package tmall.dao;

import tmall.bean.Order;
import tmall.bean.OrderItem;
import tmall.bean.Product;
import tmall.bean.User;
import tmall.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class OrderItemDAO {
	/**
	 * 获取orderItem记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from OrderItem";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个orderItem
	 * @param bean
	 */
	public void add(OrderItem bean) {
		String sql = "insert into OrderItem values(null,?,?,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getProduct().getId());

			// 订单项在创建的时候，是没有蒂订单信息的
			if (null == bean.getOrder())
				ps.setInt(2, -1);
			else
				ps.setInt(2, bean.getOrder().getId());

			ps.setInt(3, bean.getUser().getId());
			ps.setInt(4, bean.getNumber());
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个orderItem
	 * @param bean
	 */
	public void update(OrderItem bean) {
		String sql = "update OrderItem set pid= ?, oid=?, uid=?,number=?  where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, bean.getProduct().getId());
			if (null == bean.getOrder())
				ps.setInt(2, -1);
			else
				ps.setInt(2, bean.getOrder().getId());
			ps.setInt(3, bean.getUser().getId());
			ps.setInt(4, bean.getNumber());

			ps.setInt(5, bean.getId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一个orderItem
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from OrderItem where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一个orderItem
	 * @param id
	 * @return bean
	 */
	public OrderItem get(int id) {
		OrderItem bean = new OrderItem();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from OrderItem where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				int pid = rs.getInt("pid");
				int oid = rs.getInt("oid");
				int uid = rs.getInt("uid");
				int number = rs.getInt("number");
				Product product = new ProductDAO().get(pid);
				User user = new UserDAO().get(uid);
				bean.setProduct(product);
				bean.setUser(user);
				bean.setNumber(number);

				if (-1 != oid) {
					Order order = new OrderDAO().get(oid);
					bean.setOrder(order);
				}

				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 查询所有orderItem
	 * @param uid
	 * @return beans
	 */
	public List<OrderItem> listByUser(int uid) {
		return listByUser(uid, 0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询orderItem
	 * @param uid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<OrderItem> listByUser(int uid, int start, int count) {
		List<OrderItem> beans = new ArrayList<OrderItem>();
		// oid=-1说明这个OrderItem还没有对应的订单，所以显示在购物车。当oid有值得时候，就不在购物车了。
		String sql = "select * from OrderItem where uid = ? and oid=-1 order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, uid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				OrderItem bean = new OrderItem();
				int id = rs.getInt(1);

				int pid = rs.getInt("pid");
				int oid = rs.getInt("oid");
				int number = rs.getInt("number");

				Product product = new ProductDAO().get(pid);
				if (-1 != oid) {
					Order order = new OrderDAO().get(oid);
					bean.setOrder(order);
				}

				User user = new UserDAO().get(uid);
				bean.setProduct(product);

				bean.setUser(user);
				bean.setNumber(number);
				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 根据订单查询所有orderItem
	 * @param oid
	 * @return beans
	 */
	public List<OrderItem> listByOrder(int oid) {
		return listByOrder(oid, 0, Short.MAX_VALUE);
	}

	/**
	 * 根据订单分页查询orderItem
	 * @param oid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<OrderItem> listByOrder(int oid, int start, int count) {
		List<OrderItem> beans = new ArrayList<OrderItem>();

		String sql = "select * from OrderItem where oid = ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, oid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				OrderItem bean = new OrderItem();
				int id = rs.getInt(1);

				int pid = rs.getInt("pid");
				int uid = rs.getInt("uid");
				int number = rs.getInt("number");

				Product product = new ProductDAO().get(pid);
				if (-1 != oid) {
					Order order = new OrderDAO().get(oid);
					bean.setOrder(order);
				}

				User user = new UserDAO().get(uid);
				bean.setProduct(product);

				bean.setUser(user);
				bean.setNumber(number);
				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 将分属不同order的orderItem总数和orderItem，各自填充到所属的order中
	 * @param os
	 */
	public void fill(List<Order> os) {
		for (Order o : os) {
			List<OrderItem> ois = listByOrder(o.getId());
			float total = 0;
			int totalNumber = 0;
			for (OrderItem oi : ois) {
				total += oi.getNumber() * oi.getProduct().getPromotePrice();
				totalNumber += oi.getNumber();
			}
			o.setTotal(total);
			o.setOrderItems(ois);
			o.setTotalNumber(totalNumber);
		}
	}

	/**
	 * 将orderItem总数和orderItem，填充到所属的order中
	 * @param o
	 */
	public void fill(Order o) {
		List<OrderItem> ois = listByOrder(o.getId());

		float total = 0;
		for (OrderItem oi : ois) {
			total += oi.getNumber() * oi.getProduct().getPromotePrice();
		}

		o.setTotal(total);
		o.setOrderItems(ois);
	}

	/**
	 * 根据product查询所有orderItem
	 * @param pid
	 * @return beans
	 */
	public List<OrderItem> listByProduct(int pid) {
		return listByProduct(pid, 0, Short.MAX_VALUE);
	}

	/**
	 * 根据product分页查询所有orderItem
	 * @param pid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<OrderItem> listByProduct(int pid, int start, int count) {
		List<OrderItem> beans = new ArrayList<OrderItem>();

		String sql = "select * from OrderItem where pid = ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, pid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				OrderItem bean = new OrderItem();
				int id = rs.getInt(1);

				int uid = rs.getInt("uid");
				int oid = rs.getInt("oid");
				int number = rs.getInt("number");

				Product product = new ProductDAO().get(pid);
				if (-1 != oid) {
					Order order = new OrderDAO().get(oid);
					bean.setOrder(order);
				}

				User user = new UserDAO().get(uid);
				bean.setProduct(product);

				bean.setUser(user);
				bean.setNumber(number);
				bean.setId(id);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 获取某一product已售数量
	 * @param pid
	 * @return total
	 */
	public int getSaleCount(int pid) {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select sum(number) from OrderItem where pid = " + pid;

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

}

OrderDAO.java

package tmall.dao;

import tmall.bean.Order;
import tmall.bean.User;
import tmall.util.DBUtil;
import tmall.util.DateUtil;

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

public class OrderDAO {
	// 订单状态识别码
	public static final String waitPay = "waitPay"; // 待付款
	public static final String waitDelivery = "waitDelivery"; // 待发货
	public static final String waitConfirm = "waitConfirm"; // 待确认收货
	public static final String waitReview = "waitReview"; // 待评价
	public static final String finish = "finish"; // 订单已完成
	public static final String delete = "delete"; // 订单已删除

	/**
	 * 获取order记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Order_";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一个order
	 * @param bean
	 */
	public void add(Order bean) {
		String sql = "insert into order_ values(null,?,?,?,?,?,?,?,?,?,?,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getOrderCode());
			ps.setString(2, bean.getAddress());
			ps.setString(3, bean.getPost());
			ps.setString(4, bean.getReceiver());
			ps.setString(5, bean.getMobile());
			ps.setString(6, bean.getUserMessage());

			ps.setTimestamp(7, DateUtil.d2t(bean.getCreateDate()));
			ps.setTimestamp(8, DateUtil.d2t(bean.getPayDate()));
			ps.setTimestamp(9, DateUtil.d2t(bean.getDeliveryDate()));
			ps.setTimestamp(10, DateUtil.d2t(bean.getConfirmDate()));
			ps.setInt(11, bean.getUser().getId());
			ps.setString(12, bean.getStatus());

			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一个order
	 * @param bean
	 */
	public void update(Order bean) {
		String sql = "update order_ set address= ?, post=?, receiver=?,mobile=?,userMessage=? ,createDate = ? , payDate =? , deliveryDate =?, confirmDate = ? , orderCode =?, uid=?, status=? where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getAddress());
			ps.setString(2, bean.getPost());
			ps.setString(3, bean.getReceiver());
			ps.setString(4, bean.getMobile());
			ps.setString(5, bean.getUserMessage());
			ps.setTimestamp(6, DateUtil.d2t(bean.getCreateDate()));
			ps.setTimestamp(7, DateUtil.d2t(bean.getPayDate()));
			ps.setTimestamp(8, DateUtil.d2t(bean.getDeliveryDate()));
			ps.setTimestamp(9, DateUtil.d2t(bean.getConfirmDate()));
			ps.setString(10, bean.getOrderCode());
			ps.setInt(11, bean.getUser().getId());
			ps.setString(12, bean.getStatus());
			ps.setInt(13, bean.getId());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 删除一个order
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from Order_ where id = " + id;

			s.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一个order
	 * @param id
	 * @return bean
	 */
	public Order get(int id) {
		Order bean = new Order();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from Order_ where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				String orderCode = rs.getString("orderCode");
				String address = rs.getString("address");
				String post = rs.getString("post");
				String receiver = rs.getString("receiver");
				String mobile = rs.getString("mobile");
				String userMessage = rs.getString("userMessage");
				String status = rs.getString("status");
				int uid = rs.getInt("uid");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));
				Date payDate = DateUtil.t2d(rs.getTimestamp("payDate"));
				Date deliveryDate = DateUtil.t2d(rs.getTimestamp("deliveryDate"));
				Date confirmDate = DateUtil.t2d(rs.getTimestamp("confirmDate"));

				bean.setOrderCode(orderCode);
				bean.setAddress(address);
				bean.setPost(post);
				bean.setReceiver(receiver);
				bean.setMobile(mobile);
				bean.setUserMessage(userMessage);
				bean.setCreateDate(createDate);
				bean.setPayDate(payDate);
				bean.setDeliveryDate(deliveryDate);
				bean.setConfirmDate(confirmDate);
				User user = new UserDAO().get(uid);
				bean.setUser(user);
				bean.setStatus(status);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 查询所有order
	 * @return beans
	 */
	public List<Order> list() {
		return list(0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询order
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Order> list(int start, int count) {
		List<Order> beans = new ArrayList<Order>();

		String sql = "select * from Order_ order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Order bean = new Order();
				String orderCode = rs.getString("orderCode");
				String address = rs.getString("address");
				String post = rs.getString("post");
				String receiver = rs.getString("receiver");
				String mobile = rs.getString("mobile");
				String userMessage = rs.getString("userMessage");
				String status = rs.getString("status");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));
				Date payDate = DateUtil.t2d(rs.getTimestamp("payDate"));
				Date deliveryDate = DateUtil.t2d(rs.getTimestamp("deliveryDate"));
				Date confirmDate = DateUtil.t2d(rs.getTimestamp("confirmDate"));
				int uid = rs.getInt("uid");

				int id = rs.getInt("id");
				bean.setId(id);
				bean.setOrderCode(orderCode);
				bean.setAddress(address);
				bean.setPost(post);
				bean.setReceiver(receiver);
				bean.setMobile(mobile);
				bean.setUserMessage(userMessage);
				bean.setCreateDate(createDate);
				bean.setPayDate(payDate);
				bean.setDeliveryDate(deliveryDate);
				bean.setConfirmDate(confirmDate);
				User user = new UserDAO().get(uid);
				bean.setUser(user);
				bean.setStatus(status);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 查询某个user某种状态下的所有order
	 * @param uid
	 * @param excludedStatus
	 * @return beans
	 */
	public List<Order> list(int uid, String excludedStatus) {
		return list(uid, excludedStatus, 0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询某个user某种状态下的order
	 * @param uid
	 * @param excludedStatus
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Order> list(int uid, String excludedStatus, int start, int count) {
		List<Order> beans = new ArrayList<Order>();

		String sql = "select * from Order_ where uid = ? and status != ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, uid);
			ps.setString(2, excludedStatus);
			ps.setInt(3, start);
			ps.setInt(4, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Order bean = new Order();
				String orderCode = rs.getString("orderCode");
				String address = rs.getString("address");
				String post = rs.getString("post");
				String receiver = rs.getString("receiver");
				String mobile = rs.getString("mobile");
				String userMessage = rs.getString("userMessage");
				String status = rs.getString("status");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));
				Date payDate = DateUtil.t2d(rs.getTimestamp("payDate"));
				Date deliveryDate = DateUtil.t2d(rs.getTimestamp("deliveryDate"));
				Date confirmDate = DateUtil.t2d(rs.getTimestamp("confirmDate"));

				int id = rs.getInt("id");
				bean.setId(id);
				bean.setOrderCode(orderCode);
				bean.setAddress(address);
				bean.setPost(post);
				bean.setReceiver(receiver);
				bean.setMobile(mobile);
				bean.setUserMessage(userMessage);
				bean.setCreateDate(createDate);
				bean.setPayDate(payDate);
				bean.setDeliveryDate(deliveryDate);
				bean.setConfirmDate(confirmDate);
				User user = new UserDAO().get(uid);
				bean.setStatus(status);
				bean.setUser(user);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

}

ReviewDAO.java

package tmall.dao;

import tmall.bean.Product;
import tmall.bean.Review;
import tmall.bean.User;
import tmall.util.DBUtil;
import tmall.util.DateUtil;

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

public class ReviewDAO {
	/**
	 * 获取review记录总条数
	 * @return total
	 */
	public int getTotal() {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Review";

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 获取某个product的review总条数
	 * @param pid
	 * @return total
	 */
	public int getTotal(int pid) {
		int total = 0;

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select count(*) from Review where pid = " + pid;

			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return total;
	}

	/**
	 * 添加一条review
	 * @param bean
	 */
	public void add(Review bean) {
		String sql = "insert into Review values(null,?,?,?,?)";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getContent());
			ps.setInt(2, bean.getUser().getId());
			ps.setInt(3, bean.getProduct().getId());
			ps.setTimestamp(4, DateUtil.d2t(bean.getCreateDate()));

			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int id = rs.getInt(1);
				bean.setId(id);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 更新一条review
	 * @param bean
	 */
	public void update(Review bean) {
		String sql = "update Review set content= ?, uid=?, pid=? , createDate = ? where id = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, bean.getContent());
			ps.setInt(2, bean.getUser().getId());
			ps.setInt(3, bean.getProduct().getId());
			ps.setTimestamp(4, DateUtil.d2t(bean.getCreateDate()));
			ps.setInt(5, bean.getId());
			ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id删除一条review
	 * @param id
	 */
	public void delete(int id) {
		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "delete from Review where id = " + id;

			s.execute(sql);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 据id获取一条rereviw
	 * @param id
	 * @return bean
	 */
	public Review get(int id) {
		Review bean = new Review();

		try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement()) {
			String sql = "select * from Review where id = " + id;

			ResultSet rs = s.executeQuery(sql);

			if (rs.next()) {
				int pid = rs.getInt("pid");
				int uid = rs.getInt("uid");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				String content = rs.getString("content");

				Product product = new ProductDAO().get(pid);
				User user = new UserDAO().get(uid);

				bean.setContent(content);
				bean.setCreateDate(createDate);
				bean.setProduct(product);
				bean.setUser(user);
				bean.setId(id);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return bean;
	}

	/**
	 * 获取某product的review总数
	 * @param pid
	 * @return 总数/0
	 */
	public int getCount(int pid) {
		String sql = "select count(*) from Review where pid = ? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, pid);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;
	}

	/**
	 * 据product查询所有review
	 * @param pid
	 * @return
	 */
	public List<Review> list(int pid) {
		return list(pid, 0, Short.MAX_VALUE);
	}

	/**
	 * 分页查询某product的review
	 * @param pid
	 * @param start
	 * @param count
	 * @return beans
	 */
	public List<Review> list(int pid, int start, int count) {
		List<Review> beans = new ArrayList<Review>();

		String sql = "select * from Review where pid = ? order by id desc limit ?,? ";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setInt(1, pid);
			ps.setInt(2, start);
			ps.setInt(3, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Review bean = new Review();
				int id = rs.getInt(1);

				int uid = rs.getInt("uid");
				Date createDate = DateUtil.t2d(rs.getTimestamp("createDate"));

				String content = rs.getString("content");

				Product product = new ProductDAO().get(pid);
				User user = new UserDAO().get(uid);

				bean.setContent(content);
				bean.setCreateDate(createDate);
				bean.setId(id);
				bean.setProduct(product);
				bean.setUser(user);
				beans.add(bean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return beans;
	}

	/**
	 * 判断某product是否有某review
	 * @param content
	 * @param pid
	 * @return boolean
	 */
	public boolean isExist(String content, int pid) {
		String sql = "select * from Review where content = ? and pid = ?";

		try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
			ps.setString(1, content);
			ps.setInt(2, pid);

			ResultSet rs = ps.executeQuery();

			if (rs.next())
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

}

何奈之

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
模仿天猫商城的J2EE商城网站项目后端6—dao包

UserDAO.java相似的方法作用下面的类不再注释。package tmall.dao;import tmall.bean.User;import tmall.util.DBUtil;import java.sql.*;import java.util.ArrayList;import java.util.List;public class UserDAO { // 获取总记录条数 public int getTotal() { int total = 0; tr
复制链接

扫一扫