MySQL 與 Oracle 分頁查詢實戰：JSP/Servlet 代碼示例與性能優化模式

MySQL 分頁實現

<%
int pageSize = 3;
String strPageNo = request.getParameter("pageNo");
int pageNo = (strPageNo == null || strPageNo.equals("")) ? 1 : Integer.parseInt(strPageNo.trim());
if (pageNo <= 0) pageNo = 1;

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/bbs?user=root&password=mingming");
Statement stCount = conn.createStatement();
ResultSet rsCount = stCount.executeQuery("SELECT COUNT(*) FROM article WHERE pid=0");
rsCount.next();
int totalRecord = rsCount.getInt(1);
int totalPage = totalRecord % pageSize == 0 ? totalRecord/pageSize : totalRecord/pageSize + 1;
if (pageNo > totalPage) pageNo = totalPage;

int startPos = (pageNo - 1) * pageSize;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM article WHERE pid=0 ORDER BY pdate DESC LIMIT " + startPos + "," + pageSize);
%>

Oracle 分頁實現

<%
int pageSize = 3;
String s_pageNow = request.getParameter("pageNow");
int pageNow = (s_pageNow == null) ? 1 : Integer.parseInt(s_pageNow);

Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "scott", "tiger");
Statement sm = ct.createStatement();
ResultSet rs = sm.executeQuery("SELECT COUNT(*) FROM emp");
rs.next();
int rowCount = rs.getInt(1);
int pageCount = rowCount % pageSize == 0 ? rowCount/pageSize : rowCount/pageSize + 1;

rs = sm.executeQuery(
  "SELECT * FROM (SELECT a1.*, ROWNUM rn FROM (SELECT * FROM emp) a1 WHERE ROWNUM <= " + pageNow*pageSize + ") WHERE rn >= " + ((pageNow-1)*pageSize+1));
%>

前端分頁導航三種模式

1. **超鏈接翻頁**：首頁/上頁/下頁/末頁 + 頁碼鏈接。
2. **下拉框跳轉**：<select onchange="document.form1.submit()">。
3. **輸入框跳轉**：<input type="text" name="pageNo"><input type="submit" value="go">。

性能優化關鍵點

• **MySQL**：大 Offset 改用「延遲關聯/覆蓋索引回表」或 Keyset 分頁（WHERE id > last_id LIMIT N）。
• **Oracle**：12c+ 直接用 OFFSET ... FETCH NEXT；舊版必須子查詢 + ROWNUM，注意排序在內層完成。
• **總頁數計算**：totalPages = (totalRecords + pageSize - 1) / pageSize（整數運算避免浮點）。
• **連接池/預編譯**：生產環境必須使用 DataSource + PreparedStatement 防注入。

代碼規範建議

• 業務邏輯與展示分離（MVC），JSP 僅渲染。
• 異常處理與資源關閉（try-with-resources）。
• 參數校驗、SQL 注入防護。