[DB #10] Introduction to SQL Programming Techniques

이석환·2023년 12월 6일
Database
0

Database

목록 보기
11/12
post-thumbnail

1. Database Applications

1-1. Introduction to Database Progrmming

  • Host language
    • Java, C/C++/C#, COBOL, some other programming language
  • Data sublanguage
    • SQL
  • Techniques and Issues
    • Interactive interface : 화면에 직접 SQL 명령어를 친다.
      • E.g. sqlplus (or SQL Developer), mysql, shell, psql, db2, ...
    • Execute a file of commands: @<filename>.sql(in Oracle)
    • Acpplication program이나 database application은 user program이나 web interface로부터 DBMS를 통해 DB에 접근할 수 있다.

1-2. 3 Approaches to Databbase Programming

1-2-1. Embedding database commands in a general-purpose programming language(common)

  • Host language에 database commands를 내장
    - Compiler가 특수 접두사 또는 EXEC SQL 가 해당 명령어를 식별한다.
  • Precompiler 또는 Preprocessor가 소스 코드를 스캔하면서 해당 statements를 식별하고, DBMS의 처리를 위해 추출한다.
  • Compile time에 SQL statements의 error를 발견할 수 있다.
  • static 시간에 SQL 문을 최적화하기 때문에, 실행 시에 최적의 성능을 제공한다.
  • Embedded SQL라고 불린다.

[EXAMPLE]

1-2-2. Using a library of database funcitions (most common)

  • Host programming language(e.g., JAVA or C(++))에서 이용 가능한 함수의 Library를 사용한다.
    • called Àpplication Programming Interface(API)
  • 각 library는 DBMS에 접근하는 여러 functions를 포함한다.
    • ODBC (Open DataBase Connectivity):
      • C언어
    • JDBC (Java DataBase Connectivity):
      • Java SE 9에 내장 (JDK에 import됨)
      • Java-compliant object database를 사용하여 Impedance mismatch를 많이 줄였다.

Impedance mismatch

  • Datebase와 programming language 간의 데이터 표현 및 처리 방식의 불일치
  • E.g. 데이터 형식 불일치
    • Database의 날짜 형식은 YYYY-MM-DD
    • Programming Language는 MM/DD/YYYY

이 경우 Impedance mismatch 문제 발생

JDBC Driver Mananger

Process는 JDBC 호출을 받고, JDBC Driver Manager를 로드해서 Database로의 연결을 설정한 다음 SQL Query을 실행한다.

1-2-3. Designing a brand-new language (less common)

  • Database(-exclusive) programming language (DBPL)은 데이터베이스 모델 및 쿼리 언어와 호환되도록 설계되어있다.
    • 집중적인 DB 액세스가 있는 애플리케이션에 더 적합
  • Database Programming Language에 loops나 conditional statements같은 프로그래밍 구조를 추가된다.
    • 완전한 프로그래밍 언어로 변환하기 위해 추가된다.
  • Programming 관점에서는 가장 빠른 성능을 보인다.
  • DBPL example: Oracle's PL/SQL, SQL Server's T-SQL

1-3. DBMS Platform & DB Programming Type

2. JDBC : SQL Class Library for Java Programming

2-1. JDBC (Java Database Connectivity)

  • DBMS Connection/Operation을 위한 Java 기반의 API의 집합
    • Also known as driver
  • 다양한 DBMS에서 접근할 수 있도록 설계되어 있다.
    • E.g. Oracle, DB2, SQL Server, MySQL ...
    • Main advantage : 하나의 Java program에서 다양한 Database로 연결할 수 있고, 작업을 수행할 수 있다.
    • DBMS에 독립적이지만, 선택한 DBMS와 연결된 적절한 Driver를 명시적으로 Load할 필요가 있다.

2-2. Relationship among Java Objects for Database Connection (in JDBC)


DriverManager : JDBC Driver를 등록하고, Database 연결을 설정한다.

Connection : Database와 연결하는 객체; 해당 연결을 통해 SQL문을 실행하고 결과를 받는다.

Statement : SQL 쿼리를 Database로 보내는 데 사용되는 Interface.; 모든 SQL문은 Statement 객체를 통해서 실행된다.

  • executeQuery: SELECT 문과 같은 쿼리를 실행하고 결과 집합을 반환한다.
  • executeUpdate: 데이터베이스에 변경을 가하는 SQL 문(INSERT, UPDATE, DELETE)을 실행하고, 실행된 Query로 영향을 받은 행의 갯수를 정수값을 반환한다.

ResultSet : èxecuteQuery로부터 반환된 결과를 나타내는 Interface

3. TestJDBC

3-1. Connecion

import java.sql.*; // import JDBC package

public class TestJDBC {
	public static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
	public static final String USER_UNIVERSITY ="university";
	public static final String USER_PASSWD ="comp322";
	
	public static void main(String[] args) throws IOException, SQLException {
		Connection conn = null;
		Statement stmt = null;
		String sql = "";
        
		try {
	        // DBMS를 위해 필요한 JDBC driver load
			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Driver Loading: Success!");
		} catch(ClassNotFoundException e) {
			System.err.println("error = " + e.getMessage());
			System.exit(1);
		}
        
		try {
	        // Connection을 위해 필요한 정보를 전달한다.
			conn = DriverManager.getConnection(URL, USER_UNIVERSITY, USER_PASSWD); 
			System.out.println("Oracle Connected.");
		} catch(SQLException ex) {
			ex.printStackTrace();
			System.err.println("Cannot get a connection: " + ex.getLocalizedMessage());
			System.err.println("Cannot get a connection: " + ex.getMessage());
			System.exit(1);
		}
   }
}

3-2. CREATE TABLE

try {
	// Auto commit 끄기
	conn.setAutoCommit(false);
    // Connection object로부터 Statement object 생성
	stmt = conn.createStatement();

	sql = "DROP TABLE " + TABLE_NAME + " CASCADE CONSTRAINT";
    // sql문 실행 (SELECT 이외의 모든 sql은 executeUpdate로 수행)
	int res = stmt.executeUpdate(sql);
	
	if (res == 0) 
    	System.out.println("Table was successfully dropped.");
        
	StringBuffer sb = new StringBuffer();
	sb.append("CREATE TABLE " + TABLE_NAME + " (Id INT, ");
	sb.append("	Name VARCHAR2(10), ");
	sb.append("	Address VARCHAR2(20))");
 
 	// TABLE 생성을 위한 query문 작성
	sql = sb.toString();
	res = stmt.executeUpdate(sql);
	if (res == 0) 
    	System.out.println("Table was successfully created.");
    
    // 수동으로 commit
	conn.commit();
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

3-3. INSERT QUERY

try {
	sql = "INSERT INTO TEST VALUES (10, 'im2sh', 'KNU')";
	int res = stmt.executeUpdate(sql);
	if (res == 0) 
    	System.out.println(res + "개의 행이 삽입되었습니다.");
        
    // 여러 개의 SQL을 한 번에 수행하기  
	sql = "INSERT INTO TEST VALUES (20, 'PARK', 'Busan')";
	stmt.addBatch(sql);
    
	sql = "INSERT INTO TEST VALUES (30, 'Rivera', 'New York')";		
	stmt.addBatch(sql);
    
	sql = "INSERT INTO TEST VALUES (40, 'Ryu', 'Los Angeles')";
	stmt.addBatch(sql);
	
    // batch를 한 번에 수행
	int[] count = stmt.executeBatch();
	System.out.println(count.length + "개의 행이 삽입되었습니다.");
    
	conn.commit();
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}
  • Batch를 사용하면 여러 query를 Memory에 올려두었다가, èxecuteBatch를 통해 한 번에 처리할 수 있다.
  • Batch에 대한 Size를 고려해야 한다.

3-4. Query

try {
	sql = "SELECT * from TEST";
	ResultSet rs = stmt.executeQuery(sql);
	
	while (rs.next()) {
		// no impedance match in JDBC
        // rs == null 이면 return된 tuple이 없어서 반복문을 벗어난다.
        // 일반적인 프로그래밍 언어와 달리 열의 번호가 1부터 시작한다.
        
		int id = rs.getInt(1);
		String name = rs.getString(2);
		String addr = rs.getString(3);
		
		System.out.println("ID = " + id + ", Name = " + name + ", Address = " + addr);
	}
	conn.commit();
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

3-5. UPDATE/DELETE

try {
	sql = "UPDATE TEST SET Name = 'OH' WHERE Id = 40";
	int res = stmt.executeUpdate(sql);
	System.out.println(res + "개의 행이 수정되었습니다.");
    
	sql = "DELETE FROM TEST WHERE Id = 30";
	stmt.addBatch(sql);
	int[] count = stmt.executeBatch();
	System.out.println(count.length + "개의 행이 삭제되었습니다.");
	conn.commit();
} catch(SQLException ex) {`
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

3-6. PreparedStatement

try {
	// ? : parameter가 전달된다.
	sql = "SELECT * from TEST WHERE Id = ?";
	PreparedStatement pstmt = conn.prepareStatement(sql);
    
    // 첫 번째 인자는 parameter가 위치한 Index
	pstmt.setInt(1, 40);
	ResultSet rs = pstmt.executeQuery();
    
	while (rs.next()) {
		int id = rs.getInt(1);
		String name = rs.getString(2);
		String addr = rs.getString(3);
        
		System.out.println("ID = " + id + ", Name = " + name + ", Address = " + addr);
	}
	
	conn.commit();
    
    // 사용했던 resource는 모두 닫아줘야 한다.
	rs.close();
	pstmt.close();
   	stmt.close();
   	conn.close();
} catch(SQLException ex) {
	System.err.println("error = " + ex.getMessage());
	System.exit(1);
}

Reference
Database System Concepts | Abraham Silberschatz
데이터베이스 시스템 7th edition

profile
이석환
반갑습니다.
이전 포스트

[DB #09] Triggers, Views, and Schema Modification

다음 포스트

[DB #11] Transaction

0개의 댓글

관련 채용 정보