|
|
|
步骤 2 : 基本的CRUD操作 步骤 3 : 非CRUD方法
增值内容，请先登录
完整的J2EE模仿天猫项目，使用J2SE、前端技术(包含所有前端jsp文件)、J2EE一整套技术栈， 从无到有涵盖全部147个知识点，475个开发步骤, 充实J2EE项目经验，为简历加上一个有吸引力的砝码.
增值内容，点击购买
使用爬虫已经被系统记录，请勿使用爬虫，增大封号风险。 如果是误封 ，请联系站长，谢谢
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;
}
}
增值内容，请先登录
完整的J2EE模仿天猫项目，使用J2SE、前端技术(包含所有前端jsp文件)、J2EE一整套技术栈， 从无到有涵盖全部147个知识点，475个开发步骤, 充实J2EE项目经验，为简历加上一个有吸引力的砝码.
增值内容，点击购买
使用爬虫已经被系统记录，请勿使用爬虫，增大封号风险。 如果是误封 ，请联系站长，谢谢
增值内容，请先登录
完整的J2EE模仿天猫项目，使用J2SE、前端技术(包含所有前端jsp文件)、J2EE一整套技术栈， 从无到有涵盖全部147个知识点，475个开发步骤, 充实J2EE项目经验，为简历加上一个有吸引力的砝码.
增值内容，点击购买
使用爬虫已经被系统记录，请勿使用爬虫，增大封号风险。 如果是误封 ，请联系站长，谢谢
HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。
问答区域
2021-05-10
listByUser 里这个地方的代码好像是没执行到的
回答已经提交成功，正在审核。 请于 我的回答 处查看回答记录，谢谢
2021-04-30
fill方法没有返回值，设定完的数据要如何取出来？
回答已经提交成功，正在审核。 请于 我的回答 处查看回答记录，谢谢
2021-04-10
fill(List<Order> os)可以直接调用fill(Order o)吧,下面的filll(Order o)是不是少了个setTotalNumber。。。。
2019-11-26
1.这里是不是无法执行到，默认 bean.setOrder(order)=null; 2.评论中描述getSaleCount计算问题
2019-10-24
fill(Order o)这个方法中为啥没有设置订单总数
提问太多，页面渲染太慢，为了加快渲染速度，本页最多只显示几条提问。还有 16 条以前的提问，请 点击查看
提问之前请登陆
提问已经提交成功，正在审核。 请于 我的提问 处查看提问记录，谢谢
|
