messagedaoimpl.java

oa办公系统

/**
 * FileName:MessageDaoImpl.java,v 1.0 created in 2008-11-7 下午03:31:28
 * Created by 刘春阳
 * Copyright (c) 2008 华东交通大学
 * All Rights Reserved.
 */
package cn.jx.ecjtu.oa.ps.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.wanczy.dbutil.IResultSetHandler;

import cn.jx.ecjtu.oa.ps.dao.BaseDao;
import cn.jx.ecjtu.oa.ps.dao.MessageDao;
import cn.jx.ecjtu.oa.ps.pojo.Message;

/**
 * @todo:Description
 * @author liuchunyang
 * @version $Revision: 1.28 $
 * @since 1.0
 */

public class MessageDaoImpl extends BaseDao implements MessageDao {//dao实现类实现对dao层数据库增删查改的基本操作
	class ResultSet2Good implements IResultSetHandler {
		public Object handle(ResultSet rs) throws SQLException {
			return new Message(rs.getLong("msg_id"),
					rs.getInt("msg_type_id"),
					rs.getInt("sender_id"),
					rs.getTimestamp("send_time"),
					rs.getString("msg_info"),
					rs.getBoolean("read_state"),
					rs.getString("type_name"),
					rs.getString("type_image"),
					rs.getString("url"));				
		}
	}//对数据库中三表联合查询出来的的数据返回到Message对象的结果集中
	class ResultSet3Good implements IResultSetHandler {
		public Object handle(ResultSet rs) throws SQLException {
			return new Message(rs.getLong("msg_id"),
					rs.getInt("msg_type_id"),
					rs.getInt("sender_id"),
					rs.getTimestamp("send_time"),
					rs.getString("msg_info"),
					false,
					rs.getString("type_name"),
					rs.getString("type_image"),
					rs.getString("url"));				
		}
	}//对数据库中三表联合查询出来的的数据返回到Message对象的结果集中，并对是否已读项进行固定设置
	class ResultSetGood implements IResultSetHandler {
		public Object handle(ResultSet rs) throws SQLException {
			return new Long(rs.getLong("msg_id"));				
		}
	}//查询处消息的id号作为返回结果集
	class ResultSet4Good implements IResultSetHandler {
		public Object handle(ResultSet rs) throws SQLException {
			return new String(rs.getString("type_name"));				
		}
	}//查询处消息的类型名作为返回的结果集
	private ResultSet2Good resultSet2Good=new ResultSet2Good();
	private ResultSetGood resultSetGood=new ResultSetGood();
	private ResultSet3Good resultSet3Good = new ResultSet3Good();
	private ResultSet4Good resultSet4Good = new ResultSet4Good();
	/** (non-Javadoc)
	 * 在oa_accept_msg表中添加新短消息
	 * in:消息id号,接收者id号
	 * out:返回执行成功与否：true或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#addAcceptMessage(int, int)
	 **/
	public boolean addAcceptMessage(long msgid, int acceptid) {
		return dbAccess.executeUpdate(
				"Insert into oa_accept_msg(acc_id,msg_id,read_state,acc_del_flag) values(?,?,?,?)",
				new Object[]{acceptid,
							msgid,
							false,
							false})>0;
	}

	/** (non-Javadoc)
	 * 在oa_msg_type表中添加新的短消息类型纪录
	 * in：消息类型id号，消息类型名，消息图标，消息的url
	 * out:返回执行成功与否：true或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#addMsgType(int, java.lang.String, java.lang.String, java.lang.String)
	 **/
	public boolean addMsgType(int msgTypeId, String msgName, String msgImgPath,
			String url) {
		return dbAccess.executeUpdate(
				"Insert into oa_message_type(msg_type_id,type_name,type_image,url) values(?,?,?,?)",
				new Object[]{msgTypeId,
						msgName,
						msgImgPath,
						url})>0;
	}

	/**(non-Javadoc)
	 * 在oa_message表中添加新的短消息纪录
	 * in:Message对象
	 * out:返回执行成果与否：true或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#addsendmessage(cn.jx.ecjtu.oa.ps.pojo.Message)
	 **/
	public boolean addsendmessage(Message msg) {
		return dbAccess.executeUpdate(
				"Insert into oa_message(msg_type_id,sender_id,send_time,acc_num,send_del_flag,msg_info) " +
				"values(?,?,?,1,?,?)",
				new Object[]{msg.getMsgTypeId(),
							msg.getSendId(),
							msg.getSendTime(),
							false,
							msg.getMsgInfo()})>0;
	}

	/**(non-Javadoc)
	 * 查询oa_message表，对字段acc_num为0的纪录进行删除
	 * in:null
	 * out:返回执行成果与否：true或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#cleanMsg()
	 **/
	public boolean cleanMsg() {
		return dbAccess.executeUpdate("delete from oa_message where acc_num="+0+"and send_del_flag="+1)>0;
	}

	/** (non-Javadoc)
	 * 查询oa_accept_msg表
	 * in:要删除的接收者id号，消息id号
	 * out:返回执行成果与否：true或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#delAccMsg(int, long)
	 **/
	public boolean delAccMsg(int acceptId, long msgId) {
		return dbAccess.executeUpdate("delete from oa_accept_msg where acc_id="+acceptId+"and msg_id="+msgId)>0;
	}

	/** (non-Javadoc)
	 * 查询oa_message表，对要删除的相应的message纪录的flag字段置为false
	 * in：要删除的消息id号
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#delSendMsg(int)
	 **/
	public boolean delSendMsg(long msgId) {
		return dbAccess.executeUpdate(
				"update oa_message set send_del_flag="+1+"where msg_id="+msgId)>0;	
	}

	/** (non-Javadoc)
	 * 根据接收者id号查询未读得消息对象列表
	 * in:接收者id号
	 * out:返回查找到的消息对象列表
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#findNewMsgById(int)
	 **/
	public List<Message> findNewMsgById(int userId) {
		List list=dbAccess.executeQuery("Select * " +
				"from oa_accept_msg t1 left join oa_message t2 on t1.msg_id= t2.msg_id " +
				"left join oa_message_type t3 on t2.msg_type_id=t3.msg_type_id " +
				"where read_state= "+0+" and t1.acc_id="+userId+" and t1.acc_del_flag="+0 +
				"order by t2.send_time desc",
				resultSet2Good);
		return list;
	}

	/** (non-Javadoc)
	 * 根据发送者的id号查询已发送的消息但未删除的消息对象列表
	 * in:发送者id号,相对偏移页，本页要显示的纪录条数
	 * out:返回查找到的消息对象列表
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#findSendedMessage(int, int, int)
	 **/
	public List<Message> findSendedMessage(int userid, int offset, int num) {
		List<Message> list=dbAccess.executeQuery("select top "+(offset+num)+
				" * from  oa_message t1 " +
				"left join oa_message_type t2 on t1.msg_type_id=t2.msg_type_id " +
				"where t1.send_del_flag="+0+" and t1.sender_id= " +userid+
				" order by t1.send_time desc",
				resultSet3Good);
		for(int i=0;i<offset;i++){
			list.remove(0);
		}
		return list;
	}

	/** (non-Javadoc)
	 * 根据接收者id号查询已接收的删除标志位不为false的短消息对象列表
	 * in：接收者id号,相对偏移页，本页要显示的纪录条数
	 * out:返回查找到的短消息对象列表
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#getAccMsg(int, int, int)
	 **/
	public List<Message> getAccMsg(int userId, int offset, int num) {
		List list=dbAccess.executeQuery("Select top " +(offset+num)+
				" * from oa_accept_msg t1 left join oa_message t2 on t1.msg_id= t2.msg_id " +
				"left join oa_message_type t3 on t2.msg_type_id=t3.msg_type_id " +
				"where t1.acc_del_flag="+0+" and t1.acc_id=" +userId+
				"order by t2.send_time desc",
				resultSet2Good);
		for(int i=0;i<offset;i++){
			list.remove(0);
		}
		return list;
}

	/** (non-Javadoc)
	 * 获取对应的接收者接收到的短信息条数
	 * in:接收者id号
	 * out:查询出来的短信息条数
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#getNumAccMsg(int)
	 **/
	public int getNumAccMsg(int userId) {
		int ret=(Integer)dbAccess.getSingleValue("select COUNT(*) " +
				"from oa_accept_msg where acc_id="+userId);
		return ret;
	}

	/**(non-Javadoc)
	 * 获取对应的发送者id发送的短信id号
	 * in:接收者id号
	 * out:查询出来的短信息条数
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#getNumSendedMsg(int)
	 **/
	public int getNumSendedMsg(int userId) {
		int ret=(Integer)dbAccess.getSingleValue("select COUNT(*) " +
				"from oa_message where sender_id="+userId+" and send_del_flag = 0");
		return ret;
	}

	/** (non-Javadoc)
	 * 根据短消息id号，修改oa_message表的acc_num字段
	 * in：要设置的相应短信息发送给的用户数目AccNum
	 * out:修改成功与否：ture或false
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#updateAccNum(int,long)
	 **/
	public boolean updateAccNum(int AccNum,long msgId) {
		return dbAccess.executeUpdate(
				"update oa_message set acc_num=? where msg_id=?",
				new Object[]{
						AccNum,
						msgId
					} )>0;
	}
	
	
	/** (non-Javadoc)
	 * 根据短消息id号，获得接受该短消息的接受者数量
	 * in：要获取的相应短信息id号
	 * out:获取的接受者数量
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#getAccNum(long)
	 **/
	public int getAccNum(long msgid){	
		return (Integer)dbAccess.getSingleValue("select acc_num from oa_message where msg_id="+msgid);
	}
	
	/** (non-Javadoc)
	 * 根据短消息id号和接受者id号，设置短信息为已读
	 * in：要获取的相应短信息id号、接受者id号
	 * out:返回操作成功与否
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#setReadState(int,long)
	 **/
	public boolean setReadState(int accId,long msgid){	
		return dbAccess.executeUpdate(
				"update oa_accept_msg set read_state=? where msg_id=? and acc_id=?",
				new Object[]{
						1,
						msgid,
						accId
					} )>0;
	}
	/** (non-Javadoc)
	 * 在oa_message表中查询最新的未删除的短消息
	 * in：Null
	 * out:long
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#finNewMsgId()
	 **/
	public long findNewMsgId()
	{
		List list=dbAccess.executeQuery("select top "+1+" * from oa_message " +
				"order by oa_message.send_time desc",
				resultSetGood); 
		long ret=(Long)list.get(0);		
		return ret;
	}
	/** (non-Javadoc)
	 * 在oa_message_type表中根据消息id查询消息名
	 * in：long msgId
	 * out:String
	 * @see cn.jx.ecjtu.oa.ps.dao.MessageDao#finNewMsgId()
	 **/
	public String getTypeNameById(int msgTypeId)
	{
		List list=dbAccess.executeQuery("select type_name from oa_message_type " +
				"where msg_type_id="+msgTypeId,
				resultSet4Good);
		return list.get(0).toString();
	}
	/**
	 * 根据用户ID和查找前最新消息ID找出目前最新的消息
	 * in: int userId,int maxMsgId
	 * out:List<Message>
	 */
	public List<Message> getNewAccMsg(int userId,int maxMsgId){
		List list=dbAccess.executeQuery("select * from oa_accept_msg t1 left join oa_message t2 " +
				"on t1.msg_id=t2.msg_id left join oa_message_type t3 "+
				"on t2.msg_type_id=t3.msg_type_id where t1.acc_id="+userId+" and t1.msg_id>"+maxMsgId+
				" order by t2.send_time desc",
				resultSet2Good); 		
		return list;
	}
}