How to efficiently handle related data in MVC?

Question

Talking about MVC with servlets and JSP's, in a context about products and manufacturers , when querying the application for a list of products:

• the servlet loads a list of products from the database in a List object (or similar);
• the servlet sets that list as attribute of the request and forwards the request to a JSP;
• the JSP retrieves the list from the request and displays its contents on the webpage.

If the above is correct, my question is: how do I load products manufacturers (let's suppose that each product contains the id of its manufacturers)?

A common approach I saw around is that when the JSP loops inside the list object, upon showing each product is calls some other class method which finally runs a query on the database.

There are two things that I dislike about this approach:

1. it breaks the MVC pattern;
2. several, usually separate, queries are run on the database, potentially loading the same data (i.e. when two products come from the same manufacturer).

Is the above true? Are there better approaches that solve the problem?

So far I'm thinking about making a servlet load the manufacturers data in a separate object (e.g. a Map) along with the products List: both objects are set to the request, and then the JSP can access both when rendering the page.

A (potentially dangerous) drawback of this idea is that it requires more memory to hold all the informations; anything else?

Answer 1

you can use ORM (object relational mapping) like JPA or Hibernate. when you are using ORM in your model, you dont need to re-query the related object because ORM will handle it for you automatically. for example:

define your model class and it's relationship:

class Product
{
    Manufacturer manufacturer;

    //setter and getter
}

class Manufacturer
{
    Long id;
    String name;

    //setter and getter
}

calling the model in the controller's method:

public Result showProductList()
{
   List<Product> products = ProductModel.get();
   return Result.ok().render("products", products);
}

and you can call the manufacture name in product in from your product model.

<c:forEach var="product" items="${products}">
    <option value ="10"><c:out value="${product.manufacturer.name}"/></option>
</c:forEach>

disclaimer: the example code above is not the real code. I just want to show the big picture about using the ORM in your code. using this approach you won't break the MVC design pattern (because you don't need to re-query the object from your view)

Answer 2

It seems that you want map the joined tables to composite objects.

For this task is best use already mentioned ORM framework (best with JPA specification). Mapping relations to composite objects can be done in one-round-trip in ORM. ORM using more ways to optimize data access. But you must learn quite a complex framework.

Mentioned common approach with querying to DB in rendering JSP may be caused by using ORM lazy loading for associated objects. Unloaded associated objects are initialized as proxy objects (proxies have the same interface like target objects, but without values). Proxies are replaced with real objects by the querying database or cache at the time of the first access.

If you want to create code for one-round-trip query yourself, then you can use the same approach like using ORM frameworks:

public List<Product> getProducts(Connection con) {
    List<Product> result = new ArrayList<>;
    Statement stmt = null;
    try {
       stmt = con.createStatement();
       //only one query
       ResultSet rs = stmt.executeQuery("SELECT * FROM product p INNER JOIN manufacture m ON p.manufacture_id=m.id");
       while (rs.next()) {

           Product product = new Product();
           product.set...(rs.getType('column_of_product'))
           ...
           Manufacturer manufacturer = new Manufacturer();
           manufacturer.set...(rs.getType('column_of_manufacturer'))
           ...
           product.setManufacturer(manufacturer);

           result.add(product);
       }
   } catch (SQLException e) {
       JDBCTutorialUtilities.printSQLException(e);
   } finally {
       if (stmt != null) { stmt.close(); }
   }
   return result;
}

• add 1. No, it's not break MVC pattern - This has nothing to do with the MVC approach
• add 2. On the basis of this example it's seen that's no need of multiple queries into the database. Duplicating data or consuming more memory is for save processing time :)