Jsp + oracle paging implementation program code

Last Update:2017-01-13 Source: Internet

Author: User

Tags oracle database

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Today we have implemented a jsp + oracle paging implementation, which is good for beginners (you can implement it yourself after reading it ), however, for SQL statements with a basic understanding (which does not involve good layering), let's start to implement the following:

1. Create a web project first. (As shown in the figure)

2. Import the oracle driver package to the lib directory and write the database connection class DBMamager.

The code is as follows:

Copy code

Package com. page. util;

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. PreparedStatement;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;

Public class DBManager {

Private static Connection connection = null;

Static
    {
Try {
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: orcl ","******","******"); // account and password of your oracle Database
} Catch (ClassNotFoundException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
        }
    }

Protected static Connection getConnection ()
    {
Return connection;
    }

Public int update (String SQL)
    {
// Boolean flag = false;
Int row = 0;
Connection connection = DBManager. getConnection ();
PreparedStatement statement = null;
Try
        {
Statement = connection. prepareStatement (SQL );
Row = statement.exe cuteUpdate ();
// System. out. println (SQL );
        }
Catch (SQLException e)
        {
E. printStackTrace ();
        }
Return row;
    }

Public ResultSet find (String SQL)
    {
Connection connection = getConnection ();
ResultSet result = null;

PreparedStatement statement = null;
Try
        {
System. out. println (SQL );
Statement = connection. prepareStatement (SQL );
Result = statement.exe cuteQuery ();

} Catch (SQLException e)
        {
E. printStackTrace ();
        }

Return result;
    }

}

3. The code for implementing paging is as follows (first look at the comments behind the code and don't worry too much about it)

The code is as follows:

Copy code

<% @ Page import = "com. sun. crypto. provider. RSACipher" %>
<% @ Page language = "java" import = "java. util. *" pageEncoding = "UTF-8" %>
<% @ Page import = "com. page. util. *" %>
<% @ Page import = "java. SQL. *" %>
<%
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>
<Base href = "<% = basePath %>">

<Title> User Information List </title>
<Meta http-equiv = "pragma" content = "no-cache">
<Meta http-equiv = "cache-control" content = "no-cache">
<Meta http-equiv = "expires" content = "0">
<Meta http-equiv = "keywords" content = "keyword1, keyword2, keyword3">
<Meta http-equiv = "description" content = "This is my page">
<! --
<Link rel = "stylesheet" type = "text/css" href = "styles.css">
-->
</Head>

<Body>
<Table align = "center" width = "1000px" style = "margin: 100px" border = "1" cellspacing = "0" cellpadding = "0">
<Tr align = "center" bgcolor = "#3270E5" height = "30px">
<Th> Number </th>
<Th> user account </th>
<Th> user name </th>
<Th> user password </th>
<Th> user information </th>
</Tr>
<%
Int I;
Int page_size = 3; // page unit
Int all_pages; // The total number of pages.
Int pages; // page number variable accepted
Int cur_page = 1; // current page
Int start_page; // The start of the record on this page.
Int count_row; // The total number of records.
Int end_page; // The end of the record on this page.
String SQL _row = "select count (id) as count_row from page ";
DBManager dbManager = new DBManager ();
ResultSet count_rs = dbManager. find (SQL _row );
Count_rs.next ();
Count_row = count_rs.getInt ("count_row ");
All_pages = (int) Math. ceil (count_row + page_size-1)/page_size); // calculate the total number of pages

// Determine whether the parameter pages is null
If (request. getParameter ("pages") = null ){
Pages = 1;
} Else {
Pages = new Integer (request. getParameter ("pages"). intValue ();
            }
// Determine the current page
If (pages> all_pages | pages = 0 ){
Cur_page = 1;
} Else {
Cur_page = pages;
            }
Start_page = (cur_page-1) * page_size; // number of records starting on this page (the number of records in the database)
End_page = start_page + page_size; // Number of the last entry displayed on this page
String SQL = "select * from (select rownum rn, p. * from (select * from page) p where rownum <= '"+ end_page +"') where rn> '"+ start_page + "'";

ResultSet rsSet = dbManager. find (SQL );
Int t_row = 1;
String color = "# FFFFFF ";
While (rsSet. next ()){
If (t_row % 2 = 0) {// display different colors when the table looks better than the double number of rows
Color = "# EDF5FC ";
} Else {
Color = "# FFFFFF ";
                }
%>
<Tr bgcolor = <% = color %>
<Td> <% = rsSet. getString (1) %> </td>
<Td> <% = rsSet. getString (2) %> </td>
<Td> <% = rsSet. getString (3) %> </td>
<Td> <% = rsSet. getString (4) %> </td>
<Td> <% = rsSet. getString (5) %> </td>
</Tr>
<%
T_row ++;
            }
%>
<Tr>
<Td colspan = "5" align = "right">
<% If (cur_page> 1) {%> // the previous page is not displayed on the first page.
<A href = "index. jsp? Pages = cur_page-1 %> "> Previous Page </a>
<%
            }
If (cur_page <all_pages) {// The next page is displayed when the last row is not displayed.
%>
<A href = "index. jsp? Pages = <% = cur_page + 1%> "> Next page </a>
<A href = "index. jsp? Pages = <% = all_pages %> "> Last page </a> // display the last page
<%
            }
%>
<% For (I = 1; I <= all_pages; I ++) {%> // Each page is displayed cyclically. No hyperlink (no underline) is displayed on this page)
<% If (I! = Pages) {%>
<A href = "index. jsp? Pages = <% = I %> "> <% = I %> </a>
<%} Else {%>
<% = I %>
<% }%>
<% }%>
Total <% = all_pages %> pages & nbsp;
</Td>
</Tr>
</Table>
</Body>
</Html>

4. The page has been completed. Deploy the tomcat running website! (As shown in the figure)

Page 2:

Page 3:

Page 4:

Note:

Calculate the total number of pages: all_pages = (int) Math. ceil (count_row + page_size-1)/page_size); // calculate the total number of pages

The code is as follows:

Copy code

The SQL statement is: String SQL = "select * from (select rownum rn, p. * from (select * from page) p where rownum <= '"+ end_page +"') where rn> '"+ start_page + "'";

Example: select *
From
(
Select rownum rn, p .*
From
(Select *
From page order by id
) P where rownum <= 4
) Where rn> 3; // use a pseudo column !!

Finally, I attached the SQL code:

Create table page
(
Id varchar2 (6) not null,
Username varchar2 (20) not null,
Password varchar2 (20) not null,
Info varchar2 (200) default 'Hello, everyone! ',
Constraints pk_id primary key (id)
);

Select * from page;
Delete page;
Drop table page;

Insert into page (id, username, password) values ('20140901', 'huangke', '20160901 ');
Insert into page (id, username, password, info) values ('000000', 'shaow', '000000', 'I'm JJ, Lin Junjie! ');
Insert into page (id, username, password) values ('2013', 'Shen Junjie ', 'qqqqqq ');
Insert into page (id, username, password, info) values ('000000', 'Yang Xiaoyu ', '000000', 'My shift leader! ');
Insert into page (id, username, password) values ('2013', 'Xu Shiqun ', 'xxxxxx ');
Insert into page (id, username, password, info) values ('000000', 'Wang dongbao', '000000', 'Grandpa! ');
Insert into page (id, username, password, info) values ('201312', 'admin', 'admin', 'My postmaster! ');
Insert into page (id, username, password, info) values ('123456', 'Liu Peng', '123456', 'I love games! ');
Insert into page (id, username, password, info) values ('000000', 'Liu Yongjun ', 'liu666',' I am a little late haha! ');
Update page set info = 'I'm a young man, haha !! 'Where id = '2013 ';
Select rownum, p. * from page p where rownum between 1 and 4;
Select count (id) as a from page;
Select count (id) as count_row from page;
Select *
From
(
Select rownum rn, p .*
From
(Select *
From page order by id
) P where rownum <= 4
) Where rn> 3;

Select *
From
(
Select rownum rn, p .*
From
(Select *
From page) p where rownum <= 6
) Where rn> 3

1. Here our task has been completed. If you have any questions, contact me QQ: 541817557 (communication ).

2. At the same time, I hope other people can give me some stratified opinions.

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More