Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the Java paging features in Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

As we usually see in paging, paging returns not only the result set of the query (List), but also the total number of pages (pageNum), current page (pageNo) and other information, so we encapsulate a query result PageModel class with the code as follows:

Package com.bjpowernode.test; import java.util.List; public class PageModel {private List list; private int pageNo; private int pageSize; private int totalNum; private int totalPage; public List getList () {return list;} public void setList (List list) {this.list = list;} public int getPageNo () {return pageNo;} public void setPageNo (int pageNo) {this.pageNo = pageNo;} public int getPageSize () {return pageSize } public void setPageSize (int pageSize) {this.pageSize = pageSize;} public int getTotalNum () {return totalNum;} public void setTotalNum (int totalNum) {this.totalNum = totalNum; setTotalPage ((getTotalNum ()% pageSize) = = 0? (getTotalNum () / pageSize): (getTotalNum () / pageSize + 1));} public int getTotalPage () {return totalPage;} public void setTotalPage (int totalPage) {this.totalPage = totalPage;} / / get the first page public int getFirstPage () {return 1;} / / get the last page public int getLastPage () {return totalPage } / / get the front page public int getPrePage () {if (pageNo > 1) return pageNo-1; return 1;} / get the back page public int getBackPage () {if (pageNo < totalPage) return pageNo + 1; return totalPage;} / determine whether the 'home page' and 'front page' are available public String isPreable () {if (pageNo = = 1) return "disabled"; return "" } / / determine whether public String isBackable () {if (pageNo = = totalPage) return "disabled"; return ";}} is available for 'last page' and 'next page'

The use of generics is to enable the paging class to be reused, such as encapsulating User objects when querying users and single FlowCard classes when querying financial flows.

We take the query user as an example, the user selects the query condition, first calls Servlet to obtain the query parameters, and then requests the business logic layer to obtain the paging encapsulated result class. The business logic calls the Dao layer to get the result set, and the number of records in the acquisition encapsulates the component page class. Finally, Servlet sets the results to be displayed on the jsp page.

First, let's explain Servlet. The code is as follows:

Package com.bjpowernode.test; import java.io.*; import java.util.*; import javax.servlet.ServletConfig; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import kane.UserInfo; import kane.UserInfoManage; import kane.PageModel; public class UserBasicSearchServlet extends HttpServlet {private static final long serialVersionUID = 1L; private int pageSize = 0; @ Override public void init (ServletConfig config) throws ServletException {pageSize = Integer.parseInt (config.getInitParameter ("pageSize")) } @ Override protected void doGet (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {doPost (req, resp);} @ Override protected void doPost (HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {/ / 1. Get the page parameters and construct the parameter object int pageNo = Integer.parseInt (req.getParameter ("pageNo")); String sex = req.getParameter ("gender"); String home = req.getParameter ("newlocation"); String colleage = req.getParameter ("colleage"); String comingyear = req.getParameter ("ComingYear"); UserInfo u = new UserInfo (); u.setSex (sex); u.setHome (home); u.setColleage (colleage); u.setCy (comingyear); / / 2. Call the business logic to get the result set UserInfoManage userInfoManage = new UserInfoManage (); PageModel pagination = userInfoManage.userBasicSearch (u, pageNo, pageSize); List userList = pagination.getList (); / / 3. Encapsulate the returned result StringBuffer resultXML = new StringBuffer (); try {resultXML.append ("/ n"); resultXML.append ("/ n"); for (Iterator iterator = userList.iterator (); iterator .hasNext ();) {UserInfo userInfo = iterator.next (); resultXML.append ("/ n"); resultXML.append ("/ t" + userInfo.getId () + "/ n"); resultXML.append ("/ t" + userInfo.getTruename () + "/ n") ResultXML.append ("/ t" + userInfo.getSex () + "/ n"); resultXML.append ("/ t" + userInfo.getHome () + "/ n"); resultXML.append ("/ n");} resultXML.append ("/ n"); resultXML.append ("/ t" + pagination.getTotalPage () + "/ n"); resultXML.append ("/ t" + pagination.getFirstPage () + "/ n") ResultXML.append ("/ t" + pagination.getLastPage () + "/ n"); resultXML.append ("/ t" + pagination.getPageNo () + "/ n"); resultXML.append ("/ n"); resultXML.append ("/ n");} catch (Exception e) {e.printStackTrace ();} writeResponse (req, resp, resultXML.toString ());} public void writeResponse (HttpServletRequest request, HttpServletResponse response, String result) throws IOException {response.setContentType ("text/xml") Response.setHeader ("Cache-Control", "no-cache"); response.setHeader ("Content-Type", "text/xml; charset=gb18030"); PrintWriter pw = response.getWriter (); pw.write (result); pw.close ();}}

The User object code is as follows:

Package com.bjpowernode.test; import java.util.Date; public class UserInfo {private int id; private String username; private String password; private String truename; private String sex; private Date birthday; private String home; private String colleage; private String comingYear; public int getId () {return id;} public void setId (int id) {this.id = id;} public String getUsername () {return username;} public void setUsername (String username) {this.username = username } public String getPassword () {return password;} public void setPassword (String password) {this.password = password;} public String getTruename () {return truename;} public void setTruename (String truename) {this.truename = truename;} public String getSex () {return sex;} public void setSex (String sex) {this.sex = sex;} public Date getBirthday () {return birthday;} public void setBirthday (Date birthday) {this.birthday = birthday } public String getHome () {return home;} public void setHome (String home) {this.home = home;} public String getColleage () {return colleage;} public void setColleage (String colleage) {this.colleage = colleage;} public String getCy () {return comingYear;} public void setCy (String cy) {this. ComingYear= cy;}}

Then there is the business logic layer code, which is as follows:

Package com.bjpowernode.test; import java.sql.Connection; import kane.DBUtility; import kane.PageModel; public class UserInfoManage {private UserInfoDao userInfoDao = null; public UserInfoManage () {userInfoDao = new UserInfoDao ();} public PageModel userBasicSearch (UserInfo u, int pageNo, int pageSize) throws Exception {Connection connection = null; PageModel pagination = new PageModel (); try {connection = DBUtility.getConnection (); DBUtility.setAutoCommit (connection, false); pagination.setList (userInfoDao.getUserList (u, pageNo, pageSize); pagination.setPageNo (pageNo) Pagination.setPageSize (pageSize); pagination.setTotalNum (userInfoDao.getTotalNum (u)); DBUtility.commit (connection);} catch (Exception e) {DBUtility.rollBack (connection); e.printStackTrace (); throw new Exception ();} finally {DBUtility.closeConnection ();} return pagination;}}

DBUtility is the connection wrapper class of the database.

Finally, the code implementation of the Dao layer is as follows:

And home like'"+ userInfo.getHome () +"% "+" 'and colleage like' "+ userInfo.getColleage () +"% "+" 'and comingyear like' "+ userInfo.getCy () +"% "+" order by id) u where rownum=? "; userList = new ArrayList (); Connection conn = DBUtility.getConnection (); pstmt = conn.prepareStatement (sql); pstmt.setString (1, userInfo.getSex ()) Pstmt.setInt (2, pageNo * pageSize); pstmt.setInt (3, (pageNo-1) * pageSize + 1); rs = pstmt.executeQuery (); while (rs.next ()) {UserInfo user = new UserInfo (); user.setId (rs.getInt ("id")); user.setTruename (rs.getString ("truename")); user.setSex (rs.getString (sex)); user.setHome (rs.getString ("home")); userList.add (user) }} catch (SQLException e) {e.printStackTrace (); throw new Exception (e);} finally {DBUtility.closeResultSet (rs); DBUtility.closePreparedStatement (pstmt);} return userList;} public int getTotalNum (UserInfo userInfo) throws Exception {PreparedStatement pstmt = null; ResultSet rs = null; int count = 0; try {String sql = "select count (*) from user_info where sex=? And home like'"+ userInfo.getHome () +"% "+" 'and colleage like' "+ userInfo.getColleage () +"% "+" 'and comingyear like' "+ userInfo.getCy () +"% "+"'"; Connection conn = DBUtility.getConnection (); pstmt = conn.prepareStatement (sql); pstmt.setString (1, userInfo.getSex ()); rs = pstmt.executeQuery () If (rs.next ()) {count = rs.getInt (1);}} catch (SQLException e) {e.printStackTrace (); throw new Exception (e);} finally {DBUtility.closeResultSet (rs); DBUtility.closePreparedStatement (pstmt);} return count;}}

Finally, servlet returns the results to the jsp page for display.

Note: DBUtility code is the code that encapsulates the database connection operation, as follows:

1.package com.bjpowernode.test

Import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class DBUtility {private static ThreadLocal threadLocal = new ThreadLocal (); public static Connection getConnection () {Connection conn = null; conn = threadLocal.get (); if (conn = = null) {try {Class.forName ("oracle.jdbc.driver.OracleDriver") Conn = DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:oracle", "admin", "admin"); threadLocal.set (conn);} catch (ClassNotFoundException e) {e.printStackTrace ();} catch (SQLException e) {e.printStackTrace ();}} return conn;} / / Encapsulation settings Connection auto commit public static void setAutoCommit (Connection conn, Boolean flag) {try {conn.setAutoCommit (flag) } catch (SQLException e) {e.printStackTrace ();}} / set transaction commit public static void commit (Connection conn) {try {conn.commit ();} catch (SQLException e) {e.printStackTrace ();} / / Encapsulation settings Connection rollback public static void rollBack (Connection conn) {try {conn.rollback ();} catch (SQLException e) {e.printStackTrace () }} / / Encapsulation functions public static void closeConnection () {Connection conn = threadLocal.get (); try {if (conn! = null) {conn.close (); conn = null; threadLocal.remove ();}} catch (SQLException e) {e.printStackTrace ();}} public static void closePreparedStatement (PreparedStatement pstmt) {try {if (pstmt! = null) {pstmt.close (); pstmt = null } catch (SQLException e) {e.printStackTrace ();}} public static void closeResultSet (ResultSet rs) {try {if (rs! = null) {rs.close (); rs = null;}} catch (SQLException e) {e.printStackTrace ();}

ThreadLocal is used to ensure transaction consistency, so that all database operations on the same thread use the same Connection.

These are all the contents of the article "what are the Java pagination functions in Oracle?" Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.