Tomcat JSP DB(Oracle) 연동 소스코드

<%@ page import="java.sql.Connection"  %>
<%@ page import="java.sql.DriverManager"  %>
<%@ page import="java.sql.SQLException"  %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>

<%
 String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 String id = "scott";
 String pass = "tiger";

 Connection conn = null;
 Statement stmt = null;
 ResultSet rs = null;
 
 try{
  Class.forName("oracle.jdbc.driver.OracleDriver"); //드라이버 로딩
  conn = DriverManager.getConnection(url, id, pass); //접속경로, 아이디, 패스워드
  stmt = conn.createStatement(); //sql을 던지기 위한 도구 
  String query="select empno, ename, deptno from emp";
        // insert, update, delete 문은 executeUpdate() 을 생성
        // select 문은 executeQuery() 을 생성
  rs = stmt.executeQuery(query);
  
  while(rs.next()){
   out.println(rs.getInt("empno")+" : ");
   out.println(rs.getString("ename")+" : ");
   out.println(rs.getInt("deptno")+"<br>");  
  }
 } catch(ClassNotFoundException cnfe) {
  out.println("드라이버를 찾을 수 없습니다.");
 } catch(SQLException se) {
  out.println(se.toString());
 } finally { //닫을때는 맨마지막에 열린것부터 닫는다.
  if(rs != null) rs.close();
  if(stmt != null) stmt.close();
  if(conn != null) conn.close();
 }
%>
 
- 다른 방법

<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="javax.naming.NamingException" %>

<%@ page import="java.sql.Connection"  %>
<%@ page import="java.sql.SQLException"  %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>

<%
 Connection conn = null;
 Statement stmt = null;
 ResultSet rs = null;
 
 try{
  Context initCtx = new InitialContext(); //context 선언
  Context envCtx = (Context)initCtx.lookup("java:comp/env"); //톰캣의 설정을 가져옴
  DataSource ds = (DataSource)envCtx.lookup("jdbc/oracle"); //설정에서 jdbc/oracle을 가져옴
  conn = ds.getConnection();
  
  stmt = conn.createStatement(); //sql을 던지기 위한 도구 
  String query="select empno, ename, deptno from emp";
        // insert, update, delete => executeUpdate()
      // select => executeQuery()
  rs = stmt.executeQuery(query);
  
  while(rs.next()){
   out.println(rs.getInt("empno")+" : ");
   out.println(rs.getString("ename")+" : ");
   out.println(rs.getInt("deptno")+"<br>");  
  }
 } catch(NamingException ne) {
  out.println("설정명을 찾을 수 없습니다.");
 } catch(SQLException se) {
  out.println(se.toString());
 } finally { //닫을때는 맨마지막에 열린것부터 닫는다.
  if(rs != null) rs.close();
  if(stmt != null) stmt.close();
  if(conn != null) conn.close();
 }
%>

- server.xml 

<Context docBase="project1" path="/project1" reloadable="true" source="org.eclipse.jst.j2ee.server:project1">

        <Resource name="jdbc/oracle" auth="Contener" type="javax.sql.DataSource"
                driverClassName="oracle.jdbc.driver.OracleDriver" loginTimeout="10" maxWait="5000"
                username="scott" password="tiger" url="jdbc:oracle:thin:127.0.0.1:1521:orcl"
                testOnBorro="true"/>

</Context>