Spring RowMapper for OneToMany?

Question

I am trying to adapt two RowMappers for two objects which have @OneToMany relationship between them.

Assume, I have two methods:

public Account findAccount(long id) {
    SQL = "SELECT * FROM accounts WHERE id = ?";
    Account account = template.queryForObject(SQL, new Object[] { id }, MAP_ACCOUNT);
    return account;
}


public Card findCard(String number) {
    SQL = "SELECT * FROM cards WHERE number = ?";
    Card card = template.queryForObject(SQL, new Object[] { number }, MAP_CARD);
    return card;
}

And two row mappers:

   private final RowMapper<Card> MAP_CARD = new RowMapper<Card>() {

    public Card mapRow(ResultSet rs, int rowNum) throws SQLException {
        Account account = findAccount(rs.getLong("account_id"));
        Card card = new DefaultCard(rs.getString("number"), account);
        return card;
    }

};

private final RowMapper<Account> MAP_ACCOUNT = new RowMapper<Account>() {

    public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
        SQL = "SELECT * FROM cards where account_id = " + rs.getLong("id");
        List<Card> cards = template.query(SQL, MAP_CARD);
        Account account = new DefaultAccount(rs.getLong("id"), rs.getString("username"), cards);
        return account;
    }
};

Running findAccount or findCard methods will throw an exception stating "Too many connections!". This is because of the cyclic-dependency of row mappers between them. I know I've done this the wrong way and I would like to know how to correctly rewrite the row mappers. Many thanks.

Answer 1

First of all your java object constructors are "recursive" tightly coupled. Account and Card constructors expect each other as a parameter. You can have an account without a card, right? So remove the list of cards of the Account constructor.

Now moving on to the queries, when loading the cards of the account there are 2 scenarios:

1 - Loading cards from the account: You already have the account, it is not necessary to query the account for each card. So you can have a MAP_CARD_FROM_ACCOUNT row mapper that receives the account by parameter.

2- Loading a single card: In this scenario you just need the card and its account, so for the MAP_CARD mapper you can make a query to return the card and account information: SELECT * FROM cards C, accounts a WHERE c.account_id=a.id and number = ?

Below is the example of how the mapper code will look like:

 private final RowMapper<Card> MAP_CARD_FROM_ACCOUNT = new RowMapper<Card>() {

    public void setAccount(Account account){
       this.account = account;
    }

    public Card mapRow(ResultSet rs, int rowNum) throws SQLException {
        Card card = new DefaultCard(rs.getString("number"), account);
        return card;
    }

};

 private final RowMapper<Card> MAP_CARD = new RowMapper<Card>() {

    public Card mapRow(ResultSet rs, int rowNum) throws SQLException {
        Account account = new Account(rs.getLong("a.account_id"), rs.getString("a.username");
        Card card = new DefaultCard(rs.getString("c.number"), account);
        return card;
    }

};

private final RowMapper<Account> MAP_ACCOUNT = new RowMapper<Account>() {

    public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
        SQL = "SELECT * FROM cards where account_id = " + rs.getLong("id");
        Account account = new DefaultAccount(rs.getLong("id"), rs.getString("username"));
        MAP_CARD_FROM_ACCOUNT.setAccount(account)
        List<Card> cards = template.query(SQL, MAP_CARD_FROM_ACCOUNT);
        account.setCards(cards);
        return account;
    }
};