Java MVC模式下實現站內消息發送與接收的數據庫設計方案

業務場景

用戶 A 發送站內信給用戶 B，用戶 B 登錄後查看收件箱。典型的「生產者-消費者」模型，核心在於消息實體設計與查詢效率。

數據庫表設計（核心）

CREATE TABLE `message` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '消息ID',
  `sender_id` BIGINT NOT NULL COMMENT '發送者用戶ID',
  `receiver_id` BIGINT NOT NULL COMMENT '接收者用戶ID',
  `content` VARCHAR(2000) NOT NULL COMMENT '消息內容',
  `type` TINYINT DEFAULT 1 COMMENT '類型：1系統 2私信 3通知',
  `status` TINYINT DEFAULT 0 COMMENT '狀態：0未讀 1已讀 2已刪',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '發送時間',
  `read_time` DATETIME NULL COMMENT '閱讀時間',
  PRIMARY KEY (`id`),
  INDEX `idx_receiver_status_time` (`receiver_id`, `status`, `create_time`),
  INDEX `idx_sender_time` (`sender_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='站內消息表';

MVC 分層實現

Model 層

// 實體類
@Data
public class Message {
    private Long id;
    private Long senderId;
    private Long receiverId;
    private String content;
    private Integer type;
    private Integer status;
    private Date createTime;
    private Date readTime;
}

// Mapper 接口
public interface MessageMapper {
    int insert(Message message); // 發送
    List<Message> selectByReceiver(@Param("receiverId") Long receiverId, 
                                   @Param("status") Integer status,
                                   @Param("offset") int offset, @Param("limit") int limit); // 收件箱分頁
    int updateStatus(@Param("id") Long id, @Param("status") Integer status); // 標記已讀/刪除
    int countUnread(@Param("receiverId") Long receiverId); // 未讀數
}

Service 層

@Service
@Transactional
public class MessageService {
    @Autowired MessageMapper messageMapper;

    public void sendMessage(Long senderId, Long receiverId, String content) {
        Message msg = new Message();
        msg.setSenderId(senderId);
        msg.setReceiverId(receiverId);
        msg.setContent(content);
        msg.setType(2); // 私信
        msg.setStatus(0); // 未讀
        messageMapper.insert(msg);
    }

    public PageResult<Message> getInbox(Long receiverId, int page, int size) {
        PageHelper.startPage(page, size);
        List<Message> list = messageMapper.selectByReceiver(receiverId, null, 0, size);
        return new PageResult<>(list);
    }

    public void markAsRead(Long messageId) {
        messageMapper.updateStatus(messageId, 1);
    }
}

Controller 層

@RestController
@RequestMapping("/api/message")
public class MessageController {
    @Autowired MessageService messageService;

    @PostMapping("/send")
    public Result<Void> send(@RequestBody SendDTO dto) {
        messageService.sendMessage(getCurrentUserId(), dto.getReceiverId(), dto.getContent());
        return Result.success();
    }

    @GetMapping("/inbox")
    public Result<PageResult<Message>> inbox(@RequestParam(defaultValue = "1") int page,
                                              @RequestParam(defaultValue = "20") int size) {
        return Result.success(messageService.getInbox(getCurrentUserId(), page, size));
    }

    @PutMapping("/{id}/read")
    public Result<Void> read(@PathVariable Long id) {
        messageService.markAsRead(id);
        return Result.success();
    }
}

擴展優化方向

總結

站內信系統的核心是 「發送即寫入，接收即查詢」。合理的索引設計（`receiver_id + status + time`）支撐高效分頁查詢，MVC 分層保持業務邏輯清晰。隨業務增長，可平滑演進至分佈式架構。