ssm 存储过程分页
分页存储过程:
CREATE OR REPLACE PROCEDURE prc_query
(p\_tableName in varchar2, --表名p\_strWhere in varchar2, --查询条件p\_orderColumn in varchar2, --排序的列p\_orderStyle in varchar2, --排序方式p\_curPage in out number, --当前页p\_pageSize in out number, --每页显示记录条数p\_totalRecords out number, --总记录数p\_totalPages out number, --总页数v\_cur out pkg\_query.cur\_query) --返回的结果集
IS
v_sql VARCHAR2(1000) := ‘’; —sql语句
v_startRecord Number(4); —开始显示的记录条数
v_endRecord Number(4); —结束显示的记录条数
BEGIN
—记录中总记录条数
v_sql := ‘SELECT TO_NUMBER(COUNT(*)) FROM ‘ || p_tableName || ‘ WHERE 1=1’;
IF p_strWhere IS NOT NULL or p_strWhere <> ‘’ THEN
v\_sql := v\_sql || p\_strWhere;
END IF;
EXECUTE IMMEDIATE v_sql INTO p_totalRecords;
—验证页面记录大小
IF p_pageSize < 0 THEN
p\_pageSize := 0;
END IF;
—根据页大小计算总页数
IF MOD(p_totalRecords,p_pageSize) = 0 THEN
p\_totalPages := p\_totalRecords / p\_pageSize;
ELSE
p\_totalPages := p\_totalRecords / p\_pageSize + 1;
END IF;
—验证页号
IF p_curPage < 1 THEN
p\_curPage := 1;
END IF;
IF p_curPage > p_totalPages THEN
p\_curPage := p\_totalPages;
END IF;
—实现分页查询
v_startRecord := (p_curPage - 1) * p_pageSize + 1;
v_endRecord := p_curPage * p_pageSize;
v_sql := ‘SELECT * FROM (SELECT A.*, rownum r FROM ‘ ||
'(SELECT \* FROM ' || p\_tableName;
IF p_strWhere IS NOT NULL or p_strWhere <> ‘’ THEN
v\_sql := v\_sql || ' WHERE 1=1' || p\_strWhere;
END IF;
IF p_orderColumn IS NOT NULL or p_orderColumn <> ‘’ THEN
v\_sql := v\_sql || ' ORDER BY ' || p\_orderColumn || ' ' || p\_orderStyle;
END IF;
v_sql := v_sql || ‘) A WHERE rownum <= ‘ || v_endRecord || ‘) B WHERE r >= ‘
|| v\_startRecord;
DBMS_OUTPUT.put_line(v_sql);
OPEN v_cur FOR v_sql;
END prc_query;
controller：
@Controller
@RequestMapping(“/userloginLog”)
public class UserLoginLogController {
@Autowired
private IUserLoginLogService userLoginLogService;
@RequestMapping(“/getAllUser”)
public String getAllUser(HttpServletRequest request,HttpServletResponse response,Model model){
try {
String sql = “”;
int pageNo =1;
Map
String StrpageNo = request.getParameter(“pageNo”);
if(StringUtils.isNotBlank(StrpageNo)){
pageNo = Integer.parseInt(StrpageNo);
}
String loginName = request.getParameter(“loginName”);
String resultCode = request.getParameter(“resultCode”);
String channelid = request.getParameter(“channelid”);
String startTime = request.getParameter(“startTime”);
String endTime = request.getParameter(“endTime”);
if(StringUtils.isNotBlank(loginName)){
sql+=” and login_name=’” + loginName + “‘“;
model.addAttribute(“loginName”, loginName);
}
if(StringUtils.isNotBlank(resultCode)){
sql+=” and RESULT_CODE=’” + resultCode + “‘“;
model.addAttribute(“resultCode”, resultCode);
}
if(StringUtils.isNotBlank(channelid)){
sql+=” and CHANNELID=’” + channelid + “‘“;
model.addAttribute(“channelid”, channelid);
}
if(StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime) ){
sql+=” and LOGIN_TIME between to_date(‘“+startTime+”‘, ‘yyyy-mm-dd hh24
ss’) and to_date(‘“+endTime+”‘, ‘yyyy-mm-dd hh24ss’)”;
model.addAttribute(“startTime”, startTime);
model.addAttribute(“endTime”, endTime);
}
param.put("tableName", "TL\_USER\_LOGIN\_LOG");param.put("strWhere", sql);param.put("curPage", pageNo);param.put("pageSize", 20);userLoginLogService.getPrAllUser(param);//result 为在mybatis xml文件时 写的返回结果名List<TlUserLoginLog> LoginLogList= (List<TlUserLoginLog>) param.get("result");int curPage = (Integer) param.get("curPage");int totalRecords = (Integer) param.get("totalRecords");int totalPages = (Integer) param.get("totalPages");model.addAttribute("myPage", curPage);model.addAttribute("pageNo", curPage);model.addAttribute("totalCount", totalRecords);model.addAttribute("totalPage", totalPages);model.addAttribute("userLoginLogList", LoginLogList);
return “prTest”;
} catch (Exception e) {
e.printStackTrace();
model.addAttribute(“InfoMessage”,
“获取信息失败，异常：” + e.getMessage());
return “result”;
}
}
}
IUserLoginLogService：
public List
UserLoginLogServiceImpl：
@Override
public List
// TODO Auto-generated method stub
return userLoginLogDao.getPrAllUser(map);
}
IUserLoginLogDao：
public List
mybitas:
<select id="getPrAllUser" statementType="CALLABLE" >\{call prc\_query(\#\{tableName,mode=IN,jdbcType=VARCHAR\},\#\{strWhere,mode=IN,jdbcType=VARCHAR\},\#\{orderColumn,mode=IN,jdbcType=VARCHAR\},\#\{orderStyle,mode=IN,jdbcType=VARCHAR\},\#\{curPage,mode=IN,jdbcType=INTEGER\},\#\{pageSize,mode=IN,jdbcType=INTEGER\},\#\{totalRecords,mode=OUT,jdbcType=INTEGER\},\#\{totalPages,mode=OUT,jdbcType=INTEGER\},\#\{result,mode=OUT,javaType=java.sql.ResultSet,jdbcType=CURSOR,resultMap=com.ai.tyca.dao.IUserLoginLogDao.BaseResultMap\})\}
JSP:
<%@ page language=”java” import=”java.util.*“ pageEncoding=”UTF-8”%>
<%@taglib prefix=”c” uri=”http://java.sun.com/jsp/jstl/core“ %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+”://“+request.getServerName()+”:”+request.getServerPort()+path+”/“;
%>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”>
转载网址：http://6664553.blog.51cto.com/6654553/1909694/
相关阅读：MyIbatis:存储过程(增,删,改,查)
网 址：http://liuzidong.iteye.com/blog/1056671
旧城等待，
不念不忘少年蓝@
╰+攻爆jí腚メ
还没有评论，来说两句吧...