Spring Framework JDBC DAO with agrgegation/composition

Question

I have an application that is already using the Spring Framework and Spring JDBC with a DAO layer using the SimpleJdbcTemplate and RowMapper classes. This seems to work very well with small class structures being read from the database. However, we have the need to load objects that hold collections of other objects, which hold collections of other objects still.

The "obvious" solution to this problem is to create a named RowMapper class or our objects, and pass references to the proper DAO objects in the constructor. For example:

public class ProjectRowMapper implements ParameterizedRowMapper {

    public ProjectRowMapper(AccountDAO accountDAO, ) {
        this.accountDAO = accountDAO;
    }

    public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
        Project project= new Project ();
        project.setProjecttId( rs.getString("project_id") );
        project.setStartDate( rs.getDate("start_date") );
        // project.setEtcetera(...);

        // this is where the problems start
        project.setAccounts( accountDAO.getAccountsOnProject(project.getProjectId()) );
     }
}

The problem is that even though the ProjectDAO and the Account DAO share the same DataSource instance (in our case this is a connection pool), any database accesses are done through a different connection.

If the object hierarchy is even three levels deep, using this implementation results in (a) many calls by the framework to datasource.getConnection(), and (2) even worse, since we cap the number of connections allowed in our connection pool, potential race conditions while multiple threads are trying to load a Project from the database.

Is there a better way in Spring (without another full-fledged ORM tool) to achieve the loading of such object hierarchies?

Thanks, Paul

Answer 1

I guess you have reasons for not using an ORM, which is the ideal tool for this kind of problem.

The problem with multiple connections is the recursive call to another DAO. To avoid consuming additional connections, Account objects should be to be retrieved later, after the project instance has been fetched. When fetching the Project, the accountIDs are also fetched, but not "instantiated" to account instances - they remain as a list of IDs, which are then populated after the project DAO has done it's work.

For example, you can build a custom List type that takes a list of IDs and a DAO implementation. The list is populated with just the IDs in the ProjectRowMapper and assigned to the project's accounts property. The IDs are private to the list - they are not the "contents" of the list, but a means to produce the real contents later.

Once the Project DAO has fetched the projects from the RowMapper, it can then instruct the list to then fetch the accounts for the IDs that were saved in the list. The accounts are fetched as s non-nested operation and so the whole process only uses one connection at any time. Yet,the fetch is done within the scope of the DAO method, so the fetch is done eagerly - so there are no lazy-loading issues to deal with.