毕业设计javajsp网购旅游商品sqlserver-qlkrp源码工具包

package com.zuxia.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import com.zuxia.model.CustomerDetailInfo; import com.zuxia.model.CustomerInfo; import com.zuxia.model.GoodsInfo; import com.zuxia.model.GoodsType; import com.zuxia.model.OrderGoodsInfo; import com.zuxia.model.OrderInfo; import com.zuxia.util.DBUtil; import com.zuxia.util.PageModel; public class OrderDAO { //查询所有订单信息 public PageModel<OrderInfo> queryAll(int page){ PageModel<OrderInfo> pm=new PageModel<OrderInfo>(); pm.setCurrentPage(page); pm.setSize(5); pm.setSumCount(getCount()); Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; List<OrderInfo> list=new ArrayList<OrderInfo>(); String sql = "select top "+pm.getSize()+" o.orderId,o.status,o.orderTime,c.email,cd.name,cd.telphone,cd.movePhone,c.id from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id where o.orderId not in(select top (("+pm.getCurrentPage()+"-1)*"+pm.getSize()+") orderId from OrderInfo)"; try { pstmt = conn.prepareStatement(sql); // 执行查询 rs=pstmt.executeQuery(); while(rs.next()){ //封装订单数据 OrderInfo oi=new OrderInfo(); oi.setOrderId(rs.getInt("orderId")); oi.setStatus(rs.getInt("status")); oi.setOrderTime(rs.getDate("orderTime")); //封装客户详情数据 CustomerDetailInfo cdi=new CustomerDetailInfo(); cdi.setName(rs.getString("name")); cdi.setTelphone(rs.getString("telphone")); cdi.setMovePhone(rs.getString("movePhone")); //封装客户数据 CustomerInfo ci=new CustomerInfo(); ci.setId(rs.getInt("id")); ci.setEmail(rs.getString("email")); ci.setCustomerDetailInfo(cdi); oi.setCustomerInfo(ci); //订单添加到集合中 list.add(oi); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } pm.setList(list); return pm; } //得到所有条数 private int getCount(){ Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; String sql = "select count(1) from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id"; try { pstmt = conn.prepareStatement(sql); // 执行查询 rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } return 0; } //模糊查询 public PageModel<OrderInfo> querySome(int page,String key){ PageModel<OrderInfo> pm=new PageModel<OrderInfo>(); pm.setCurrentPage(page); pm.setSize(5); pm.setSumCount(getCount(key)); Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; List<OrderInfo> list=new ArrayList<OrderInfo>(); String sql = "select top "+pm.getSize()+" o.orderId,o.status,o.orderTime,c.email,cd.name,cd.telphone,cd.movePhone from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id where (o.orderId like ? or c.email like ?) and o.orderId not in(select top (("+pm.getCurrentPage()+"-1)*"+pm.getSize()+") orderId from OrderInfo)"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%"+key+"%"); pstmt.setString(2, "%"+key+"%"); // 执行查询 rs=pstmt.executeQuery(); while(rs.next()){ //封装订单数据 OrderInfo oi=new OrderInfo(); oi.setOrderId(rs.getInt("orderId")); oi.setStatus(rs.getInt("status")); oi.setOrderTime(rs.getDate("orderTime")); //封装客户详情数据 CustomerDetailInfo cdi=new CustomerDetailInfo(); cdi.setName(rs.getString("name")); cdi.setTelphone(rs.getString("telphone")); cdi.setMovePhone(rs.getString("movePhone")); //封装客户数据 CustomerInfo ci=new CustomerInfo(); ci.setEmail(rs.getString("email")); ci.setCustomerDetailInfo(cdi); oi.setCustomerInfo(ci); //订单添加到集合中 list.add(oi); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } pm.setList(list); return pm; } //得到所有条数 private int getCount(String key){ Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; String sql = "select count(1) from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id where o.orderId like ? or c.email like ?"; try { pstmt = conn.prepareStatement(sql); pstmt.setString(1, "%"+key+"%"); pstmt.setString(2, "%"+key+"%"); // 执行查询 rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } return 0; } //状态分页查询 public PageModel<OrderInfo> queryByStatus(int page,int status){ PageModel<OrderInfo> pm=new PageModel<OrderInfo>(); pm.setCurrentPage(page); pm.setSize(5); pm.setSumCount(getCount(status)); Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; List<OrderInfo> list=new ArrayList<OrderInfo>(); String sql = "select top "+pm.getSize()+" o.orderId,o.status,o.orderTime,c.email,cd.name,cd.telphone,cd.movePhone from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id where o.status=? and o.orderId not in(select top (("+pm.getCurrentPage()+"-1)*"+pm.getSize()+") orderId from OrderInfo)"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, status); // 执行查询 rs=pstmt.executeQuery(); while(rs.next()){ //封装订单数据 OrderInfo oi=new OrderInfo(); oi.setOrderId(rs.getInt("orderId")); oi.setStatus(rs.getInt("status")); oi.setOrderTime(rs.getDate("orderTime")); //封装客户详情数据 CustomerDetailInfo cdi=new CustomerDetailInfo(); cdi.setName(rs.getString("name")); cdi.setTelphone(rs.getString("telphone")); cdi.setMovePhone(rs.getString("movePhone")); //封装客户数据 CustomerInfo ci=new CustomerInfo(); ci.setEmail(rs.getString("email")); ci.setCustomerDetailInfo(cdi); oi.setCustomerInfo(ci); //订单添加到集合中 list.add(oi); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } pm.setList(list); return pm; } //得到所有条数 private int getCount(int status){ Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; ResultSet rs=null; String sql = "select count(1) from OrderInfo o inner join CustomerInfo c on o.customerId=c.id left join CustomerDetailInfo cd on cd.customerId=c.id where o.status=?"; try { pstmt = conn.prepareStatement(sql); pstmt.setInt(1, status); // 执行查询 rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.getClose(conn, pstmt, rs); } return 0; } //删除单个 public boolean delete(int id){ Connection conn = DBUtil.getConn();; PreparedStatement pstmt = null; String sql = "delete from OrderGoodsInfo where orderId=? "; String sql1 = "delete from OrderInfo where orderId=? "; try { pstmt = conn.prepareStatement(sql+sql1); pstmt.setInt(1, id); pstmt.setInt(2, id); // 执行查询 int count=pstmt.executeUpdate(); if(count>0){ return true; } } catch (Exception e) { e.printStackTrace(); } finally { DBUtil.g