package org.apache.jsp;

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

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

public class ServletConnectionPool extends HttpServlet {
	private DataSource orasrc;
	
	public void init()
	{
		try {
		      Context initcontext = new InitialContext();
		      Context context = (Context)initcontext.lookup("java:comp/env");
		      orasrc = (DataSource)context.lookup("jdbc/OracleDB");
		}
		catch(NamingException ne) {
			  ne.printStackTrace();
		}
	}
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws IOException {
		response.setContentType("text/html;charset=GBK");
		PrintWriter out = response.getWriter();
		
		try {
			  Connection con = orasrc.getConnection();
			  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(SQLException e) {
			e.printStackTrace();
		}
	}
}