js
function load(){
var where = window.dialogArguments ;
conditions();
$("#Certificates").datagrid({
title : '资质证书',
url : '/ServiceAction/com.velcro.ygzz.servlet.ygzzAction?action=queryCertificates_r',
fitColumns : true,
singleSelect : false,
rownumbers : true,
checkOnSelect:false,//点击复选框才可勾选
selectOnCheck:false,
striped:true,
idField:'id',
queryParams:{where:where,
parameter:parameter
},
columns:[[
{
field : 'bid',
title : '资质借出id',
hidden: true
},{
field : 'zid',
title : '资质id',
hidden: true
},{
field : 'zsbh',
title : '证书编号',
align : 'center',
width: 50
}, {
field : 'czmc',
title : '证书名称',
align : 'center',
width: 50
} , {
field : 'zsjb',
title : '证书级别',
align : 'center',
width: 40
} , {
field : 'zscfd',
title : '证书存放地',
align : 'center',
width: 30
}, {
field : 'objname',
title : '借证员工',
align : 'center',
width: 40
}
]],
pagination : true,
pageSize : 20,
pageList : [20,40,60,80],
onClickRow : returnVal
});
}
jsp
<div class="details" id="Certificates" style="width:600px;"></div>
action
/**
* 资质归还弹出框
* @param request
* @param response
* @throws IOException
*/
private void queryCertificates_r(HttpServletRequest request, HttpServletResponse response) throws IOException {
String arg0 = request.getParameter("where");
String arg1 = request.getParameter("parameter");
int startP = NumberHelper.string2Int(request.getParameter("page"));
int pageSize = NumberHelper.string2Int(request.getParameter("rows"));
DataGrid data = ygzzService.queryCertificate_re(startP, pageSize, arg0, arg1);
WorkHourUtils.flushData(request, response, JSONValue.toJSONString(data));
}
daoImpl
@Override
public DataGrid queryCertificate_r(int startP, int pageSize, String arg0, String arg1) {
int nextPage = pageSize * (startP - 1);
int end = nextPage + pageSize;
String sql = "SELECT " +
" b.id bid, " +
" z.id zid, " +
" z.zsbh, " +
" z.czmc, " +
" z.zsjb, " +
" z.zscfd, " +
" h.objname ," +
"row_number() over(order by z.id) pos " +
"FROM\n" +
" user_zizhiz_b b, " +
" humres h, " +
" user_zizhi z "+
"WHERE " +
" createUser = h.id " +
"AND zzId = z.id " +
"AND b.id NOT IN (SELECT zzbId from user_zizhi_r)";
if(arg0 != ""){
sql += arg0;
}
sql += arg1;
String sqlQuery = "select * from (";
sqlQuery = sqlQuery + sql;
sqlQuery = sqlQuery + ") t0 WHERE " + nextPage + " < t0.pos and t0.pos <= " + end;
List<?> list = this.wHBaseDao.getJdbcTemplate().queryForList(sql);
DataGrid data = new DataGrid();
if ((list != null) && (!list.isEmpty())){
data.setRows(list);
data.setTotal(getTotalForPages(sql));
return data;
}
return data;
}
getTotalForPages方法
private int getTotalForPages(String sql) {
String totalsql = "select count(1) total from (" + sql + ") t";
return this.wHBaseDao.getJdbcTemplate().queryForInt(totalsql);
}