Basic Concepts of BBS

After accessing the Scott account with SQL*PLUS, use the following script to create the table and sequence for the bulletin board.

board-schema.sql
create table board(
 no number primary key,
 title varchar2(200) not null,
 content varchar2(4000),
 wdate date
)
/
create sequence board_no_seq
start with 1
increment by 1
/

To use the board-schema.sql file, access SQL*PLUS with the Scott account, type @ followed by the full path to the board-schema.sql file. For example, if there is the board-schema.sql file in C:\, execute it as follows.

C:\ Command Prompt 
SQL>@C:\board-schema.sql

Run the following insert statements while logged in to SQL * PLUS with the Scott account.

board-data.sql
insert into board values (board_no_seq.nextval, '000001','',sysdate);
insert into board values (board_no_seq.nextval, '000002','',sysdate);
insert into board values (board_no_seq.nextval, '000003','',sysdate);
insert into board values (board_no_seq.nextval, '000004','',sysdate);
insert into board values (board_no_seq.nextval, '000005','',sysdate);
insert into board values (board_no_seq.nextval, '000006','',sysdate);
insert into board values (board_no_seq.nextval, '000007','',sysdate);
insert into board values (board_no_seq.nextval, '000008','',sysdate);
insert into board values (board_no_seq.nextval, '000009','',sysdate);
insert into board values (board_no_seq.nextval, '000010','',sysdate);
insert into board values (board_no_seq.nextval, '000011','',sysdate);
insert into board values (board_no_seq.nextval, '000012','',sysdate);
insert into board values (board_no_seq.nextval, '000013','',sysdate);
insert into board values (board_no_seq.nextval, '000014','',sysdate);
insert into board values (board_no_seq.nextval, '000015','',sysdate);
insert into board values (board_no_seq.nextval, '000016','',sysdate);
insert into board values (board_no_seq.nextval, '000017','',sysdate);
insert into board values (board_no_seq.nextval, '000018','',sysdate);
insert into board values (board_no_seq.nextval, '000019','',sysdate);
insert into board values (board_no_seq.nextval, '000020','',sysdate);
insert into board values (board_no_seq.nextval, '000021','',sysdate);
insert into board values (board_no_seq.nextval, '000022','',sysdate);
insert into board values (board_no_seq.nextval, '000023','',sysdate);
insert into board values (board_no_seq.nextval, '000024','',sysdate);
insert into board values (board_no_seq.nextval, '000025','',sysdate);
insert into board values (board_no_seq.nextval, '000026','',sysdate);
insert into board values (board_no_seq.nextval, '000027','',sysdate);
insert into board values (board_no_seq.nextval, '000028','',sysdate);
insert into board values (board_no_seq.nextval, '000029','',sysdate);
insert into board values (board_no_seq.nextval, '000030','',sysdate);
insert into board values (board_no_seq.nextval, '000031','',sysdate);
insert into board values (board_no_seq.nextval, '000032','',sysdate);
insert into board values (board_no_seq.nextval, '000033','',sysdate);
insert into board values (board_no_seq.nextval, '000034','',sysdate);
insert into board values (board_no_seq.nextval, '000035','',sysdate);
insert into board values (board_no_seq.nextval, '000036','',sysdate);
insert into board values (board_no_seq.nextval, '000037','',sysdate);
insert into board values (board_no_seq.nextval, '000038','',sysdate);
insert into board values (board_no_seq.nextval, '000039','',sysdate);
insert into board values (board_no_seq.nextval, '000040','',sysdate);
insert into board values (board_no_seq.nextval, '000041','',sysdate);
insert into board values (board_no_seq.nextval, '000042','',sysdate);
insert into board values (board_no_seq.nextval, '000043','',sysdate);
insert into board values (board_no_seq.nextval, '000044','',sysdate);
insert into board values (board_no_seq.nextval, '000045','',sysdate);
insert into board values (board_no_seq.nextval, '000046','',sysdate);
insert into board values (board_no_seq.nextval, '000047','',sysdate);
insert into board values (board_no_seq.nextval, '000048','',sysdate);
insert into board values (board_no_seq.nextval, '000049','',sysdate);
insert into board values (board_no_seq.nextval, '000050','',sysdate);
insert into board values (board_no_seq.nextval, '000051','',sysdate);
insert into board values (board_no_seq.nextval, '000052','',sysdate);
insert into board values (board_no_seq.nextval, '000053','',sysdate);
insert into board values (board_no_seq.nextval, '000054','',sysdate);
insert into board values (board_no_seq.nextval, '000055','',sysdate);
insert into board values (board_no_seq.nextval, '000056','',sysdate);
insert into board values (board_no_seq.nextval, '000057','',sysdate);
insert into board values (board_no_seq.nextval, '000058','',sysdate);
insert into board values (board_no_seq.nextval, '000059','',sysdate);
insert into board values (board_no_seq.nextval, '000060','',sysdate);
insert into board values (board_no_seq.nextval, '000061','',sysdate);
insert into board values (board_no_seq.nextval, '000062','',sysdate);
insert into board values (board_no_seq.nextval, '000063','',sysdate);
insert into board values (board_no_seq.nextval, '000064','',sysdate);
insert into board values (board_no_seq.nextval, '000065','',sysdate);
insert into board values (board_no_seq.nextval, '000066','',sysdate);
insert into board values (board_no_seq.nextval, '000067','',sysdate);
insert into board values (board_no_seq.nextval, '000068','',sysdate);
insert into board values (board_no_seq.nextval, '000069','',sysdate);
insert into board values (board_no_seq.nextval, '000070','',sysdate);
insert into board values (board_no_seq.nextval, '000071','',sysdate);
insert into board values (board_no_seq.nextval, '000072','',sysdate);
insert into board values (board_no_seq.nextval, '000073','',sysdate);
insert into board values (board_no_seq.nextval, '000074','',sysdate);
insert into board values (board_no_seq.nextval, '000075','',sysdate);
insert into board values (board_no_seq.nextval, '000076','',sysdate);
insert into board values (board_no_seq.nextval, '000077','',sysdate);
insert into board values (board_no_seq.nextval, '000078','',sysdate);
insert into board values (board_no_seq.nextval, '000079','',sysdate);
insert into board values (board_no_seq.nextval, '000080','',sysdate);
insert into board values (board_no_seq.nextval, '000081','',sysdate);
insert into board values (board_no_seq.nextval, '000082','',sysdate);
insert into board values (board_no_seq.nextval, '000083','',sysdate);
insert into board values (board_no_seq.nextval, '000084','',sysdate);
insert into board values (board_no_seq.nextval, '000085','',sysdate);
insert into board values (board_no_seq.nextval, '000086','',sysdate);
insert into board values (board_no_seq.nextval, '000087','',sysdate);
insert into board values (board_no_seq.nextval, '000088','',sysdate);
insert into board values (board_no_seq.nextval, '000089','',sysdate);
insert into board values (board_no_seq.nextval, '000090','',sysdate);
insert into board values (board_no_seq.nextval, '000091','',sysdate);
insert into board values (board_no_seq.nextval, '000092','',sysdate);
insert into board values (board_no_seq.nextval, '000093','',sysdate);
insert into board values (board_no_seq.nextval, '000094','',sysdate);
insert into board values (board_no_seq.nextval, '000095','',sysdate);
insert into board values (board_no_seq.nextval, '000096','',sysdate);
insert into board values (board_no_seq.nextval, '000097','',sysdate);
insert into board values (board_no_seq.nextval, '000098','',sysdate);
insert into board values (board_no_seq.nextval, '000099','',sysdate);
insert into board values (board_no_seq.nextval, '000100','',sysdate);
commit;

Or execute the following PL/SQL statement. 

DECLARE
  counter INTEGER;
BEGIN
  counter := 1;
  FOR counter IN 1..100 LOOP
    insert into board values (board_no_seq.nextval, LPAD(board_no_seq.currval, 6, 0),'',sysdate); 
  END LOOP;
END;
/

The table below shows files for the bulletin board system.

list.jsp Shows list of posts
write_form.jsp Post Writing Form
BoardWriter.java Servlet to insert a post into the database table
view.jsp Show the details of the post
modify_form.jsp Post Modify Form
BoardModifier.java Servlet to update the post data in the database table
BoardDeleter.java Servlet to delete the post data in the database table

The flow of the bulletin board program is as follows. 

list.jsp → write_form.jsp → BoardWriter.java (Run insert) → list.jsp
  └── view.jsp
        └── modify_form.jsp → BoardModifier.java (Rnu update) → view.jsp
        └── BoardDeleter.java (Run delete) → list.jsp

Let's create all JSP files in the subdirectory named board under the root directory of the ROOT application.

We practiced a custom connection pooling example, and its bytecode exists in WEB-INF/classes in the ROOT application. Also, we practiced a listner example that the OracleConnectionManager in the ServletContext when the web application starts. (MyServletContextListener.java) This listener is running in the ROOT application.

List and Writing

The first step of the list page shows all the records.

list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>List</title>
</head>
<body>
<h1>List</h1>
<%
Log log = new Log();

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

String sql = "SELECT no, title, wdate FROM board ORDER BY no DESC";

try {
  con = dbmgr.getConnection();

  stmt = con.prepareStatement(sql);
  rs = stmt.executeQuery();

  while (rs.next()) {
    int no = rs.getInt("no");
    String title = rs.getString("title");
    Date wdate = rs.getDate("wdate");
%>
<%=no %> <a href="view.jsp?no=<%=no %>"><%=title %></a> <%= wdate.toString() %><br />
<hr />
<%
  }
} catch(SQLException e) {
  log.debug("Error Source: board/list.jsp : SQLException");
  log.debug("SQLState: " + e.getSQLState());
  log.debug("Message: " + e.getMessage());
  log.debug("Oracle Error Code: " + e.getErrorCode());
  log.debug("sql: " + sql);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (con != null) {
    dbmgr.freeConnection(con);
  }
  log.close();
}
%>
<p>
<a href="write_form.jsp">Add New Post</a>
</p>
</body>
</html>
write_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
  pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Add New Post</title>
</head>
<body>
<h1>Add New Post</h1>
<form action="../servlet/BoardWriter" method="post">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50"></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="20" cols="100"></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="list.jsp">List</a>
  </td>
</tr>
</table>
</form>  
</body>
</html>
BoardWriter.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardWriter extends HttpServlet {
  private static final long serialVersionUID = 5698354994510824246L;
	
  OracleConnectionManager dbmgr = null;

  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    String title = req.getParameter("title");
    String content = req.getParameter("content");
		
    Connection con = dbmgr.getConnection();
    PreparedStatement stmt = null;
    String sql = "INSERT INTO board VALUES (board_no_seq.nextval, ?, ?, sysdate)";
		
    try {
      stmt = con.prepareStatement(sql);
      stmt.setString(1, title);
      stmt.setString(2, content);
      stmt.executeUpdate();
    } catch (SQLException e) {
      log.debug("Error Source: BoardWriter.java: SQLException");
      log.debug("SQLState: " + e.getSQLState());
      log.debug("Message: " + e.getMessage());
      log.debug("Oracle Error Code: " + e.getErrorCode());
      log.debug("sql: " + sql);
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      resp.sendRedirect(path + "/board/list.jsp");
    }
  }
}

Register this servlet in web.xml and set the URI mapping to /servlet/BoardWriter.

View Post

view.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
  pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>View Post</title>
<script type="text/javascript">
function goModify(no) {
  location.href="modify_form.jsp?no=" + no;
}

function goDelete(no) {
  var check = confirm('Are you sure you want to delete it?');
  if (check) {
    location.href="../servlet/BoardDeleter?no=" + no;
  }
}
</script>
</head>
<body>
<h1>View Post</h1>
<%
int no = Integer.parseInt(request.getParameter("no"));
Log log = new Log();
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String sql = "SELECT no, title, content, wdate FROM board WHERE no = ?";
try {
  con = dbmgr.getConnection();
  stmt = con.prepareStatement(sql);
  stmt.setInt(1, no);
  rs = stmt.executeQuery();
	
  while (rs.next()) {
    String title = rs.getString("title");
    String content = rs.getString("content");
    Date wdate = rs.getDate("wdate");
    if (content == null) content = "";
%>
<h2>Title: <%=title %>, Date Created: <%=wdate.toString() %></h2>
<p>
<%=content = content.replaceAll(System.getProperty("line.separator"), "<br />") %>
</p>
<%
  }
} catch (SQLException e) {
  log.debug("Error Source : board/view.jsp: SQLException");
  log.debug("SQLState: " + e.getSQLState());
  log.debug("Message: " + e.getMessage());
  log.debug("Oracle Error Code: " + e.getErrorCode());
  log.debug("sql: " + sql);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (con != null) {
    dbmgr.freeConnection(con);
  }
  log.close();
}
%>
<a href="list.jsp">List</a>
<input type="button" value="Edit Post" onclick="javascript:goModify('<%=no %>')">
<input type="button" value="Delete" onclick="javascript:goDelete('<%=no %>')">
</body>
</html>

Edit Post

modify_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
  pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<%
int no = Integer.parseInt(request.getParameter("no"));

Log log = new Log();

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

String title = null;
String content = null;
String sql = "SELECT title, content FROM board WHERE no = ?";

try {
  con = dbmgr.getConnection();
  stmt = con.prepareStatement(sql);
  stmt.setInt(1, no);
  rs = stmt.executeQuery();
	
  if (rs.next()) {
    title = rs.getString("title");
    content = rs.getString("content");
    if (content == null) content = "";
  }
} catch (SQLException e) {
  log.debug("Error Source: board/modify_form.jsp: SQLException");
  log.debug("SQLState: " + e.getSQLState());
  log.debug("Message: " + e.getMessage());
  log.debug("Oracle Error Code: " + e.getErrorCode());
  log.debug("sql: " + sql);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (con != null) {
    dbmgr.freeConnection(con);
  }
  log.close();
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Edit Post</title>
</head>
<body>
<h1>Edit Post</h1>
<form action="../servlet/BoardModifier" method="post">
<input type="hidden" name="no" value="<%=no %>">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50" value="<%=title %>" /></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="30" cols="100"><%=content %></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="view.jsp?no=<%=no %>">View Post</a>
  </td>
</tr>
</table>
</form>
</body>
</html>
BoardModifier.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardModifier extends HttpServlet {
  
  OracleConnectionManager dbmgr = null;
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
	
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int no = Integer.parseInt(req.getParameter("no"));
    String title = req.getParameter("title");
    String content = req.getParameter("content");
		
    Connection con = dbmgr.getConnection();
    PreparedStatement stmt = null;
		
    String sql = "UPDATE board SET title = ?, content = ? WHERE no = ?";
		
    try {
      stmt = con.prepareStatement(sql);
      stmt.setString(1, title);
      stmt.setString(2, content);
      stmt.setInt(3, no);
      stmt.executeUpdate();
    } catch (SQLException e) {
      log.debug("Error Source: BoardModifier.java: SQLException");
      log.debug("SQLState: " + e.getSQLState());
      log.debug("Message: " + e.getMessage());
      log.debug("Oracle Error Code: " + e.getErrorCode());
      log.debug("sql: " + sql);
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      resp.sendRedirect( path + "/board/view.jsp?no=" + no);
    }
  }
}

Register this servlet in web.xml and set the URI mapping to /servlet/BoardModifier.

Delete Post

BoardDeleter.java
package net.java_school.board;

import java.io.*;

import javax.servlet.*;
import javax.servlet.http.*;

import java.sql.*;

import net.java_school.db.dbpool.*;
import net.java_school.util.*;

public class BoardDeleter extends HttpServlet {

  OracleConnectionManager dbmgr = null;
	
  @Override
  public void init() throws ServletException {
    ServletContext sc = getServletContext();
    dbmgr = (OracleConnectionManager)sc.getAttribute("dbmgr");
  }
	
  @Override
  public void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    doPost(req, resp);
  }
	
  @Override
  public void doPost(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
		
    req.setCharacterEncoding("UTF-8");
    Log log = new Log();
		
    int no = Integer.parseInt(req.getParameter("no"));
		
    Connection con = dbmgr.getConnection();
    PreparedStatement stmt = null;
    String sql = "delete board where no = ?";
		
    try {
      stmt = con.prepareStatement(sql);
      stmt.setInt(1, no);
      stmt.executeUpdate();
    } catch (SQLException e) {
      log.debug("Error Source: BoardDeleter.java: SQLException");
      log.debug("SQLState: " + e.getSQLState());
      log.debug("Message: " + e.getMessage());
      log.debug("Oracle Error Code: " + e.getErrorCode());
      log.debug("sql: " + sql);
    } finally {
      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (con != null) {
        dbmgr.freeConnection(con);
      }
      log.close();
      String path = req.getContextPath();
      resp.sendRedirect(path + "/board/list.jsp");
    }
  }
}

Register this servlet in web.xml and set the URI mapping to /servlet/BoardDeleter.

Splitting a list page

If you have a lot of data, you need to show the records in groups without showing them all.

Queries for grouping records

If you are grouping records by 10, the Oracle query statement that fetches each group is as follows:

Group 1
SELECT no,title,wdate FROM (
  SELECT ROWNUM R, A.* FROM (select no, title, wdate
    FROM board ORDER BY no DESC) A)
WHERE R BETWEEN 1 and 10;
Group 2
SELECT no,title,wdate FROM (
  SELECT ROWNUM R, A.* FROM (select no, title, wdate
    FROM board ORDER BY no DESC) A)
WHERE R BETWEEN 11 and 20;
Group 3
SELECT no,title,wdate FROM (
  SELECT ROWNUM R, A.* FROM (select no, title, wdate
    FROM board ORDER BY no DESC) A)
WHERE R BETWEEN 21 and 30;

The ROWNUM is a Pseudocolumn. In the query result, the ROWNUM returns a number indicating the order in which Oracle selects the row from a table.

If you use ROWNUM in the condition of the WHERE clause, you can extract records corresponding to a group when requesting the list page. You need to pass the record group number as a parameter to the list page to do this. Also, you need to implement the list page to get the starting record number and the last record number of ROWNUM corresponding to the group with the parameter.

Let's specify the number of the record group that the user sees as the curPage (meaning the current page) parameter.

Modify the list page referring to the following code.

list.jsp
<%

// .. Omit ..

int curPage = (request.getParameter("curPage") == null) ? 1 : Integer.parseInt(request.getParameter("curPage"));
// Start ROWNUM  
int start = (curPage - 1) * 10 + 1;
// End ROWNUM
int end = start + 10 - 1;

// .. Omit ..

String sql = "SELECT no,title,wdate FROM (" + 
    "SELECT ROWNUM R, A.* FROM (SELECT no, title, wdate " + 
    "FROM board ORDER BY no DESC) A) " + 
  "WHERE R BETWEEN ? AND ?";
      
stmt = con.prepareStatement(sql);
stmt.setInt(1, start);
stmt.setInt(2, end);
rs = pstmt.executeQuery();

// .. Omit ..

%>

If you request http://localhost:8080/board/list.jsp?curPage=1, you can see group 1. If you request http://localhost:8080/board/list.jsp?curPage=2, you can see group 2. However, it is inconvenient to change the curPage parameter in the address bar of a web browser. A typical bulletin board we know of provides links to move a page, as shown below. 

<a href="list.jsp?curPage=1">[1]</a>

If you know the last page number, you can use a loop statement to create links from 1 to the last page. Since the page number starts at 1, the final page number equals the total number of pages. Thus, dividing the total number of records by 10 gives you the total number of pages. (Here 10 is the number of records per page)

Add the following code snippets to the appropriate location in list.jsp. 

int totalRecord = 0;
String sql = "SELECT count(*) FROM board";
stmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
rs.next();
totalRecord = rs.getInt(1);

int totalPage = 0;

if (totalRecord != 0) {
  if (totalRecord % 10 == 0) {
    totalPage = totalRecord / 10;
  } else {
    totalPage = totalRecord / 10 + 1;
  }
}

Now we get the total number of pages, the last page number. At this point, We may refine the code to get ROWNUMs of start and final record more elegantly by declaring a variable named numPerPage, which stores the number of records per page.

Modify the list.jsp referring to the code below. 

int numPerPage = 10; //the number of records per page
int start = (curPage - 1) * numPerPage + 1; //the ROWNUM start record
int end = start + numPerPage - 1; //the ROWNUM end record

Change the code to getting the total number of pages like below. 

int totalPage = 0;

if (totalRecord != 0) {
  if (totalRecord % numPerPage == 0) {
    totalPage = totalRecord / numPerPage;
  } else {
    totalPage = totalRecord / numPerPage + 1;
  }	
}

Now that we've got the total number of pages, we can produce page links.

Add the following code to the bottom of the list.jsp. 

<%
for (int i = 1; i <= totalPage; i++) {
%>
  <a href="list.jsp?curPage=<%=i%>">[<%=i%>]</a>
<%
}
%>

The page partitioning algorithm is summarized as follows.

  1. Get the total number of records.
  2. Specify the number of records per page and obtain the total number of pages.
  3. Get the first ROWNUM and the last ROWNUM of the group.

The following is the whole source of the list page.

list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>List <%=curPage %> page</title>
</head>
<body style="font-size: 11px;">
<h1>List <%=curPage %> page</h1>
<%
Log log = new Log();

Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;

String sql = null;

//1. Get the total number of records.
int totalRecord = 0;
try {
  con = dbmgr.getConnection();
  sql = "SELECT count(*) FROM board";
  stmt = con.prepareStatement(sql);
  rs = stmt.executeQuery();
  rs.next();
  totalRecord = rs.getInt(1);
} catch (SQLException e) {
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (con != null) {
    dbmgr.freeConnection(con);
  }	
}

//2. Set the number of records per page and obtain the total number of pages.
int numPerPage = 10; //Set the number of records per page
int totalPage = 0;
if (totalRecord != 0) {
  if (totalRecord % numPerPage == 0) {
    totalPage = totalRecord / numPerPage;
  } else {
    totalPage = totalRecord / numPerPage + 1;
  }
}

//3. Find the first ROWNUM number and the last ROWNUM number of the group.
int curPage = request.getParameter("curPage") == null ? 1 : Integer.parseInt(request.getParameter("curPage"));

//first ROWNUM 
int start = (curPage - 1) * numPerPage + 1;
//last ROWNUM
int end = start + numPerPage - 1;

try {
  con = dbmgr.getConnection();
  sql="SELECT no,title,wdate FROM (" +
        "SELECT ROWNUM R, A.* FROM (" + 
          "SELECT no, title, wdate FROM board ORDER BY no DESC) A) " +
      "WHERE R BETWEEN ? AND ?";
	
  stmt = con.prepareStatement(sql);
  stmt.setInt(1, start);
  stmt.setInt(2, end);
  rs = stmt.executeQuery();

  while (rs.next()) {
    int no = rs.getInt("no");
    String title = rs.getString("title");
    Date wdate = rs.getDate("wdate");
%>
<%=no %> <a href="view.jsp?no=<%=no %>"><%=title %></a> <%= wdate.toString() %><br />
<hr />
<%
  }
} catch(SQLException e) {
  log.debug("Error Source: board/list.jsp's SQLException");
  log.debug("SQLState: " + e.getSQLState());
  log.debug("Message: " + e.getMessage());
  log.debug("Oracle Error Code: " + e.getErrorCode());
  log.debug("sql: " + sql);
} finally {
  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (stmt != null) {
    try {
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  if (con != null) {
    dbmgr.freeConnection(con);
  }
  log.close();
}

//4. Create page links.
for (int i = 1; i <= totalPage; i++) {
%>
  <a href="list.jsp?curPage=<%=i %>">[<%=i %>]</a>
<%
}
%>
<p>
<a href="write_form.jsp?curPage=<%=curPage %>">Add New Post</a>
</p>
</body>
</html>

Visit the list page and click a page link to test it.

Why can not we use a variables named page in JSP scripting?
When a servlet container converts JSP to a servlet, it generates the following code and adds it to the servlet's service method. 
Object page = this;
Oracle 11g's rank() function
You can use the rank() function from Oracle 11g. For example, the following is a query statement that sorts the employee data in salary order in the emp table of the Scott account. 
SELECT empno,ename,sal,rank() over (order by sal desc) as rank FROM emp;
With this function, you can change the query statement corresponding to the bulletin board list as follows. 
SELECT no,title,wdate 
FROM (
  SELECT rank() over (order by no desc) R,no,title,wdate FROM board
) 
WHERE R BETWEEN 1 and 10;

Limit the number of page links

There is also a problem with the page partitioning above. If there are 10000 records and numPerPage is 10, 1000 page links are generated as [1] [2] [3] ...... [999] [1000]. One thousand page links will ruin the web design. The solution is to divide the page links into groups as well. Let's call this feature Limit the number of page links.

Divide page links into groups

Suppose you restrict the number of page links to five. Links [1] [2] [3] [4] [5] will belong to group 1, and links [6] [7] [8] [9] [10] will belong to group 2. Divide page links into groups If the page group unit is called pagePerBlock and the page group to which curPage belongs is called block, the following code calculates the block's value. 

//Declaring and initializing named block variable. 
int block = 1;

//Declaring and initializing named pagePerBlock variable.
int pagePerBlock = 5;

if (curPage % pagePerBlock == 0) {
  block = curPage / pagePerBlock;
} else {
  block = curPage / pagePerBlock + 1;
}

The following code can obtain the first page and last page number belonging to the block. 

//The first page belonging to the block 
int firstPage = (block - 1) * pagePerBlock + 1;

//The last page belonging to the block
int lastPage =  block * pagePerBlock;

Use the loop statement to create links from the first page to the last page. 

<%
for (int i = firstPage; i <= lastPage; i++) {
%>
  <a href="list.jsp?curPage=<%=i%>">[<%=i%>]</a>
<%
}
%>

Group page links

Obtaining the page group to which the current page belongs and showing only page links belonging to the page group succeeded. But there is no way to move to another page group. The most common solution is to create links to move to an adjacent page group. If the block is greater than 1, Creates a [<] link and link "list.jsp?curPage=<%=firstPage-1 %>" to that. If the block is smaller than the total number of blocks (the total number of blocks is equal to the last block number), Create a [>] link and link "list.jsp?curPage=<%=lastPage+1 %>"to that. These links allow you to navigate to adjacent page groups. To create a [>] link, you need to get the total number of blocks. 

int totalBlock = 0;

if (totalPage > 0) { 
  if (totalPage % pagePerBlock == 0) {
    totalBlock = totalPage / pagePerBlock;
  } else {
    totalBlock = totalPage / pagePerBlock + 1;
  }
}

You can generate the [<] and [>] links with the following code. 

<%
int prevPage = 0;
if(block > 1) {
  prevPage = firstPage - 1;
%>
  <a href="list.jsp?curPage=<%=prevPage %>">[<]</a>
<%
}
%>

<%
if(block < totalBlock) {
  int nextPage = lastPage + 1;
%>
  <a href="list.jsp?curPage=<%=nextPage %>">[>]</a>
<%
}
%>

Group pages

Group pages

There are still bugs. After adding the records so that the total number of records is 101, move to the last block using the [>] link.

Group pages

We can confirme that unnecessary page links ([12] [13] [14] [15]) in the last block. If numPerPage is 10 when the record is 101, totalPage is 11. And if pagePerBlock is 5, then totalBlock is 3. The last page number in the last block is calculated as 15 so that our code generates unnecessary links [12] [13] [14] [15]. To prevent this, we should set the last page number in the last block to the total number of pages.

Add the following code before the loop statement that prints the page links: 

if (block >= totalBlock) {
  lastPage = totalPage;
}

Limit the number of page links algorithm

  1. Specify the number of page links per block.
  2. Calculate the total number of blocks.
  3. Calculate the block to which the current page belongs.
  4. Calculate the first and last page to link in the block.
  5. Remove unnecessary pages if the block is the last one.
  6. If block > 1, create a [<] link to go to firstPage - 1.
  7. Use the loop statement to create links from the first page of the block to the last page of the block.
  8. If block > totalBlcok, creates a [>] link go to lastPage + 1.

Pass the curPage parameter to board components

After visiting view.jsp from list.jsp?curPage=5 and clicking the "List" link in view.jsp, you will be visiting list.jsp not list.jsp?curPage=5. It is common sense if you saw the View a post on page 5 and go back to page 5 when you click the "List" link. To do so, you have to pass the curPage parameter to all components associated with the bulletin board, except for the servlet that registers a new post.

Modify the following query strings of the list page as shown below. 

<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>"><%=title %></a> <%= wdate.toString() %>

<a href="write_form.jsp?curPage=<%=curPage %>">Add New Post</a>

Modify the view.jsp referring to the highlighted part below.

/board/view.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<%
int no = Integer.parseInt(request.getParameter("no"));
String curPage = request.getParameter("curPage");
%>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>View Post</title>
<script type="text/javascript">
function goModify(no,curPage) {
  location.href="modify_form.jsp?no=" + no + "&curPage=" + curPage;
}

function goDelete(no,curPage) {
  var check = confirm('Are you sure you want to delete it?');
  if (check) {
    location.href="../servlet/BoardDeleter?no=" + no + "&curPage=" + curPage;
  }
}
</script>
</head>
<body>
<h1>View Post</h1>

.. Omit ..

<a href="list.jsp?curPage=<%=curPage %>">List</a>
<input type="button" value="Edit Post" onclick="javascript:goModify('<%=no %>','<%=curPage %>')">
<input type="button" value="Delete Post" onclick="javascript:goDelete('<%=no %>','<%=curPage %>')">
</body>
</html>

Open the write_form.jsp file and modify the link to the list. But, do not change the form action property to ../servlet/BoardWriter?curPage=<%=curPage%>. Because new posts, not replies, are always visible on the first page of the list. If you register a new article, it is correct to go to the first page of the list.

write_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
String curPage = request.getParameter("curPage");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Add New Post</title>
</head>
<body>
<h1>Add New Post</h1>
<form action="../servlet/BoardWriter" method="post">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50"></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="20" cols="100"></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="list.jsp?curPage=<%=curPage %>">List</a>
  </td>
</tr>
</table>
</form>  
</body>
</html>
modify_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<%
int no = Integer.parseInt(request.getParameter("no"));
String curPage = request.getParameter("curPage");

Log log = new Log();


//.. Omit ..

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>Edit Post</title>
</head>
<body>
<h1>Edit Post</h1>
<form action="../servlet/BoardModifier" method="post">
<input type="hidden" name="no" value="<%=no %>">
<input type="hidden" name="curPage" value="<%=curPage %>">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50" value="<%=title %>" /></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="30" cols="100"><%=content %></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>">View Post</a>
  </td>
</tr>
</table>
</form>
</body>
</html>
BoardModifier.java
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  req.setCharacterEncoding("UTF-8");
  Log log = new Log();
	
  int no = Integer.parseInt(req.getParameter("no"));
  String curPage = req.getParameter("curPage");
	
  //.. Omit ..
				
  String path = req.getContextPath();
  resp.sendRedirect(path + "/board/view.jsp?no=" + no + "&curPage=" + curPage);
	
}
BoardDeleter.java
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {
	
  req.setCharacterEncoding("UTF-8");
  Log log = new Log();
	
  int no = Integer.parseInt(req.getParameter("no"));
  String curPage = req.getParameter("curPage");
		
  //.. Omit ..
	
  String path = req.getContextPath();
  resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage);
	
}

Web programmers should be familiar with paging.

Search

Add the following code at the bottom of list.jsp. 

<form action="list.jsp" method="post">
  <input type="text" size="10" maxlength="30" name="keyword" />
  <input type="submit" value="Search" />
</form>

Now you need to pass the keyword parameter to the list page in addition to curPage. If the received keyword is null, you better replace it with empty character.

Add the following code to the list page. 

String keyword = request.getParameter("keyword");
if (keyword == null) keyword = "";

Now that the total number of records changes according to the search condition. Therefore, modify the part of obtaining the total records on list page. 

if (keyword.equals("")) {
  sql = "SELECT count(*) FROM board";
} else {
  sql = "SELECT count(*) FROM board " +
    "WHERE title LIKE '%" + keyword + "%' " + 
    "OR content LIKE '%" + keyword + "%'";
}

Also, modify the query to retrieve records on the list page. 

if (keyword.equals("")) {
  sql = "SELECT no,title,wdate " + 
    "FROM (SELECT ROWNUM R, A.* FROM (" +
    "SELECT no,title,wdate FROM board ORDER BY no DESC) A) " +
    "WHERE R BETWEEN ? AND ?";
} else {
  sql = "SELECT no,title,wdate " +
    "FROM (SELECT ROWNUM R, A.* FROM (" +
    "SELECT no,title,wdate FROM board " +
    "WHERE title LIKE '%" + keyword + "%' OR content LIKE '%" + keyword + "%' " +
    "ORDER BY no DESC) A) " +
    "WHERE R BETWEEN ? AND ?";
}

When you click the 2th-page link at the bottom of the list page of search results, the system takes you to the 2nd page of the unscanned list. To fix this, add the keyword parameter to the list links on the list page as below. 

<a href="list.jsp?curPage=<%=prevPage %>&keyword=<%=keyword %>">[<]</a>

<a href="list.jsp?curPage=<%=i %>&keyword=<%=keyword %>">[<%=i %>]</a>

<a href="list.jsp?curPage=<%=nextPage %>&keyword=<%=keyword %>">[>]</a>

When you move to the detailed view or new post write form page from the scanned list and then use the [List] link to return to the list, you will return to the unscanned list. To fix this, you need to add the keyword parameter to the following links on the list page. 

<a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>"><%=title %></a> <%= wdate.toString() %>

<a href="write_form.jsp?curPage=<%=curPage %>&keyword=<%=keyword %>">Add New Post</a>

The detailed view page receives the curPage and keyword parameters. Modify the detailed view page to use the curPage and keyword parameters when returning to the list page. Other components of the bulletin board, except for the servlet that inserts new post data into the database, also need the keyword parameter to return users to the list page or detailed view page.

/board/view.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<%
request.setCharacterEncoding("UTF-8");
int no = Integer.parseInt(request.getParameter("no"));
String curPage = request.getParameter("curPage");
String keyword = request.getParameter("keyword");
%>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<title>View Post</title>
<script type="text/javascript">
function goModify(no,curPage,keyword) {
  location.href="modify_form.jsp?no=" + no + "&curPage=" + curPage + "&keyword=" + keyword;
}

function goDelete(no,curPage,keyword) {
  var check = confirm('Are you sure you want to delete it?');
  if (check) {
    location.href="../servlet/BoardDeleter?no=" + no + "&curPage=" + curPage + "&keyword=" + keyword;
  }
}
</script>
</head>
<body>
<h1>View Post</h1>

.. Omit ..

<a href="list.jsp?curPage=<%=curPage %>&keyword=<%=keyword %>">List</a>
<input type="button" value="Edit Post" onclick="javascript:goModify('<%=no %>','<%=curPage %>','<%=keyword %>')">
<input type="button" value="Delete Post" onclick="javascript:goDelete('<%=no %>','<%=curPage %>','<%=keyword %>')">
</body>
</html>

Modify the following components for the same reason as above. However, BoardWriter.java does not need to be modified.

write_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
String curPage = request.getParameter("curPage");
String keyword = request.getParameter("keyword");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Add New Post</title>
</head>
<body>
<h1>Add New Post</h1>
<form action="../servlet/BoardWriter" method="post">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50"></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="20" cols="100"></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="list.jsp?curPage=<%=curPage %>&keyword=<%=keyword %>">List</a>
  </td>
</tr>
</table>
</form>  
</body>
</html>
modify_form.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="net.java_school.util.*" %>
<%@ page import="net.java_school.db.dbpool.*" %>
<jsp:useBean id="dbmgr" scope="application" class="net.java_school.db.dbpool.OracleConnectionManager" />
<%
request.setCharacterEncoding("UTF-8");
String no = request.getParameter("no");
String curPage = request.getParameter("curPage");
String keyword = request.getParameter("keyword");
Log log = new Log();

//.. Omit ..

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Edit Post</title>
</head>
<body>
<h1>Edit Post</h1>
<form action="../servlet/BoardModifier" method="post">
<input type="hidden" name="no" value="<%=no %>">
<input type="hidden" name="curPage" value="<%=curPage %>">
<input type="hidden" name="keyword" value="<%=keyword %>">
<table>
<tr>
  <td>Title</td>
  <td><input type="text" name="title" size="50" value="<%=title %>" /></td>
</tr>
<tr>
  <td colspan="2">
    <textarea name="content" rows="30" cols="100"><%=content %></textarea>
  </td>
</tr>
<tr>
  <td colspan="2">
    <input type="submit" value="Submit">
    <input type="reset" value="Reset">
    <a href="view.jsp?no=<%=no %>&curPage=<%=curPage %>&keyword=<%=keyword %>">View Post</a>
  </td>
</tr>
</table>
</form>
</body>
</html>
BoardModifier.java
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {

  req.setCharacterEncoding("UTF-8");
  Log log = new Log();
	
  int no = Integer.parseInt(req.getParameter("no"));
  String curPage = req.getParameter("curPage");
  String keyword = req.getParameter("keyword");
	
  //.. Omit ..
				
  String path = req.getContextPath();
  keyword = java.net.URLEncoder.encode(keyword,"UTF-8");
  resp.sendRedirect(path + "/board/view.jsp?no=" + no + "&curPage=" + curPage + "&keyword=" + keyword);
	
}
BoardDeleter.java
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
    throws ServletException, IOException {
	
  req.setCharacterEncoding("UTF-8");
  Log log = new Log();
	
  int no = Integer.parseInt(req.getParameter("no"));
  String curPage = req.getParameter("curPage");
  String keyword = req.getParameter("keyword");
		
  //.. Omit ..
	
  String path = req.getContextPath();
  keyword = java.net.URLEncoder.encode(keyword,"UTF-8");
  resp.sendRedirect(path + "/board/list.jsp?curPage=" + curPage + "&keyword=" + keyword);

}

resp.sendRedirect("....." + keyword);

The encoding of the URL address character may be different from the encoding of the string, which is an argument to the sendRedirect() method of HttpServletResponse. This case occurs when the characters used as keywords are non-English characters.

You can solve the problem by making the encoding of your keywords the same encoding that Tomcat uses for URLs. But Tomcat uses ISO-8859-1 as the default character encoding for URLs that contain query strings. (In other words, the encoding of GET parameters is ISO-8859-1) If you used Unicode characters in your keywords, changing the encoding of your keywords to ISO-8859-1 is not a solution.

Since we are using an Oracle database, Let's change the encoding that Tomcat uses for URLs to UTF-8.

Open the CATALINA_HOME/conf/server.xml file and ensure that the Connector element has the URIEncoding attribute set to UTF-8, as shown below, with the Connector element having the port attribute value of 8080. If there is no URIEncoding attribute, add it as follows. 

<Connector port="8080" protocol="HTTP/1.1" 
  connectionTimeout="20000" 
  URIEncoding="UTF-8"
  redirectPort="8443" />

URIEncoding="UTF-8" setting changes the character encoding for the URL from ISO-8859-1 to UTF-8. With this setting, it is possible to transmit a parameter whose values are non-English characters by the GET method. To apply this setting, rerun Tomcat. For English-only sites, the above settings may not be necessary.

keyword = java.net.URLEncoder.encode(keyword,"UTF-8");

The code above is executed first before passing keywords as arguments to the sendRedirect() method. You can use URLEncoder's encode() to get the byte value for a character in the encoding you want.

References