/*
 * s2jsp.xmal.dao.MessageDao.java
 * 2007-08-10
 * 短消息數據庫訪問類
 */
package s2jsp.xmal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

import s2jsp.xmal.entity.Message;

public class MessageDao extends BaseDao {
    private Connection        conn  = null;   // 數據庫連接
    private PreparedStatement pstmt = null;   // 創建PreparedStatement對象
    private ResultSet         rs    = null;   // 創建結果集對象

    /**
     * 保存一條消息
     * @param message
     * @return
     */
    public int save(Message message){
        String   sql   = "insert into TBL_MESSAGE(note,sendUname,receiveUname,postTime,readSign) values(?,?,?,?,0)";
        String[] param = {message.getNote(),message.getSendUname(),message.getReceiveUname(),message.getPostTime()};
        return super.executeSQL(sql, param);
    }

    /**
     * 刪除一條消息
     * @param message
     * @return
     */
    public int delete(int id){
        String sql = "delete from TBL_MESSAGE where id="+id;
        return super.executeSQL(sql, null);
    }

    /**
     * 更新讀標記
     * @param message
     * @return
     */
    public int update(Message message){
        String sql = "update TBL_MESSAGE set readSign=1 where id="+message.getId();
        return super.executeSQL(sql, null);
    }

    /**
     * 查詢參數用戶收到的消息列表
     * @param uid
     * @return
     */
    public List listByReceiveUname(String uname) {
        List list = new ArrayList();
        String sql = "select * from TBL_MESSAGE where receiveUname=?";
        try {
            conn  = this.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, uname);
            rs    = pstmt.executeQuery();
            while(rs.next()) {
                Message message = new Message();
                message.setId(rs.getInt("id"));
                message.setNote(rs.getString("note"));
                message.setSendUname(rs.getString("sendUname"));
                message.setReceiveUname(rs.getString("receiveUname"));
                message.setPostTime(rs.getString("postTime"));
                message.setReadSign(rs.getInt("readSign"));
                list.add(message);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            this.closeAll(conn, pstmt, rs);
        }
        return list;
    }
    
    /**
     * 根據短信息id查一條短信息
     * @param id
     * @return
     */
    public Message findMessage(int id){
        Message message = null;
        String sql = "select * from TBL_MESSAGE where id=?";
        try {
            conn  = this.getConn();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs    = pstmt.executeQuery();
            while(rs.next()) {
                message = new Message();
                message.setId(rs.getInt("id"));
                message.setNote(rs.getString("note"));
                message.setSendUname(rs.getString("sendUname"));
                message.setReceiveUname(rs.getString("receiveUname"));
                message.setPostTime(rs.getString("postTime"));
            }
        } catch (ClassNotFoundException e) {
            message = null;
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            this.closeAll(conn, pstmt, rs);
        }
        return message;
    }
}