私信之显示私信列表
-
私信列表
- 查询当前用户的会话列表,每个会话只显示一条最新的私信。
- 支持分页显示。
-
显示未读私信的数量
-
私信详情
- 查询某个会话所包含的私信。
- 支持分页显示
1.创建私信数据表
DROP TABLE IF EXISTS `message`;
SET character_set_client = utf8mb4 ;
CREATE TABLE `message` (
`id` int(11) NOT NULL AUTO_INCREMENT, //id
`from_id` int(11) DEFAULT NULL, //发送者id
`to_id` int(11) DEFAULT NULL, //接收者id
`conversation_id` varchar(45) NOT NULL, //会话id conversasion_id:表明通信的双方id拼接,规定小的id在前大的在后
`content` text, //私信内容
`status` int(11) DEFAULT NULL COMMENT //0-未读;1-已读;2-删除
`create_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_from_id` (`from_id`),
KEY `index_to_id` (`to_id`),
KEY `index_conversation_id` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.对应的实体类Message
import java.util.Date;
public class Message {
private int id;
private int fromId;
private int toId;
private String conversationId;
private String content;
private int status;
private Date createTime;
}
3.MessageMapper
@Mapper
@Repository
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-mapperxml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.nowcoder.community.dao.MessageMapper">
<sql id="selectFields">
id, 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) <!--每个会话只显示最近的私信内容,id最大的私信即创建的时间最近-->
from message
where status!=2 <!--状态有效-->
and from_id!=1 <!--from_id=1为系统消息-->
and (from_id = #{userId} or to_id = #{userId}) <!--发送方或者接收方等于userId即有效-->
group by conversation_id <!--根据conversation_id进行划分-->
)
order by desc
limit #{offset}, #{limit}
</select>
<!--为了支持分页,获取全部会话的数量-->
<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 create_time desc
limit #{offset},#{limit}