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

Ashwani Sharma picture Ashwani Sharma · Feb 15, 2011 · Viewed 58.4k times · Source

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?

Answer

BalusC picture BalusC · Feb 15, 2011

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: