public Page findPageByUserName(String username, int pageNo, int pageSize, int totalCount) {
Page page = new Page();
String sql;
if (username != null && username != "") {
sql =
"select * from user where username like '%"
+ username
+ "%' limit "
+ page.getOffset(pageNo)
+ ","
+ pageSize;
} else {
sql = "select * from user limit " + page.getOffset(pageNo) + "," + pageSize;
}
DBConnection db = new DBConnection();
conn = db.getConn();
List<User> userList = new ArrayList<User>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
User user = new User();
user.setUserid(rs.getInt("userid"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setRealname(rs.getString("realname"));
user.setEmail(rs.getString("email"));
user.setStatus(rs.getInt("status"));
user.setRoleid(rs.getInt("roleid"));
userList.add(user);
}
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
page.setPageItem(userList);
return page;
}
public Page findDrugPage(
String drugName, int drugType, int pageNo, int pageSize, int totalCount) {
Page page = new Page();
String sql;
if (drugName != null) {
if (drugType == 0) {
sql =
"select * from drugvariety where drugName like '%"
+ drugName
+ "%' limit "
+ page.getOffset(pageNo)
+ ","
+ pageSize;
} else if (drugType == 4) {
sql =
"select * from drugvariety where drugName like '%"
+ drugName
+ "%' and (drugType=1 or drugType=2) limit "
+ page.getOffset(pageNo)
+ ","
+ pageSize;
} else {
sql =
"select * from drugvariety where drugName like '%"
+ drugName
+ "%' and drugType="
+ drugType
+ " limit "
+ page.getOffset(pageNo)
+ ","
+ pageSize;
}
} else {
sql = "select * from drugvariety limit " + page.getOffset(pageNo) + "," + pageSize;
}
DBConnection db = new DBConnection();
conn = db.getConn();
List<Drug> drugList = new ArrayList<Drug>();
try {
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Drug drug = new Drug();
drug.setDID(rs.getString("DID"));
drug.setDrugURL(rs.getString("drugURL"));
drug.setPurchasing_price(rs.getDouble("purchasing_price"));
drug.setSelling_price(rs.getDouble("selling_price"));
drug.setDrugName(rs.getString("drugName"));
drug.setDrugType(rs.getInt("drugType"));
drug.setDescription(rs.getString("description"));
drug.setProduction_date(rs.getString("production_date"));
drug.setExpiration_date(rs.getString("expiration_date"));
drug.setShelf_life(rs.getInt("shelf_life"));
drug.setDetail(rs.getString("detail"));
drug.setManufacturer(rs.getString("manufacturer"));
drug.setDirections(rs.getString("directions"));
drug.setCountpurchases(rs.getInt("countpurchases"));
drug.setInventory(rs.getInt("inventory"));
drug.setDrugflag(rs.getInt("drugflag"));
drug.setDrugcomment(rs.getString("drugcomment"));
drugList.add(drug);
}
rs.close();
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
page.setPageItem(drugList);
return page;
}