의존성 설정

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

PostgreSQL 설치 및 실행 (Docker)

docker run -p 5432:5432 -e POSTGRES_PASSWORD=pass -e POSTGRES_USER=dsunni -e POSTGRES_DB=springboot --name postgres_boot -d postgres

docker exec -i -t postgres_boot bash

apt-get update && apt-get install -y procps

su - postgres

psql --username=dsunni --dbname=springboot

데이터베이스 조회
\list

테이블 조회
\dt

쿼리
SELECT * FROM account;

• 컨테이너 안으로 bash 접속

SpringBoot & PostgreSQL 연결

application.properties

spring.datasource.hikari.maximum-pool-size=4

spring.datasource.url=jdbc:postgresql://localhost:5432/springboot
spring.datasource.username=dsunni
spring.datasource.password=pass

PostgreSQLRunner

@Component
public class PostgreSQLRunner implements ApplicationRunner {
    @Autowired
    DataSource dataSource;

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        try (Connection connection = dataSource.getConnection()){
            System.out.println(dataSource.getClass());
            System.out.println(connection.getMetaData().getURL());
            System.out.println(connection.getMetaData().getUserName());

            Statement statement = connection.createStatement();
            String sql = "CREATE TABLE account(ID INTEGER NOT NULL, name VARCHAR(255), PRIMARY KEY (id))";
            statement.executeUpdate(sql);
        }
        jdbcTemplate.execute("INSERT INTO account VALUES (1, 'dsunni')");
    }
}

참고로 PostgreSQL에서 USER는 키워드이기 때문에 USER로 테이블을 만들면 안된다. USER대신 account를 사용해서 생성하자

class com.zaxxer.hikari.HikariDataSource
jdbc:postgresql://localhost:5432/springboot
dsunni

콘솔에 정상적으로 DB정보가 출력된다.

DB 확인

springboot=# \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | account | table | dsunni
(1 row)

springboot=# select * from account;
 id |  name
----+--------
  1 | dsunni
(1 row)

\q
exit

• \q : DB 나가기
• \list : DB 조회
• \l : 테이블 조회

DBeaver에서도 확인 가능하다.