数据库设计
message表
message_inbox表
junit单元测试
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
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工具类可以从我之前的帖子中找一下)
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
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
<?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需要根据自己的实际情况进行改动，根据用户查询用户所拥有的信息，需要改成自己项目中响应的用户表信息