package com.jspdev.ch9;

import javax.servlet.*;
import javax.servlet.http.*;
import com.microsoft.jdbc.sqlserver.SQLServerDriver;
import java.sql.*;
import java.util.*;
import java.io.*;
public class ViewMessageServlet extends HttpServlet
{

	private Connection con;
	
	/**
	 *接收GET請求，執行數據庫操作，保存結果到request中，
	 *把視圖派發到下一個頁面
	 */
	public void doGet(HttpServletRequest request,
                      HttpServletResponse response)
        throws IOException, ServletException
    {
    	Collection ret=new ArrayList();
		try
		{
			Statement stm=con.createStatement();
			ResultSet result=stm.executeQuery("select count(*) from message");		
			int message_count=0;
			if(result.next())
			{
				message_count=result.getInt(1);
				result.close();			
			}
			if(message_count>0)
			{
				result=stm.executeQuery("select * from message order by time desc");
				
				while(result.next())
				{ 			
					String title=result.getString("title");
					String name=result.getString("name");
					String mail=result.getString("mail");
					String content=result.getString("content");
					java.sql.Date date=result.getDate("time");
					MessageVO message=new MessageVO();
					message.setName(name);
					message.setTitle(title);
					message.setContent(content);
					message.setDate(date);
					message.setEmail(mail);
					ret.add(message);						
				}
			result.close();
			stm.close();
			}			
            //保存執行結果，把視圖派發到目標
			request.setAttribute("messages",ret);
			RequestDispatcher requestDispatcher = request.getRequestDispatcher("/leaveword/jsp_servlet_javabean/viewMessages.jsp");
		    requestDispatcher.forward(request,response);
		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		
		}
    	
    }
    
    /*
     *初始化到數據庫的連接
     */
    public ViewMessageServlet()
	{
	 	String CLASSFORNAME="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	    String SERVANDDB="jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=jspdev";
	    String USER="bn";
	    String PWD="bn"; 
	    try
	    {
	    	
	    	Class.forName(CLASSFORNAME);
			con = DriverManager.getConnection(SERVANDDB,USER,PWD);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	/**
     *和GET請求的處理一樣
     */
	public void doPost(HttpServletRequest request,
	                      HttpServletResponse response)
	        throws IOException, ServletException
	    {
	    	doGet(request,response);
	    }
}