[Computer Science]커넥션풀(DBCP) connection pool(오라클연결MVC2)

커넥션풀

디비와 커넥션을 맺으면 매우 느려지기때문에 데이터베이스와 애플리케이션을 횽류적으로 연결하기 위해 커넥션풀라이브러리가 생김

. Context configuration : Server에서 context.xml 파일에 웹사이트 내용을복사해 넣고 수정한다.

<Context>

    <!-- maxTotal: Maximum number of database connections in pool. Make sure you
         configure your mysqld max_connections large enough to handle
         all of your db connections. Set to -1 for no limit.
         -->

    <!-- maxIdle: Maximum number of idle database connections to retain in pool.
         Set to -1 for no limit.  See also the DBCP 2 documentation on this
         and the minEvictableIdleTimeMillis configuration parameter.
         -->

    <!-- maxWaitMillis: Maximum time to wait for a database connection to become available
         in ms, in this example 10 seconds. An Exception is thrown if
         this timeout is exceeded.  Set to -1 to wait indefinitely.
         -->

    <!-- username and password: MySQL username and password for database connections  -->

    <!-- driverClassName: Class name for the old mm.mysql JDBC driver is
         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
         -->

    <!-- url: The JDBC connection url for connecting to your MySQL database.
         -->

  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxTotal="100" maxIdle="30" maxWaitMillis="10000"
               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/javatest"/>

</Context>

(콘텍스트 안에있음)

그리고, web.xml에서 web-inf\web.xml에 res-ref-name을 확인한다

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
    version="2.4">
  <description>MySQL Test App</description>
  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
</web-app>

그리고 jsp

   public static Connection getConnection(){
		      Connection conn=null;
		      try {
		    	  Context ctx=new InitialContext();
					Context envContext  = (Context)ctx.lookup("java:/comp/env");
					DataSource ds = (DataSource)envContext.lookup("jdbc/javadbOracle");
					conn=ds.getConnection();
		      } catch (Exception e) {
		         e.printStackTrace();
		      }
		      return conn;
		   }

그 후 자바 ee툴에서 제네레이트 디플로이먼트 디스크립터 서브에 들어감

아파치 톰캣 컨텍스트 xml파일이 있음

서버.xml에 들어가면

 <Context docBase="web-06" path="/web-06" reloadable="true" source="org.eclipse.jst.jee.server:web-06"/></Host>

서버에 이걸 돌릴 수있다고 볼 수 있음

톰캣 서버에서 모듈을 보면 현재 서비스하고 있는 웹서버를 볼 수 있음

(여기다가 등록하게 되면 실행하게되면 여기 등록되어 있는 아이들이 뜨게됨)

 

 

 

 

 

오라클 설정

. Context configuration

<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
              username="scott" password="tiger" maxTotal="20" maxIdle="10"
              maxWaitMillis="-1"/>

대소문자 구분하고

jdbc에 드라이버 이름 말하고 유알엘에 xe버전

유저이름, 비밀번호를 설정함

 web.xml configuration

<resource-ref>
 <description>Oracle Datasource example</description>
 <res-ref-name>jdbc/myoracle</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>

그다음에 ref-name을 위에 맞춰놓음//이름이 반드시 같아야함

. Code example

연결해주는 class만듬

   public static Connection getConnection(){
		      Connection conn=null;
		      try {
		    	  Context ctx=new InitialContext();
					Context envContext  = (Context)ctx.lookup("java:/comp/env");
					DataSource ds = (DataSource)envContext.lookup("jdbc/javadbOracle");
					conn=ds.getConnection();
		      } catch (Exception e) {
		         e.printStackTrace();
		      }
		      return conn;
		   }

 

 

그리고 닫는창 하나 더 만들기

	   public static void close(PreparedStatement pstmt, Connection conn) {
		   
			try {
				if(pstmt!=null) pstmt.close();
				if(conn!=null) conn.close();
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	   }
	   public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		   
				try {
					if(rs!=null) rs.close();
					if(pstmt!=null) pstmt.close();
					if(conn!=null) conn.close();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		   }