|
|
|
步骤 2 : 基本的CRUD操作 步骤 3 : 非CRUD方法 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;
}
}
基本的CRUD操作与CategoryDAO的CRUD一样，在此不做赘述
获取某一种产品的销量。 产品销量就是这种产品对应的订单项OrderItem的number字段的总和
public int getSaleCount(int pid) 查询某种订单下所有的订单项 public List<OrderItem> listByOrder(int oid) 查询某个用户的未生成订单的订单项(既购物车中的订单项) public List<OrderItem> listByUser(int uid) 为订单设置订单项集合 public void fill(Order o) public void fill(List<Order> os) 注：部分非CRUD的业务方法，需要结合业务场景才能更好地理解，现在理解不透彻也很正常，在后续学习到相关场景的时候，再回过头来看，就明白了。
HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。
提问之前请登陆
提问已经提交成功，正在审核。 请于 我的提问 处查看提问记录，谢谢
|
