ssm 存储过程分页

缺乏、安全感 2022-06-07 04:24 261阅读 0赞

分页存储过程:

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

\{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 id="p" class="easyui-panel" title="查询框" style="width:99%;height:100px;"

data-options="collapsible:true">

<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" style="width:99%;height:95%"

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>

转载网址：[http://6664553.blog.51cto.com/6654553/1909694/][http_6664553.blog.51cto.com_6654553_1909694]

相关阅读：MyIbatis:存储过程(增,删,改,查)

网    址：[http://liuzidong.iteye.com/blog/1056671][http_liuzidong.iteye.com_blog_1056671]

[http_6664553.blog.51cto.com_6654553_1909694]: http://6664553.blog.51cto.com/6654553/1909694/
[http_liuzidong.iteye.com_blog_1056671]: http://liuzidong.iteye.com/blog/1056671

ssm 存储过程分页

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySql 分页存储过程

相关 SQL 分页存储过程

相关 mysql分页通用存储过程

相关分页存储过程

相关 Oracle分页存储过程

相关 ssm 存储过程分页

相关通用分页存储过程

相关 SQL通用分页存储过程

相关通用存储过程.分页存储过程

相关简单的分页存储过程！