JDBC SQL , Connection

https://hahagogo.tistory.com/67

JDBC 다운로드 및 Connection 테스트

---

트랜젝션 (Transaction) : 실제 커밋(DB에 반영) 되기 전까지의 모든 DB 데이터 수정 작업의 기록

커밋(Commit) : 트랜잭션 처리 과정에서 변경된 데이터를 실제 DB에 영구 저장하는 행위

롤백(Rollback) : 쉽게 말해 마지막 커밋 시점까지 되돌리기

 

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {

	public static void main(String[] args) {

		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		
		String driver = "oracle.jdbc.driver.mysqlDriver";
		String url = "jdbc:mysql:testdb:@localhost:8080";
		String uid = "user";
		String upw = "1111";
		String query = "select * from USER";
		
		try {
			Class.forName(driver);
		
			connection = DriverManager.getConnection(url, uid, upw);
			statement = connection.createStatement();
			resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				String id = resultSet.getString("user_id");
				String pw = resultSet.getString("user_pw");
				System.out.println(id + " : " + pw);
			}

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (resultSet != null)
			try {				
				resultSet.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			if (statement != null)
			try {
				statement.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			
			if (connection != null)
			try {
				connection.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
}

 

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectTest {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try{
            // 1. 드라이버 로딩
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 연결하기
            String url = "jdbc:mysql://localhost/dev";
            conn = DriverManager.getConnection(url, "dev", "dev");


            // 3. 쿼리 수행을 위한 Statement 객체 생성
            stmt = conn.createStatement();

            // 4. SQL 쿼리 작성
            // 주의사항
            // 1) JDBC에서 쿼리를 작성할 때는 세미콜론(;)을 빼고 작성한다.
            // 2) SELECT 할 때 * 으로 모든 칼럼을 가져오는 것보다
            //   가져와야 할 칼럼을 직접 명시해주는 것이 좋다.
            // 3) 원하는 결과는 쿼리로써 마무리 짓고, java 코드로 후작업 하는 것은 권하지 않음
            // 4) 쿼리를 한 줄로 쓰기 어려운 경우 들여쓰기를 사용해도 되지만 띄어쓰기에 유의 !!
            String sql = "SELECT name, owner, date_format(birth, '%Y년%m월%d일' date FROM pet";


            // 5. 쿼리 수행
            // 레코드들은 ResultSet 객체에 추가된다.
            rs = stmt.executeQuery(sql);

            // 6. 실행결과 출력하기
            while(rs.next()){
                // 레코드의 칼럼은 배열과 달리 0부터 시작하지 않고 1부터 시작한다.
                // 데이터베이스에서 가져오는 데이터의 타입에 맞게 getString 또는 getInt 등을 호출한다.
                String name = rs.getString(1);
                String owner = rs.getString(2);
                String date = rs.getString(3);

                System.out.println(name + " " + owner + " " + date);
            }
        }
        catch( ClassNotFoundException e){
            System.out.println("드라이버 로딩 실패");
        }
        catch( SQLException e){
            System.out.println("에러 " + e);
        }
        finally{
            try{
                if( conn != null && !conn.isClosed()){
                    conn.close();
                }
                if( stmt != null && !stmt.isClosed()){
                    stmt.close();
                }
                if( rs != null && !rs.isClosed()){
                    rs.close();
                }
            }
            catch( SQLException e){
                e.printStackTrace();
            }
        }
    }
}

 

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertTest {
    public static void main(String[] args) { 
        insert("와호이", "victolee", "ghd1", "m", "2022-11-11", null);
    }

    public static void insert(String name, String owner, String species,
                              String gender, String birth, String death){
        Connection conn = null;
        PreparedStatement pstmt = null;

        try{
            // 1. 드라이버 로딩
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 연결하기
            String url = "jdbc:mysql://localhost/dev";
            conn = DriverManager.getConnection(url, "dev", "dev");


            // 3. SQL 쿼리 준비
            // 추가하려는 데이터의 값은 전달된 인자를 통해 동적으로 할당되는 값이다.
            // 즉 어떤 값이 전달될지 모르므로 Select 할 때와 달리
            // stmt = conn.createStatement(); 를 작성하지 않고
            // pstmt = conn.prepareStatement(sql); 로 작성하여 데이터를 추가할 것임을 알립니다.
            // 물론 sql 쿼리 내에서 + 연산자로 한 줄로 작성할 수 있지만 가독성이 너무 떨어지게 되므로
            // 이 방법을 권합니다.
            String sql = "INSERT INTO pet VALUES (?,?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);


            // 4. 데이터 binding
            pstmt.setString(1, name);
            pstmt.setString(2, owner);
            pstmt.setString(3, species);
            pstmt.setString(4, gender);
            pstmt.setString(5, birth);
            pstmt.setString(6, death);


            // 5. 쿼리 실행 및 결과 처리
            // SELECT와 달리 INSERT는 반환되는 데이터들이 없으므로
            // ResultSet 객체가 필요 없고, 바로 pstmt.executeUpdate()메서드를 호출하면 됩니다.
            // INSERT, UPDATE, DELETE 쿼리는 이와 같이 메서드를 호출하며
            // SELECT에서는 stmt.executeQuery(sql); 메서드를 사용했었습니다.
            // @return     int - 몇 개의 row가 영향을 미쳤는지를 반환
            int count = pstmt.executeUpdate();
            if( count == 0 ){
                System.out.println("데이터 입력 실패");
            }
            else{
                System.out.println("데이터 입력 성공");
            }
        }
        catch( ClassNotFoundException e){
            System.out.println("드라이버 로딩 실패");
        }
        catch( SQLException e){
            System.out.println("에러 " + e);
        }
        finally{
            try{
                if( conn != null && !conn.isClosed()){
                    conn.close();
                }
                if( pstmt != null && !pstmt.isClosed()){
                    pstmt.close();
                }
            }
            catch( SQLException e){
                e.printStackTrace();
            }
        }
    }
}

오라클 DB의 경우 트랜젝션 작업 후에는 항상 쿼리를 실행한 뒤 커밋 을 해줘야 합니다. 

MSSQL 같은 경우는 실행 즉시 자동 커밋 됩니다.

 

뷰(View)

테이블들의 내용은 나중에 WAS에서 쉽게 가져갈 수 있도록 하기 위해 가상 조회용 테이블을 만듭니다.

호출될 때마다 새로 만들어지기 때문에 테이블의 내용이 수정되면 뷰의 내용도 같이 바뀝니다. 

CREATE VIEW VIEW_USER_INFO AS

-- 기준테이블(a)와 조인 테이블(b)에서 가져올 컬럼 지정
select a.user_id, a.user_pw, b.phone, b.email, b.address from user a
left outer join user_info b      -- 조인 테이블
on a.user_id = b.user_id;        -- 조인 조건

-- 조인된 완성본에서 다시 조회 조건을 부여할 때 조인 후 조건문 사용
-- where b.phone is not null;

---

JDBC 기본적인 연결 후 사용하기

 

1) import java.sql.*;

2) JDBC 드라이버 로딩

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

Class.forName("com.mysql.jdbc.Driver");

 

3) DBMS 서버(MySQL)와의 연결 작업을 위한 Connection 객체 생성

Connection conn = DriverManager.getConnection(url, id, password);

1. String url :  "jdbc:드라이버 종류://IP:포트번호:DB명"
2. String id : DB서버에 로그인할 계정
3. String password : DB 서버에 로그인할 비밀번호

 IP 대신 골뱅이(@)를 붙여 DNS 또는 HOST 파일에 등록된 호스트네임 또는 URL주소로 대신 사용할 수 있습니다.

jdbc:oracle:thin:@localhost:8080:DB_name

실제 DB의 IP가 바뀌는 경우들이 있을 수 있기 때문에 대부분의 연결작업은 IP로 직접하기보다는 간접적인 방식으로 설정해두고 DNS에서 IP 매핑을 시켜두는 경우가 많습니다.

 

Connection 객체는 디폴트로 오토커밋이 활성화 돼있습니다. 이 경우 insert와 같은 쿼리를 입력하면 바로 커밋이 되기 때문에 문제 발생 시 롤백이 어렵게 됩니다. 이 때 오토커밋을 비활성화 할 수 있습니다.

Connection의 커밋 메소드	설명
void setAutoCommit(boolean autoCommit)	매개변수를 false로 주면 오토커밋 해제
void commit()	커밋 수행
void rollback()	롤백 수행

try{ 
    conn.setAutoCommit(false); 
    Statement stmt = conn.createStatement(); 
    // Savepoint 설정 

    Savepoint savepoint1 = conn.setSavepoint("Savepoint1"); 
    String SQL = "INSERT INTO Employees " + "VALUES (106, 20, 'Rita', 'Tez')"; 
    stmt.executeUpdate(SQL); 

    String SQL = "INSERTED IN Employees " + "VALUES (107, 22, 'Sita', 'Tez')"; 
    stmt.executeUpdate(SQL); 
    conn.commit(); 
    
}catch(SQLException se){ 
       try {
            conn.rollback();
        } catch (Exception rollbackEx) {
            System.out.println("rollback Exception");
            rollbackEx.printStackTrace();
        }
}

 

4-1) Statement 객체를 생성하여 질의 수행

자바프로그램이 DB쪽으로 SQL query문을 전송하고,

DB가 처리된 결과를 다시 자바프로그램쪽으로 전달할 수 있도록 돕는 객체입니다.

Statement stmt = conn.createStatement();

statement = connection.createStatement();
String user_id = "coconut";
String user_pw = "12345";
String insertQuery = "insert into ORG_USER (user_id, user_pw) "
					+ "values('" + user_id + "','" + user_pw + "')";
resultCount = statement.executeUpdate(insertQuery);

Statement 주요 메소드	설명
ResultSet executeQuery(String sql)	select를 통한 정보 조회 쿼리 전송 및 결과 객체 반환
int executeUpdate(String sql)	Insert, Update, Delete 쿼리 전송 및 변경된 레코드 수 반환

 

4-2) PreparedStatement

Statement 객체와 동일하나, 쿼리를 좀 더 편리하고 정확하게 사용할 수 있다.

String user_id = "coconut";
String user_pw = "12345";
String insertQuery = 
		"insert into ORG_USER (user_id, user_pw) values (?, ?)";
PreparedStatement preState = connection.prepareStatement(insertQuery);
preState.setString(1, user_id);
preState.setString(2, user_pw);
preState.executeUpdate();

PreparedStatement 주요 메소드	설명
ResultSet executeQuery()	select를 통한 정보 조회 쿼리 전송 및 결과 객체 반환
int executeUpdate(String sql)	Insert, Update, Delete 쿼리 전송 및 변경된 레코드 수 반환

 

5) SQL문 실행 및 결과 반환

결과 반환은 ResultSet 객체를 통한다.

SQL 쿼리의 실행 결과를 ResultSet으로 받아서 정보를 저장할 오브젝트(JavaBean, DTO)에 옮겨준다. 

statement = connection.createStatement();
resultSet = statement.executeQuery(query);
resultSet.first();
while (resultSet.next()) {
    String id = resultSet.getString("user_id");
    String pw = resultSet.getString("user_pw");
    System.out.println(id + " : " + pw);
}

ResultSet 주요 메소드	설명
boolean next()	읽어올 레코드(행)가 있으면 true, 없으면 false 반환
boolean previous()	이전 레코드로 이동 (가장 첫 행이면 false 반환)
boolean first()	처음 위치로 이동 (레코드가 없을 경우 false 반환)
boolean last()	마지막 위치로 이동 (레코드가 없을 경우 false 반환)
String getString(String columnLabel)	(현재 커서가 읽을 위치의) 컬럼명에 해당하는 문자열 반환
int getInt(String columnLabel)	컬럼명에 해당하는 정수값 반환
String getString(columnIndex)	컬럼 인덱스에 해당하는 문자열 반환(1부터 시작)
int getInt(columnIndex)	컬럼 인덱스에 해당하는 정수값 반환(1부터 시작)

 

6) 자원해제 : DB 관련 작업을 하며 사용한 리소스들을 메모리에서 해제( 최근 사용한 객체부터 )

Connection의 자원은 꼭 해제해줘야 합니다. 스트림과 마찬가지입니다.

커넥션풀을 사용하더라도 반납을 위해 스레드에서 자원 해제를 해줘야 합니다.

또한 데이터 작업을 위해 생성된 Statement와 ResultSet 객체 또한 자원을 해제해줘야 합니다.

ResultSet → Statement → Connection 

String url = "jdbc:mysql://localhost/dev?useSSL=false";

    finally {
        if (resultSet != null)
        try {				
            resultSet.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        if (statement != null)
        try {
            statement.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        if (connection != null)
        try {
            connction.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

---

JDBC 커넥션풀(Connection Pool) / 스레드풀

커넥션 객체를 일정량 모아둔 Pool을 생성해두고 계속 재활용하는 방법

요청을 처리하는 각 스레드에서 커넥션을 따로 생성해주지 않고

커넥션풀의 커넥션 객체를 할당받아 사용한 뒤 반납

1. 커넥션풀(Connection Pool) 생성

톰캣 구동 시 Cannot load JDBC driver class '~~~~.XXXDriver' 에러 메세지가 뜨는 경우,

톰캣이 설치된 폴더 >  'lib' 을 찾아 드라이버 라이브러리 파일(.jar)을 넣어줍니다.

• javax.naming.Context 인터페이스 / InitialContext 클래스를 이용해 생성
• 커넥션풀을 관리해주는 javax.sql.DataSource 객체가 동시에 생성
• context.xml 파일에 DB 관련 설정 정보 입력 필요

context.xml
____________________
<Resource
    auth="Container"
    driverClassName ="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@localhost:1521:xe"
    username="admin"
    password="oracle"
    name="jdbc/Oracle11g"
    type="javax.sql.DataSource"
    initialSize="5"
    minIdle="5"
    maxIdle="50"
    maxActive="50"
    maxWait="1000"
/>

• maxIdle , maxActive 는 기본값을 같게 맞춰 주는것이 좋다.

커넥션풀 설정값 (DBCP2에서 이름)	설명
initialSize	커넥션풀 생성 시 최초 생성한 Connection 객체의 수 (기본값 0)
minIdle	최소한으로 유지될 Connection 객체의 수 (기본값 0)
maxIdle	반납된 유휴 Connection 객체를 유지할 수 있는 최대 값 (기본값 8)
maxActive (maxTotal)	동시에 사용할 수 있는 최대 커넥션 갯수 (기본값 8)
maxWait (maxWaitMillis)	할당받을 Connection 객체가 없을 때 스레드를 블록시킬 시간 (1/1000초 단위)

package control;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

@WebServlet("/")
public class Controller extends HttpServlet {
	private static final long serialVersionUID = 1L;

	public Controller() {
		super();
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doAct(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doAct(request, response);
	}

	protected void doAct(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		DataSource dataSource = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet result = null;

		try {
			// 커넥션풀 관리 객체 생성
			Context context = new InitialContext();
			dataSource = (DataSource) context.lookup
					("java:comp/env/jdbc/Oracle11g");
		} catch (NamingException e) {
			e.printStackTrace();
		}

		try {
			
			// 커넥션 객체 얻기
			conn = dataSource.getConnection();
			
			// 이후는 DB작업 동일
			stmt = conn.createStatement();
			result = stmt.executeQuery("select * from ORG_USER");

			String id, pw;
			while (result.next()) {
				id = result.getString("user_id");
				pw = result.getString("user_pw");
				System.out.println(id + " : " + pw);
			}

		} catch (SQLException e) {
			e.printStackTrace();

		// 자원 반납 필수
		} finally {
			if (result != null)
			try {				
				result.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			if (stmt != null)
			try {
				stmt.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
			
			if (conn != null)
			try {
				conn.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
	}
}