package book.suggest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class SearchSuggest extends HttpServlet {
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, java.io.IOException {
		String search = request.getParameter("search");//獲得請求中cate的值
		//定義查詢數據庫的SQL語句
		String sql = "select title from suggest where title like '"+search+"%' order by title";

		Connection conn = null;//聲明Connection對象
		Statement stmt = null;//聲明Statement對象
		ResultSet rs = null;//聲明ResultSet對象
		Vector vData = new Vector();
		//response.setContentType("text/xml");//設置返回數據類型為xml格式
		java.io.PrintWriter out = response.getWriter();

		try {
			// 加載數據庫驅動類
			Class.forName("com.mysql.jdbc.Driver");
			// 訪問數據庫的地址
			String url = "jdbc:mysql://localhost/search";
			//創建Connection對象
			conn = DriverManager.getConnection(url, "root", "");
			// 創建Statement對象
			stmt = conn.createStatement();
			// 執行SQL語句，返回記錄集
			rs = stmt.executeQuery(sql);
			//定義AblumEO實體對象
			while (rs.next())
			{
				vData.add(rs.getString("TITLE"));
			}
			StringBuffer buf = new StringBuffer();
			for (int i=0;i<vData.size();i++)
			{
				String keyword = (String)vData.get(i);
				buf.append(keyword+"\n");
			}
			out.print(buf.toString());
//			out.print(parasToXML(vData));//調用parasToXML()方法
		} catch (Exception e) {

			e.printStackTrace();

		} finally {//最后關必記錄集，Connection對象
			try {
				// this will close any associated ResultSets
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException sqle) {
			}
		}
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, java.io.IOException {

		doPost(request, response);
	}
/*
	public String parasToXML(Vector v) {// 該方法將數據轉化成XML格式輸出
		StringBuffer buf = new StringBuffer();
		buf.append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
		buf.append("<pictures>");
		for (int i = 0; i < v.size(); i++) {
			AlbumEO album = (AlbumEO) v.get(i);
			buf.append("<item>");
			buf.append("<name>" + album.getAlbumName() + "</name>");
			buf.append("<url>" + album.getAlbumURL() + "</url>");
			buf.append("<description>" + album.getAlbumDescription()
					+ "</description>");
			buf.append("</item>");
		}
		buf.append("</pictures>");
		return buf.toString();
	}
*/
}