/*
 * Created on 2007-4-16
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package cn.edu.zucc.news.model;

import java.sql.*;
import java.util.*;


/**
 * @author wengwy
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class MsgDAO {
    private Connection conn = null;
    private Statement st = null;
    public MsgDAO() {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager
                    .getConnection(
                            "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=j2ee_news",
                            "sa", "sa");
            st = conn.createStatement();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public synchronized void release() {
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }

    }

    public synchronized List loadAllMsgs() throws Exception{
        List result = new ArrayList();
        try {
            ResultSet rs = st.executeQuery("select * from tb_msg");
            while (rs.next()) {
            	Msg msg=new Msg();
            	msg.SetMsg(rs.getString("msg"));
            	msg.SetMsg_id( Integer.parseInt(rs.getString("msg_id")));
            	msg.setSend_time(rs.getDate("send_time"));
            	msg.SetUserid(rs.getString("userid"));
                result.add(msg);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("數據庫查詢錯誤");
        }
        return result;
    }
    public synchronized void addMsg(Msg msg) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_msg where msg_id="+msg.getMsg_id());
            if (rs.next()) {
                throw new Exception("ID不能重復");
            }
            String sql="insert into tb_msg(userid,msg,send_time) values('"
            +msg.getUserid()+"','"+msg.getMsg()+"',getdate())";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
        
    }
    public synchronized void saveMsg(Msg msg) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_msg where msg_id="+msg.getMsg_id());
            if (!rs.next()) {
                throw new Exception("留言信息不存在");
            }
            String sql="update tb_msg set msg='"+msg.getMsg()
                    +"',userid='"+msg.getUserid()+"',send_time=getdate() "
                    +" where msg_id='"+msg.getMsg_id()+"'";
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
        
    }
    public synchronized void delMsg(int msg_id) throws Exception{
        try {
            ResultSet rs = st.executeQuery("select * from tb_msg where msg_id="+msg_id);
            if (!rs.next()) {
                throw new Exception("留言信息不存在");
            }
            String sql="delete from tb_msg where msg_id="+msg_id;
            System.out.println(sql);
            st.execute(sql);
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new Exception("數據庫錯誤");
        }
    }
    public synchronized Msg readMsg(String msg_id) throws Exception{
    	Msg result=null;
        try {
            ResultSet rs = st.executeQuery("select * from tb_users where msg_id="+msg_id);
            if (rs.next()) {
                result=new Msg();
                result.SetMsg(rs.getString("msg"));
                //result.SetMsg_id(rs.getString("msg_id"));
                result.SetMsg_id( Integer.parseInt(rs.getString("msg_id")));
                result.setSend_time(rs.getDate("send_time"));
                result.SetUserid(rs.getString("userid"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new Exception("數據庫查詢錯誤");
        }
        
        return result;
        
    }
    

}