目录
模仿天猫商城的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;
}
}