I have a class that looks like this. I need to populate it from two database tables, which are also shown below. Is there any preferred way to do this?
My thought is to have a service class to select a List<>
via a ResultSetExtractor
from a DAO. Then do a foreach
on that list, and select a List<>
of emails for the individual person via another ResultSetExtractor
, and attach it from with the foreach
loop.
Is there a better way, or is this as good as it gets?
public class Person {
private String personId;
private String Name;
private ArrayList<String> emails;
}
create table Person (
person_id varchar2(10),
name varchar2(30)
);
create table email (
person_id varchar2(10),
email varchar2(30)
);
All you need to do is create a ResultSetExtractor using the JdbcTemplateMapperFactory. private final ResultSetExtractor<List<User>> resultSetExtractor = JdbcTemplateMapperFactory . newInstance() . addKeys("id") // the column name you expect the user id to be on .
A RowMapper is usually a simpler choice for ResultSet processing, mapping one result object per row instead of one result object for the entire ResultSet. ResultSetExtractor is suppose to extract the whole ResultSet (possibly multiple rows), while RowMapper is feeded with row at a time.
Interface RowMapper<T>An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object, but don't need to worry about exception handling.
ResultSetExtractor is an interface that is used to fetch the records from the database. It's a callback interface that is used by JDBC Template's query() method where we need to pass the instance of ResultSetExtractor in order to fetch the data.
This is best solved by an ORM. With JDBC, you have to do by hand what an ORM would do for you. Executing N + 1 queries is very inefficient. You should execute a single query, and build your objects manually. Cumbersome, but not hard:
select person.id, person.name, email.email from person person
left join email on person.id = email.person_id
...
Map<Long, Person> personsById = new HashMap<>();
while (rs.next()) {
Long id = rs.getLong("id");
String name = rs.getString("name");
String email = rs.getString("email");
Person person = personsById.get(id);
if (person == null) {
person = new Person(id, name);
personsById.put(person.getId(), person);
}
person.addEmail(email);
}
Collection<Person> persons = personsById.values();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With