package org.apache.jsp;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

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

public class SelectTableServlet extends HttpServlet {

	public void doPost(HttpServletRequest request, HttpServletResponse response)
	throws IOException
	{ }
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws IOException
	{ 
		response.setContentType("text/html;charset=GBK");
		PrintWriter out = response.getWriter(); 
		
		try {
	          Class.forName("oracle.jdbc.driver.OracleDriver");
	    	  Connection con = DriverManager.getConnection("jdbc:oracle:thin:@garfield:1521:orcl","scott","tiger");
	          Statement stmt = con.createStatement();
	          String SQL_stmt = "select empno, ename, job, mgr, hiredate, sal, deptno from emp";
	          ResultSet rs = stmt.executeQuery(SQL_stmt);
	          	          
	          out.print("<TABLE BORDER='1' CELLSPACING='0' CELLPADDING='5'>");
	  		  out.print("<TR bgcolor=cyan><TH>雇員編號</TH><TH>雇員姓名</TH><TH>職位</TH><TH>所屬經理</TH><TH>雇傭日期</TH><TH>月薪</TH><TH>所在部門</TH></TR>");
	  	      	   
	  	      int empno; String ename; String job; int mgr; 
	  	      Date hiredate; double sal; int deptno;
	  	    
	  		  while(rs.next()) {        	     
	  	          empno = rs.getInt("empno");
	  	          ename = rs.getString("ename");
	              job = rs.getString("job");
	              mgr = rs.getInt("mgr");
	              hiredate = rs.getDate("hiredate");
	              sal = rs.getDouble("sal");
	              deptno = rs.getInt("deptno");

	  	    	  out.println("<TR><TD>" + empno + "</TD><TD>" + ename + "</TD><TD>" + job + "</TD><TD>"
	  	    	  		+ mgr + "</TD><TD>" + hiredate + "</TD><TD>" + sal + "</TD><TD>" + deptno + "</TD></TR>"); }
	  		  
	  		  out.println("</TABLE>");
	  		  con.close(); 
		}
		catch (ClassNotFoundException e) {
		      out.println(e.getMessage());
		}
		catch (SQLException e) { 
		      out.println(e.getMessage());
		}      
	}
}