데이터베이스와 JDBC 연결하기

PostgreSQL CLI

PostgreSQL은 Command Line Interface로 psql이라는 실행파일을 제공한다. 이를 통해 데이터베이스를 관리할 수 있다. 콘솔로 psql을 직접 실행해도 되고 PostgreSQL에서 제공하는 SQL Shell을 사용해도 된다. 실행하면 아래와 같은 화면이 나온다.

서버 위치와 데이터베이스 이름, 포트 번호, 사용자 이름과 암호를 입력하면 해당 데이터베이스에 접근할 수 있게 된다. 여기에 SQL문을 입력하면 된다.

1. 테이블 생성

create table account(
  account_number char(5) not null,
  balance int not null,
  primary key(account_number)
);

account_number	balance

위 SQL문으로 acccount 테이블을 생성할 수 있다. account_number의 자료형은 5자리 고정 문자열이고, balance의 자료형은 정수이다. 여기까지 실행했을 때 모습은 위와 같다.

2. 레코드 삽입

insert into account values('10000', 500);
insert into account values('20000', 700);

account_number	balance
'10000'	500
'20000'	700

위 SQL문으로 account 테이블에 레코드를 삽입할 수 있다. 여기까지 실행했을 때 모습은 위와 같다.

3. 레코드 수정

update account set balance = 3000 where account_number = '10000'
update account set balance = 4500 where account_number = '20000'

account_number	balance
'10000'	3000
'20000'	4500

위 SQL문으로 account 테이블의 특정 레코드 필드를 수정할 수 있다. 여기까지 실행했을 때 모습은 위와 같다.

JDBC 불러오기

SQL문만으론 상용 어플리케이션의 비즈니스 요구사항을 충족하기가 쉽지 않다. 그래서 보통 SQL은 보조 언어로 사용하고 어플리케이션의 주언어는 일반 프로그래밍 언어로 개발하는 경우가 많다.

여기선 자바를 기준으로 살펴볼 것이다. JDBC는 자바 내부에서 SQL문을 사용해 데이터베이스에 접근할 수 있게 도와주는 라이브러리다. 일종의 API로서 데이터베이스에서 자료를 쿼리하거나 업데이트하는 방법을 제공한다. PostgreSQL의 JDBC는 아래 링크에서 다운로드 받을 수 있다.

https://jdbc.postgresql.org/download.html

IntelliJ IDEA 기준

File ➞ Project Structure... ➞ Project Settings ➞ Libraries ➞ + 버튼 ➞ Java ➞ 다운받은 PostgreSQL JDBC 추가 후 OK

JDBC 사용법

1. 데이터베이스 접속

String url = "jdbc:postgresql://<DB서버주소>:<DB서버포트>/<데이터베이스명>";
String user = "<사용자명>";
String password = "<비밀번호>";
Connection connection;

try {
    connection = DriverManager.getConnection(url, user, password);
    connection.setAutoCommit(false);
} catch (SQLException e) {
    e.printStackTrace();
    return;
}

• DB 서버 주소가 localhost일 경우: 127.0.0.1

• 기본 DB 서버 포트: 5432

자바 프로그램에서 데이터베이스 서버에 접속하기 위해 DriverManager.getConnection()으로 연결한다. 여기에 DB 서버 주소와 사용자 이름, 비밀번호를 넘겨준다. 그리고 사용자가 정의한 Transaction 단위로 commit을 진행하기 위해 자동 커밋 기능을 꺼준다.

2. SQL문 객체 생성

정적 SQL

Statement stmt = connection.createStatement();
String sql = "select balance from account where acct_no=10000";
ResultSet rs = stmt.executeQuery(sql);

String sql2 = "insert into account values('10000', 2000)";
stmt.executeUpdate(sql2);

앞서 생성한 connection 객체엔 createStatement() 메소드가 존재한다. 이 메소드는 Statement 객체를 반환하는데 Statement 객체는 내부에 저장된 SQL문을 데이터베이스로 요청하는 역할을 한다. 따라서 이 객체를 통해 데이터베이스에 쿼리나 업데이트 요청을 할 수 있다.

동적 SQL

String sql = "select balance from account where acct_no=?";
PreparedStatement getBalance = connection.prepareStatement(sql);
getBalance.setString(1, from);

앞서 생성한 connection 객체엔 prepareStatement() 메소드도 존재하는데 이는 하드코딩되지 않은 쿼리문을 생성할 때 쓰인다. 프로그래밍 언에에서 함수에 파라미터가 있는 것처럼, 일반적으로 쿼리문을 작성할 땐 유연성을 위해 쿼리문의 각 요소값을 고정하지 않는 경우가 많다. 이때 동적 SQL문인 PreparedStatement 객체를 사용한다.

이 객체는 ?가 섞인 SQL문을 파라미터로 받아서 나중에 set자료형(?번호, 값) 메소드로 SQL문의 ?에 값을 채워줄 수 있다.

쿼리

업데이트

3. 쿼리 결과 조회

String sql = "select * from account";
ResultSet rs = statement.executeQuery(sql);

while(rs.next()) {
    System.out.println(rs.getString(1));
}

Statement 객체의 executeQuery() 메소드를 실행하면 ResultSet 객체가 반환된다. 이 객체는 쿼리의 결과를 테이블 형태로 가지고 있어 next() 메소드로 레코드를 순차적으로 조회할 수 있다. 각 레코드에 기록된 필드값은 get자료형(column 번호 또는 이름)로 조회할 수 있다.

account_number	balance
'10000'	3000
'20000'	4500

ResultSet은 내부에 커서(레코드 포인터)를 저장하고 있다. ResultSet 객체가 반환됐을 때 커서가 가리키는 레코드는 첫번째 레코드 바로 전이기 때문에 ResultSet 객체의 next() 메소드를 1번 실행해야 첫번째 레코드를 조회할 수 있다. 만약 쿼리 결과가 위와 같을 때 next() 메소드를 처음 실행하면 1번째 레코드로 커서가 이동돼서, getString(1) 또는 getString("account_number") 메소드로 10000을 반환받을 수 있다. 그리고 getInt(2) 또는 getInt("balance") 메소드로 3000도 반환받을 수 있다. 다음 레코드를 조회하고 싶다면 next() 메소드를 다시 호출한다.

일반적으로 쿼리 결과의 전부를 조회하기 위해 위와 같이 반복문의 형태를 사용한다.

Transaction

Commit 또는 Abort(Rollback)

import java.sql.*;

public class Test {
    public static void main(String[] argv) {
        System.out.println("-------- PostgreSQL JDBC Connection Testing ------------");

        String url = "jdbc:postgresql://127.0.0.1:5432/postgres";
        String user = "postgres";
        String password = "lovetaeuk159!";

        Connection connection;

        try {
            connection = DriverManager.getConnection(url, user, password);
            Transaction tx = new Transaction(connection);
            if (tx.send("10000", "20000", -111)) return;
            if (tx.send("10000", "20000", 1000000000)) return;
            if (!tx.send("20000", "10000", 100)) return;
        } catch (SQLException sqle) {
            System.out.println("SQLException : " + sqle);
        }

        System.out.println("-------- PostgreSQL Test Complete --------");
    }
}

class Transaction {
    Connection connection;

    public Transaction(Connection conn) {
        connection = conn;
    }

    public boolean send(String from, String to, int amount) {
        System.out.println("Sender: " + from + ", Recipient: " + to + ", Sending amount: " + amount);
        if (amount <= 0) {
            // System.out.println("Sending amount is 0 or less");
            return false;
        }
        try {
            boolean prevAutoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);

            // System.out.println("Check sender's balance");
            PreparedStatement getBalance = connection.prepareStatement("select balance from account where acct_no=?");
            getBalance.setString(1, from);
            ResultSet rS = getBalance.executeQuery();
            int senderBalance = 0;
            if (rS.next()) {
                senderBalance = rS.getInt(1);
                if (senderBalance < amount) {
                    // System.out.println("Not enough sender's balance");
                    connection.rollback();
                    return false;
                }
            }

            // System.out.println("Check recipient's balance");
            getBalance.setString(1, to);
            ResultSet rS2 = getBalance.executeQuery();
            int recipientBalance = 0;
            if (rS2.next()) {
                recipientBalance = rS2.getInt("balance");
            }

            PreparedStatement updateBalance = connection.prepareStatement("update account set balance=? where acct_no=?");
            // System.out.println("Update sender's balance");
            updateBalance.setInt(1, senderBalance - amount);
            updateBalance.setString(2, from);
            updateBalance.executeUpdate();
            // System.out.println("Update recipient's balance");
            updateBalance.setInt(1, recipientBalance + amount);
            updateBalance.setString(2, to);
            updateBalance.executeUpdate();

            connection.commit();
            connection.setAutoCommit(prevAutoCommit);
            return true;
        } catch (SQLException sqle) {
            System.out.println("SQLException : " + sqle);
            return false;
        }
    }
}