前面说了最基本的bean包，现在是dao包
Data Access Object 数据访问对象
这里存放的全部是数据访问对象的方法，根据MVC的思想，可以知道一个程序设计完成需要
用户输入 ← 得到反馈
↓ ↑
人机交互 人机交互
↓ ↑
Controller View
↘ ↗
Model
↓ ↑
DataBase
用户点击button，button触发事件，servlet根据事件选择对应的方法，通过调用根据bean设计的dao类对数据进行操作，获得返回的数据后，根据返回值调用新的JSP页面。
以上即为 JSP+servlet+javabean
在Tmall项目，思想是这个思想，在具体表现上会有出入，后面会重新说明。
由此可以看出DAO类的重要，没有它就无法对数据（库）进行操作，（其实每个步骤都重要）
说到DAO的话，可以一个个的说
先说整体工具类：DBUtil
用来连接数据库，便于统一维护，这里有点要注意的，因为很多操作放在try-catch块中了，所以可以不用写抛出异常的代码。
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 {
//1 初始化驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
//2 建立和数据库的Connection连接
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类的日期类有区别，sql包和util包不一样，需要转换一下。
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());
}
}
和bean相关的dao
CategoryDao，作为Category类的ORM，可以根据用户需求或者系统需求，在数据库中查找相关的数据。
这里要熟悉ORM基本CRUD操作，execute，res等，这里暂时都是一些比较简单的查询，后期有的还会涉及到左子树查询等。
且try-with-resource 可以自动关闭连接。
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 {
//观察getTotal方法，首先int一个total，然后在try块中进行了数据库的连接操作，然后用statements，rs，对数据库进行访问，并且得到结果。
public int getTotal() {
int total = 0;
// 3 创建statement
try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
// 4 执行sql语句
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) {
//比较安全的 prepareStatement
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();
}
}
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;
}
}
UserDAO
也是一样的，建立和User的ORM，不过考虑到创建用户的时候，会有验证账号是否重复，所以还会添加get（name）方法,isExist方法，登录的时候会通过账号密码进行查询，而不是查询所有的用户信息get（name，pwd）方法
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;
}
}
PropertyDAO
同上，建立property对象ORM
比如电脑，都有CPU，这个CPU就是property，property表中就有cid，因为都要建立在电脑这个分类上面
还有list方法，查询这个分类下的所有属性
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) {
//计算这个分类（电脑）到底有多少个分类（CPU,内存等）分页属性会用到
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) {
//给某个分类加属性，如电脑中除了CPU还有系统
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;
}
}
ProductImageDAO
一样的，产品图片ORM，
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;
}
}
PropertyValueDAO
PV的ORM
有自己的id，有product的id，property的id，还有value
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 ) {
//通过产品id和属性id找到值，比如R720的CPU是多少？
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);
//DAO的add方法，insert新的pv
this.add(pv);
}
}
}
public List<PropertyValue> list(int pid) {
//查询某个产品下面所有属性，R720的所有属性
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;
}
}ReviewDAO
评论的DAO
一样的，因为系统后期会有各种统计评论的信息，比如指定商品有多少个评论，都是什么，客户有什么评论等等，所以dao方法还是很多的
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;
}
}
OrderDAO
Order的ORM
一个订单包含很多信息，收件人，购买人，电话，是否支付，删除订单，等等信息。
加入购物车，会产生订单项OrderItem，但是不会产生订单Order 只有在结算页面，点击“提交订单” 按钮，才会产生订单Order
一个Order里可以存在多个OrderItem，比如我一个order买了两个杯子，一个足球。会有两个订单项，A订单和B订单，其中B中的数量是2，然后就有Order的总数量是3,（产品数量）总价是xx。
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;
}
}
OrderItemDAO
一样是OrderItem的ORM
OrderItem 有 id productid orderid userid 以及num
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) {
//生成订单信息，listByOrder方法查询该订单下的所有orderItem
List<OrderItem> ois=listByOrder(o.getId());
float total = 0;
for (OrderItem oi : ois) {
total+=oi.getNumber()*oi.getProduct().getPromotePrice();
}
//total是总金额
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) {
//计算某种商品销量综合，通过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;
}
}
ProductDAO
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) {
//category目录底下一共有多少product
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) {
//通过分类展示product
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) {
//通过cid，对商品进行填充，目录类底下就包含所有产品，平时一对多，多对一就是这个时候填充的
List<Product> ps = this.list(c.getId());
c.setProducts(ps);
}
public void fillByRow(List<Category> cs) {
//有两个分类 电脑 电视，先电脑，找出电脑，假设有14个电脑，
//先找出14个电脑，然后建一个productsByRow
//i=0，i<14,i+=8
//第一次i=0，所以size=8
//第一次三目后size=8
//分隔前8个电脑，加在productsOfEachRow 里，然后集合加进productsByRow
//第二次i=8，size=8+8=16
//三目后 size=14
//分隔第8个电脑到13个电脑，从0计数，然后添加进去
//最后得出的productsByRow，被加进category中
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;
}
}所有DAO到此结束，小结一下，所有的方法是根据业务需求、实际运用从而做出来的，并不是凭空产生的，假设一些方法现在想不到也没有关系，后期等需要用到查询的时候，自然会再来添加DAO方法。
比如做了CRUD后，再来逐渐完善DAO，就目前而言， DAO 都不用自己做了，学习了SSH和SSM后，这些都有工具自动完成了，开发人员负责调用就行了。
607
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
