先附上DAO方法:有两个方法构成。一个查询一页内的数据记录,一个查询记录总数。
map里存放这查询条件和参数值 还有第几页,一页多少行
@Override
@SuppressWarnings("unchecked")
public List selectStudentsList(Map map) {
/**
*对方法功能的描述
*@param参数1
*@param参数2
*@return 返回类型
*@throws这个方法所抛出的异常
*/
StringBuffer sb=new StringBuffer("SELECT s.NO_ as return1,u.NAME_ as return2,u.SEX_ as return3,sub.SHORT_TITLE_ as return4,g.NAME_ as return5,c.NAME_ as return6 FROM users_ u,login_ l, students_ s LEFT JOIN spe_subject_ sub ON s.PRO_ID_ = sub.ID_ LEFT JOIN grade_ g ON s.GRADE_ = g.ID_ LEFT JOIN classes_ c ON s.CLASS_ID_ = c.ID_ WHERE s.NO_=u.ID_ AND l.ID_=s.NO_ ");
Vector vector=new Vector();
if(map.get("no")!=null){
sb.append(" and s.NO_ like ?");
vector.add("%"+(String)map.get("no")+"%");
}
if(map.get("name")!=null){
sb.append(" and u.NAME_ like ?");
vector.add("%"+(String)map.get("name")+"%");
}
if(map.get("sex")!=null){
sb.append(" and u.SEX_=?");
vector.add((String)map.get("sex"));
}
if(map.get("proId")!=null){
sb.append(" and s.PRO_ID_=?");
vector.add((String)map.get("proId"));
}
if(map.get("grade")!=null){
sb.append(" and s.GRADE_=?");
vector.add((String)map.get("grade"));
}
if(map.get("classId")!=null){
sb.append(" and s.CLASS_ID_ =?");
vector.add((String)map.get("classId"));
}
sb.append(" and (l.ACCOUNT_STATUS_!='5' or l.ACCOUNT_STATUS_ is NULL)");
sb.append(" order by s.NO_ asc limit "+(Integer)map.get("begin")+","+(Integer)map.get("size"));
// System.out.println(sb.toString());
List list=getJdbcTemplate().queryForList(sb.toString(),vector.toArray());
List resList=new ArrayList();
for(Map imap:(List<Map>)list){
ReturnBean rb=new ReturnBean();
rb.setReturn1((String)imap.get("return1"));
rb.setReturn2((String)imap.get("return2"));
rb.setReturn3((String)imap.get("return3"));
rb.setReturn4((String)imap.get("return4"));
rb.setReturn5((String)imap.get("return5"));
rb.setReturn6((String)imap.get("return6"));
resList.add(rb);
}
return resList;
}
@Override
@SuppressWarnings("unchecked")
public int selectStudentsLength(Map map) {
StringBuffer sb=new StringBuffer("SELECT count(s.NO_) FROM users_ u,login_ l, students_ s WHERE s.NO_=u.ID_ AND l.ID_=s.NO_ ");
Vector vector=new Vector();
if(map.get("no")!=null){
sb.append(" and s.NO_ like ?");
vector.add("%"+(String)map.get("no")+"%");
}
if(map.get("name")!=null){
sb.append(" and u.NAME_ like ?");
vector.add("%"+(String)map.get("name")+"%");
}
if(map.get("sex")!=null){
sb.append(" and u.SEX_=?");
vector.add((String)map.get("sex"));
}
if(map.get("proId")!=null){
sb.append(" and s.PRO_ID_=?");
vector.add((String)map.get("proId"));
}
if(map.get("grade")!=null){
sb.append(" and s.GRADE_=?");
vector.add((String)map.get("grade"));
}
if(map.get("classId")!=null){
sb.append(" and s.CLASS_ID_ =?");
vector.add((String)map.get("classId"));
}
sb.append(" and (l.ACCOUNT_STATUS_!='5' or l.ACCOUNT_STATUS_ is NULL)");
return getJdbcTemplate().queryForInt(sb.toString(),vector.toArray());
}
查询的业务方法:查询结果会包装到PageInfo的Bean中
@Override
public PageInfo findStudentsToPage(Map map) {
PageInfo pageInfo=new PageInfo();
pageInfo.setPageIndex((Integer)map.get("pageIndex"));
map.put("begin", (pageInfo.getPageIndex()-1)*pageInfo.getPageSize());
map.put("size", pageInfo.getPageSize());
pageInfo.setPageList(studentsDAO.selectStudentsList(map));
pageInfo.setTotalNum(studentsDAO.selectStudentsLength(map));
pageInfo.countPageNum();
return pageInfo;
}