Oracle page on the Web page, oracle page on the web
The code in this example only describes how to use common Oracle paging methods on the Web. It does not involve MVC, code optimization, and so on. For more information, see.
Zookeeper -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<Body>
* ************************************ Oracle Paging important code, I learned how to operate Oracle in Java ***************************** *********** <br>
<Br>
<Form action = "/TestOracleFenye/index. jsp">
<Input type = "text" name = "pageSize">
<Input type = "submit" name = "submit" value = "set pageSize">
<Br/>
</Form>
<Table>
<Tr>
<Td> name </td>
<Td> salary </td>
</Tr>
<%
// Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
// Establish a connection
Connection conn = DriverManager. getConnection (
"Jdbc: oracle: thin: @ 192.168.1.5: 1521: BJSXT", "scott", "tiger ");
// Define the result set
ResultSet rs = null;
// The same as SqlServer from below
Statement sm = conn. createStatement ();
// Initialize the number of records on each page
Int pageSize = 20;
String s_PageSize = request. getParameter ("pageSize ");
If (s_PageSize! = Null &&! "". Equals (s_PageSize.trim ())){
PageSize = Integer. parseInt (s_PageSize );
}
// Calculate the total number of pages
Int pageCount = 0;
// Query the total number of records
Int rowCount = 0;
// Current page
Int currentPage = 1;
// Number of records whose initialization starts and ends
Int start = 1;
Int end = pageSize;
// Obtain the total number of records
Rs = sm.exe cuteQuery ("select count (*) from emp ");
While (rs. next ()){
RowCount = rs. getInt (1 );
If (rowCount % pageSize = 0 ){
PageCount = rowCount/pageSize;
} Else {
PageCount = rowCount/pageSize + 1;
}
}
// Current page number
String s_CurrentPage = request. getParameter ("currentPage ");
If (s_CurrentPage! = Null &&! "". Equals (s_CurrentPage.trim ())){
CurrentPage = Integer. parseInt (s_CurrentPage );
Start = (currentPage-1) * pageSize + 1;
End = currentPage * pageSize;
}
// Key code for paging
String fenyeSql = "select * from (select a1. *, rownum bieming from (select * from emp) a1 where rownum <=" + end + ") where bieming> =" + start;
Out. println (fenyeSql + "<br/> ");
Rs = sm.exe cuteQuery (fenyeSql );
// ## Query all employees rs = sm.exe cuteQuery ("select * from emp ");
While (rs. next ()){
Out. print ("<tr> ");
Out. print ("<td>" + rs. getString (2) + "</td> ");
Out. print ("<td>" + rs. getString (6) + "</td> ");
Out. print ("</tr> ");
}
// Print the total number of pages
For (int I = 1; I <= pageCount; I ++ ){
Out. print ("<a href = '" + basePath + "index. jsp? CurrentPage = "+ I +" & pageSize = "+ pageSize +" '> "+ I +" </a> ");
}
Rs. close ();
Sm. close ();
Conn. close ();
%>
</Table>
</Body>
Zookeeper -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1) oracle sorts data by pseudo-column rowid by default.
The oracle manual clearly states that there is no order by clause in the SELECT statement, and oracle does not guarantee that the output records of the two statements are in the same ORDER.
Select a. *, rownum rn from (select * from emp order by deptno, rowid);
Select a. *, rownum rn from (select * from emp order by deptno, rowid) a where rownum <11;
2) I am not ugly about the second problem. I am not familiar with the cursor.
Write the main SQL statement.
Select * from (select a. *, rownum rn from (select * from emp order by deptno, rowid) a where rownum <11) where rn> 5;
How Does oracle implement paging?
Paging Query format:
SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn> = 21
SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 40 and RN> = 21 control the range of each page of the paging query.
The paging query statement given above has a high efficiency in most cases. The purpose of paging is to control the size of the output result set and return the result as soon as possible. In the preceding paging query statement, this consideration is mainly reflected in the where rownum <= 40 sentence.
There are two methods to select 21st to 40 records. One is that the second layer of the query in the example above uses ROWNUM <= 40 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 40 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. Here is the query statement:
SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
)
Where rn between 21 AND 40
In most cases, the efficiency of the first query is much higher than that of the second query.
This is because in the CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the first query statement, the SQL query condition WHERE ROWNUM <= 40 can be pushed to the inner query by Oracle. In this way, once the query result of Oracle exceeds the ROWNUM limit, the query is terminated and the result is returned.
The second query statement, because the query conditions BETWEEN 21 AND 40 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.
The query analyzed above is not only a simple query for a single table, but also effective for complex multi-table joint queries or sorting in the innermost query.
The query that contains sorting is not described here, and the next article will explain in detail through examples. Next we will briefly discuss the situation of Multi-table join. For the most common equi JOIN queries, CBO generally uses two JOIN Methods: nested loop and hash join (merge join is less efficient than hash join, which is not considered by CBO in general ). Because paging is used, the maximum number of records returned is specified. When the number of returned records exceeds the maximum value, the nested loop can immediately stop and return the results to the intermediate layer, however, hash join must process all result sets (so does merge join ). In most cases, it is highly efficient to select nested loop as the query connection method for paging queries (most of the time when querying by page is the data of the first few pages, the lower the page number, the lower the chance of access ).
Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement:
SELECT/* + FIRST_ROWS */* FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 40
)
Where rn & g ...... remaining full text>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
