结合oracle数据库的分页排序jqgrid表格

结合oracle数据库的分页、排序jqgrid表格包含内容1、Servlet程序2、JSP页面3、jqgrid表格图一、Servlet程序packagecom.lyk.servlet;importjava.io.IOException;importjava.io.PrintWriter;importjava.sql.Connection;importjava.sql.SQLException;importjava.sql.Statement;importjavax.mail.Session;importjavax.naming.Context;importjavax.naming.InitialContext;importjavax.naming.NamingException;importjavax.servlet.ServletException;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjavax.sql.DataSource;importjava.sql.ResultSet;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importnet.sf.json.JSONArray;importnet.sf.json.JSONObject;importmons.dbutils.QueryRunner;importmons.dbutils.handlers.BeanListHandler;importcom.google.gson.Gson;importcom.google.gson.JsonArray;importcom.google.gson.JsonElement;importcom.google.gson.JsonObject;importcom.google.gson.JsonParser;importcom.lyk.ip.Test;publicclassjqgridTest4extendsHttpServlet{ /** *ThedoPostmethodoftheservlet.<br> * *Thismethodiscalledwhenaformhasitstagvaluemethodequalsto *post. * *@paramrequest *therequestsendbytheclienttotheserver *@paramresponse *theresponsesendbytheservertotheclient *@throwsServletException *ifanerroroccurred *@throwsIOException *ifanerroroccurred */ @SuppressWarnings("unused") publicvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse) throwsServletException,IOException{ List<Test>rtnList=newArrayList<Test>(); Stringpage=request.getParameter("page");//取得当前页数 Stringrows=request.getParameter("rows");//取得每页显示行数rowList Stringsidx=request.getParameter("sidx"); Stringsord=request.getParameter("sord"); if(request.getSession().getAttribute("rows")==null){ request.getSession().setAttribute("rows",rows); }else{ Stringrows2=(String)request.getSession().getAttribute("rows"); request.getSession().setAttribute("rows",rows); if(rows2.equals(rows)){ }else{ page="1"; } } ; intab=0; intpageSize=Integer.parseInt(rows); intkaishi=(Integer.parseInt(page)-1)*pageSize+1; intjieshu=Integer.parseInt(page)*pageSize+1; try{ ContextinitContext=newInitialContext(); DataSourceds=(DataSource)initContext .lookup("java:/comp/env/jdbc/oracleds2"); Connectionconn=ds.getConnection(); Statementpstmt=conn.createStatement(); ResultSetresultset=pstmt .executeQuery("selectcount(*)asaccfromsecu_t_userwhereuserlogidlike'jk%'"); resultset.next(); ab=resultset.getInt(1); resultset.close(); pstmt.close(); QueryRunnerqr=newQueryRunner(ds); try{ rtnList=qr .query("selectuserlogid,logpasswordfrom(selectrownummyno,a.*from(select*fromsecu_t_userwhereuserlogidlike'jk%'orderby" +sidx +"" +sord +")a)wheremyno>=" +kaishi+"andmyno<"+jieshu+"", newBeanListHandler<Test>(Test.class)); }catch(SQLExceptione){ e.printStackTrace(); } conn.close(); initContext.close(); }catch(Exceptione){ e.printStackTrace(); } Gsongson=newGson(); Stringjson=gson.toJson(rtnList); JsonParserparser=newJsonParser(); JsonElementel=parser.parse(json); JsonArrayjsonArray=null; if(el.isJsonArray()){ jsonArray=el.getAsJsonArray(); } inttotalRecord=ab;//总记录数(应根据数据库取得，在此只是模拟) inttotalPage=totalRecord%Integer.parseInt(rows)==0?totalRecord /Integer.parseInt(rows):totalRecord/Integer.parseInt(rows) +1;//计算总页数 try{ intindex=(Integer.parseInt(page)-1)*Integer.parseInt(rows);//开始记录数 //以下模拟构造JSON数据对象 json="{total:"+totalPage+",page:"+page+",records:" +ab+",rows:["; JsonObjectjobj=(JsonObject)jsonArray.get(0); StringoptionName=jobj.get("userlogid").toString(); Stringmima=jobj.get("logpassword").toString(); json=json+"{cell:["+optionName+","+mima+"]}"; for(inti=1;i<(jsonArray.size());i++){ jobj=(JsonObject)jsonArray.get(i); optionName=jobj.get("userlogid").toString(); mima=jobj.get("logpassword").toString(); json=json+",{cell:["+optionName+","+mima+"]}"; } json=json+"]}"; //System.out.println(json); response.getWriter().write(json); }catch(Exceptione){ } return; }}二、JSP页面<%@pagelanguage="java"import="java.util.*"pageEncoding="utf-8"%><%@pageimport="com.lyk.ip.*"%><%@pageimport="java.sql.*"%><%@pageimport="mons.dbutils.handlers.*"%><%@pageimport="mons.dbutils.*"%><% Stringpath=request.getContextPath(); StringbasePath=request.getScheme()+"://" +request.getServerName()+":"+request.getServerPort() +path+"/";%><jsp:useBeanid="db"class="com.lyk.ip.OracleUtil"scope="request"/><jsp:useBeanid="gb"class="com.lyk.ip.T_ip"scope="request"/><!DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN"><html><head><basehref="<%=basePath%>"><title>MyJSP'test.jsp'startingpage</title><metahttp-equiv="pragma"content="no-cache"><metahttp-equiv="cache-control"content="no-cache"><metahttp-equiv="expires"content="0"><metahttp-equiv="keywords"content="keyword1,keyword2,keyword3"><metahttp-equiv="description"content="Thisismypage"><!-- <linkrel="stylesheet"type="text/css"href="styles.css"> --><styletype="text/css">table.imagetable{ font-family:verdana,arial,sans-serif; font-size:11px; color:#333333; border-width:1px; border-color:#999999; border-collapse:collapse;}table.imagetableth{ background:#b5cfd2url('images/cell-blue.jpg'); border-width:1px; padding:8px; border-style:solid; border-color:#999999;}table.imagetabletd{ background:#dcddc0url('images/cell-grey.jpg'); border-width:1px; padding:8px; border-style:solid; border-color:#999999;}</style><scripttype="text/javascript"src="scripts/jquery-1.7.1.js"></script><linkrel="stylesheet"type="text/css"media="screen" href="css/grid.css"/> <linkhref="css/ui.jqgrid.css"rel="stylesheet"type="text/css"><linkhref="plugins/searchFilter.css"rel="stylesheet"type="text/css"><linkhref="plugins/ui.multiselect.css"rel="stylesheet"type="text/css"><linkhref="css/jquery-ui-1.9.2.custom.css"rel="stylesheet"type="text/css"><linkhref="css/jquery-ui-1.9.2.custom.min.css"rel="stylesheet"type="text/css"><style>html,body{margin:0;padding:0;font-size:75%;}.ui-jqgrid.ui-jqgrid-htablethdiv{height:80px;}</style><scripttype="text/javascript"src="js/jquery.min.js"></script><scripttype="text/javascript"src="js/jquery-1.7.2.min.js"></script><scripttype="text/javascript"src="js/jquery.jqGrid.min.js"></script><scripttype="text/javascript"src="js/jquery.jqGrid.src.js"></script><scripttype="text/javascript"src="plugins/jquery.tablednd.js"></script><scripttype="text/javascript"src="plugins/grid.setcolumns.js"></script><scripttype="text/javascript"src="js/i18n/grid.locale-cn.js"></script><scripttype="text/javascript"src="js/i18n/grid.locale-en.js"></script><scripttype="text/javascript"src="js/jquery-ui-1.9.2.custom.js"></script><scripttype="text/javascript"src="js/jquery-ui-1.9.2.custom.min.js"></script><scripttype="text/javascript"src="src/grid.base.js"></script><scripttype="text/javascript"src="js/jsonMap.js"></script><scripttype="text/javascript"src="js/jquery.jqGrid.js"></script><!--<scripttype="text/javascript"src="scripts/jquery-1.7.1.js"></script>--><scripttype="text/javascript">$(document).ready(function(){ jQuery("#list4").jqGrid({ datatype:"json",//将这里改为使用JSON数据 url:'jqgridTest4',//这是Action的请求地址 mtype:'POST', height:250,width:400, colNames:['编号','姓名'],colModel:[{name:'userlogid',index:'userlogid',width:90},