[데이터베이스] Chapter 5: Advanced SQL

Accessing SQL from a Programming Language

* DB 프로그래머가 general-purpose programming language를 써야 하는 이유

1. SQL은 범용 프로그래밍 언어가 아니라 모든 쿼리를 표현할 수 없다.

2. Non-declarative actions 은 sql로 수행될 수 없다.

* two approaches to accessing SQL from a general-purpose programming language

(1) A general-purpose program - can connect to and communicate with a database server using a collection of functions (ex. JDBC)

(2) Embedded SQL - provides a means by which a program can interact with a database server.

  - SQL statements가 compile time에 번역되어 function calls로 바뀐다.

  - 이 function calls는 런타임에서 dynamic SQL facilities를 제공하는) API를 사용해 데이터베이스와 연결한다.

 

JDBC

JDBC: Java API for communicating with database systems supporting SQL.

JDBC supports a variety of features for querying and updating data, and for retrieving query results.
- JDBC also supports metadata retrieval, such as querying about relations present in the database and the names and types of relation attributes. (스키마 관련 정보도 가져올 수 잇음)
- Model for communicating with the database:
(1) Open a connection
(2) Create a “statement” object
(3) Execute queries using the statement object to send queries and fetch results
 * Exception mechanism to handle errors

 

JDBC Code

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);

: DB 연결에 필요한 정보들 제공.

Statement stmt = conn.createStatement(): SQL을 담을 그릇, statement를 만듬

try-catch 문과 SQLException e: 예외처리

public static void JDBCexample(String dbid, String userid, String passwd)
{
    try (Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);
            Statement stmt = conn.createStatement();
        )
    {
        ... Do Actual Work ....
    }
    catch (SQLException sqle) {
        System.out.println("SQLException : " + sqle);
    }
}

* try 소괄호에 connection 을 넣으면 try block이 끝날 때 자동으로 close된다.

 

  데이터베이스 업데이트 (ex. insert 문) - stmt.executeUpdate(); 그리고 catch (SQLException sqle)

try {
    stmt.executeUpdate(
    "insert into instructor values('77987', 'Kim', 'Physics', 98000)");
} catch (SQLException sqle)
{
    System.out.println("Could not insert tuple. " + sqle);
}

 select 문 쿼리를 실행하고 결과를 가져와 출력 - ResultSet rset = stmt.executeQuery(); 그리고 rset.next()

ResultSet rset = stmt.executeQuery(
    "select dept_name, avg (salary)
    from instructor
    group by dept_name");

while (rset.next()) {
    System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
}

 

  ResultSet rs에서 가리키고있는 원소의 특정 컬럼 값 가져오기 - rs.get타입("컬럼명"), rs.get타입(컬럼번호)

* getString, getBoolean, getInt, getFloat, getDouble, getBigDecimal, getDate, getTimestamp, getBlob 등...

- rs.getString(“dept_name”): dept_name 컬럼의 값을 문자열로 가져온다.

- rs.getInt(1): 1번째 열의 값을 정수로 가져온다.

 

 rs.getString(“dept_name”) and rs.getString(1) equivalent if
     dept_name is the first argument of select result.
 // 둘이 같다면 dept_name가 첫번째 열이라는 의미

  null 값 다루기 - rs.wasNull()

- 바로 전에 접근한 것이 null이었는지를 체크 (true/false)

int a = rs.getInt(“a”);
if (rs.wasNull()) Systems.out.println(“Got null value”);

 

Prepared Statement

PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");

pStmt.setString(1, "88877");

- ?를 사용해서 비워둔 후에 나중에 setString(컬럼번호, 값)으로 구문에 값을 넣을 수 있다. (컬럼명 안되고 컬럼번호여야 한다)

- pStmt.executeUpdate();는 일반 statement 경우와 같은 실행 구문.

PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");

pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();

 주의: 사용자가 입력한 값을 쿼리에 넣을 때는 꼭 Prepared Statement를 써야 한다. 문자열을 더하는 식으로 하면 sql injection문제!!

 

SQL Injection

Suppose query is constructed using
• "select * from instructor where name = '" + name + "'"
 Suppose the user, instead of entering a name, enters:
• X' or 'Y' = 'Y
 then the resulting statement becomes:
• "select * from instructor where name = '" + "X' or 'Y' = 'Y" + "'"
• which is:
 select * from instructor where name = 'X' or 'Y' = 'Y'
• User could have even used
 X'; update instructor set salary = salary + 10000; --

 Prepared stament internally uses:
"select * from instructor where name = 'X\' or \'Y\' = \'Y'
• Always use prepared statements, with user inputs as parameters