본문 바로가기

데이터베이스 Database

Chapter 6 SQL - JDBC를 이용한 자바응용프로그램 개발

DB 인터페이스

  • 응용프로그램 개발을 위한 DB 인터페이스의 종류
    • 내장 SQL 방식(Embedded SQL)
      • 응용프로그램 개발 언어(호스트 언어) + SQL
      • Precompile이 필요
      • ex) ESQL/C, ESQL/Java
    • CLI(Call Level Interface) 방식
      • 함수(메소드)를 사용하여 DBMS 접근
      • 대표적인 예: DB2 CLI
      • ODBC, JDBC로 발전함

ESQL/C 예

sample.ec

rc = EmbAppInit(dbAlias, user, pswd);
if(rc != 0) return(rc);

EXEC SQL SELECT FIRSTNAME INTO :firstname
    FROM employee
    WHERE LASTNAME = 'JOHNSON';
EMB_SQL_CHECK("SELECT statement");

printf("First name = %s\n", firstname);
rc = EmbAppTerm(dbAlias);
return(rc);

ESQL/C의 실행

JDBC

  • Java 언어를 사용하여 응용프로그램을 개발하는 경우 가장 널리 사용되는 표준적인 DBMS 인터페이스 방식
  • DBMS에 접근하여 데이터를 사용하기 위한 Java API(Application Program Interface)들을 제공
  • 기능
    • DBMS 서버에 접속(connect to server)
    • SQL 문을 전송(send SQL statements)
    • 전달된 결과를 처리(process result)

JDBC 드라이버의 종류

  • Type 1
    • JDBC-ODBC 브리지와 ODBC 드라이버 사용
  • Type 2
    • Native-API(클라이언트 라이브러리) + Java JDBC 드라이버
  • Type 3
    • 순수 Java JDBC 드라이버 + 서버 라이브러리
  • Type 4
    • 순수 Java JDBC 드라이버
    • 네트워크 프로토콜은 DBMS 별로 다름

JDBC 프로그램 구조

JDBC 프로그램 예

Class.forName(DRIVERNAME);	// JDBC 드라이버 로드

Connection con = DriverManager.getConnection(CONNECTIONURL, DBID, DBPASSWORD);	// 데이터베이스 연결

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a,b,c FROM member");	// SQL문 실행

while(rs.next())
{
    Int x = rs.getInt("a");	// 결과 처리
    String s = rs.getString("b");
    Float f = rs.getFloat("c");
}

rs.close();	// 연결 닫기
stmt.close();
con.close();

Step 1 : JDBC 드라이버 로드

드라이버 인스턴스 로드(Load)

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

ex) JDBC-ODBC : sun.jdbc.odbc.JdbcOdbcDriver / MySQL : com.mysql.jdbc.Driver

여러 개의 드라이버를 로드할 수 있음

로드된 드라이버들은 DriverManager가 관리

Step 2 : 데이터베이스 연결

  • DriverManager가 JDBC URL에 해당하는 JDBC 드라이버를 연결시키고 데이터베이스 서버에 접속된 Connection 객체를 돌려줌
  • JDBC URL: 연결할 데이터베이스를 지정하는 URL
    •  jdbc:<subprotocol>:<subname>
      • Protocol: JDBC
      • Sub_protocol: 데이터베이스 드라이버를 지정
      • Subname: 위치, 데이터베이스 이름, 아이디, 암호 등을 지정
Connection connection = 
DriverManager.getConnection("jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", "userid", "password");

MySQL의 URL: jdbc:mysql://db.pknu.ac.kr/mydb

Step 3 : SQL문 실행

  • Connection 객체로부터 Statement 객체를 생성
Statement statement = connection.createStatement();
  • Statement 객체를 통해 SQL문을 실행
    • executeQuery()
      • SELECT문을 실행 (검색)
      • 결과 레코드들을 ResultSet 객체로 변환
    • executeUpdate()
      • INSERT, UPDATE, DELETE 또는 DDL문을 실행 (갱신)
      • 갱신된 레코드의 개수를 반환
    • execute()
      • 검색 또는 갱신 모두 사용
// 조회(select)
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery(
				"select * from STUDENT where DEPT = '컴퓨터'");	// 조회된 결과 행(투플)의 집합
                
// 변경(update / insert / delete)
Statement stmt = conn.createStatement();
int rowcount = stmt.executeUpdate(
		"delete from STUDENT where SNO = 100");	// 변경된 행(투플)의 개수

Step 4 : 결과 처리

  • executeQuery 메소드의 실행 결과는 ResultSet 객체로 전달됨
  • ResultSet 내의 레코드들은 커서를 이용하여 방문
    • next 메소드를 이용하여 커서를 이동
      • next가 True를 리턴하면 다음 방문할 값이 존재함을 의미
    • getString(), getInt() and getXXX(): 레코드 내의 컬럼 값을 읽는 함수
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from STUDENT where DEPT = '컴퓨터'");

while(rs.next())
{
    int no = rs.getInt("SNO");
    String name = rs.getString("sname");
    int year = rs.getFloat(3);
    String dept = rs.getString(4);
    System.out.format("학번: %d 이름: %s 학년: %d 학과: %s\n", no, name, year, dept);
}

Step 5 : 데이터베이스 연결 닫기

  • 사용이 끝난 Statement, ResultSet, Connection 객체는 close를 호출하여 더이상 사용하지 않음을 알린다.
re.close();
stmt.close();
connection.close();

 

JDBCExample.java

package pknu.it;

import java.sql.*;

public class JDBCExample {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection conn = DriverManager.getConnection(
								"jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", 
								"userid", 
								"password");
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from student");
			
			while(rs.next()) {
				int sno = rs.getInt("sno");
				String sname = rs.getString("sname");
				int year = rs.getInt(3);
				String dept = rs.getString(4);
				System.out.format("학번: %d, 이름: %s, 학년: %d, 학과: %s \n", sno, sname, year, dept);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

 

PreparedStatement

  • SQL문을 한 번만 컴파일해서 여러 번 사용할 수 있게 함
  • 인자를 사용하여 SQL 내의 값을 변경할 수 있음
  • Statement는 실행 효율 및 보안(security) 측면에서 좋지 않음
PreparedStatement pstmt = conn.prepareStatement(
			"update STUDENT set DEPT = ? WHERE SNO = ?");
pstmt.setString(1, "IT");
pstmt.setInt(2, 300);

pstmt.executeUpdate();

JDBCExample2.java

package pknu.it;

import java.sql.*;

public class JDBCExample2 {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection conn = DriverManager.getConnection(
								"jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", 
								"userid", 
								"password");
			
			PreparedStatement stmt = conn.prepareStatement("select * from student where year = ?");
			int yearToSearch = 4;
			stmt.setInt(1, yearToSearch);
			
			ResultSet rs = stmt.executeQuery();
			
			while(rs.next()) {
				int sno = rs.getInt("sno");
				String sname = rs.getString("sname");
				int year = rs.getInt(3);
				String dept = rs.getString(4);
				System.out.format("학번: %d, 이름: %s, 학년: %d, 학과: %s \n", sno, sname, year, dept);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

 

트랜잭션의 지정

  • Connection 객체는 Default로 AutoCommit 모드임
    • 각각의SQL문이 하나의 트랜잭션으로 동작
  • 여러 개의 SQL문을 묶어서 하나의 트랜잭션을 구성하려면 AutoCommit을 False로 해야 함
con.setAutoCommit(false);
try {
	PreparedStatement pstmt = con.prepareStatement(
    "update BankAccount set amount = ? where accountId = ?");
    
    pstmt.setInt(1, -100);	pstmt.setInt(2, 13);
    pstmt.executeUpdate();
    
    pstmt.setInt(1, 100);	pstmt.setInt(2, 72);
    pstmt.executeUpdate();
    
    con.commit();
}
catch(SQLException e) {
	con.rollback();
}

JDBCExample3.java

package pknu.it;

import java.sql.*;

public class JDBCExample3 {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection conn = DriverManager.getConnection(
								"jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", 
								"userid", 
								"password");
			
			PreparedStatement stmt = conn.prepareStatement("update student set dept = ? where sno = ?");
			stmt.setString(1, "IT");
			stmt.setInt(2, 100);
			
			int rs = stmt.executeUpdate();
			
			System.out.format("%d개의 행이 변경되었습니다.\n", rs);
			
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

package pknu.it;

import java.sql.*;

public class JDBCExample {

	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection conn = DriverManager.getConnection(
								"jdbc:oracle:thin:@db.pknu.ac.kr:1521:xe", 
								"userid", 
								"password");
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from student");
			
			while(rs.next()) {
				int sno = rs.getInt("sno");
				String sname = rs.getString("sname");
				int year = rs.getInt(3);
				String dept = rs.getString(4);
				System.out.format("학번: %d, 이름: %s, 학년: %d, 학과: %s \n", sno, sname, year, dept);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

학번이 100인 학생의 학과가 IT로 변경되었다.

Developer에서도 확인해보자.

select * from student

Oracle SQL Developer에서 select문을 실행해봐도 바뀌어 있다.

(100번 학생의 학과를 다시 컴퓨터로 바꿔주었음)

developer에서 rollback 명령어를 사용해보자.

delete from student where sno = 800;

developer에는 delete문이 적용되었음
java에는 행이 삭제되지 않았음

rollback;

developer에서도 java에서 출력했을 때와 똑같은 데이터가 출력됨