package com.jspdev.ch9;

import com.microsoft.jdbc.sqlserver.SQLServerDriver;
import java.sql.*;
import java.util.*;

public class MessageBean
{
	private Connection con;
	MessageVO msg;
	
	//獲得數(shù)據(jù)庫連接。
	public MessageBean()
	{
	 	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
	    {
	    	//裝載數(shù)據(jù)庫驅(qū)動(dòng)程序。
	    	Class.forName(CLASSFORNAME);
			con = DriverManager.getConnection(SERVANDDB,USER,PWD);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	//設(shè)置屬性。
	public void setMessage(MessageVO msg)
	{
		this.msg=msg;
	}
	
	//增加一個(gè)消息
	public void addMessage()throws Exception
	{
		try
		{
			PreparedStatement stm=con.prepareStatement("insert into message values(?,?,?,?,?)");
			stm.setString(1,msg.getTitle());
			stm.setString(2,msg.getName());
			stm.setDate(3,new java.sql.Date(new java.util.Date().getTime()));
			if((msg.getEmail()).length()==0)
			stm.setString(5,null);
			else stm.setString(5,msg.getEmail());	
			stm.setString(4,msg.getContent());
			try 
			{
				stm.executeQuery();
			} 
			catch(Exception e) 
			{
			}
			
				con.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
	}
	//獲得所有留言消息，并且返回結(jié)果到JSP頁面。
	public Collection getMessage()throws Exception
	{
		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();
			}
			
			con.close();
		
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw e;
		}
		return ret;
	}
}