牛客网项目——项目开发（五）：私信列表，发送私信，异常处理，记录日志

1. 私信列表

1.1 数据访问层

1.1.1 数据库表

from_id：发送人id
to_id：接收人id
conversation_id：会话id，两人id拼接
status：0-未读;1-已读;2-删除
create_time：创建时间

1.1.2 entity下实现实体类Message

package com.nowcoder.community.entity;

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;

    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 +
                '}';
    }
}

1.1.3 dao下实现MessageMapper接口

package com.nowcoder.community.dao;

import com.nowcoder.community.entity.Message;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@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);
}

1.1.4 mapper下实现message-mapper.xml

<?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>

    <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>

    <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>

    <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>

    <insert id="insertMessage" parameterType="Message" keyProperty="id">
        insert into message(<include refid="insertFields"></include>)
        values(#{fromId},#{toId},#{conversationId},#{content},#{status},#{createTime})
    </insert>

    <update id="updateStatus">
        update message set status = #{status}
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

</mapper>

1.1.5 测试

@Test
public void testSelectLetters() {
   
    List<Message> list = messageMapper.selectConversations(111, 0, 20);
    for (Message message : list) {
   
        System.out.println(message);