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

Question 1

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

java jsp servlets model-view-controller jdbc

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

Answer

Answer

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

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

Answer

See also:

Related questions