Connection객체와 getConnection()메소드

package dao;

import dto.Dto;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

public class Dao {

  Connection con = null;

  PreparedStatement pstmt = null;

  ResultSet rs = null;

  static {

    try {

      Class.forName("oracle.jdbc.OracleDriver");

    } catch (ClassNotFoundException e) {

      e.printStackTrace();

    }

  }

  public Connection getConnection() {

    String url = "jdbc:oracle:thin:@localhost:1521:xe";

    String user = "hr";

    String password = "hr";

    try {

      con = DriverManager.getConnection(url, user, password);

    } catch (SQLException e) {

      e.printStackTrace();

    }

    return con;

  }

  // 전체조회 - Read

  public List<Dto> getList() {

    con = getConnection();

    String sql =

      "SELECT no, title, created_at, completed FROM todotbl order by no";

    List<Dto> list = new ArrayList<>();

    try {

      pstmt = con.prepareStatement(sql);

      rs = pstmt.executeQuery();

      while (rs.next()) {

        Dto dto = new Dto();

        dto.setNo((rs.getInt(1)));

        dto.setTitle(rs.getString(2));

        dto.setCreated_at(rs.getDate(3));

        dto.setCompleted(rs.getBoolean(4));

        list.add(dto);

      }

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      close(con, pstmt, rs);

    }

    return list;

  }

  public Dto getRow(String no) {

    con = getConnection();

    String sql = "SELECT * FROM todotbl where no = ?";

    Dto dto = null;

    try {

      pstmt = con.prepareStatement(sql);

      pstmt.setInt(1, Integer.parseInt(no));

      rs = pstmt.executeQuery();

      if (rs.next()) {

        dto = new Dto();

        dto.setNo((rs.getInt(1)));

        dto.setTitle(rs.getString(2));

        dto.setCreated_at(rs.getDate(3));

        dto.setCompleted(rs.getBoolean(4));

        dto.setDescription(rs.getString(5));

      }

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      close(con, pstmt, rs);

    }

    return dto;

  }

  public int update(Dto updatedto) {

    con = getConnection();

    String sql =

      "UPDATE TODOTBL SET COMPLETED = ?, DESCRIPTION =? WHERE NO = ?";

    int result = 0;

    try {

      pstmt = con.prepareStatement(sql);

      pstmt.setBoolean(1, updatedto.isCompleted());

      pstmt.setString(2, updatedto.getDescription());

      pstmt.setInt(3, updatedto.getNo());

      result = pstmt.executeUpdate();

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      close(con, pstmt);

    }

    return result;

  }

  public int delete(String no) {

    con = getConnection();

    String sql = "DELETE FROM TODOTBL WHERE NO = ?";

    int result = 0;

    try {

      pstmt = con.prepareStatement(sql);

      pstmt.setInt(1, Integer.parseInt(no));

      result = pstmt.executeUpdate();

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      close(con, pstmt);

    }

    return result;

  }

  public void close(Connection con, PreparedStatement pstmt, ResultSet rs) {

    try {

      if (rs != null) {

        rs.close();

      }

      if (pstmt != null) {

        pstmt.close();

      }

      if (con != null) {

        con.close();

      }

    } catch (Exception e) {

      e.printStackTrace();

    }

  }

  public void close(Connection con, PreparedStatement pstmt) {

    try {

      if (pstmt != null) {

        pstmt.close();

      }

      if (con != null) {

        con.close();

      }

    } catch (Exception e) {

      e.printStackTrace();

    }

  }

  public int insert(Dto insertdto) {

    con = getConnection();

    String sql =

      "insert into todotbl(NO,TITLE,DESCRIPTION) values(todo_seq.nextval,?,?)";

    int result = 0;

    try {

      pstmt = con.prepareStatement(sql);

      pstmt.setString(1, insertdto.getTitle());

      pstmt.setString(2, insertdto.getDescription());

      result = pstmt.executeUpdate();

    } catch (SQLException e) {

      e.printStackTrace();

    } finally {

      close(con, pstmt);

    }

    return result;

  }

}

이 코드에서 볼 수 있듯이 DB와 연동하게 할 수 있는 Connection 객체와 getConnection메소드가 구현되어있다. 데이터를 조회, 수정, 삭제, 삽입 등을 실행할 때 각 메소드별로 con = getConnection(); 이 작성되어있다. 개수가 별로 되지 않아 무방하다 볼 수 있지만 이를 구현하는 메소드가 100개가 넘고 그 이상이 된다면 매번 DB 서버에 요청을 하여 부담이 될 수 있다. 따라서 매번 DB 서버에 요청을 하는 것이 아니라 미리 가져와 필요할 때 사용가능하게 하는 것이다.

 

https://tomcat.apache.org/ ==> Documentation의 tomcat 9.0(사용하는 버전으로) ==>  10. JDBC DataSource

==> 4. Oracle 8i, 9i & 10g

1. Context configuration

<Context docBase="todo_m2" path="" reloadable="true">
<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
              url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
              username="c##test2" password="test" maxTotal="20" maxIdle="10"
              maxWaitMillis="-1"/>
</Context>

 

2. web.xml configuration

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
<display-name>Archetype Created Web Application</display-name>
<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>
</web-app>

 

3. Code example

public Connection getConnection() {
        Context initContext;
        try {
            initContext = new InitialContext();
            // java:/comp/env : 등록된 이름들을 관리하는 곳
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup("jdbc/myoracle");
            con = ds.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return con;
    }