查询sql语句

 

@RequestMapping(params = "datagrid")
public void datagrid2(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
String fpeople = request.getParameter("fpeople");
String projectcode = request.getParameter("projectcode");
String fdate1 = request.getParameter("fdate_begin");
String fdate2 = request.getParameter("fdate_end");
String sql = "select * from z_gongshi where 1=1 ";
if (StringUtil.isNotEmpty(fpeople)) {
sql = sql + "and fpeople like '%" + fpeople + "%' ";
}
if (StringUtil.isNotEmpty(projectcode)) {
sql = sql + "and projectcode = '" + projectcode + "' ";
}
if (StringUtil.isNotEmpty(fdate1) && StringUtil.isNotEmpty(fdate2)) {
sql = sql + "and fdate between '" + fdate1 + "' and '" + fdate2 + "'";
}
Map queryparams = new LinkedHashMap<String, Object>();
List<Map<String, Object>> result = cgReportService.queryByCgReportSql(sql, queryparams, null,
dataGrid.getPage(), dataGrid.getRows());
int fhour = 0;
for (Map<String, Object> map : result) {
fhour = fhour + Integer.parseInt(map.get("fhour").toString());
}
Long size = cgReportService.countQueryByCgReportSql(sql, queryparams, null);
response.setContentType("application/json");
response.setHeader("Cache-Control", "no-store");
PrintWriter writer = null;
try {
writer = response.getWriter();
String json = CgReportQueryParamUtil.getJson(result, size);
JSONObject j = JSONObject.fromObject(json);
j.put("footer", "[{\"fhour\":\"" + fhour + "\",\"fpeople\":\"合计\"}]");
writer.println(j.toString());
logger.info(j.toString());
writer.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
writer.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
TagUtil.datagrid(response, dataGrid);
}

 

 

调用存储过程

@RequestMapping(params = "datagrid")
public void datagrid3(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid) {
String begin_fdate = request.getParameter("fdate_begin");
String end_fdate = request.getParameter("fdate_end");
TSUser user = ResourceUtil.getSessionUser();
if(begin_fdate!=null && end_fdate!=null) {
String sql="exec  dbo.XP_YF_DUIZHANG_HZ '"+begin_fdate+"','"+end_fdate+"','"+user.getUserName()+"','"+user.getUserName()+"' ";
List<Map<String,Object>> data =jdbcTemplate.queryForList(sql);
dataGrid.setTotal(data.size());
int showLen = dataGrid.getRows();
List<Map<String, Object>> showList = null;
int totlePage = data.size()%showLen == 0 ? data.size()/showLen : data.size()/showLen + 1;
if(dataGrid.getPage() == 1 ) {
if(showLen > data.size()) {
showLen = data.size();
}
showList = data.subList(0, showLen);
}else {
if(dataGrid.getPage() == totlePage) {
showList = data.subList(((dataGrid.getPage()-1)*showLen),data.size());
}else {
showList = data.subList(((dataGrid.getPage()-1)*showLen),((dataGrid.getPage()-1)*showLen)+showLen);
}
}
dataGrid.setResults(showList);
TagUtil.datagrid(response, dataGrid);
}
}