Spring JDBC DAO Structure with Aggregation / Composition

Question

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

The “obvious” solution to this problem is to create the named RowMapper class or our objects and pass references to the corresponding DAO objects in the constructor. For instance:

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 ProjectDAO and the DAO account use the same instance of the DataSource (in our case, this is the connection pool), any database calls are made through another connection.

If the hierarchy of objects has three levels of complexity, using this implementation leads to (a) many structure calls to datasource.getConnection () and (2) even worse, since we limit the number of connections allowed in our connection pool to potential race conditions, while while several 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 loading such object hierarchies?

Thanks Paul

Answer 1

I think you have reasons not to use ORM, which is the perfect tool for this kind of problem.

The problem with multiple connections is the recursive call of another DAO. To avoid the use of additional connections, account objects should be retrieved later after the project instance has been retrieved. When a project is retrieved, account identifiers are also retrieved, but are not "instantiated" for account instances - they remain as a list of identifiers, which are then populated after the DAO project has completed it.

For example, you can create your own list type that accepts a list of identifiers and a DAO implementation. The list is populated only with identifiers in ProjectRowMapper and assigned to the property of the project accounts. Identifiers are closed to the list - they are not the "contents" of the list, but a means to create real content later.

Once a DAO project selects projects from RowMapper, it can then instruct the list to then obtain the accounts for the identifiers stored in the list. Accounts are selected as non-nested operations, so the whole process uses only one connection at any time. However, fetching is done as part of the DAO method, so fetching is impatient - so there is no problem with lazy loading.