Search code examples
javajspjoindaodto

How to manipulate Data Transfer Object if sql join 2 tables?

I have a query in Data Access Object DAOComments that joins users table and comments table and then store the result into Data Transfer Object DTOComments:

private static final String SQL_FIND_WITH_USERNAME =
"SELECT u.username, comments.* FROM users u JOIN comments 
ON u.id = comments.id ORDER BY created_date DESC LIMIT 10;";

However, DTOComments does not have property "username" since it is the property of DTOUsers.

Option 1

So I decided to use Map<String, DTOComments>

In this case the map has username as KEY, DTOComments as VALUE.

But this approach will fails, because I care about the ORDER of result and that's why my query returns result in descending order. If I iterate the map on JSP page, the order is not consistent, so my JSP page would output the comment in random order.

(Even if order doesn't matter, I don't know if JSTL can display map's KEY. I know displaying the VALUE though)

Option 2

I could put the query result into ArrayList<DTOComments>

But I don't see any room to store the "username" now. Maybe I can add new property to DTOComments like private String username;

hmm... this would violate the concept of having DTO since it SHOULD reflect the database table schema.

Option 3

Create new class that hold all the information I need (ie. username + properties of DTOComments).

But just because I need one more property "username" in addition to the properties of DTOComments, creating new class seems not right way.

Could anyone give me advice how can I store all info returned by the query above in more organized way?

Solution

  • If I iterate the map on JSP page, the order is not consistent, so my JSP page would output the comment in random order.

    That's the nature of HashMap. If you want to maintain insertion order in a Map, then you should be using LinkedHashMap instead. But the Map<User, Comment> approach has another disadvantage, if an user has posted more than one comment, you would be overwriting the previously inserted comment this way. You would like to use a Map<Comment, User> instead.

    But IMHO it's better to make the User a property in Comment class, indicating a many-to-one relationship:

    public class Comment {
    private User user; // +getter +setter
}

    This way you can end up with a List<Comment>.

    That said, I wanted to comment on another statement of you:

    I don't know if JSTL can display map's KEY. I know displaying the VALUE though)

    You can iterate over a map using <c:forEach>. It goes over Map#entrySet(). Each iteration gives a Map.Entry object back which in turn has getKey() and getValue() methods.

    Here's a kickoff example:

    <c:forEach items="${map}" var="entry">
    Key: ${entry.key}, value: ${entry.value}<br>
</c:forEach>