ssm存储过程分页
分页存储过程:
创新互联长期为超过千家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为顺平企业提供专业的成都做网站、网站建设,顺平网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。
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 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
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:
{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+"/";
%>
$(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
});
}
});
});
用户名: | |
请求时间: | |
用户类型: | |
账号类型: | |
返回结果: | |
平台系统: | |
返回报文: | |
解密后报文: |
当前题目:ssm存储过程分页
当前URL:http://cdiso.cn/article/joggso.html