How to create relationships between objects in Spring JDBC?

Question

I want to implement relationships from JPA to Spring JDBC. For instance, assume I have Account and Advert objects. The relationship between Account and Advert is @OneToMany according to JPA.

Account class:

public class Account {

private Long id;
private String username;
private Set<Advert> adverts = new HashSet<Advert>();

// getters + setters
}

Advert class:

public class Advert {

private Long id;
private String text;
private Account account;

// getters + setters
}

AccountMapper:

public class AccountMapper implements RowMapper<Account> {

public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
    Account account = new Account();
    account.setId(rs.getLong("id"));
    account.setUsername(rs.getString("username"));
    return account;
}
}

Now, I am trying to create a Mapper for the Advert class. How can I map the account variable from the Advert class to a row? Many thanks

Answer 1

You can use Hibernate without affecting your application performance, just check out this Hibernate tutorial for hundreds of examples related too mapping entities.

As for doing that in JDBC, you need to doo the following steps:

1. You need to use aliases to all selected columns so that the ids columns won't clash.

2. You can define two row mappers and use a join from Advert to Account and pass it to the AccountMapper:

   public class AdvertMapper implements RowMapper<Advert> {
   
       public Advert mapRow(ResultSet rs, int rowNum) throws SQLException {
           Advert advert = new Advert();
           advert.setId(rs.getLong("advert_id"));
           advert.setText(rs.getString("advert_text"));
           return advert;      
       }
   }
   
   public class AccountMapper implements RowMapper<Account> {
   
       private final AdvertMapper advertMapper;
   
       public AccountMapper(AdvertMapper advertMapper) {
           this.advertMapper = advertMapper;
       }
   
       public Account mapRow(ResultSet rs, int rowNum) throws SQLException {
           Account account = new Account();
           account.setId(rs.getLong("account_id"));
           account.setUsername(rs.getString("account_username"));
   
           Advert advert = this.advertMapper.mapRow(rs, rowNum);
           advert.setAccount(account);
           account.getAdverts().add(advert);
   
           return account;
       }
   }
   

The AccountMapper uses the AdvertMapper to create Adverts from the joined data.

Compare this to Hibernate, where all these mappings are resolved for you.

Answer 2

Well if you do not use an ORM ... you have no object relation mapping ! After all the ORMs were created for that reason :-)

More seriously, ORM saves you from writing a lot of boilerplate code. Using direct JDBC instead of JPA is a code optimisation. Like any other code optimisation, it should be used when appropriate. It is relevant for :

• libraries using few tables that do not want to rely on an ORM (ex: user, roles, and ACL in spring security)
• identified bottlenecks in larger application

My advice should be to first use JPA or native hibernate hidden in a DAO layer. Then carefully analyze your performance problems and rewrite the most expensive parts in JDBC.

Of course, you can directly code you DAO implementations in JDBC, but it will be much longer to write.

I almost forgot the essential part : in an ORM you map classes and relations, in JDBC you write independant SQL queries.

Answer 3

Solving the one to one case is easy with as Vlad answered, If you want to map a one to many as your Account - Advert suggest you can't do that with a RowMapper because you will try to map multiple rows of your ResultSet to one Account, many Advert.

You can also do that manually or you can also use http://simpleflatmapper.org that provides mapping from ResultSet to POJO with one to many support. Beware that the bidirectional relationship is not great there if you really want those it's possible but they won't be the same instance.

Checkout http://simpleflatmapper.org/0104-getting-started-springjdbc.html and https://arnaudroger.github.io/blog/2017/02/27/jooq-one-to-many.html

you will need to get a ResutlSetExtractor - it's thread safe so only need one instance -,

    private final ResultSetExtractor<List<Account>> mapper = 
        JdbcTemplateMapperFactory
            .newInstance()
            .addKeys("id") // assuming the account id will be on that column 
            .newResultSetExtractor(Account.class);

    // in the method
    String query = 
        "SELECT ac.id as id, ac.username, ad.id as adverts_id, ad.text as adverts_text"
        + "FROM account ac LEFT OUTER JOIN advert ad ON ad.account_id = ac.id order by id " 
        // the order by id is important here as it uses the break on id on the root object 
        // to detect new root object creation

    List<Account> results = template.query(query, mapper);

with that you should get a list of account with the list of adverts populated. but advert won't have the account.