package org.sunxin.guestbook.beans;

import java.io.*;
import java.sql.*;
import javax.naming.*;
import javax.sql.*;

import org.sunxin.guestbook.*;

public class GuestbookDB implements Serializable
{
    private DataSource ds=null;

    public GuestbookDB()
    {
        try
        {
            Context ctx = new InitialContext();
            ds = (DataSource) ctx.lookup("java:comp/env/jdbc/guestbook");
        }
        catch (NamingException ne)
        {
            System.err.println(ne.toString());
        }
    }

    /**
     * 得到數據庫連接。
     */
    public Connection getConnection() throws SQLException
    {
       return ds.getConnection();
    }

    /**
     * 關閉連接對象。
     */
    protected void closeConnection(Connection conn)
    {
        if(conn!=null)
        {
            try
            {
                conn.close();
                conn=null;
            }
            catch (SQLException ex)
            {
                System.err.println(ex.toString());
            }
        }
    }

    /**
     * 關閉Statement對象。
     */
    protected void closeStatement(Statement stmt)
    {
        if(stmt!=null)
        {
            try
            {
                stmt.close();
                stmt=null;
            }
            catch (SQLException ex)
            {
                System.err.println(ex.toString());
            }
        }
    }

    /**
     * 關閉PreparedStatement對象。
     */
    protected void closePreparedStatement(PreparedStatement pstmt)
    {
        if(pstmt!=null)
        {
            try
            {
                pstmt.close();
                pstmt=null;
            }
            catch (SQLException ex)
            {
                System.err.println(ex.toString());
            }
        }
    }
    /**
     * 關閉ResultSet對象。
     */
    protected void closeResultSet(ResultSet rs)
    {
        if(rs!=null)
        {
            try
            {
                rs.close();
                rs=null;
            }
            catch (SQLException ex)
            {
                System.err.println(ex.toString());
            }
        }
    }
    /**
     * 注冊用戶。
     */
    public void registerUser(User user) throws UserException
    {
        Connection conn=null;
        ResultSet rs=null;
        PreparedStatement pstmt=null;
        try
        {
            conn = getConnection();
            pstmt = conn.prepareStatement(
                "select username from users where username=?");
            pstmt.setString(1,user.getUsername());
            rs=pstmt.executeQuery();
            if(rs.next())
                throw new UserException("用戶名已經存在");
            pstmt = conn.prepareStatement(
                "insert into users values(?,?,?,?)");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(3, user.getEmail());
            pstmt.setString(4, user.getStyle());
            pstmt.executeUpdate();
        }
        catch(SQLException se)
        {
            System.err.println(se.toString());
            throw new UserException("用戶注冊失??！");
        }
        finally
        {
            closeResultSet(rs);
            closePreparedStatement(pstmt);
            closeConnection(conn);
        }
    }
    /**
     * 對登錄用戶進行驗證。
     */
    public String validateUser(User user) throws UserException
    {
        Connection conn=null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = getConnection();
            pstmt = conn.prepareStatement(
                "select style from users where username=? and password=?");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            rs = pstmt.executeQuery();
            if (rs.next())
                return rs.getString(1);
            else
                return "";
        }
        catch (SQLException se)
        {
            System.err.println(se.toString());
            throw new UserException("服務器忙，無法驗證用戶，請聯系管理員。");
        }
        finally
        {
            closeResultSet(rs);
            closePreparedStatement(pstmt);
            closeConnection(conn);
        }
    }

    /**
     * 保存留言。
     */
    public void saveMessage(Message msg) throws GuestbookException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = getConnection();
            pstmt = conn.prepareStatement(
                "insert into gst(gst_user,gst_title,gst_content,gst_ip) values(?,?,?,?)");
            pstmt.setString(1, msg.getUsername());
            pstmt.setString(2, msg.getTitle());
            pstmt.setString(3, msg.getContent());
            pstmt.setString(4, msg.getIp());
            pstmt.executeUpdate();
        }
        catch (SQLException se)
        {
           System.err.println(se.toString());
           throw new GuestbookException("保存留言失敗！");
        }
        finally
        {
            closePreparedStatement(pstmt);
            closeConnection(conn);
        }
    }
    /**
     * 獲取所有留言。
     * @return ResultSet
     */
    public ResultSet getMessages() throws GuestbookException
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs=null;
        try
        {
            conn = getConnection();
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs=stmt.executeQuery("select * from gst order by gst_time desc");
            return rs;
        }
        catch (SQLException se)
        {
           System.err.println(se.toString());
           throw new GuestbookException("獲取留言操作失敗，請聯系管理員。");
        }
    }
    /**
     * 對管理員登錄進行驗證。
     */
    public boolean validateManager(User user) throws UserException
    {
        Connection conn=null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = getConnection();
            pstmt = conn.prepareStatement(
                "select username from managers where username=? and password=?");
            pstmt.setString(1, user.getUsername());
            pstmt.setString(2, user.getPassword());
            rs = pstmt.executeQuery();
            if (rs.next())
                return true;
            else
                return false;
        }
        catch (SQLException se)
        {
            System.err.println(se.toString());
            throw new UserException("服務器忙，無法驗證用戶。");
        }
        finally
        {
            closeResultSet(rs);
            closePreparedStatement(pstmt);
            closeConnection(conn);
        }
    }

    /**
     * 刪除指定的留言。
     */
    public void deleteMessage(int id) throws GuestbookException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try
        {
            conn = getConnection();
            pstmt = conn.prepareStatement(
                "delete from gst where gst_id = ?");
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        }
        catch (SQLException se)
        {
           System.err.println(se.toString());
           throw new GuestbookException("刪除留言失??！");
        }
        finally
        {
            closePreparedStatement(pstmt);
            closeConnection(conn);
        }
    }

}