TL;DR: How do you use Spring JDBC to populate a complex domain model in the best way?
I've previously only used JPA to retrieve stuff from the database, but our db admins complained how many queries the framework sent to the database and how inefficient they were, so on our new project we decided to try out Spring JDBC instead. I started to implement retrieval of our fairly complex domain model using a one query per entity approach, but the logic to put results where they belong in the model became difficult to follow very quickly.
For example: Items can have many Actions affect them and an Action can affect many Items. When I fetch an Item, I want to see its Actions, and I also want to see their affected Items, excluding the Item that I fetched in the first place. So this data:
Item: | id | name | Action: | id | actNo | itemId |
| 1 | 'One' | | 1 | '001-1' | 1 |
| 2 | 'Two' | | 1 | '001-1' | 2 |
| 2 | '002-2' | 2 |
Would produce this result when fetching "Two":
Item {id: 2, name: 'Two',
actionList: {
Action {id: 1, actNo: '001-1',
itemList: {
Item {id: 1, name: 'One'}
}
},
Action {id: 2, actNo: '002-2'}
}
}
This is the code I've got so far:
@Transactional
public List<Item> getItems(List<Integer> idList) {
initializeTempTable(idList);
return runQueries();
}
private void initializeTempTable(List<Integer> idList) {
String createSql = "create temporary table if not exists temp_table (id int) on commit delete rows";
jdbcTemplate.update(createSql, (SqlParameterSource) null);
String insertSql = "insert into temp_table (id) values (:value)";
List<MapSqlParameterSource> parameters = new ArrayList<MapSqlParameterSource>(idList.size());
for(Integer id : idList) {
parameters.add(new MapSqlParameterSource("value", id));
}
jdbcTemplate.batchUpdate(insertSql, parameters.toArray(new SqlParameterSource[parameters.size()]));
}
private List<Item> runQueries() {
List<Item> itemList = getItems();
addActions(itemList);
// Add the rest...
return itemList;
}
private List<Item> getItems() {
String sql = "select i.* from item i join temp_table t on i.id = t.id";
return jdbcTemplate.query(sql, (SqlParameterSource) null, new RowMapper<Item>() {
public Item mapRow(ResultSet rs, int rowNum) throws SQLException {
Item item = new Item();
item.setId(rs.getInt("id"));
item.setName(rs.getString("name"));
return item;
}
});
}
private void addActions(List<Item> itemList) {
String sql = "select a.* from action a " +
"join item i on a.itemId = i.id " +
"join temp_table t on i.id = t.id;
final Map<Integer, List<Item>> resultMap = new HashMap<Integer, List<Item>>();
jdbcTemplate.query(sql, (SqlParameterMap) null, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
Action action = new Action();
action.setId(rs.getInt("id"));
action.setActNo(rs.getString("actNo"));
int itemId = rs.getInt("itemId");
if(resultMap.containsKey(itemId)) {
List<Action> actionList = resultMap.get(itemId);
actionList.add(action);
} else {
List<Action> actionList = new ArrayList<Action>(Arrays.asList(action));
resultMap.put(itemId, actionList);
}
}
});
for(Item item : itemList) {
List<Action> actionList = resultMap.get(item.getId());
item.setActionList(actionList);
}
addItemsToActions(resultMap);
}
private void addItemsToActions(final Map<Integer, List<Action>> resultMap) {
String sql = "select i2.*, a2.id as actionId, i.id as orgId from item i2 " +
"join action a2 on i2.id = a2.itemId " +
"join action a on a2.id = a.id " +
"join item i on a.itemId = i.id " +
"join temp_table t on i.id = t.id " +
"where i2.id != i.id";
jdbcTemplate,query(sql, (SqlParameterSource) null, new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
Item item = new Item();
item.setId(rs.getInt("id"));
item.setName(rs.getString("name"));
int orgItemId = rs.getInt("orgId");
if(resultMap.containsKey(orgItemId)) {
List<Action> actionList = resultMap.get(orgItemId);
int actionId = rs.getInt("actionId");
for(Action action : actionList) {
if(action.getId() == actionId) {
if(action.getItemList() == null) {
action.setItemList(new ArrayList<Item>());
}
action.getItemList().add(item);
break;
}
}
}
}
});
}
As you can see, for such a simple relation I get some non-obvious sql and a lot of hard-to-follow mapping code. And the only way I can see how to combat this is to do exactly what the JPA framework did: traverse the model depth-first and run a lot of small queries to populate each instance as you come by them. Which will make the db admins unhappy again.
Is there a better way?
