DAO类设计
DAO是Data Access Object的缩写，这些类专门用于进行数据库访问的操作。
DBUtil：数据库工具类，这个类的作用是初始化驱动，并且提供一个getConnection用于获取连接。在后续的所有DAO中，当需要获取连接的时候，都采用这种方式进行。数据库连接的参数，如数据库名称、账号密码，编码方式等都设计在属性上，便于统一修改，降低维护成本。
package tmall.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBUtil {
static String ip = "127.0.0.1";
static int port = 3306;
static String database = "tmall";
static String encoding = "UTF-8";
static String loginName = "root";
static String password = "admin";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
String url = String.format("jdbc:mysql://%s:%d/%s?characterEncoding=%s", ip, port, database, encoding);
return DriverManager.getConnection(url, loginName, password);
}
public static void main(String[] args) throws SQLException {
System.out.println(getConnection());
}
}
DateUtil
这个日期工具类主要用于java.util.Date类与java.sql.Timestamp类的互相转换。
因为在实体类中日期类型的属性，使用的都是java.util.Date类。而为了在MySQL中的日期格式里保存时间信息，必须使用datetime类型的字段，而jdbc要获取datetime类型字段的信息，需要采用java.sql.Timestamp来获取，否则只会保留日期信息，而丢失时间信息。
package tmall.util;
public class DateUtil {
public static java.sql.Timestamp d2t(java.util.Date d) {
if (null == d)
return null;
return new java.sql.Timestamp(d.getTime());
}
public static java.util.Date t2d(java.sql.Timestamp t) {
if (null == t)
return null;
return new java.util.Date(t.getTime());
}
}
1. CategoryDao类设计
CategoryDAO用于建立对于Category对象的ORM映射。这个类比起后面的DAO比较单纯，基本上就是提供数据库相关的CRUD操作。
注：executeQuery 方法 导致。executeQuery只能用于查询，execute方法才可以执行insert，update，delete操作。
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.Category;
import tmall.util.DBUtil;
public class CategoryDao {
//获取总数
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;
}
//增加
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();
}
}
//修改
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();
}
}
//删除
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获取
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;
}
//查询所有
public List<Category> list(){
return list(0,Short.MAX_VALUE);
}
//分页查询
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;
}
}
2. UserDao类
UserDAO用于建立对User对象的ORM映射
基本的CRUD操作与CategoryDAO的CRUD一样，除此之外，UserDAO还提供了一些用于支持业务的方法。
在业务上，注册的时候，需要判断某个用户是否已经存在，账号密码是否正确等操作，UserDAO特别提供如下方法进行支持：
- 根据用户名获取对象：public User get(String name)
- 以boolean形式返回某个用户名是否已经存在：public boolean isExist(String name)
- 根据账号和密码获取对象，这才是合理的判断账号密码是否正确的方式，而不是一下把所有的用户信息查出来，在内存中进行比较：public User get(String name,String password)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.User;
import tmall.util.DBUtil;
public class UserDAO {
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
public List<User> list() {
return list(0, Short.MAX_VALUE);
}
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;
}
public boolean isExist(String name) {
User user = get(name);
return user!=null;
}
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;
}
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;
}
}
注册验证账号是否重复
3. PropertyDAO类
PropertyDAO用于建立对于Property对象的ORM映射
基本的CRUD操作与CategoryDAO的CRUD一样。
除CRUD方法之外，还提供一些其他用于支持业务的方法。
- 获取某种分类下的属性总数，在分页显示的时候会用到：public int getTotal(int cid)
- 查询某个分类下的属性对象:public List<Property> list(int cid,int start,int count) public List<Property> list(int cid)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import tmall.bean.Category;
import tmall.bean.Product;
import tmall.bean.Property;
import tmall.util.DBUtil;
import tmall.util.DateUtil;
public class PropertyDAO {
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
public List<Property> list(int cid) {
return list(cid, 0, Short.MAX_VALUE);
}
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;
}
}
4. ProductImageDAO类
用于建立对于ProductImage对象的ORM映射
基本CRUD操作与CategoryDAO的CRUD一样
两种静态属性分别表示单个图片和详情图片
- public static final String type_single="type_single";
- public static final String type_detail="type_detail";
非CRUD方法，查询指定产品下，某种类型的ProductImage：
- public List<ProductImage> list(Product p,String type)
- public List<ProductImage> list(Product p,String type,int start,int count)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.Product;
import tmall.bean.ProductImage;
import tmall.util.DBUtil;
public class ProductImageDAO {
public static final String type_single = "type_single";
public static final String type_detail = "type_detail";
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;
}
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) {
}
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();
}
}
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;
}
public List<ProductImage> list(Product p, String type) {
return list(p, type,0, Short.MAX_VALUE);
}
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;
}
}
5. PropertyValueDAO类
用于建立对于PropertyValue对象的ORM映射
除CRUD之外，PropertyValueDAO还提供了一些用于支持其他业务的方法。
根据属性id和产品id，获取一个PropertyValue对象
- public PropertyValue get(int ptid,int pid)
初始化某个产品对应的属性值，初始化逻辑：
- 根据分类获取所有的属性
- 遍历每一个属性
- 根据属性和产品，获取属性值
- 如果属性值不存在，就创建一个属性值对象
- public void init(Product p)
查询某个产品下所有的属性值
- public List<PropertyValue> list(int pid)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.Product;
import tmall.bean.Property;
import tmall.bean.PropertyValue;
import tmall.util.DBUtil;
public class PropertyValueDAO {
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
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;
}
public List<PropertyValue> list() {
return list(0, Short.MAX_VALUE);
}
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;
}
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);
}
}
}
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;
}
}
6. ReviewDAO类
用于建立对于Review对象的ORM映射
非CRUD方法：
获取指定产品一共有多少条评价
- public int getCount(int pid)
获取指定产品的评价
- public List<Review> list(int pid)
- public List<Review> list(int pid,int start,int count)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import tmall.bean.Product;
import tmall.bean.Review;
import tmall.bean.User;
import tmall.util.DBUtil;
import tmall.util.DateUtil;
public class ReviewDAO {
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;
}
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
public List<Review> list(int pid) {
return list(pid, 0, Short.MAX_VALUE);
}
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;
}
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;
}
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;
}
}
7. OrderDAO类
用于建立对于Order对象的ORM映射
订单类型属性
这些public static final 修饰的常量字符串，用于表示订单类型，在实体类Order的getStatusDesc方法中就用到这些属性
- 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";
非CRUD方法：
查询指定用户的订单（去掉某种订单状态，通常是"delete"）
- public List<Order> list(int uid,String excludedStatus)
- public List<Order> list(int uid,String excludedStatus,int start, int count)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import tmall.bean.Order;
import tmall.bean.User;
import tmall.util.DBUtil;
import tmall.util.DateUtil;
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";
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
public List<Order> list() {
return list(0, Short.MAX_VALUE);
}
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;
}
public List<Order> list(int uid,String excludedStatus) {
return list(uid,excludedStatus,0, Short.MAX_VALUE);
}
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;
}
}
8. OrderItemDAO类
用于建立对于OrderItem对象的ORM映射
非CRUD方法：
获取某一种产品的销量。产品销量就是这种产品对应的订单项OrderItem的number字段的总和
- public int getSaleCount(int pid)
查询某种订单下所有的订单项
- public List<OrderItem> listByOrder(int oid)
查询某个用户的为完成订单的订单项（即购物车中的订单项）
- public List<OrderItem> listByOrder(int uid)
为订单设置订单项集合
- public void fill(Order o)
- public void fill(List<Order> o)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import tmall.bean.Category;
import tmall.bean.Order;
import tmall.bean.OrderItem;
import tmall.bean.Product;
import tmall.bean.User;
import tmall.util.DBUtil;
public class OrderItemDAO {
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;
}
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();
}
}
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();
}
}
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();
}
}
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;
}
public List<OrderItem> listByUser(int uid) {
return listByUser(uid, 0, Short.MAX_VALUE);
}
public List<OrderItem> listByUser(int uid, int start, int count) {
List<OrderItem> beans = new ArrayList<OrderItem>();
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;
}
public List<OrderItem> listByOrder(int oid) {
return listByOrder(oid, 0, Short.MAX_VALUE);
}
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;
}
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);
}
}
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);
}
public List<OrderItem> listByProduct(int pid) {
return listByProduct(pid, 0, Short.MAX_VALUE);
}
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;
}
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;
}
}
9. ProductDAO类
用于建立对于Product对象的ORM映射
非CRUD方法：
查询分类下的产品
- public List<Product> list(int cid)
- public List<Product> list(int cid,int start,int count)
获取某种分类下的产品数量
- public int getTotal(int cid)
为分类填充产品集合
- public void fill(Category c)
- public void fill(List<Category> cs)
为多个分类设置productsByRow属性
- public void fillByRow(List<Category> cs)
根据关键字查询产品
- public List<Product> search(String keyword,int start,int count)
一个产品有多个图片，但是只有一个主图片，把第一个图片设置为主图片
- public void setFirstProductImage(Product p)
为产品设置销售和评价数量
- public void setSaleAndReviewNumber(List<Product> products)
- public void setSaleAndReviewNumber(Product p)
package tmall.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import tmall.bean.Category;
import tmall.bean.Product;
import tmall.bean.ProductImage;
import tmall.util.DBUtil;
import tmall.util.DateUtil;
public class ProductDAO {
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;
}
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.getOrignalPrice());
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();
}
}
public void update(Product bean) {
String sql = "update Product set name= ?, subTitle=?, orignalPrice=?,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.getOrignalPrice());
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();
}
}
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();
}
}
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 orignalPrice = rs.getFloat("orignalPrice");
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.setOrignalPrice(orignalPrice);
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;
}
public List<Product> list(int cid) {
return list(cid,0, Short.MAX_VALUE);
}
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 orignalPrice = rs.getFloat("orignalPrice");
float promotePrice = rs.getFloat("promotePrice");
int stock = rs.getInt("stock");
Date createDate = DateUtil.t2d( rs.getTimestamp("createDate"));
bean.setName(name);
bean.setSubTitle(subTitle);
bean.setOrignalPrice(orignalPrice);
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;
}
public List<Product> list() {
return list(0,Short.MAX_VALUE);
}
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 orignalPrice = rs.getFloat("orignalPrice");
float promotePrice = rs.getFloat("promotePrice");
int stock = rs.getInt("stock");
Date createDate = DateUtil.t2d( rs.getTimestamp("createDate"));
bean.setName(name);
bean.setSubTitle(subTitle);
bean.setOrignalPrice(orignalPrice);
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;
}
public void fill(List<Category> cs) {
for (Category c : cs)
fill(c);
}
public void fill(Category c) {
List<Product> ps = this.list(c.getId());
c.setProducts(ps);
}
public void fillByRow(List<Category> cs) {
int productNumberEachRow = 8;
for (Category c : cs) {
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);
}
}
public void setFirstProductImage(Product p) {
List<ProductImage> pis= new ProductImageDAO().list(p, ProductImageDAO.type_single);
if(!pis.isEmpty())
p.setFirstProductImage(pis.get(0));
}
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);
}
public void setSaleAndReviewNumber(List<Product> products) {
for (Product p : products) {
setSaleAndReviewNumber(p);
}
}
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 orignalPrice = rs.getFloat("orignalPrice");
float promotePrice = rs.getFloat("promotePrice");
int stock = rs.getInt("stock");
Date createDate = DateUtil.t2d( rs.getTimestamp("createDate"));
bean.setName(name);
bean.setSubTitle(subTitle);
bean.setOrignalPrice(orignalPrice);
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;
}
}
538
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
