ssm 存储过程分页

发布时间：2020-07-22 09:28:39 作者：4586558899
来源：网络阅读：1373

分页存储过程:

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) --返回的结果集

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 hh34:mi:ss') and to_date('"+endTime+"', 'yyyy-mm-dd hh34: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:

{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>

<title>Basic Form - jQuery EasyUI Demo</title>

$(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>

<div >

<div id="p" class="easyui-panel" title="查询框"

data-options="collapsible:true">

<div >

<tr>

<td>请求结果类型:</td>

<td>

<option value="" selected>可选择结果类型</option>

<option value="0000" <c:if test="${'0000' eq resultCode}">selected</c:if>>成功</option>

<option value="8002" <c:if test="${'8002' eq resultCode}">selected</c:if>>密码错误</option>

<option value="8008" <c:if test="${'8008' eq resultCode}">selected</c:if>>账号已锁定</option>

<option value="8001" <c:if test="${'8001' eq resultCode}">selected</c:if>>账号不存在</option>

<option value="9008" <c:if test="${'9008' eq resultCode}">selected</c:if>>签名验证错误</option>

<option value="9006" <c:if test="${'9006' eq resultCode}">selected</c:if>>认证请求报文格式错误</option>

</select>

</td>

<td>

<option value="" selected>可选择用户平台</option>

<option value="10001" <c:if test="${'10001' eq channelid}">selected</c:if>>店员奖励系统</option>

<option value="10002" <c:if test="${'10002' eq channelid}">selected</c:if>>直供系统</option>

<option value="10003" <c:if test="${'10003' eq channelid}">selected</c:if>>终端信息平台</option>

<option value="10004" <c:if test="${'10004' eq channelid}">selected</c:if>>售后</option>

<option value="10005" <c:if test="${'10005' eq channelid}">selected</c:if>>用户信息管理平台</option>

</select>

</td>

</tr>

</table>

</div>

<table id="dataID" class="easyui-datagrid"

data-options="singleSelect:true,fitColumns:true,method:'get'">

<thead>

<tr>

</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}">

</c:when>

<c:when test="${'10003' eq loginList.channelid}">

<td>终端信息平台</td>

</c:when>

<c:when test="${'10004' eq loginList.channelid}">

</c:when>

<c:when test="${'10005' eq loginList.channelid}">

<td>用户信息管理平台</td>

</c:when>

<c:otherwise>

</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>

</span>

共<var id="pagePiece" class="orange">${totalCount}</var>条<var id="pageTotal">${pageNo}/${totalPage}</var>

<c:forEach begin="1" end="${totalPage }" var="myPage">

<option value="${myPage }">第${myPage }页</option>

</c:forEach>

</select>

</span>

</div>

</form>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td>解密后报文:</td>

</tr>

</table>

</div>

</body>

</html>

ssm 存储过程分页

相关阅读