I have a Java + Spring + MySQL web application which stores and displays a list of books. There are three SQL tables - Authors, Books and Book_Authors.
------------ Authors ------------ author_id firstname surname ------------ ------------ Books ------------ book_id title ------------ ------------ Book_Authors ------------ author_id book_id ------------
Note that the relation between books and authors is M:N, i.e. it is possible for a single person to be an author of more than one book and at the same time a single book can be authored by more than one person.
I have got the following DAO classes.
@Service("bookDao")
public class BookDao
{
@Resource(name="jdbcTemplate")
private JdbcTemplate jdbcTemplate;
private RowMapper<Book> mapper = new RowMapper<Book>() {
...
};
public List<Book> selectAll() {
...
}
...
}
@Service("authorDao")
public class AuthorDao
{
@Resource(name="jdbcTemplate")
private JdbcTemplate jdbcTemplate;
private RowMapper<Author> mapper = new RowMapper<Author>() {
...
};
public List<Author> selectAll() {
...
}
}
For some of my views I need to load (a subset of) all books, including the set of authors of each of them. So, my data class and controller should look like this.
public class Book {
private List<Author> authors = ...;
...
}
@Controller
public class Controller
{
@Resource(name = "authorDao")
private AuthorDao authorDao;
@Resource(name = "bookDao")
private BookDao bookDao;
@RequestMapping(value = "/books.html")
public String books()
{
List<Book> books = ???;
...
return "books";
}
}
The question is, how do I design the operation to load books including authors (using a single JOIN query)? Should it be the responsibility of the BookDao class to load the books including authors? If so then how should the RowMapper look like? Or should I create a third Dao called say BookAuthorsDao for that purpose? Or another way?
