如果sql连接2个表,如何操作数据传输对象?
我在数据访问对象 DAOComments 中有一个查询,该查询连接用户表和评论表,然后将结果存储到数据传输对象 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;";
但是,DTOComments 没有属性“用户名” “因为它是 DTOUsers 的财产。
选项 1
所以我决定使用 Map
在这种情况下,地图的用户名作为 KEY,DTOComments 作为 VALUE。
但是这个方法将失败,因为我关心结果的顺序,这就是我的查询按降序返回结果的原因。如果我在 JSP 页面上迭代地图,则顺序不一致,因此我的 JSP 页面将以随机顺序输出注释。
(即使顺序不重要,我也不知道JSTL是否可以显示地图的KEY。不过我知道显示VALUE)
选项2
我可以将查询结果放入ArrayList
但我现在没有看到任何空间来存储“用户名”
。也许我可以向 DTOComments 添加新属性,例如 private String username;
嗯...这会违反 DTO 的概念,因为它应该反映数据库表架构。
选项 3
创建新类来保存我需要的所有信息(即用户名 + DTOComments 的属性)。
但仅仅因为除了 DTOComments 的属性之外我还需要一个属性“用户名”,创建新类似乎不是正确的方法。
谁能给我建议如何以更有条理的方式存储上述查询返回的所有信息?
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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这就是
HashMap
的本质。如果您想在Map
中维护插入顺序,那么您应该使用LinkedHashMap
代替。但是Map
方法还有另一个缺点,如果用户发布了多个评论,您将通过这种方式覆盖之前插入的评论。您想改用Map
。但恕我直言,最好将
User
设为Comment
类中的一个属性,表示多对一关系:这样您就可以得到一个
List
评论>
。也就是说,我想对你的另一句话发表评论:
您可以使用
迭代地图。它遍历Map #entrySet()
。每次迭代都会给出一个Map.Entry< /code>
对象返回,该对象又具有
getKey()
和getValue()
方法。这是一个启动示例:
That's the nature of
HashMap
. If you want to maintain insertion order in aMap
, then you should be usingLinkedHashMap
instead. But theMap<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 aMap<Comment, User>
instead.But IMHO it's better to make the
User
a property inComment
class, indicating a many-to-one relationship:This way you can end up with a
List<Comment>
.That said, I wanted to comment on another statement of you:
You can iterate over a map using
<c:forEach>
. It goes overMap#entrySet()
. Each iteration gives aMap.Entry
object back which in turn hasgetKey()
andgetValue()
methods.Here's a kickoff example: