private List<Bill> findBillsByPageAndMohuImpl(int currentPage, int pageSize, String[] keyWords)
throws Exception {
List<Bill> bills = new ArrayList<Bill>();
con = BaseDao.getConnection();
String sql = "select * from t_bill where productname like ? AND suppliername like ? limit ?,?;";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + keyWords[0] + "%");
pstmt.setString(2, "%" + keyWords[1] + "%");
pstmt.setInt(3, (currentPage - 1) * pageSize);
pstmt.setInt(4, pageSize);
rs = pstmt.executeQuery();
while (rs.next()) {
Bill bill = new Bill();
bill.setBill_id(rs.getInt("bill_id"));
bill.setproductName(rs.getString("productname"));
bill.setAmount(rs.getInt("amount"));
bill.setPrice(rs.getFloat("price"));
bill.setPay(rs.getFloat("pay"));
bill.setsupplierName(rs.getString("suppliername"));
bill.setBilltime(rs.getString("billtime"));
bill.setsaleworker(rs.getString("saleworker"));
bills.add(bill);
}
BaseDao.closeAll(rs, pstmt, con);
return bills;
}
// 只限制他可以查询订单商品名称，供应商姓名，时间和saleworker是谁的模糊。
@Override
public List<Bill> findBillsByMohu(String... keyWords) throws Exception {
List<Bill> bills = new ArrayList<Bill>();
con = BaseDao.getConnection();
String sql =
"select * from t_bill where productname like ? AND suppliername like? AND billtime like ? AND saleworker like ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, "%" + keyWords[0] + "%");
pstmt.setString(2, "%" + keyWords[1] + "%");
pstmt.setString(3, "%" + keyWords[2] + "%");
pstmt.setString(4, "%" + keyWords[3] + "%");
rs = pstmt.executeQuery();
while (rs.next()) {
Bill bill = new Bill();
bill.setBill_id(Integer.parseInt(rs.getString("bill_id")));
bill.setproductName(rs.getString("productname"));
bill.setAmount(rs.getInt("amount"));
bill.setPrice(rs.getFloat("price"));
bill.setPay(rs.getFloat("pay"));
bill.setsupplierName(rs.getString("suppliername"));
bill.setBilltime(rs.getString("billtime"));
bill.setsaleworker(rs.getString("saleworker"));
bills.add(bill);
}
BaseDao.closeAll(rs, pstmt, con);
return bills;
}
@Override
public Bill findBillById(int id) throws Exception {
Bill bill = new Bill();
con = BaseDao.getConnection();
String sql =
"select bill_id,productname,amount,price,pay,suppliername,billtime,saleworker from t_bill where bill_id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
while (rs.next()) {
bill.setproductName(rs.getString("productname"));
bill.setAmount(rs.getInt("amount"));
bill.setPrice(rs.getFloat("price"));
bill.setPay(rs.getFloat("pay"));
bill.setsupplierName(rs.getString("suppliername"));
bill.setBilltime(rs.getString("billtime"));
bill.setsaleworker(rs.getString("saleworker"));
bill.setBill_id(Integer.parseInt(rs.getString("bill_id")));
}
BaseDao.closeAll(rs, pstmt, con);
return bill;
}
@Override
public List<Bill> findAllBills() throws Exception {
List<Bill> bills = new ArrayList<Bill>();
con = BaseDao.getConnection();
String sql =
"select bill_id,productname,amount,price,pay,suppliername,billtime,saleworker from t_bill";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Bill bill = new Bill();
bill.setBill_id(rs.getInt("bill_id"));
bill.setproductName(rs.getString("productname"));
bill.setAmount(rs.getInt("amount"));
bill.setPrice(rs.getFloat("price"));
bill.setPay(rs.getFloat("pay"));
bill.setsupplierName(rs.getString("suppliername"));
bill.setsaleworker(rs.getString("saleworker"));
bill.setBilltime(rs.getString("billtime"));
bills.add(bill);
}
BaseDao.closeAll(rs, pstmt, con);
return bills;
}