1、创建Message实体类
public class Message {
private int id;
private int fromId;
private int toId;
private String conversationId;
private String content;
private int status;
private Date createTime;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getFromId() {
return fromId;
}
public void setFromId(int fromId) {
this.fromId = fromId;
}
public int getToId() {
return toId;
}
public void setToId(int toId) {
this.toId = toId;
}
public String getConversationId() {
return conversationId;
}
public void setConversationId(String conversationId) {
this.conversationId = conversationId;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
@Override
public String toString() {
return "Message{" +
"id=" + id +
", fromId=" + fromId +
", toId=" + toId +
", conversationId='" + conversationId + '\'' +
", content='" + content + '\'' +
", status=" + status +
", createTime=" + createTime +
'}';
}
}
2、数据访问层MessageMapper
@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);
}
message-mapper.xml
<mapper namespace="com.nowcoder.community.dao.MessageMapper">
<sql id="selectFields">
id, from_id, to_id, conversation_id, content, status, create_time
</sql>
<!-- // 查询当前用户的会话列表,针对每个会话只返回一条最新的私信.-->
<!-- List<Message> selectConversations(int userId, int offset, int limit);-->
<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>
<!-- // 查询当前用户的会话数量.-->
<!-- int selectConversationCount(int userId);-->
<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>
<!-- // 查询某个会话所包含的私信列表.-->
<!-- List<Message> selectLetters(String conversationId, int offset, int limit);-->
<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>
<!-- // 查询某个会话所包含的私信数量.-->
<!-- int selectLetterCount(String conversationId);-->
<select id="selectLetterCount" resultType="int">
select count(id)
from message
where status != 2
and from_id != 1
and conversation_id = #{conversationId}
</select>
<!-- // 查询未读私信的数量-->
<!-- int selectLetterUnreadCount(int userId, String conversationId);-->
<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>
</mapper>
测试
@Test
public void testSelectLetters() {
List<Message> list = messageMapper.selectConversations(111, 0, 20);
for (Message message : list) {
System.out.println(message);
}
int count = messageMapper.selectConversationCount(111);
System.out.println(count);
list = messageMapper.selectLetters("111_112", 0, 10);
for (Message message : list) {
System.out.println(message);
}
count = messageMapper.selectLetterCount("111_112");
System.out.println(count);
count = messageMapper.selectLetterUnreadCount(131, "111_131");
System.out.println(count);
}
3、业务层
@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);
}
}
4、表现层
会话列表->指定人私信
@Controller
public class MessageController {
@Autowired
private MessageService messageService;
@Autowired
private HostHolder hostHolder;
@Autowired
private UserService userService;
// 私信列表
@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("letterCount", messageService.findLetterCount(message.getConversationId()));
map.put("unreadCount", messageService.findLetterUnreadCount(user.getId(), message.getConversationId()));
int targetId = user.getId() == message.getFromId() ? message.getToId() : message.getFromId();
map.put("target", userService.findUserById(targetId));
conversations.add(map);
}
}
model.addAttribute("conversations", conversations);
// 查询未读消息数量
int letterUnreadCount = messageService.findLetterUnreadCount(user.getId(), null);
model.addAttribute("letterUnreadCount", letterUnreadCount);
return "/site/letter";
}
@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);
}
}
}
index.html, letter.html, letter-detail.html