【Java Web】论坛实现私信列表

• 私信列表
  • 查询当前用户的会话列表，每个会话一条最新的私信；
  • 支持分页显示；
• 私信详情
  • 查询某个会话所包含的私信；
  • 支持分页显示。

一、数据访问层

message表中各个字段组成：

CREATE TABLE `message` (
  `id` int NOT NULL AUTO_INCREMENT,
  `from_id` int DEFAULT NULL,
  `to_id` int DEFAULT NULL,
  `conversation_id` varchar(45) NOT NULL,
  `content` text,
  `status` int 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 AUTO_INCREMENT=355 DEFAULT CHARSET=utf8mb3

1.1 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.2 MessageMapper.java

定义对数据库的操作：

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.3 Message-Mapper.xml

具体的SQL语句：

<?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) 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(id) 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
        )
    </select>
    
    <select id="selectLetters" resultType="Message">
        select <include refid="selectFields"></include>
        from message
        where conversation_id = #{conversationId}
        and status != 2
        and from_id != 1
        order by id desc
        limit #{offset}, #{limit}
    </select>

    <select id="selectLetterCount" resultType="int">
        select count(id)
        from message
        where conversation_id = #{conversationId}
        and status != 2
        and from_id != 1
    </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>

</mapper>

二、业务逻辑层

2.1 MessageService.java

package com.nowcoder.community.service;

import com.nowcoder.community.dao.MessageMapper;
import com.nowcoder.community.entity.Message;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@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 int findLetterUnreadCount(int userId, String conversationId){
        return messageMapper.selectLetterUnreadCount(userId, conversationId);
    }

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

}

三、表现层

3.1 MessageController

package com.nowcoder.community.controller;

import com.nowcoder.community.dao.MessageMapper;
import com.nowcoder.community.dao.UserMapper;
import com.nowcoder.community.entity.Message;
import com.nowcoder.community.entity.Page;
import com.nowcoder.community.entity.User;
import com.nowcoder.community.service.MessageService;
import com.nowcoder.community.service.UserService;
import com.nowcoder.community.util.HostHolder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Controller
public class MessageController {

    @Autowired
    private MessageService messageService;

    @Autowired
    private HostHolder hostHolder;

    @Autowired
    private UserMapper userMapper;

    @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("unreadCount", messageService.findLetterUnreadCount(user.getId(),message.getConversationId()));
                map.put("letterCount",messageService.findLetterCount(message.getConversationId()));
                int targetId = user.getId() == message.getFromId() ? message.getToId() : message.getFromId();
                map.put("target", userMapper.selectById(targetId));

                conversations.add(map);
            }
        }

        model.addAttribute("conversations", conversations);

        // 查询未读消息数量
        int letterUnreadCount = messageService.findLetterUnreadCount(user.getId(), null);
        model.addAttribute(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);
        }
    }

}

3.2 letter-detail.html

<!doctype html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
	<link rel="icon" href="https://static.nowcoder.com/images/logo_87_87.png"/>
	<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" crossorigin="anonymous">
	<link rel="stylesheet" th:href="@{/css/global.css}" />
	<link rel="stylesheet" th:href="@{/css/letter.css}" />
	<title>牛客网-私信详情</title>
</head>
<body>
	<div class="nk-container">
		<!-- 头部 -->
		<header class="bg-dark sticky-top" th:replace="index::header">
			<div class="container">
				<!-- 导航 -->
				<nav class="navbar navbar-expand-lg navbar-dark">
					<!-- logo -->
					<a class="navbar-brand" href="#"></a>
					<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
						<span class="navbar-toggler-icon"></span>
					</button>
					<!-- 功能 -->
					<div class="collapse navbar-collapse" id="navbarSupportedContent">
						<ul class="navbar-nav mr-auto">
							<li class="nav-item ml-3 btn-group-vertical">
								<a class="nav-link" href="../index.html">首页</a>
							</li>
							<li class="nav-item ml-3 btn-group-vertical">
								<a class="nav-link position-relative" href="letter.html">消息<span class="badge badge-danger">12</span></a>
							</li>
							<li class="nav-item ml-3 btn-group-vertical">
								<a class="nav-link" href="register.html">注册</a>
							</li>
							<li class="nav-item ml-3 btn-group-vertical">
								<a class="nav-link" href="login.html">登录</a>
							</li>
							<li class="nav-item ml-3 btn-group-vertical dropdown">
								<a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
									<img src="http://images.nowcoder.com/head/1t.png" class="rounded-circle" style="width:30px;"/>
								</a>
								<div class="dropdown-menu" aria-labelledby="navbarDropdown">
									<a class="dropdown-item text-center" href="profile.html">个人主页</a>
									<a class="dropdown-item text-center" href="setting.html">账号设置</a>
									<a class="dropdown-item text-center" href="login.html">退出登录</a>
									<div class="dropdown-divider"></div>
									<span class="dropdown-item text-center text-secondary">nowcoder</span>
								</div>
							</li>
						</ul>
						<!-- 搜索 -->
						<form class="form-inline my-2 my-lg-0" action="search.html">
							<input class="form-control mr-sm-2" type="search" aria-label="Search" />
							<button class="btn btn-outline-light my-2 my-sm-0" type="submit">搜索</button>
						</form>
					</div>
				</nav>
			</div>
		</header>

		<!-- 内容 -->
		<div class="main">
			<div class="container">
				<div class="row">
					<div class="col-8">
						<h6><b class="square"></b> 来自 <i class="text-success" th:utext="${target.username}">落基山脉下的闲人</i> 的私信</h6>
					</div>
					<div class="col-4 text-right">
						<button type="button" class="btn btn-secondary btn-sm" onclick="back();">返回</button>
						<button type="button" class="btn btn-primary btn-sm" data-toggle="modal" data-target="#sendModal">给TA私信</button>
					</div>
				</div>
				<!-- 弹出框 -->
				<div class="modal fade" id="sendModal" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
					<div class="modal-dialog modal-lg" role="document">
						<div class="modal-content">
							<div class="modal-header">
								<h5 class="modal-title" id="exampleModalLabel">发私信</h5>
								<button type="button" class="close" data-dismiss="modal" aria-label="Close">
									<span aria-hidden="true">&times;</span>
								</button>
							</div>
							<div class="modal-body">
								<form>
									<div class="form-group">
										<label for="recipient-name" class="col-form-label">发给：</label>
										<input type="text" class="form-control" id="recipient-name" value="落基山脉下的闲人">
									</div>
									<div class="form-group">
										<label for="message-text" class="col-form-label">内容：</label>
										<textarea class="form-control" id="message-text" rows="10"></textarea>
									</div>
								</form>
							</div>
							<div class="modal-footer">
								<button type="button" class="btn btn-secondary" data-dismiss="modal">取消</button>
								<button type="button" class="btn btn-primary" id="sendBtn">发送</button>
							</div>
						</div>
					</div>
				</div>
				<!-- 提示框 -->
				<div class="modal fade" id="hintModal" tabindex="-1" role="dialog" aria-labelledby="hintModalLabel" aria-hidden="true">
					<div class="modal-dialog modal-lg" role="document">
						<div class="modal-content">
							<div class="modal-header">
								<h5 class="modal-title" id="hintModalLabel">提示</h5>
							</div>
							<div class="modal-body" id="hintBody">
								发送完毕!
							</div>
						</div>
					</div>
				</div>						
				
				<!-- 私信列表 -->
				<ul class="list-unstyled mt-4">
					<li class="media pb-3 pt-3 mb-2" th:each="map:${letters}">
						<a href="profile.html">
							<img th:src="${map.fromUser.headerUrl}" class="mr-4 rounded-circle user-header" alt="用户头像" >
						</a>
						<div class="toast show d-lg-block" role="alert" aria-live="assertive" aria-atomic="true">
							<div class="toast-header">
								<strong class="mr-auto" th:utext="${map.fromUser.username}">落基山脉下的闲人</strong>
								<small th:text="${#dates.format(map.letter.createTime,'yyyy-MM-dd HH:mm:ss')}">2019-04-25 15:49:32</smallth:>
								<button type="button" class="ml-2 mb-1 close" data-dismiss="toast" aria-label="Close">
									<span aria-hidden="true">&times;</span>
								</button>
							</div>
							<div class="toast-body" th:utext="${map.letter.content}">
								君不见, 黄河之水天上来, 奔流到海不复回!
							</div>
						</div>
					</li>
				</ul>
				<!-- 分页 -->
				<nav class="mt-5" th:replace="index::pagination">
					<ul class="pagination justify-content-center">
						<li class="page-item"><a class="page-link" href="#">首页</a></li>
						<li class="page-item disabled"><a class="page-link" href="#">上一页</a></li>
						<li class="page-item active"><a class="page-link" href="#">1</a></li>
						<li class="page-item"><a class="page-link" href="#">2</a></li>
						<li class="page-item"><a class="page-link" href="#">3</a></li>
						<li class="page-item"><a class="page-link" href="#">4</a></li>
						<li class="page-item"><a class="page-link" href="#">5</a></li>
						<li class="page-item"><a class="page-link" href="#">下一页</a></li>
						<li class="page-item"><a class="page-link" href="#">末页</a></li>
					</ul>
				</nav>
			</div>
		</div>

		<!-- 尾部 -->
		<footer class="bg-dark">
			<div class="container">
				<div class="row">
					<!-- 二维码 -->
					<div class="col-4 qrcode">
						<img src="https://uploadfiles.nowcoder.com/app/app_download.png" class="img-thumbnail" style="width:136px;" />
					</div>
					<!-- 公司信息 -->
					<div class="col-8 detail-info">
						<div class="row">
							<div class="col">
								<ul class="nav">
									<li class="nav-item">
										<a class="nav-link text-light" href="#">关于我们</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">加入我们</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">意见反馈</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">企业服务</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">联系我们</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">免责声明</a>
									</li>
									<li class="nav-item">
										<a class="nav-link text-light" href="#">友情链接</a>
									</li>
								</ul>
							</div>
						</div>
						<div class="row">
							<div class="col">
								<ul class="nav btn-group-vertical company-info">
									<li class="nav-item text-white-50">
										公司地址：北京市朝阳区大屯路东金泉时代3-2708北京牛客科技有限公司
									</li>
									<li class="nav-item text-white-50">
										联系方式：010-60728802(电话)&nbsp;&nbsp;&nbsp;&nbsp;admin@nowcoder.com
									</li>
									<li class="nav-item text-white-50">
										牛客科技©2018 All rights reserved
									</li>
									<li class="nav-item text-white-50">
										京ICP备14055008号-4 &nbsp;&nbsp;&nbsp;&nbsp;
										<img src="http://static.nowcoder.com/company/images/res/ghs.png" style="width:18px;" />
										京公网安备 11010502036488号
									</li>
								</ul>
							</div>
						</div>
					</div>
				</div>
			</div>
		</footer>
	</div>

	<script src="https://code.jquery.com/jquery-3.3.1.min.js" crossorigin="anonymous"></script>
	<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" crossorigin="anonymous"></script>
	<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" crossorigin="anonymous"></script>
	<script th:src="@{/js/global.js}"></script>
	<script th:src="@{/js/letter.js}"></script>
	<script>
		function back(){
			location.href = CONTEXT_PATH + "/letter/list";
		}
	</script>
</body>
</html>