- 参数命名要采用小驼峰命名法:第一个单词以小写字母开始,第二个单词后的首字母大写。
- xml中书写sql语句时,为保证查找效率,可以将查询条件放在sql语句里
JOIN G_OG_ORGAN GOO ON GOOE.DPT_ID = GOO.ORGAN_ID
WHERE 1=1
<if test="searchCicleType != '' and searchCicleType !=null">
AND srste.CICLE_TYPE = #{searchCicleType}
</if>
<if test="searchTaskName != '' and searchTaskName != null"> <!--模糊查询-->
AND srstm.TASK_NAME LIKE '%${search_task_name}%'
</if>
<if test="searchOrganId!='' and searchOrganId!=null"> <!--多选遍历-->
AND GOO.ORGAN_ID in
<foreach collection="searchOrganId.split(',')" item="item" open="(" close=")" separator="," >
'${item}'
</foreach>
</if>
GROUP BY
nvl( srstm.PARENT_TASK_ID, srstm.TASK_ID ),
srstm.TASK_NAME,
- 前端的查询条件传给后端,在xml的对应sql语句中进行拼接
前端界面,查询条件
<script type="text/javascript">
function getQueryParms(parms){
var search_task_name=$("input[name*='search_task_name']",$("#mainForm")).val();
var search_organ_id=$("input[name*='search_organ_id']",$("#mainForm")).val();
var search_emp_name=$("input[name *='search_emp_name']",$("#mainForm")).val();
var search_begin=$("input[name *='search_begin_month']",$("#mainForm")).val();
var search_end=$("input[name *='search_end_month']",$("#mainForm")).val();
parms.searchTaskName=search_task_name;
parms.searchOrganId=search_organ_id;
parms.searchEmpName=search_emp_name;
parms.searchBegin=search_begin;
parms.searchEnd=search_end;
parms.searchCicleType='03';
return parms;
}
</script>
后端controller层
/**
* 获取列表分页数据
*/
@RequestMapping("/queryAllPage")
public void queryAllPage(){
setSession("scoretasksum_","search");
QueryPage<Record> page = new QueryPage<>(scoreTaskSumService.paginate(getQueryParms()));
renderJson(page);
}
后端service层
/**
* 简要说明:分页查询
* @param
*/
public Page<Record> paginate(QueryParms qp) {
StringBuffer qSql=new StringBuffer();
//把核心sql再包一层
qSql.append(" FROM ( ") ;
qSql.append(ToolSqlHelper.getMapperSql(scoreTaskSumMapper,"queryAllPage",qp.getParaMap()));
qSql.append(" ) A");
qSql.append(" WHERE 1=1 ");
//表格搜索框显示时模糊匹配对应的字段
if(!"".equals(qp.getPara("searchText", ""))){
qSql.append(qp.genSearchTextSql(ConstantConfig.DB_DATASOURCE_MAIN, "A.TASK_NAME,A.ORGAN_NAME,A.OBJECT_USER,A.EMP_NAME,A.SUM_SCORE,A.AVG_SCORE"));
}
if(qp.isSort()){
qSql.append(" ORDER BY ").append(qp.getSortName()).append(" ").append(qp.getSortOrder());
}else{ //添加默认排序
qSql.append(" ORDER BY A.TASK_NAME,A.ORGAN_NAME ASC ");
}
return Db.paginate(qp.getPageNumber(), qp.getPageSize(), "SELECT *", qSql.toString());
}