가장 먼저 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를 사용하기 위해서는 해당 database에 맞는 driver jar파일을 가지고 온 다음, 사용하는 곳에서 해당 driver jar 파일을 등록해주어야 한다.
maven이나 gradle을 사용하고 있다면 해당 페이지에서 가져올 수 있다.
https://mvnrepository.com/artifact/org.postgresql/postgresql/42.7.5
이제 우리의 gradle project에 추가해주도록 하자.
...
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
부분이 설정한 값과 다르면 에러가 발생하게 된다.
이제 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);
이제 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를 사용해서 데이터를 추가해보도록 하자.
다른 부분들은 거의 모두 동일한데, 단지 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
로 실행한다.