模糊查询
dao层
package com.offcn.dao;
import com.offcn.entity.User;
import java.util.List;
public interface UserDao {
List<User> selectAll();
User selectByName(String uname);
User selectByUid(int uid);
int updateByUid(User user);
Integer selectCount(String uname,String startTime,String endTime);
List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo,Integer pageSize);
int addUser(User user);
int deleteByUid(String ids);
}
dao实现层
@Override
public Integer selectCount(String uname,String startTime,String endTime) {
StringBuffer sb = new StringBuffer("select count(1) from user where 1=1 ");
List<Object> userList = new ArrayList<Object>();
if (uname != null && uname != ""){
sb.append(" and uname like ?");
userList.add("%"+uname+"%");
}
if (startTime != null && startTime != ""){
sb.append(" and udate > ? ");
userList.add(startTime);
}
if (endTime != null && endTime != ""){
sb.append(" and udate < ?");
userList.add(endTime);
}
Long count = null;
try {
count = (Long)qr.query(sb.toString(),new ScalarHandler(),userList.toArray());
} catch (SQLException e) {
e.printStackTrace();
}
return Integer.parseInt(count+"");
}
@Override
public List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo, Integer pageSize) {
StringBuffer sb = new StringBuffer("select * from user where 1=1 ");
List<Object> userList = new ArrayList<Object>();
if (uname != null && uname != ""){
sb.append(" and uname like ?");
userList.add("%"+uname+"%");
}
if (startTime != null && startTime != ""){
sb.append(" and udate > ? ");
userList.add(startTime);
}
if (endTime != null && endTime != ""){
sb.append(" and udate < ?");
userList.add(endTime);
}
sb.append(" order by uid desc");
sb.append(" limit ?,? ");
userList.add((currentPageNo-1)*pageSize);
userList.add(pageSize);
List<User> list = null;
try {
list = qr.query(sb.toString(),new BeanListHandler<User>(User.class),userList.toArray());
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
service 层
Integer selectCount(String uname,String startTime,String endTime);
List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo,Integer pageSize);
service 实现层
@Override
public Integer selectCount(String uname,String startTime,String endTime) {
return userDao.selectCount(uname, startTime, endTime);
}
@Override
public List<User> selectAll(String uname,String startTime,String endTime,Integer currentPageNo, Integer pageSize) {
return userDao.selectAll(uname,startTime,endTime,currentPageNo,pageSize);
}
servlet
String uname = req.getParameter("uname");
String startTime = req.getParameter("startTime");
String endTime = req.getParameter("endTime");
req.setAttribute("uname",uname);
req.setAttribute("startTime",startTime);
req.setAttribute("endTime",endTime);
String pageSizestr = req.getParameter("pageSize");
Integer pageSize;
if (pageSizestr == null || pageSizestr == ""){
pageSize = 3;
}else {
pageSize = Integer.parseInt(pageSizestr);
}
Integer totalPageCount = userService.selectCount(uname, startTime, endTime);
Integer totalPageSize = totalPageCount % pageSize == 0 ? totalPageCount / pageSize : totalPageCount / pageSize + 1;
String currentPageNostr = req.getParameter("currentPageNo");
Integer currentPageNo;
if (currentPageNostr == null || currentPageNostr == ""){
currentPageNo = 1;
}else {
currentPageNo = Integer.parseInt(currentPageNostr);
if (currentPageNo <= 1){
currentPageNo = 1;
}
if (currentPageNo >= totalPageSize){
currentPageNo = totalPageSize;
}
}
List<User> userList = userService.selectAll(uname, startTime, endTime,currentPageNo,pageSize);
Utils<User> userUtils = new Utils<User>();
userUtils.setCurrentPageNo(currentPageNo);
userUtils.setPageSize(pageSize);
userUtils.setTotalPageCount(totalPageCount);
userUtils.setTotalPageSize(totalPageSize);
userUtils.setuList(userList);
req.setAttribute("userUtils",userUtils);
req.getRequestDispatcher("select.jsp").forward(req,resp);
前台页面(设置查询回显)
<form style="margin-left: 120px" method="post" action="userServlet">
<p>查询:
姓名<input type="text" name="uname" value="${uname}">
开始时间<input type="date" name="startTime" value="${startTime}">
结束时间<input type="date" name="endTime" value="${endTime}">
<input type="submit" value="搜索">
</p>
</form>