Show JDBC ResultSet in HTML in JSP page using MVC and DAO pattern

Question

I'm implementing MVC using JSP and JDBC. I have imported a database class file to my JSP file and I would like to show the data of a DB table. I don't know how I should return the ResultSet from the Java class to the JSP page and embed it in HTML.

How can I achieve this?

Solution 1

In a well designed MVC approach, the JSP file should not contain any line of Java code and the servlet class should not contain any line of JDBC code.

Assuming that you want to show a list of products in a webshop, the following code needs to be created.

• A Product class representing a real world entity of a product, it should be just a Javabean.

  public class Product {
  
      private Long id; 
      private String name;
      private String description;
      private BigDecimal price;
  
      // Add/generate getters/setters/c'tors/equals/hashcode boilerplate.
  }
  

• A DAO class which does all the nasty JDBC work and returns a nice List<Product>.

  public class ProductDAO {
  
      private DataSource dataSource;
  
      public ProductDAO(DataSource dataSource) {
          this.dataSource = dataSource;
      }
  
      public List<Product> list() throws SQLException {
          List<Product> products = new ArrayList<Product>();
  
          try (
              Connection connection = dataSource.getConnection();
              PreparedStatement statement = connection.prepareStatement("SELECT id, name, description, price FROM product");
              ResultSet resultSet = statement.executeQuery();
          ) {
              while (resultSet.next()) {
                  Product product = new Product();
                  product.setId(resultSet.getLong("id"));
                  product.setName(resultSet.getString("name"));
                  product.setDescription(resultSet.getString("description"));
                  product.setPrice(resultSet.getBigDecimal("price"));
                  products.add(product);
              }
          }
  
          return products;
      }
  
  }
  

• A servlet class which obtains the list and puts it in the request scope.

  @WebServlet("/products")
  public class ProductsServlet extends HttpServlet {
  
      @Resource(name="jdbc/YourDB") // For Tomcat, define as <Resource> in context.xml and declare as <resource-ref> in web.xml.
      private DataSource dataSource;
      private ProductDAO productDAO;
  
      @Override
      public void init() {
          productDAO = new ProductDAO(dataSource);
      }
  
      @Override
      protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
          try {
              List<Product> products = productDAO.list();
              request.setAttribute("products", products); // Will be available as ${products} in JSP
              request.getRequestDispatcher("/WEB-INF/products.jsp").forward(request, response);
          } catch (SQLException e) {
              throw new ServletException("Cannot obtain products from DB", e);
          }
      }
  
  }
  

• Finally a JSP file in /WEB-INF/products.jsp which uses JSTL <c:forEach> to iterate over List<Product> which is made available in EL by ${products}, and uses JSTL <c:out> to escape string properties in order to avoid XSS holes when it concerns user-controlled input.

  <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
  <%@ taglib uri="http://java.sun.com/jsp/jstl/format" prefix="fmt" %>
  ...
  <table>
      <c:forEach items="${products}" var="product">
          <tr>
              <td>${product.id}</td>
              <td><c:out value="${product.name}" /></td>
              <td><c:out value="${product.description}" /></td>
              <td><fmt:formatNumber value="${product.price}" type="currency" currencyCode="USD" /></td>
          </tr>
      </c:forEach>
  </table>
  

To get it to work, just call the servlet by its URL. Provided that the servlet is annotated @WebServlet("/products") or mapped in web.xml with <url-pattern>/products</url-pattern>, then you can call it by http://example.com/contextname/products

See also:

• How to avoid Java code in JSP files?
• doGet and doPost in Servlets
• How should I connect to JDBC database / datasource in a servlet based application?
• Design Patterns web based applications
• RequestDispatcher.forward() vs HttpServletResponse.sendRedirect()
• How to map a ResultSet with unknown amount of columns to a List and display it in a HTML table?
• How do I pass current item to Java method by clicking a hyperlink or button in JSP page?

Solution 2

MVC, in a web application context, doesn't consist in using a class from a JSP. It consists in using the following model :

1. browser sends a request to a web server
2. the web server is configured so that the request is handled by a servlet or a filter (the controller : Java code, not JSP code)
3. The servlet/filter usually dispatches the request to a specific class (called an Action, the specific part of the controller), based on configuration/annotations
4. The action executes the business logic (i.e. fetch the data from the database in your example : the model)
5. The action forwards the request to a JSP. The role of the JSP is only to generate HTML code (i.e. display your data : the view)

Since the JSP usually uses JSP tags (the JSTL, for example) and the JSP expression language, and since JSP tags and the EL are designed to get information from JavaBeans, you'd better have your data available in the form of JavaBeans or collections of JavaBeans.

The role of the controller (the action class) is thus to fetch the data, to create JavaBean instances containing the data, in a suitable format for the JSP, to put them in request attributes, and then to dispatch to the JSP. The JSP will then iterate through the JavaBean instances and display what they contain.

You should not implement the MVC framework yourself. Use existing ones (Stripes, Struts, etc.)

Solution 3

I don't know how should I return the ResultSet from the class file to the JSP page

Well, you don't.

The point of MVC is to separate your model ( the M DB info in this case ) from your view ( V a jsp, in this case ) in such a way you can change the view without braking to application.

To do this you might use an intermediate object to represent your data ( usually called DTO - after Data Transfer Object -, don't know how they call it these days ), and other object to fetch it ( usually a DAO ).

So basically you have your JSP file, get the request parameters, and then invoke a method from the DAO. The dao, internally has the means to connect to the db and fetch the data and builds a collections of DTO's which are returned to the JSP for rendering.

Something like this extremely simplified ( and insecure ) code:

Employee.java

class Employee {
   String name;
   int emplid;
}

EmployeeDAO.java

class EmployeeDAO { 
   ... method to connect 
   etc. 
   List<Employee> getAllNamed( String name ) { 
       String query = "SELECT name, emplid FROM employee where name like ?";
       ResultSet rs = preparedStatement.executeQuery etc etc.
       List<Employee> results = ....
       while( rs.hasNext() ) { 
          results.add( new Employee( rs.getString("name"), rs.getInt("emplid")));
       }
       // close resources etc 
       return results;
    }
}

employee.jsp

<%
   request.setAttribute("employees", dao.getAllNamed( request.getParameter("name") );
%>
<table>
<c:forEach items="${employees}" var="employee">
<tr><td>${employee.emplid}</td><td>${employee.name}</td></tr>
</c:forEach>
</table>

I hope this give you a better idea.