首页 > 代码库 > ssm 存储过程分页
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, Object> param = new HashMap<String, Object>();
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:mi:ss‘) and to_date(‘"+endTime+"‘, ‘yyyy-mm-dd hh24:mi:ss‘)";
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<TlUserLoginLog> getPrAllUser(Map map);
UserLoginLogServiceImpl：
@Override
public List<TlUserLoginLog> getPrAllUser(Map map) {
// TODO Auto-generated method stub
return userLoginLogDao.getPrAllUser(map);
}
IUserLoginLogDao：
public List<TlUserLoginLog> getPrAllUser(Map map);
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}
)}
</select>
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">
<html>
<head>
<base href="http://www.mamicode.com/">
<title>Basic Form - jQuery EasyUI Demo</title>
<link rel="stylesheet" type="text/css" href="http://www.mamicode.com/common/jquery-easyui-1.5.1/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="http://www.mamicode.com/common/jquery-easyui-1.5.1/themes/icon.css">
<script type="text/javascript" src="http://www.mamicode.com/common/jquery-easyui-1.5.1/jquery.min.js"></script>
<script type="text/javascript" src="http://www.mamicode.com/common/jquery-easyui-1.5.1/jquery.easyui.min.js"></script>
<script type="text/javascript" src="http://www.mamicode.com/common/jquery-easyui-1.5.1/easyui-lang-zh_CN.js"></script>
<script type="text/javascript">
$(function(){
var pageNo = parseInt($("#currentPageNo").val());
var totalPage = parseInt($("#totalPage").val());
if(pageNo == 1 && totalPage == pageNo){
$("#previous").hide();
$("#next").hide();
}else if(pageNo == 1 && totalPage > 1){
$("#previous").hide();
$("#next").show();
}else if(pageNo > 1 && pageNo < totalPage){
$("#previous").show();
$("#next").show();
}else if(pageNo == totalPage && pageNo > 1){
$("#previous").show();
$("#next").hide();
}
$("#previous").click(function(){
$("#pageNo").val(--pageNo);
$("#form1").submit();
});
$("#next").click(function(){
$("#pageNo").val(++pageNo);
$("#form1").submit();
});
$("#firstPage").click(function(){
$("#pageNo").val(1);
$("#form1").submit();
});
$("#lastPage").click(function(){
$("#pageNo").val(totalPage);
$("#form1").submit();
});
$("#selectPage").change(function(){
$("#pageNo").val($(this).val());
$("#form1").submit();
});
$("#selectPage").val(pageNo);
$("#addItemNoteConfirm").click(function(){
//textarea可以使用val获得值
var notes = $("#itemNote").val();
$("#notes").val(notes);
$("#showForm").submit();
});
$("#goSearch").click(function(){
var start = $("#startRequestTime").val();
var end = $("#endRequestTime").val();
if(start != null && end != null && $.trim(start) != ‘‘ && $.trim(end) != ‘‘){
if(start >= end){
alert("开始时间不能大于结束时间！");
return false;
}
}
$("#form1").submit();
});
$("#reset").click(function(){
$(‘input‘).val(‘‘);
});
$(‘#dataID‘).datagrid({
onDblClickRow: function(rowIndex) {
$(‘#dataID‘).datagrid(‘selectRow‘,rowIndex);
var currentRow =$("#dataID").datagrid("getSelected");
$.ajax({
type:‘post‘,
url:‘Security/getSecurity.do‘,
type:"post",
dataType:"text",
data:{
originalxml:currentRow.originalxml
},
cache:false ,
success:function(orgXML){
$("#dialogorgxml").textbox(‘setValue‘,orgXML);
}
});
if(currentRow.loginUserType == "01"){
var loginUserType = "手机账号";
}else if(currentRow.loginUserType == "02"){
var loginUserType = "别名账号";
}
if(currentRow.loginAccountType == "01"){
var loginAccountType ="个人用户";
}else if(currentRow.loginAccountType == "02"){
var loginAccountType ="企业用户";
}
$("#dialogName").textbox(‘setValue‘,currentRow.loginName);
$("#dialogTime").textbox(‘setValue‘,currentRow.requestTime);
$("#dialogUserType").textbox(‘setValue‘,loginUserType);
$("#dialogAccountType").textbox(‘setValue‘,loginAccountType);
$("#dialogResult").textbox(‘setValue‘,currentRow.result);
$("#dialogDesc").textbox(‘setValue‘,currentRow.resultDesc);
$("#dialogRspxml").textbox(‘setValue‘,currentRow.rspxml);
$("#dialogChannelid").textbox(‘setValue‘,currentRow.channelid);
$("#dialogorgxml").textbox(‘setValue‘,currentRow.originalxml);
$(‘#roleDialog‘).show().dialog({
left:400,
top:50,
modal : true,
title : ‘详细信息‘,
modal: true,
closable: true,
draggable: true,
width: 550,
height: 580
});
}
});
});
</script>
</head>
<body>
<form id="form1" name="form1" action="${basePath}userloginLog/getAllUser.do" method="post">
<div style="width:100%;height:95%" >
<div id="p" class="easyui-panel" title="查询框" style="width:99%;height:100px;"
data-options="collapsible:true">
<div style="width:100%;height:100%;">
<table id="searchId" style="width:100%;height:50px;">
<tr>
<td>用户名:</td>
<td><input class="easyui-textbox" style="width: 140px;" value="http://www.mamicode.com/${loginName}" name="loginName" ></input></td>
<td>起始时间:</td>
<td><input id="startRequestTime" class="easyui-datetimebox" style="width: 140px;" value="http://www.mamicode.com/${startTime}" name="startTime" ></input></td>
<td>结束时间:</td>
<td><input id="endRequestTime" class="easyui-datetimebox" style="width: 140px;" value="http://www.mamicode.com/${endTime}" name="endTime" ></input></td>
<td>请求结果类型:</td>
<td>
<select id="resultCode" class="easyui-combobox" name="resultCode" style="width:150px">
<option value="" selected>可选择结果类型</option>
<option value="http://www.mamicode.com/0000" <c:if test="${‘0000‘ eq resultCode}">selected</c:if>>成功</option>
<option value="http://www.mamicode.com/8002" <c:if test="${‘8002‘ eq resultCode}">selected</c:if>>密码错误</option>
<option value="http://www.mamicode.com/8008" <c:if test="${‘8008‘ eq resultCode}">selected</c:if>>账号已锁定</option>
<option value="http://www.mamicode.com/8001" <c:if test="${‘8001‘ eq resultCode}">selected</c:if>>账号不存在</option>
<option value="http://www.mamicode.com/9008" <c:if test="${‘9008‘ eq resultCode}">selected</c:if>>签名验证错误</option>
<option value="http://www.mamicode.com/9006" <c:if test="${‘9006‘ eq resultCode}">selected</c:if>>认证请求报文格式错误</option>
</select>
</td>
<td>平台:</td>
<td>
<select id="channelid" class="easyui-combobox" name="channelid" style="width:150px">
<option value="" selected>可选择用户平台</option>
<option value="http://www.mamicode.com/10001" <c:if test="${‘10001‘ eq channelid}">selected</c:if>>店员奖励系统</option>
<option value="http://www.mamicode.com/10002" <c:if test="${‘10002‘ eq channelid}">selected</c:if>>直供系统</option>
<option value="http://www.mamicode.com/10003" <c:if test="${‘10003‘ eq channelid}">selected</c:if>>终端信息平台</option>
<option value="http://www.mamicode.com/10004" <c:if test="${‘10004‘ eq channelid}">selected</c:if>>售后</option>
<option value="http://www.mamicode.com/10005" <c:if test="${‘10005‘ eq channelid}">selected</c:if>>用户信息管理平台</option>
</select>
</td>
<td><a id="goSearch" class="easyui-linkbutton" type="submit" iconcls="icon-search" href="javascript:void(0)" onclick="login()">查询</a></td>
<td><a id="reset" class="easyui-linkbutton" type="reset" iconCls="icon-undo" href="javascript:void(0)" onclick="reset()">重置</a></td>
</tr>
</table>
</div>
</div>
<table id="dataID" class="easyui-datagrid" style="width:99%;height:95%"
data-options="singleSelect:true,fitColumns:true,method:‘get‘">
<thead>
<tr>
<th data-options="field:‘sequence‘,width:5,align:‘center‘">序列</th>
<th data-options="field:‘loginName‘,width:10,align:‘center‘">用户名</th>
<th data-options="field:‘requestTime‘,width:13,align:‘center‘">请求时间</th>
<th data-options="field:‘resultDesc‘,width:10,align:‘left‘">请求结果</th>
<th data-options="field:‘channelid‘,width:20,align:‘center‘">渠道</th>
<th data-options="field:‘loginUserType‘,width:10,align:‘center‘,hidden:true">用户类型</th>
<th data-options="field:‘loginAccountType‘,width:10,align:‘center‘,hidden:true">账号类型</th>
<th data-options="field:‘originalxml‘,width:20,align:‘left‘,hidden:true">加密报文</th>
<th data-options="field:‘rspxml‘,width:20,align:‘right‘,hidden:true">返回报文</th>
</tr>
</thead>
<tbody>
<c:forEach items="${userLoginLogList}" var="loginList" varStatus="sequence">
<tr>
<td>${sequence.count}</td>
<td>${loginList.loginName }</td>
<td >${loginList.loginTime }</td>
<td>${loginList.resultDesc }</td>
<c:choose>
<c:when test="${‘10001‘ eq loginList.channelid}">
<td>店员奖励系统</td>
</c:when>
<c:when test="${‘10002‘ eq loginList.channelid}">
<td>直供系统</td>
</c:when>
<c:when test="${‘10003‘ eq loginList.channelid}">
<td>终端信息平台</td>
</c:when>
<c:when test="${‘10004‘ eq loginList.channelid}">
<td>售后</td>
</c:when>
<c:when test="${‘10005‘ eq loginList.channelid}">
<td>用户信息管理平台</td>
</c:when>
<c:otherwise>
<td></td>
</c:otherwise>
</c:choose>
<td>${loginList.loginUserType }</td>
<td>${loginList.loginAccountType }</td>
<td>${loginList.originalxml }</td>
<td>${loginList.rspxml }</td>
</tr>
</c:forEach>
</tbody>
</table>
<div class="page_c">
<span class="l inb_a">
</span>
<span class="r page">
<input type="hidden" id="pageNo" name="pageNo" />
<input type="hidden" value="http://www.mamicode.com/${totalCount}" id="totalCount" name="totalCount" />
<input type="hidden" value="http://www.mamicode.com/${pageNo}" id="currentPageNo" name="currentPageNo" />
<input type="hidden" value="http://www.mamicode.com/${totalPage}" id="totalPage" name="totalPage" />
共<var id="pagePiece" class="orange">${totalCount}</var>条<var id="pageTotal">${pageNo}/${totalPage}</var>
<a href="javascript:void(0);" id="firstPage" >首页</a>
<a href="javascript:void(0);" id="previous" class="hidden" title="上一页">上一页</a>
<a href="javascript:void(0);" id="next" class="hidden" title="下一页">下一页</a>
<select id="selectPage" >
<c:forEach begin="1" end="${totalPage }" var="myPage">
<option value="http://www.mamicode.com/${myPage }">第${myPage }页</option>
</c:forEach>
</select>
<a href="javascript:void(0);" id="lastPage" >尾页</a>
</span>
</div>
</div>
</form>
<div id="roleDialog" style="display: none;overflow: hidden;">
<table id="detail" cellpadding="5" style="margin-left:20%;margin-top:5%;">
<tr>
<td>用户名:</td>
<td><input id="dialogName" class="easyui-textbox" style="width: 240px;" name="loginName" ></input></td>
</tr>
<tr>
<td>请求时间:</td>
<td><input id="dialogTime" class="easyui-textbox" style="width: 240px;" value="" name="loginName" ></input></td>
</tr>
<tr>
<td>用户类型:</td>
<td><input id="dialogUserType" class="easyui-textbox" style="width: 240px;" value="" name="loginUserType" ></input></td>
</tr>
<tr>
<td>账号类型:</td>
<td><input id="dialogAccountType" class="easyui-textbox" style="width: 240px;" value="" name="loginAccountType" ></input></td>
</tr>
<tr>
<td>返回结果:</td>
<td><input id="dialogDesc" class="easyui-textbox" style="width: 240px;" value="" name="loginName" ></input></td>
</tr>
<tr>
<td>平台系统:</td>
<td><input id="dialogChannelid" class="easyui-textbox" style="width: 240px;" value="" name="loginName" ></input></td>
</tr>
<tr>
<td>返回报文:</td>
<td><input id="dialogRspxml" data-options="multiline:true" class="easyui-textbox" style="width: 240px;height:100px;" value="" name="loginName" ></input></td>
</tr>
<tr>
<td>解密后报文:</td>
<td><input id="dialogorgxml" data-options="multiline:true" class="easyui-textbox" style="width: 240px;height:130px;" value="" name="loginName" ></input></td>
</tr>
</table>
</div>
</body>
</html>
ssm 存储过程分页