JAVA 웹 개발/4. JDBC

01. JDBC

코딩 펭귄 2022. 8. 14. 09:45
📅 2021.10.04 ~ 2021.10.07

JDBC(Java Database Connectivity)

  • 자바에서 데이터베이스(오라클, MySQL, Sybase 등)에 접근할 수 있게 해 주는 Programming API이다.
  • 각 데이터베이스의 드라이버를 연결하여 데이터베이스와 자바를 연결해 주며, 해당 데이터베이스의 드라이버가 존재하여야 연결 가능하다.
  • java.sql 패키지에 존재한다.
  • OJDBC를 설치하여야 JDBC를 사용 가능하며, 다운로드 방법은 아래 두 가지 방법이 존재한다.

 

JDBC 사용 객체 ⭐⭐⭐⭐

🟥 DriverManager

  • 데이터 원본에 JDBC 드라이버를 통해 커넥션을 만드는 역할을 한다.
  • Class.forName() 메소드를 통해 생성되며, 반드시 ClassNotFoundException에 대한 예외처리를 진행하여야 한다.
  • 싱글톤 패턴으로 직접 객체 생성이 불가능하고 getConnection() 메소드를 이용하여 객체 생성이 가능하다.

🟧 Connection

  • 특정 데이터 원본과 연결된 커넥션을 나타내며, Statement 객체를 생성할 때도 Connection 객체를 사용하여 createStatement() 메소드를 호출하여 생성한다.
  • 커넥션은 Java와 DB가 연결되는 일종의 길로 쿼리와 결과물을 보내는 역할을 한다. 따라서 SQL 문장을 실행시키기 전에 우선 Connection 객체가 존재하여야 한다.

🟨 Statement

  • Connection 객체에 의해 프로그램에 리턴되는 객체를 통해 구현되는 일종의 메소드 집합 정의로 쿼리를 담는 역할을 한다.
  • Connection 클래스의 createStatement() 메소드를 호출하여 얻어지며, 생성된 Statement 객체로 질의 문장(쿼리)을 String 객체에 담아 인자로 전달하고, executeQuery() 메소드를 호출하여 SQL 질의를 수행한다.
  • 정적인 값을 넣을 때 주로 이용되지만, 동적인 값을 넣을 수 없는 것은 아니다. 
    // 동적인 값을 넣는 경우
Scanner sc = new Scanner(System.in);
int empId = sc.nextInt();
					
String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = " + empId;  
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(query);

🟩 PreparedStatement

  • Connection 객체의 prepareStatement() 메소드를 사용하여 객체를 생성한다.
  • SQL 문장이 미리 컴파일된 후 실행 시간 동안 인수 값을 위한 공간을 확보하는 점이 Statement와 다른 점이다.
  • 각 인수에 대해 위치홀더(?)를 사용하여 SQL 문장을 정의할 수 있게 한다. 
    Scanner sc = new Scanner(System.in);
int empId = sc.nextInt();
							
String query = "SELECT * FROM EMPLOYEE WHERE EMP_ID = ?";
PreparedStatement pstmt = conn.preparedStatement(query);
pstmt.seInt(1, empId); // 첫 번째 ?에 Scanner로 입력받은 empId 값을 넣음
pstmt.executeAuery(query);
  • 유동적인 값이 있는 경우 사용하기 용이하나, 정적인 값의 경우에는 사용이 불가능한 것은 아니다.

🟦 Statement vs PreparedStatement

    • Statement의 경우 주로 정적인 값을 넣고, PreparedStatement의 경우 위치 홀더(?)를 사용하여 주로 동적인 값을 넣는다.
    • Statement는 쿼리가 이미 완성된 상태이기 때문에 따로 건드릴 이유가 없다. 따라서 Connection.createStatement()를 이용하여 Statement를 만든 후 바로 Statement.executeQuery()의 매개 변수에 쿼리를 담아 보낸다. 
      String query = "SELECT * FROM EMP";
Statement stmt = Connection.createStatement();
ResultSet = stmt.executeQuery(query);
    • PreparedStatement의 경우 위치 홀더로 인해 생긴 빈 공간을 채워 준 후 쿼리를 전송하여야 하기 때문에 PreparedStatement 객체를 만들 때 쿼리를 전송한다. 그 후 setInt()/setString()/setDate() 등의 메소드를 이용하여 위치 홀더 공간을 값으로 채워 준 후 executeQuery()를 이용해 쿼리를 보낸다. 이때 쿼리는 이미 PreparedStatement가 가지고 있기 때문에 매개변수로 다시 넘기지 않아도 된다.
      setInt(parameterIndex, x):void
      parameterIndex: 위치 홀더의 자리 번호 (처음 ?가 1번이 된다.)
      x: 세팅할 값 (해당 값의 타입에 따라 메소드 이름도 달라진다. → setInt()/setString() 등) 
      String query = "SELECT * FROM EMP WHERE EMPNO = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setInt(1, empNo);
ResultSet rset = pstmt.executeQuery();

🟪 ResultSet

  • SELECT문을 사용한 질의 성공 시 반환되는 값으로, SQL 질의에 의해 생성된 테이블을 담고 있으며, 커서(cursor)를 이용하여 특정 행에 대한 참조 조작이 가능하다.

 

JDBC 코딩 절차

testJDBC1

      1. Driver 등록: DriverManager에 해당 DBMS Driver를 등록한다.
        Class.forName("JDBC 드라이버"); 형태로 드라이버를 등록하며, DBMS 종류에 따라 다르게 작성하여야 한다. 
        // 오라클 드라이버
Class.forName("oracle.jdbc.driver.OracleDriver");

// MSSQL 드라이버
Class.forName("com.Microsoft.jdbc.sqlserver.SQLServerDriver");

// MySQL 드라이버
Class.forName("org.gjt.mm.mysql.Driver");

        이때 Class.forName은 Driver가 잘못 입력되었거나, 해당 드라이버를 가지고 오기 위한 JAR 파일이 없는 경우 등 드라이버를 원활히 가져올 수 없는 경우를 위해 ClassNotFoundException가 throws 되어 있으므로 예외 처리를 진행한다.

        ⚠️ java.lang.ClassNotFoundException
        DBMS 연결 전 라이브러리 등록을 하지 않는 경우 해당 에러가 발생한다. 존재하지 않는 드라이버를 넣는 경우 발생하는 에러로 해당 드라이버가 존재하지 않거나 잘못됐다는 의미이다. 라이브러리 등록은 다음 순서로 진행한다.
        프로젝트 우클릭 → Properties → Java Build Path → Libraries → Add External JARs → 복사한 jar 파일(ojdbc8.jar) Open → Apply and Close

      2. DBMS 연결: 해당 Driver로부터 Connection 인스턴스를 획득한다.
        DriverManager를 이용하여 Connection을 생성한다.
        DriverManager.getConnection(String url, String user, String password):Connection
          • 특정 DB에 연결된 Connection을 반환하는 메소드이다.
          • url: 어느 컴퓨터의 DB에 연결할 것인지를 작성한다.
          • user: 연결할 계정 이름을 작성한다. 이때 오라클 18C 버전의 경우 C##을 붙여 계정을 생성하였다면 C##을 붙여 작성하여야 한다.
          • password: 연결할 계정의 비밀번호를 작성한다.
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe",
																				  	  "SCOTT", "SCOTT");
// jdbc:oracle:thin:  jdbc 드라이버가 thin 타입임을 의미한다.
// @127.0.0.1         접근할 데이터베이스가 내 컴퓨터에 있음을 의미한다. (내 컴퓨터에 대한 ip주소)
// 1521:xe            포트 번호(1521)와 xe 타입임을 의미한다.
        127.0.0.1의 경우 localhost로 변경하여 작성할 수 있다.

        ☝ 127.0.0.1과 localhost
        127.0.0.1은 예약된 IP 주소로 인터넷 상에 일반 IP로는 쓰일 수 없는 주소이다. OS에서 가상으로 지원하며, 랜카드 등 디바이스 자체를 통과하지 않고 소프트웨어적으로 실행된다. 예를 들면 125.209.222.141(네이버 ip주소)라고 할 수 있다.
        localhost는 컴퓨터 네트워크에서 사용하는 루프백(loopvack) 호스트명으로 자신의 컴퓨터를 의미한다. 예를 들면 www.naver.com이라고 할 수 있다.

      3. Statement 생성
      4. SQL 전송: Statement의 method를 이용하여 SQL문을 실행한다.JDBC로 쿼리를 작성하여 보내는 경우 DB에서 쿼리 문장 끝에 자동으로 세미콜론을 삽입한 후 쿼리를 수행하기 때문에 쿼리 문장에 세미콜론을 삽입하지 않아도 된다. 
        String query = "SELECT * FROM EMP";
Statement stmt = conn.createStatement();
        이때 쿼리 혹은 PreparedStatement의 set 메소드에 작성되는 컬럼명은 대소문자를 구분하지 않는다. (DB에서는 대소문자를 구분하지 않는다.)
      5. 결과 받기: 실행 결과를 ResultSet 혹은 int형 변수로 받아 처리한다.
        • 작성된 쿼리가 SELECT인 경우
          ResultSet으로 받지 않고 바로 출력하는 경우에는 'oracle.jdbc.driver.ForwardOnlyResultSet@b7f23d9'과 같은 해석 불가능한 문장이 출력되기 때문에 ResultSet으로 받아 변환 과정을 거쳐야 한다.
          ResultSet 안에 0~1개의 행이 들어가 있을 가능성이 있는 경우 if문을 이용하여 1회성으로 출력하며, 0~n개의 행이 들어가 있을 가능성이 있는 경우 while문을 이용하여 다회성으로 출력한다.
          ResultSet rset = stmt.executeQuery(query);

ArrayList<Employee> list = new ArrayList<>(); // 타입 추론 : jdk 1.6 이상부터 뒷부분의 제네릭을 생략 가능하고, 생략하는 경우 앞의 제네릭 타입을 따라간다.

while(rset.next()) { // next():boolean 다음 행이 존재한다면 true 반환
	int empNo = rset.getInt("EMPNO");
	String empName = rset.getString("ENAME");
	String job = rset.getString("JOB");
	int mgr = rset.getInt("MGR");
	Date hireDate = rset.getDate("HIREDATE");
	int sal = rset.getInt("SAL");
	int comm = rset.getInt("COMM");
	int deptNo = rset.getInt("DEPTNO");
				
	Employee emp = new Employee(empNo, empName, job, mgr, hireDate, sal, comm, deptNo);
	list.add(emp);
}
          ResultSet을 가져오는 경우 처음 커서 위치는 0이며, next() 메소드를 사용하는 경우 다음 행이 있다면 true를 반환한 후 다음 행에 커서가 놓이게 된다. 이때 getInt("컬럼명")/getString("컬럼명")/getDate("컬럼명") 메소드를 사용하는 경우 현재 머무르고 있는 행에서 해당 컬럼의 값을 가지고 온다.
          이때 컬럼명은 테이블에 존재하는 컬럼명이 아닌 쿼리에 작성된 컬럼명을 기준으로 한다. 따라서 해당 컬럼에 별칭이 있는 경우 별칭을 작성해 주어야 한다.
          ResultSet을 변환할 때 사용되는 메소드인 set타입() 메소드는 매개변수가 String 타입인 경우와 int 타입인 두 가지 경우에 대해 오버로딩 되어 있다. 이는 컬럼명으로도 가지고 올 수 있고, 컬럼의 순번으로도 가지고 올 수 있음을 의미한다. 
          // rset.getInt(columnLabel); --> 컬럼명으로 데이터 추출
result = rset.getInt("MEMBER_ID");

// rset.getInt(columnIndex); --> 컬럼 순번으로 데이터 추출
result = rset.getInt(1);
           
        • 작성된 쿼리가 SELECT가 아닌 DML(INSERT, UPDATE, DELETE)인 경우
          반환값을 executeQuery()를 사용하여 ResultSet을 이용하여 받는 것은 SELECT문으로 작성된 쿼리일 경우에만 가능하며, SELECT로 작성된 쿼리가 아닌 DML(INSERT, UPDATE, DELETE)의 경우 executeUpdate() 메소드를 사용하여 int를 반환받아야 한다.
          DB에서 DML 사용 시 "1 행이(가) 삽입/수정/삭제되었습니다"라는 문구가 출력된다. 해당 문구에서 제일 앞의 숫자와 뒤의 문구는 분리해서 봐야 하는데, 이때 1은 DB에서 정상적으로 삽입/수정/삭제가 되었을 시 반환되는 숫자이며, 뒤의 문구는 sqldeveloper가 사용자를 위해 지정해 놓은 문구이다. 따라서 executeUpdate() 메소드는 앞의 숫자를 결과로 반환한다.
          따라서 쿼리가 정상적으로 실행되었을 경우 1 이상의 값이 반환된다.
          DML이 진행된 이후에는 트랜젝션(COMMIT, ROLLBACK)까지 완료하여야 완료된 것으로 간주한다. 따라서 쿼리가 정상적으로 실행되었을 경우 커밋, 아닐 경우 롤백을 실행하여야 한다. 커밋과 롤백은 Connection의 commit(), rollback() 메소드를 이용한다.
          디폴트로 자동 커밋이 설정되어 있는데, 자동 커밋을 켜고 commit() 메소드를 실행하는 경우 "java.sql.SQLException: 자동 커밋이 설정된 채 커밋할 수 없습니다."라는 오류가 발생한다. 따라서 자동 커밋 설정을 다음과 같이 꺼 주어야 한다. 
          Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "SCOTT", "SCOTT");
conn.setAutoCommit(false);
          개발자에게 트랜젝션 권한을 주기 위한 경우 주로 자동 커밋을 끈다. 컴퓨터에게 권한을 주는 경우 원하지 않는 경우에 커밋이 될 가능성이 있다.
      6. 닫기(객체 반환)
        close의 경우 열린 순서가 아니라 반대로 진행되어야 한다. 따라서 ResultSet → Statement → Connection 순서로 close 해주어야 한다.
        객체 반환의 경우 오류가 생기더라도 필수로 진행되어야 하기 때문에 finally에 작성해 주어야 한다.

 

testJDBC2

      • testJDBC1에서 DAO가 맡은 업무
        1. JDBC 드라이버 등록
        2. DB 연결 및 Connection 객체 생성
        3. SQL 실행
        4. 트랜잭션 처리
        5. 자원 반납
        이때 원래 DAO에서 하는 업무는 SQL문을 DB로 전달하고, 전달 후 나온 결과 값을 반환하는 일만 진행하여야 한다. (3번 업무) 하지만, 지금까지는 하지 않아도 되는 1, 2, 4, 5번 업무까지 DAO가 하고 있었기 때문에 해당 업무들을 다른 클래스에서 처리해 준다. 먼저 공통적인 업무는 JDBCTemplate 클래스에서 처리하고, Controller와의 연결, 트랜잭션 처리, JDBCTemplate 불러오기 등의 업무는 Service 클래스를 만들어 처리한다.
        JDBCTemplate을 불러오는 경우 Connection 객체를 생성하여야 하는데, 이는 DAO 성격에 맞지 않기 때문에 Service 클래스로 이관한다. DAO에서는 데이터를 다루는 처리만 진행하여야 한다.
      • JDBCTemplate
        • JDBCTemplate의 경우 기본 생성자를 private으로 하고, 다른 필드와 메소드를 public static으로 선언하는 싱글톤 패턴으로 작성한다. 이는 많이 사용되는 클래스에서 계속하여 객체를 생성하는 경우 메모리에 무리를 줄 수 있기 때문에 객체를 처음 한 번만 생성하여 사용하려는 목적으로 고안된 패턴이다.
          하지만 JDBCTemplate의 경우 Connection 객체를 private static 필드로 선언하였기 때문에 완벽한 싱글톤 패턴은 아니다. (완벽한 싱글톤 패턴의 경우 모든 필드와 메소드가 public static이다.
        • DriverManager.getConnection()
          사용자 정의 메소드로 만들어서 사용한다.
          이때 Class.forName()의 매개변수 값이나 DriverManager.getConnection()의 매개변수 값을 직접 입력하는 것도 가능하지만 프로퍼티 파일에 저장한 후 해당 파일을 읽어오는 방법으로 사용할 수도 있다. 
          public static Connection getConnection() {
	if(conn == null) {
		try {
			Properties prop = new Properties();
			prop.load(new FileReader("driver.properties"));
			Class.forName(prop.getProperty("driver"));
			conn = DriverManager.getConnection(prop.getProperty("url"),
			prop.getProperty("user"),
			prop.getProperty("password"));
				
			conn.setAutoCommit(false);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}	
	}	
		return conn;
}
          프로퍼티는 키와 값이 String 타입으로만 제한된 Map이며, 파일 저장 시 아래와 같이 저장하여 키 값을 이용해 value 값을 불러온다. driver=oracle.jdbc.driver.OracleDriver
        • commit/rollback
          트랜잭션 처리 또한 반복되는 구문이기 때문에 JDBCTemplate에 메소드를 만든 후 해당 메소드를 호출해 사용하며, Connection 객체가 null이 아닌 경우와 닫히지 않은 경우에 트랜잭션 처리를 진행한다. 
          public static void commit(Connection conn) {
	try {
		if(conn != null && !conn.isClosed()) {
			conn.commit();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}
        • close
          자원 반환의 경우 여러 객체를 닫아야 하기 때문에 메소드 오버로딩을 사용한다. 자원 반환 또한 객체가 null이 아니며 이미 닫히지 않았을 경우에 닫아 주어야 한다. 
          public static void close(ResultSet rset) {
	try {
		if(rset != null && !rset.isClosed()) {
			rset.close();
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}
          이때 PreparedStatement의 경우 따로 메소드를 만들어 주지 않아도 된다. 그 이유는 PreparedStatement가 Statement를 상속받고 있기 때문에 Statement를 닫는 메소드만 있으면 다형성이 적용되어 해당 메소드에서 둘 다 처리가 가능하기 때문이다.
      • Service
        • Controller에서 호출된 후 Connection 객체를 생성하여 DAO로 보낸다. 그 후 DAO에서 받아온 객체를 트랜잭션 처리한 후 다시 Controller로 보내는 역할을 한다.
      • DAO
        • 쿼리 삽입 또한 프로퍼티 파일로 읽어 진행 가능하다.
          쿼리에 LIKE가 들어가는 경우 내가 원하는 일부분만 일치해도 가지고 오겠다는 뜻이기 때문에 데이터에 %나 _와 같은 와일드카드가 존재하여야 한다. 이때 쿼리에 LIKE가 들어가는 경우 Statement와 PreparedStatement의 코드 작성 방법에 차이가 생긴다.
          1. Statement
            String query = prop.getProperty("selectMemberId") + "'%" + id + "%'";
stmt = conn.createStatement();
rset = stmt.executeQuery(query);

            쿼리: selectMemberId=SELECT * FROM MEMBER WHERE MEMBER_ID LIKE
            쿼리 맨 끝에 공백을 넣어야 하며, 쿼리 작성 시 ' '를 추가해서 작성한다.
          2. PreparedStatement
            String query = prop.getProperty("selectMemberId");
pstmt = conn.prepareStatement(query);
pstmt.setString(1, "%" + id + "%");
rset = pstmt.executeQuery();

            쿼리: selectMemberId=SELECT * FROM MEMBER WHERE MEMBER_ID LIKE ?
            쿼리 작성 시 ' '를 작성하지 않아도 된다.
          위와 같은 차이가 발생하는 이유는 위치홀더(?) 사용 시 위치홀더 자리에 데이터가 들어가면 자동으로 그 데이터를 ' '를 이용해 감싸 주기 때문이다.
        • 위치 홀더의 값을 세팅하는 경우 PreparedStatement.setChar()는 존재하지 않기 때문에 char 타입을 넣는 경우 아래와 같이 ""을 삽입하여 문자열로 만들어 주어야 한다. 
          pstmt.setString(4, mem.getGender()+"");
        • JDBCTemplate의 메소드를 사용하는 경우 아래와 같이 메소드 앞에 JDBCTemplate을 작성해 주어야 한다. 
          JDBCTemplate.close(pstmt);
          다음과 같이 import를 하는 경우에는 생략 가능하다. 
          import static com.kh.common.JDBCTemplate.close;
        • Connection의 경우 객체를 하나만 만들어서 계속 사용하기 때문에 메소드마다 close 해 주지 않아도 되며, 프로그램이 종료되는 경우에 한 번만 close하면 된다. 
          // Connection close 함수 생성하여 사용
public void exitProgram() {
	Connection conn = getConnection();
	close(conn);
}
        • 쿼리에서 COUNT(*) 등의 그룹 함수를 사용하는 경우 무조건 ResultSet에는 결과 값이 1개 들어간다. ResultSet에 담기는 결과 값의 수는 행의 수인데, 그룹 함수를 사용하는 경우에는 해당 행에 담긴 데이터의 값이 얼마이든 행 자체는 하나만 출력되기 때문에 한 개의 결과 값만 담길 수 있다.
        🚫 PreparedStatement의 치명적인 단점
        비밀번호 수정/이메일 수정/전화번호 수정 등 하나의 값을 가지고 여러 개의 컬럼을 구분해야 하는 경우 PreparedStatement를 사용하려 아래와 같이 쿼리를 작성하려고 할 수 있으나, 이 경우 에러가 발생한다.
        UPDATE MEMBER SET ? = ? WHERE MEMBER_ID = ?
        그 이유는 PreparedStatement에서 위치 홀더를 사용하는 경우 항상 ' '로 데이터를 묶어서 쿼리로 보내는데, 컬럼명을 ' '로 묶는 경우 해당 값을 컬럼명으로 인식하지 못한다.
        ex. UPDATE MEMBER SET 'member_pwd' = '123' WHERE MEMBER_ID = 'USER01' → 'member_pwd'를 컬럼명으로 사용하려고 의도하였으나, ' '가 붙어 컬럼명으로 인식하지 못하게 된다.
        따라서 2개 이상의 경우가 존재하는 경우 아래와 같이 쿼리를 나누어 작성해 주어야 한다.
        • 잘못된 예 
          String col = null;
switch(sel) {
	case 1: col = "member_pwd"; break;
	case 2: col = "email"; break;
	case 3: col = "phone"; break;
	case 4: col = "address"; break;
}

String query = prop.getProperty("updateMember");
// updateMember=UPDATE MEMBER SET ? = ? WHERE MEMBER_ID = ?
pstmt.setString(1,col);
// 이하 생략
        • 올바른 예 
          String query = prop.getProperty("updateMember" + sel);
pstmt = conn.prepareStatement(query);
pstmt.setString(1, input);
pstmt.setString(2, memberId);

// updateMember1=UPDATE MEMBER SET MEMBER_PWD = ? WHERE MEMBER_ID = ?
// updateMember2=UPDATE MEMBER SET EMAIL = ? WHERE MEMBER_ID = ?
// updateMember3=UPDATE MEMBER SET PHONE = ? WHERE MEMBER_ID = ?
// updateMember4=UPDATE MEMBER SET ADDRESS = ? WHERE MEMBER_ID = ?
저작자표시 비영리 동일조건 (새창열림)