JDBC

postgres 설정

가장 먼저 postgres를 설치해주도록 하자. docker를 사용하여 간단한 postgres server를 구축하면 된다.

docker pull postgres:latest

docker run -p 5432:5432 --name postgres \
-e POSTGRES_PASSWORD=1234 \
-e TZ=Asia/Seoul \
-d postgres:latest

docker exec -it postgres bash

postgres container에 접속되었다면 이제 postgres database 내부로 접속해보도록 하자.

psql -U postgres

비밀번호를 묻는다면 '1234'를 입력하면 된다.

create database demo;

database 목록은 \l을 통해서 확인이 가능하다.

\l

                                                    List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
 demo      | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/postgres          +
           |          |          |                 |            |            |        |           | postgres=CTc/postgres

demo database에 접속하기 위해서는 \c을 사용하면 된다.

\c demo

You are now connected to database "demo" as user "postgres".
demo=# 

postgres는 재밌게도 database 안에서 schema를 만들어 table에 할당해주어야 한다. mysql의 경우 database가 하나의 scheme 역할을 하는 것과 다른 것이다.

CREATE SCHEMA my_schema;

\dn
        List of schemas
   Name    |       Owner       
-----------+-------------------
 my_schema | postgres
 public    | pg_database_owner

\dn으로 schema를 볼 수 있다.

이제 table을 생성해보도록 하자.

CREATE TABLE my_schema.student(
sid INTEGER NOT NULL PRIMARY KEY,
sname TEXT,
marks INTEGER
);

table이 잘 생성되었는 지 검색하기 위해서는 \dt를 사용하면 되는데, postgres에서는 search_path로 우리의 schema를 설정해주어야 한다.

SET search_path TO my_schema

이제 \dt를 입력해보도록 하자.

\dt
           List of relations
  Schema   |  Name   | Type  |  Owner   
-----------+---------+-------+----------
 my_schema | student | table | postgres
(1 row)

이제 데이터를 넣어보도록 하자.

INSERT INTO student values (1, 'Navin', 50);

제대로 들어갔다면 이제 확인해보도록 하자.

SELECT * FROM student;
 sid | sname | marks 
-----+-------+-------
   1 | Navin |    50

제대로 잘 들어간 것을 확인할 수 있다.

JDBC(java database connector) 연결

jdbc를 사용하기 위해서는 해당 database에 맞는 driver jar파일을 가지고 온 다음, 사용하는 곳에서 해당 driver jar 파일을 등록해주어야 한다.

maven이나 gradle을 사용하고 있다면 해당 페이지에서 가져올 수 있다.
https://mvnrepository.com/artifact/org.postgresql/postgresql/42.7.5

이제 우리의 gradle project에 추가해주도록 하자.

• build.gradle.kts

...
dependencies {
    implementation("org.postgresql:postgresql:42.7.5")
    testImplementation(platform("org.junit:junit-bom:5.10.0"))
    testImplementation("org.junit.jupiter:junit-jupiter")
}
...

이제 jdbc를 java code에 추가하여 postgresql과 연동시켜주ㅗ록 하자.

package org.example;

// import package
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        /*
            import package
            load and register
            create connection
            create statement
            execute statement
            process the results
            close
        */
        String url = "jdbc:postgresql://localhost:5432/demo";
        String uname = "postgres";
        String pass = "1234";
        //load and register driver
        Class.forName("org.postgresql.Driver");
        //create statement
        Connection con = DriverManager.getConnection(url, uname, pass);

        System.out.println("Connection Established");
    }
}

Class.forName("org.postgresql.Driver")을 추가시키면 jdbc가 해당 database driver를 로드하여 database connection을 맺는다.

url 부분을 잘보면 아래와 같은 규칙으로 되어 있는 것을 알 수 있다.

String url = "jdbc:{database종류}://{database-url}/{접속한 database이름}";

우리의 경우 접속한 database이름이 demo이므로 demo로 적어준 것이다.

Connection 객체와 DriverManager 객체는 jdbc 객체로 jdbc 객체들을 통해서 database와 상호작용하는 것이다. 이 jdbc 객체들은 내부적으로 database driver를 갖는데, 이 driver를 Class.forName으로 로드해준 것이다.

만약 url, uname, pass 부분이 설정한 값과 다르면 에러가 발생하게 된다.

JDBC query

이제 data를 가져오는 query statement를 만들도록 하자.

package org.example;

// import package
import java.sql.*;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        /*
            import package
            load and register
            create connection
            create statement
            execute statement
            process the results
            close
        */
        String url = "jdbc:postgresql://localhost:5432/demo";
        String uname = "postgres";
        String pass = "1234";
        String query = "select sname from my_schema.student where sid = 1";

        //load and register driver
        Class.forName("org.postgresql.Driver");
        //create statement
        Connection con = DriverManager.getConnection(url, uname, pass);
        Statement st = con.createStatement();
        // execute statement
        // st.execute 는 무언가를 바꾸는 insert, delete, update 문에서 사용한다.
        ResultSet rs = st.executeQuery(query);
        // process the results
        // rs.next는 다음 데이터가 있으면 true, 없으면 false이다.
        // column 이름을 넘겨주면 해당 타입으로 변환해서 가져와 준다.
        rs.next();
        String sname = rs.getString("sname");
        System.out.println("Name of a student is: " + sname);
        //close
        con.close();
    }
}

String query = "select sname from my_schema.student where sid = 1";문을 잘보면 실제로 쓰이는 statement라는 것을 볼 수 있다.

student는 my_schema 안에 있으므로 my_schema를 적어주어야 한다.

ResultSet을 통해서 결과를 받아내는데, 결과각 N개 있을 수 있다. rs.next를 사용해서 데이터 row에 대한 pointer를 얻어올 수 있는데, 만약 더이상 data가 없다면 false가 나오고 데이터가 더 있다면 true가 나온다.

맨 처음은 pointer가 아무것도 지칭하지 않기 때문에 rs.next를 먼저 실행해주어야 한다.

추가로 postgres에 데이터를 넣어보도록 하자.

\c demo
INSERT INTO my_schema.student VALUES(2, 'kiran', 50);
INSERT INTO my_schema.student VALUES(3, 'Halshi', 45);
INSERT INTO my_schema.student VALUES(4, 'Sushil', 40);

JDBC all query

이제 java로 이 모든 data들을 가져오도록 하자.

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:postgresql://localhost:5432/demo";
        String uname = "postgres";
        String pass = "1234";
        String query = "select * from my_schema.student";

        //load and register driver
        Class.forName("org.postgresql.Driver");
        //create statement
        Connection con = DriverManager.getConnection(url, uname, pass);
        Statement st = con.createStatement();
        // execute statement
        // st.execute 는 무언가를 바꾸는 insert, delete, update 문에서 사용한다.
        ResultSet rs = st.executeQuery(query);
        // process the results
        while(rs.next()) {
            System.out.print(rs.getInt(1) + "-");
            System.out.print(rs.getString(2) + "-");
            System.out.println(rs.getInt(3));
        }

        //close
        con.close();
    }
}

위와 같이 수정해주면 된다. while문을 순회해서 select * from의 결과를 가져와 주면 된다.

여기서 이전과 다른 점 하나는 rs.getInt, rs.getString에서 column 이름이 아니라 column number를 사용하고 있다는 것이다. 물론 이는 좋은 방법은 아니다.

결과는 아래와 같다.

1-Navin-50
2-kiran-50
3-Halshi-45
4-Sushil-40

다음으로 jdbc를 사용해서 데이터를 추가해보도록 하자.

JDBC CRUD

다른 부분들은 거의 모두 동일한데, 단지 executeQuery를 execute로 바꿔줘야 한다. 이는 INSERT, UPDATE, DELETE에 대해서는 execute를 사용하고, SELECT와 같은 문은 executeQuery를 사용하기 때문이다.

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:postgresql://localhost:5432/demo";
        String uname = "postgres";
        String pass = "1234";
        String insertQuery = "INSERT INTO my_schema.student VALUES(5, 'John', 48)";
        String updateQuery = "UPDATE my_schema.student SET sname = 'MAX' where sid=5";
        String readQuery = "SELECT * FROM my_schema.student WHERE sid=5";
        String deleteQuery = "DELETE FROM my_schema.student WHERE sid=5";

        Class.forName("org.postgresql.Driver");
        Connection con = DriverManager.getConnection(url, uname, pass);
        Statement st = con.createStatement();
        // INSERT, DELETE, UPDATE와 관련된 명령어는 execute이다.
        // create
        st.execute(insertQuery);
        // update
        st.execute(updateQuery);
        ResultSet rs = st.executeQuery(readQuery);
        while(rs.next()){
            // read
            String sname = rs.getString("sname");
            System.out.println(sname); // MAX
        }
        // delete
        st.execute(deleteQuery);

        con.close();
    }
}

sid=5번에 해당하는 데이터를 추가하고, 수정한 다음에 가져오고 삭제하는 코드이다. 성공 시에 MAX가 출력될 것이다.

추가적으로 sql statement문에 특정 값들을 변수로 받고 싶을 때가 있다. 가령 INSERT를 하고 싶은데, 사용자 입력으로 sid, sname, marks를 받고 싶은 것이다.

이때 사용하는 것이 prepared statement로 ?를 통해서 statement 인자를 받는 것이다.

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        String url = "jdbc:postgresql://localhost:5432/demo";
        String uname = "postgres";
        String pass = "1234";

        int sid = 102;
        String name ="Jasmine";
        int marks = 50;

        String insertQuery = "INSERT INTO my_schema.student VALUES(?, ?, ?)";
        String query = "SELECT * FROM my_schema.student WHERE sid = ?";

        Class.forName("org.postgresql.Driver");
        Connection con = DriverManager.getConnection(url, uname, pass);

        PreparedStatement st = con.prepareStatement(insertQuery);
        st.setInt(1, sid);
        st.setString(2, name);
        st.setInt(3, marks);
        st.execute();

        PreparedStatement st2 = con.prepareStatement(query);
        st2.setInt(1, sid);
        ResultSet rs = st2.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
        }

        con.close();
    }
}

기존의 statement가 아니라, prepareStatement을 사용하는 것을 주목하자. 기존의 statement는 statement에 sql문을 적재하여 실행하였지만, prepareStatement의 경우는 처음부터 sql문을 받아 생성된다.

이후에는 각 ?의 위치마다 데이터를 넣는 setInt, setString을 사용하여 sql문을 완성하고 execute로 실행한다.