How to retrieve data from MySQL using JSP, Servlet and display records in a table format. In this Java web application tutorial, we are going to continue the same project and add a new page to view the user list from the database.

Retrieve data in table format in Jsp

• Design a userview.jsp page in JSP.
• Careate a method in UserModel to get the list of users from the database.
• Implement a UserList Controller under the controller package.
• Display user list in userview.jsp

Design a user view page in JSP.

userList.jsp

<%@ include file="header.jsp"%>
<table class="table table-striped">
  <thead>
    <tr>
      <th scope="col">Id</th>
      <th scope="col">Name</th>
      <th scope="col">Login</th>
      <th scope="col">MobileNo</th>
      <th scope="col">Date of Birth</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <th scope="row">1</th>
      <td>1</td>
      <td>2</td>
      <td>3</td>
      <td>4</td>
    </tr>

  </tbody>
</table>
<%@ include file="footer.jsp"%>

Add a method in UserModel

This list() method gets the data from the database and return a list of user data.

UserModel.java

public static List list() {
  ArrayList list=new ArrayList();
  Connection conn=null;
  try {
     conn=JDBCDataSource.getConnection();
    PreparedStatement pstmt=conn.prepareStatement("Select * from user");
    ResultSet rs= pstmt.executeQuery();
    while (rs.next()) {
    UserBeans user=new UserBeans();
    user.setId(rs.getLong("id"));
    user.setFirstName(rs.getString("fname"));
    user.setLastName(rs.getString("laname"));
    user.setLogin(rs.getString("login"));
    user.setPassword(rs.getString("password"));
    user.setDob(rs.getDate("dob"));
    user.setMobileNo(rs.getString("mobile"));
    list.add(user);
    }
  } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
  }finally {
    JDBCDataSource.closeConnection(conn);
  }
  return list;
}

Create a Servlet UserListCTL

This servlet get the data from the model and forward the data to View(userList.jsp).

UserListCTL.java

package com.javawebapp.Controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.javawebapp.model.UserModel;
import com.javawebapp.utility.ServletUtility;

/**
 * Servlet implementation class UserListCtl
 */
@WebServlet(name="UserListCtl" ,urlPatterns= {"/UserListCtl"})
public class UserListCtl extends HttpServlet {
  private static final long serialVersionUID = 1L;
       
    
    public UserListCtl() {
        super();
        // TODO Auto-generated constructor stub
    }

  /**
   * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    UserModel model=new UserModel();
    List list=null;
    list=model.list();
    System.out.println(list.size());
    
    if(list==null && list.size()==0){
      ServletUtility.setErrorMessage("Record Not Found", request);
    }
    
    ServletUtility.setList(list, request);
    ServletUtility.forward("jsp/userList.jsp", request, response);
  }

  /**
   * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
   */
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    doGet(request, response);
  }

}

Display user list in userview.jsp

<%@page import="com.javawebapp.utility.DataUtility"%>
<%@page import="com.javawebapp.beans.UserBeans"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.List"%>
<%@page import="com.javawebapp.utility.ServletUtility"%>
<%@ include file="header.jsp"%>

<br>
<h2>User List</h2>
<br>
<h4 style="color: red;"><%=ServletUtility.getErrorMessage(request)%></h4>
<h4 style="color: green;"><%=ServletUtility.getSuccessMessage(request)%></h4>
<table class="table table-striped">
  <thead>
    <tr>
      <th scope="col">Id</th>
      <th scope="col">Name</th>
      <th scope="col">Login</th>
      <th scope="col">MobileNo</th>
      <th scope="col">Date of Birth</th>
    </tr>
  </thead>
  <tbody>
  <%
  		int index=1;
  		List list=ServletUtility.getList(request);
  		Iterator it=list.iterator();
  		while(it.hasNext()){
  		UserBeans user=(UserBeans)it.next();
  %>
    <tr>
      <th scope="row"><%=index++%></th>
      <td><%=user.getFirstName()+" "+user.getLastName()%></td>
      <td><%=user.getLogin()%></td>
      <td><%=user.getMobileNo()%></td>
      <td><%=DataUtility.getDateString(user.getDob())%></td>
    </tr>
<%} %>
  </tbody>
</table>
<%@ include file="footer.jsp"%>