b) 私信列表所展现的是当前用户和某些人的会话
c) 设置会话id的目的,方便之后的筛选方便
d) 写mapper
e) 写service,就是思考业务层的功能,如果是直接拿取Dao层的话就直接简单实现就可以了
数据库设计
from_id,to_id:从哪里来,到哪里去
conversation_id:把上面哪两个id通过 _ 连在一起。对于方向的问题,其实他们都是两个人之间的会话,就不必要分成不同方向了,就规定小id在前。
@Mapper
public interface MessageMapper {
// 查询当前用户的会话列表,针对每个会话只返回一条最新的私信.
List<Message> selectConversations(int userId, int offset, int limit);
// 查询当前用户的会话数量.
int selectConversationCount(int userId);
// 查询某个会话所包含的私信列表.
List<Message> selectLetters(String conversationId, int offset, int limit);
// 查询某个会话所包含的私信数量.
int selectLetterCount(String conversationId);
// 查询未读私信的数量
int selectLetterUnreadCount(int userId, String conversationId);
}
对应的mapper.xml
<!-- 复用-->
<sql id="selectFields">
id, from_id, to_id, conversation_id, content, status, create_time
</sql>
<sql id="insertFields">
from_id, to_id, conversation_id, content, status, create_time
</sql>
<!-- 用子查询去找 -->
<select id="selectConversations" resultType="Message">
select
<include refid="selectFields"></include>
from message
where id in (
select max(id) from message
where status != 2
and from_id != 1
and (from_id = #{userId} or to_id = #{userId})
group by conversation_id
)
order by id desc
limit #{offset}, #{limit}
</select>
<!-- 从这个子查询中查询最大id的数量-->
<select id="selectConversationCount" resultType="int">
select count(m.maxid) from (
select max(id) as maxid from message
where status != 2
and from_id != 1
and (from_id = #{userId} or to_id = #{userId})
group by conversation_id
) as m
</select>
<select id="selectLetters" resultType="Message">
select
<include refid="selectFields"></include>
from message
where status != 2
and from_id != 1
and conversation_id = #{conversationId}
order by id desc
limit #{offset}, #{limit}
</select>
<select id="selectLetterCount" resultType="int">
select count(id)
from message
where status != 2
and from_id != 1
and conversation_id = #{conversationId}
</select>
<!-- 未读,状态 0 -->
<select id="selectLetterUnreadCount" resultType="int">
select count(id)
from message
where status = 0
and from_id != 1
and to_id = #{userId}
<if test="conversationId!=null">
and conversation_id = #{conversationId}
</if>
</select>
Service
简单调用一下DAO层
@Service
public class MessageService {
@Autowired
private MessageMapper messageMapper;
public List<Message> findConversations(int userId, int offset, int limit) {
return messageMapper.selectConversations(userId, offset, limit);
}
public int findConversationCount(int userId) {
return messageMapper.selectConversationCount(userId);
}
public List<Message> findLetters(String conversationId, int offset, int limit) {
return messageMapper.selectLetters(conversationId, offset, limit);
}
public int findLetterCount(String conversationId) {
return messageMapper.selectLetterCount(conversationId);
}
public int findLetterUnreadCount(int userId, String conversationId) {
return messageMapper.selectLetterUnreadCount(userId, conversationId);
}
}
Controller
//处理私信列表
@RequestMapping(path = "/letter/list",method = RequestMethod.GET)
public String getLetterList(Model model, Page page){
User user = hostHolder.getUser();
//设置分页信息
page.setLimit(5);
page.setPath("/letter/list");
page.setRows(messageService.findConversationCount(user.getId())); //一共多少条数据
//查询会话列表
List<Message> conversationList = messageService.findConversations(user.getId(), page.getOffset(), page.getLimit());
List<Map<String,Object>> conversations = new ArrayList<>();
if (conversationList!=null){
for (Message message : conversationList){
Map<String,Object> map = new HashMap<>();
map.put("conversation",message);
//未读消息数量
map.put("unreadCount",messageService.findLetterUnreadCount(user.getId(),message.getConversationId()));
//显示会话包含几个消息
map.put("letterCount",messageService.findLetterCount(message.getConversationId()));
//判断目标Id,如果当前用户是发起者,那么targetId就是toId,否者,就是fromId
int targetId = user.getId() == message.getFromId() ? message.getToId(): message.getFromId();
map.put("target",userService.findUserById(targetId));
map.put("createTime",message.getCreateTime());
conversations.add(map);
}
}
model.addAttribute("conversations",conversations);
//查询未读消息数量
int letterUnreadCount = messageService.findLetterUnreadCount(user.getId(),null);
model.addAttribute("letterUnreadCount",letterUnreadCount);
return "site/letter";
}
//在路径之中就包含会话ID
@RequestMapping(path = "/letter/detail/{conversationId}",method = RequestMethod.GET)
public String getLetterDetail(@PathVariable("conversationId") String conversationId,Page page,Model model){
//分页信息
page.setLimit(5);
page.setPath("/letter/detail/"+conversationId);
page.setRows(messageService.findLetterCount(conversationId));
List<Message> letterList = messageService.findLetters(conversationId, page.getOffset(), page.getLimit());
List<Map<String,Object>> letters = new ArrayList<>();
if (letterList != null){
for (Message message : letterList){
Map<String,Object> map = new HashMap<>();
map.put("letter",message);
map.put("fromUser",userService.findUserById(message.getFromId()));
letters.add(map);
}
}
model.addAttribute("letters",letters);
//私信目标
model.addAttribute("target",getLetterTarget(conversationId));
return"site/letter-detail";
}
private User getLetterTarget(String conversationId){
String[] ids = conversationId.split("_");
int id0 = Integer.parseInt(ids[0]);
int id1 = Integer.parseInt(ids[1]);
//取的是目标的用户,就是当前用户对应的会话用户,所以是返回另一个
if (hostHolder.getUser().getId() == id0 ){
return userService.findUserById(id1);
}else {
return userService.findUserById(id0);
}
}
前端
<ul class="list-unstyled">
<li class="media pb-3 pt-3 mb-3 border-bottom position-relative" th:each="map:${conversations}">
<span class="badge badge-danger" th:text="${map.unreadCount}" th:if="${map.unreadCount!=0}">3</span>
<a href="profile.html">
<!-- $取变量,把地址给src,所以是变量,而不是直接去访问-->
<img th:src="${map.target.headerUrl}" class="mr-4 rounded-circle user-header" alt="用户头像" >
</a>
<div class="media-body">
<h6 class="mt-0 mb-3">
<span class="text-success" th:utext="${map.target.username}">落基山脉下的闲人</span>
<span class="float-right text-muted font-size-12" th:text="${#dates.format(map.conversation.createTime,'yyyy-MM-dd HH:mm:ss')}">2019-04-28 14:13:25</span>
</h6>
<div>
<a th:href="@{|/letter/detail/${map.conversation.conversationId}|}" th:utext="${map.conversation.content}">米粉车, 你来吧!</a>
<ul class="d-inline font-size-12 float-right">
<li class="d-inline ml-2"><a href="#" class="text-primary">共<i th:text="${map.letterCount}"></i>条会话</a></li>
</ul>
</div>
</div>
</li>
</ul>
坑:要注意model的对应属性!!!这里的conversation和conversations一个是总体的,一个是下面分属的属性!!名称要取好。