package book.albumshow;

import java.sql.*;
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 pictureServlet extends HttpServlet {
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, java.io.IOException {
		String category = request.getParameter("cate");//獲得請求中cate的值
		//定義查詢數據庫的SQL語句
		String sql = "select * from album where active_status='Y' and album_category='"
				+ category.toUpperCase() + "'";

		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/albumshow";
			//創建Connection對象
			conn = DriverManager.getConnection(url, "root", "");
			// 創建Statement對象
			stmt = conn.createStatement();
			// 執行SQL語句，返回記錄集
			rs = stmt.executeQuery(sql);
			//定義AblumEO實體對象
			AlbumEO album;
			while (rs.next()) {//循環記錄集
				album = new AlbumEO();
				album.setAlbumID(rs.getInt("ALBUM_ID"));
				album.setAlbumName(rs.getString("ALBUM_NAME"));
				album.setAlbumURL(rs.getString("ALBUM_URL"));
				album.setAlbumDescription(rs.getString("ALBUM_DESC"));
				album.setAlbumCategory(rs.getString("ALBUM_CATEGORY"));
				album.setActiveStatus(rs.getString("ACTIVE_STATUS"));
				vData.add(album);//獲取數據庫中的數據，添加到向量中
			}
			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();
	}
}