How to use JdbcTemplate to perform Join queries

Question

I have the following database model

       create table Diary (id bigint NOT NULL AUTO_INCREMENT,
                creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                name varchar(255) not null, 
                description text,
                viewtype varchar(255) not null,
                member bigint,
                primary key (id),
                foreign key (member) references Member(id));


       create table Page (id bigint NOT NULL AUTO_INCREMENT,
                creationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                viewtype varchar(255) not null,
                diary bigint,
                member bigint,
                primary key (id),
                foreign key (diary) references Diary(id),
                foreign key (member) references Member(id));

       create table Comment (id bigint NOT NULL AUTO_INCREMENT,
                postingDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                comment text not null,
                page bigint,
                member bigint,
                primary key (id),
                foreign key (page) references Page(id)
                foreign key (member) references Member(id));

I am using spring JDbc template.

      My interface looks like follows: accountid is the memeberid in the database.

     Collection<Diary> getDiaries(Long accountId);

And my diary looks like follows:

           public class Diary {
           private Collection<Page> pages;
           private Long id;
           private LocalTime creationDate;
           private String name;
           private String description;
           private ViewType type;
           }

I wanted to know how the query will look now if I wanted to prepare a Diary Object using jdbc template. Is is also possible to just fire only one query and just prepare this Diary object because I will avoid for same request multiple firing of queries. For the above interface its very possible that I will be using s join query or is there any more simpler way possible using spring JDBC template framework.

Answer 1

You would be able to create a single query using outer joins (I'm assuming here that it is possible to have a diary that doesn't have any pages and have pages that don't have comments).

Now, instead of doing multiple queries (one for each page), this does a single query using outer joins to connection Diary, Page and Comment. As you can see below, this will mean that the diary and page information is returned multiple times, but I think there is a tradeoff between having multiple DB calls and a bit of redundant information.

 class FullDiaryRowCallbackHandler implements RowCallbackHandler {
    private Collection<Diary> diaries = new ArrayList<Diary>();
    private Diary currentDiary = null;
    private Page currentPage = null;

    public void processRow(ResultSet rs) {
       long diaryId = rs.getLong("d.id");
       if (currentDiary == null || diaryId != currentDiary.getId()) {
          currentDiary = new Diary();
          currentPage = null;
          diaries.add(currentDiary);
          currentDiary.setId(diaryId);
          currentDiary.setCreationDate(toLocalTime(rs.getTimestamp("d.creationDate")));
          currentDiary.setDescription(rs.getString("d.description"));
          ...
       }
       long pageId = rs.getLong("p.id");
       if (!rs.wasNull() && currentPage != null && currentPage.getId() != pageId) {
          currentPage = new Page();
          if (currentDiary.getPages() == null) {
              currentDiary.setPages(new ArrayList<Page>());
          }
          currentDiary.getPages().add(currentPage);
          currentPage.setId(pageId);
          currentPage.setCreationDate(toLocalTime(rs.getTimestamp("p.creationDate")));
          ...
       }
       long commentId = rs.getLong("c.id");
       if (!rs.wasNull() && currentPage != null) {
          Comment comment = new Comment();
          if (currentPage.getComments() == null) {
              currentPage.setComments(new ArrayList<Comment>());
          }
          currentPage.getComments().add(comment);
          comment.setId(commentId);
          comment.setPostingDate(toLocalTime(rs.getTimestamp("c.postingDate")));
          comment.setComment(rs.getString("c.comment"));
       }
    }

    public Collection<Diary> getDiaries() {
       return diaries;
    }
 }

 FullDiaryRowCallbackHandler rowCallbackHandler = new FullDiaryRowCallbackHandler();
 Collection<Diary> result = jdbcTemplate.query(
    "select d.id, " +
           "d.creationDate, " +
           "d.description, " +
           "p.id, " +
           "p.creationDate, " +
           "c.id, " +
           "c.postingDate, " +
           "c.comment " +
      "from Diary d " +
      "left outer join Page p on d.id = p.diary " +
      "left outer join Comment c on p.id = c.page " +
     "where d.member = ? " +
     "order by d.id, p.id, c.id",
    rowCallbackHandler,
    myMemberId);
Collection<Diary> diariesForMember = rowCallbackHandler.getDiaries();

Note, the code isn't particularly pretty, because you have to handle the result sets and take care of when a new page is returned (that's why the order by clause is important), but that's the sort of thing that the likes of Hibernate take care for you under the hood when they are eagerly fetching (I'm not saying Hibernate is better or not, for I like using JdbcTemplate for the control it provides over the queries that I'm issuing, but Hibernate (or JPA) do a lot of the heavy lifting when it comes to populating object graphs).

See also JdbcTemplate#query

EDIT:

Modified to return all diaries, pages, comments for a member.