java web 站内信

数据库设计

message表

 

 

message_inbox表

 

 

junit单元测试

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
package com.alphajuns.junit;
 
import com.alphajuns.ssm.service.MessageService;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
 
import java.util.HashMap;
import java.util.Map;
 
public class MessageTest extends SuperJunitTest {
 
    @Autowired
    private MessageService messageService;
 
    @Test
    public void saveMessageTest() {
        // 封装发送消息
        // { sender:"AlphaJunS", topic: "ceshi", body:"message body" , url:"http://www.baidu.com" ,receivers:"zhangsan,lisi" }
        Map<String, String> mailMap = new HashMap<String, String>();
        mailMap.put("sender", "AlphaJunS");
        mailMap.put("topic", "通知");
        mailMap.put("body", "message body");
        mailMap.put("url", "http://www.baidu.com");
        mailMap.put("receivers", "zhangsan,lisi");
        messageService.createMessage(mailMap);
    }
 
}

 

 

MessageService.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
package com.alphajuns.ssm.service;
 
import java.util.List;
import java.util.Map;
 
public interface MessageService {
 
    public List<Map<String,?>> invoke(String methodName, Map<String,?> param) throws ReflectiveOperationException;
 
    public void deleteMessageByReceiver(Map<String,?> param);
 
    public void createMessage(Map<String,?> param);
 
    void createMessageByUserList(Map<String, ?> param);
 
}

 MessageServiceImpl.java(MyBatisBatchHelper工具类可以从我之前的帖子中找一下)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
package com.alphajuns.ssm.service.impl;
 
import com.alphajuns.ssm.mybatis.MessageMapper;
import com.alphajuns.ssm.service.MessageService;
import com.alphajuns.ssm.util.MyBatisBatchHelper;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
@Service
public class MessageServiceImpl implements MessageService {
 
    private static Logger logger = Logger.getLogger(MessageServiceImpl.class);
 
    @Autowired
    private MessageMapper messageMapper;
 
    @Autowired
    private SqlSessionFactory sqlSessionFactoryBiz;
 
    @SuppressWarnings("unchecked")
    public List<Map<String,?>> invoke(String methodName, Map<String,?> param) throws ReflectiveOperationException {
        List<Map<String,?>> returnList = null;
        Class clazz = messageMapper.getClass();
        try {
            Method m = clazz.getDeclaredMethod(methodName, Map.class);
            Object returnObj = m.invoke(messageMapper, param);
            if(returnObj instanceof List<?>){
                returnList = (List<Map<String, ?>>) returnObj;
                //调用post方法来继续处理某些负责情况
                Class serviceClazz = this.getClass();
                Method[] methods = serviceClazz.getDeclaredMethods();
                boolean hasPostMethod = false;
                for(Method method : methods){
                    logger.debug(serviceClazz.getCanonicalName()+"method:"+method);
                    if(method.getName().contains(methodName+"Post")){
                        hasPostMethod = true;
                        Object newReturnObj = method.invoke(this, param,returnList);
                        if(newReturnObj instanceof List<?>){
                            returnList = (List<Map<String, ?>>) newReturnObj;
                        }
                    }
                }
                if(!hasPostMethod){
                    logger.warn("You could add ["+methodName+"Post] to class "+serviceClazz.getCanonicalName());
                }
            }
        } catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw e;
        }
        return returnList;
    }
 
    /**
     * deleteMessageByReceiver
     * @param param(receiver, msgId)
     * @return
     */
    @Override
    public void deleteMessageByReceiver(Map<String, ?> param) {
        messageMapper.deleteMessageByReceiver(param);
        int count = messageMapper.countMessageUserByMsgId(param);
        if (count == 0) {
            messageMapper.deleteMessageByMsgId(param);
        }
    }
 
    /**
     * createMessage
     * @param param { sender:"xiazj", topic: "ceshi", body:"sdfsdf" , url:"http://www.baidu.com" ,receivers:"gengjw,Devin" }
     *
     * @return
     */
    @Override
    public void createMessage(Map<String, ?> param) {
        messageMapper.insertMessage(param);
        int msgId = (Integer) param.get("id");
 
        String receivers = (String) param.get("receivers");
        String[] receiverArr = receivers.split(",");
        Map<String, Object> tmpParam = new HashMap<String, Object>();
        tmpParam.put("msgId", msgId);
        for (String receiver : receiverArr) {
            tmpParam.put("receiver", receiver);
            messageMapper.insertMessageInbox(tmpParam);
        }
    }
 
    @SuppressWarnings("unchecked")
    @Override
    public void createMessageByUserList(Map<String, ?> param) {
        SqlSession sqlSession = MyBatisBatchHelper.openSession(sqlSessionFactoryBiz);
        MessageMapper batchMessageMapper = sqlSession.getMapper(MessageMapper.class);
        try{
            batchMessageMapper.insertMessage(param);
            int msgId = (Integer) param.get("id");
            List<String> receiverList = (List<String>) param.get("receiverList");
            Map<String, Object> tmpParam = new HashMap<String, Object>();
            tmpParam.put("msgId", msgId);
            if(receiverList!=null){
                for (String receiver : receiverList) {
                    tmpParam.put("receiver", receiver);
                    batchMessageMapper.insertMessageInbox(tmpParam);
                }
            }
        }catch(Exception e){
            MyBatisBatchHelper.rollback(sqlSession);
        }
        MyBatisBatchHelper.commit(sqlSession);
        MyBatisBatchHelper.close(sqlSession);
    }
 
}

 MessageMapper.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
package com.alphajuns.ssm.mybatis;
 
import org.apache.ibatis.annotations.Param;
 
import java.util.List;
import java.util.Map;
 
public interface MessageMapper {
 
    /**
     * deleteMessageByReceiver
     * @param param
     * @return
     */
    public abstract void deleteMessageByReceiver(@Param("paramMap") Map<String,?> param);
 
    /**
     * countMessageUserByMsgId
     * @param param
     * @return
     */
    public abstract int countMessageUserByMsgId(@Param("paramMap") Map<String,?> param);
 
    /**
     * deleteMessageByMsgId
     * @param param
     * @return
     */
    public abstract void deleteMessageByMsgId(@Param("paramMap") Map<String,?> param);
 
    /**
     * insertMessage
     * @param param
     * @return
     */
    public abstract void insertMessage(Map<String,?> param);
 
    /**
     * insertMessageInbox
     * @param param
     * @return
     */
    public abstract void insertMessageInbox(@Param("paramMap") Map<String,?> param);
 
    /**
     * queryUnreadMessageNum
     * @param param
     * @return
     */
    public abstract List<Map<String,?>> queryUnreadMessageNum(@Param("paramMap") Map<String,?> param);
 
    /**
     * updateMessageStatus
     * @param param
     * @return
     */
    public abstract void updateMessageStatus(@Param("paramMap") Map<String,?> param);
 
    /**
     * queryMessageDetail
     * @param param
     * @return
     */
    public abstract List<Map<String,?>> queryMessageDetail(@Param("paramMap") Map<String,?> param);
 
    /**
     * queryMessageList
     * @param param
     * @return
     */
    public abstract List<Map<String,?>> queryMessageList(@Param("paramMap") Map<String,?> param);
 
    /**查询类似的消息
     * querySameContentMsg
     * @param param
     * @return
     */
    public abstract List<Map<String,?>> querySameContentMsg(@Param("paramMap") Map<String,?> param);
 
}

 MessageMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
<?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.alphajuns.ssm.mybatis.MessageMapper" >
 
    <!-- 删除用户消息 -->
    <delete id="deleteMessageByReceiver" parameterType="map">
         delete from message_inbox
         where receiver = #{paramMap.receiver,jdbcType=VARCHAR}
         and msg_id = #{paramMap.msgId, jdbcType=INTEGER}
    </delete>
 
    <!-- 查询消息用户数 -->
    <select id="countMessageUserByMsgId" resultType="java.lang.Integer" parameterType="map">
        SELECT count(1)
        FROM message_inbox
        WHERE msg_id = #{paramMap.msgId, jdbcType=INTEGER}
    </select>
 
    <!-- 删除消息 -->
    <delete id="deleteMessageByMsgId" parameterType="map">
         delete from message
         where msg_id = #{paramMap.msgId, jdbcType=INTEGER}
    </delete>
 
    <!-- 创建消息 -->
    <insert id="insertMessage" parameterType="map"  >
        <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="id">
            SELECT LAST_INSERT_ID()
        </selectKey>
        insert into message (msg_id, sender, topic, body, url, msg_date)
        values (#{id, jdbcType=INTEGER}, #{sender, jdbcType=VARCHAR}, #{topic, jdbcType=VARCHAR},
        #{body, jdbcType=VARCHAR},#{url, jdbcType=VARCHAR}, now())
    </insert>
 
    <!-- 新建消息收件人关系-->
    <insert id="insertMessageInbox" parameterType="map" >
      insert into message_inbox (msg_id,receiver,flag)
      values (#{paramMap.msgId, jdbcType=INTEGER}, #{paramMap.receiver, jdbcType=VARCHAR}, '0')
    </insert>
 
    <!-- 查询未读消息数 -->
    <select id="queryUnreadMessageNum" resultType="map" parameterType="map">
        SELECT count(1) as count
        FROM MESSAGE_INBOX
        WHERE receiver = #{paramMap.receiver, jdbcType=VARCHAR}
        AND flag = '0'
    </select>
 
    <!-- 更新消息状态 -->
    <update id="updateMessageStatus" parameterType="map" >
      update message_inbox
      set    flag = #{paramMap.flag, jdbcType=VARCHAR}
      where  receiver = #{paramMap.receiver, jdbcType=VARCHAR}
      and    msg_id = #{paramMap.msgId, jdbcType=INTEGER}
    </update>
 
    <!-- 查询消息-->
    <select id="queryMessageDetail" resultType="map" parameterType="map">
        SELECT T1.FLAG, T2.MSG_ID, T2.TOPIC, T2.BODY, T2.MSG_DATE, T2.URL, NVL(T6.FULL_NAME,T2.SENDER) AS SENDER, TO_CHAR(WMSYS.WM_CONCAT (t5.FULL_NAME)) AS RECEIVERS
        FROM (SELECT * FROM TM_MESSAGE_INBOX
                WHERE MSG_ID = #{paramMap.msgId, jdbcType=INTEGER} AND RECEIVER = #{paramMap.receiver,jdbcType=VARCHAR}
            ) T1
        LEFT JOIN TM_MESSAGE T2 ON T1.MSG_ID = T2.MSG_ID
        LEFT JOIN TM_USER T6 ON T2.SENDER = T6.USER_ACCOUNT
        LEFT JOIN ( SELECT T4.FULL_NAME, T3.MSG_ID
                    FROM (SELECT RECEIVER, MSG_ID FROM TM_MESSAGE_INBOX
                            WHERE MSG_ID = #{paramMap.msgId, jdbcType=INTEGER}
                        ) T3
                    LEFT JOIN TM_USER t4 ON T3.RECEIVER = T4.USER_ACCOUNT ) T5 ON T1.MSG_ID = T5.MSG_ID
        GROUP BY T1.flag, T2.MSG_ID, T2.TOPIC, T2.BODY, T2.MSG_DATE, T2.URL, NVL(T6.FULL_NAME,T2.SENDER)
    </select>
 
    <!-- 查询消息列表 -->
    <select id="queryMessageList" resultType="map" parameterType="map">
        SELECT T1.MSG_ID, T1.FLAG, T2.SENDER, T2.TOPIC, T2.MSG_DATE, NVL(T3.FULL_NAME, T2.SENDER) as SENDER_NAME
        FROM (select MSG_ID, FLAG from TM_MESSAGE_INBOX where RECEIVER = #{paramMap.receiver, jdbcType=VARCHAR}) T1
        LEFT JOIN TM_MESSAGE T2 ON T1.MSG_ID = T2.MSG_ID
        LEFT JOIN TM_USER T3 ON T2.SENDER = T3.USER_ACCOUNT
    </select>
 
    <!-- 查询是否有相同的内容-->
    <select id="querySameContentMsg" resultType="map" parameterType="map">
        SELECT count(1) as count
        FROM TM_MESSAGE
        WHERE TOPIC like '%' || '${paramMap.topicTemplate}' || '%'
        and BODY like '%' || '${paramMap.bodyContent}' || '%'
        <if test="paramMap.limitTime!=null">
            and (sysdate>Cast(MSG_DATE As Date))<![CDATA[<]]>3
        </if>
    </select>
 
</mapper>

 

对于mapper的xml中部分statement需要根据自己的实际情况进行改动，根据用户查询用户所拥有的信息，需要改成自己项目中响应的用户表信息

 

posted @   AlphaJunS  阅读(1763)  评论(0)  编辑  收藏  举报
编辑推荐：
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
SQL AI 助手