package jp.ac.kcska.questionsystem;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


@WebServlet("/ShowDBServlet")
public class ShowDBServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

		HttpSession session = request.getSession();

		Mst_userVo userVo = (Mst_userVo)session.getAttribute("userVo");
		int userid = userVo.getId();

		int categoryid = Integer.parseInt(request.getParameter("categoryid"));

		ArrayList<RecordviewVo> recordlist = execute(userid,categoryid);

		session.setAttribute("Recordlist", recordlist);
		RequestDispatcher dispatcher = request.getRequestDispatcher("/detailrecord.jsp");
		dispatcher.forward(request, response);

	}
	private ArrayList<RecordviewVo> execute(int userid,int categoryid) {

		ArrayList<RecordviewVo> recordlist = new ArrayList<RecordviewVo>();
		Connection connect = getConnection();
		PreparedStatement stmt = null;
		ResultSet rslt = null;

		try {

			String sql = "select * from recordview where (userid=?) and (categoryid=?)";
			stmt=connect.prepareStatement(sql);
			stmt.setInt(1, userid);
			stmt.setInt(2, categoryid);

			rslt = stmt.executeQuery();

			while(rslt.next()){
				RecordviewVo rec = new RecordviewVo();
				rec.setId(rslt.getInt("id"));
				rec.setUserid(rslt.getInt("userid"));
				rec.setUsername(rslt.getString("username"));
				rec.setCategoryid(rslt.getInt("categoryid"));
				rec.setCategoryname(rslt.getString("categoryname"));
				rec.setExamid(rslt.getInt("examid"));
				rec.setExamname(rslt.getString("examname"));
				rec.setDate(rslt.getTimestamp("date"));
				rec.setScore(rslt.getInt("score"));
				recordlist.add(rec);
			}

		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				connect.close();
			} catch (Exception e2) {
				// TODO: handle exception
				e2.printStackTrace();
			}
		}

		return recordlist;
	}

	private static Connection getConnection(){
		Connection connect =null;
		try {
			connect = DriverManager.getConnection(
					"jdbc:mysql://localhost/questionsystem", "root", "mysql");
			connect.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		}

		return connect;

	}

}