[java]PostgreSQL DB (데이터베이스)에 file(이미지) 넣기

# Java와 PostgreSQL을 통한 파일을 집어 넣기 전 준비 사항

 

1. postgreSQL JDBC Jar 추가하기 (https://jdbc.postgresql.org/download.html)

PostgreSQL JDBC Download

2. PostgreSQL 테이블 생성

-- Drop table

-- DROP TABLE public.img_test;

CREATE TABLE public.img_test (
	file_id int4 NOT NULL DEFAULT nextval('newtable_1_file_id_seq'::regclass),
	filename varchar NULL,
	filesize int8 NULL,
	file bytea NULL,
	inst_dt timestamp(0) NULL
);

---

# DB에 이미지 넣는 부분 소스 코드

 public void insertImg() {
        File file = new File("../TestFileDir/잔망루피 4월 달력_새싹.jpg");
        FileInputStream fis = null;

        String name = file.getName();
        System.out.println("name = " + name);

        String sql = "INSERT INTO public.img_test (filename, filesize, file, inst_dt) VALUES(?, ?, ?, ?);";
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            fis = new FileInputStream(file); 

            connection = DriverManager.getConnection(connurl, user, password);
            pstmt = connection.prepareStatement(sql);

            pstmt.setString(1, file.getName());
            pstmt.setLong(2, file.length());
            pstmt.setBinaryStream(3, fis);
            pstmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));

            pstmt.executeUpdate();
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

 

# 결과 화면

---

# 전체소스코드

package com.example.Img;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.nio.file.Files;
import java.sql.*;
import java.time.LocalDateTime;

public class DatabaseSaveImg {

    private String connurl = "jdbc:postgresql://localhost:5432/DB명";
    private String user = "postgres";
    private String password = "postgres";

    public void connTest() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        try (Connection connection = DriverManager.getConnection(connurl, user, password);) {
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT VERSION() AS version");

            while (rs.next()) {
                String version = rs.getString("version");

                System.out.println(version);
            }
            rs.close();
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void insertImg() {
        File file = new File("D:/SOURCE_CODE/2022/study/Database/insertImg/TestFileDir/잔망루피 4월 달력_새싹.jpg");
        FileInputStream fis = null;

        String name = file.getName();
        System.out.println("name = " + name);

        String sql = "INSERT INTO public.img_test (filename, filesize, file, inst_dt) VALUES(?, ?, ?, ?);";
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            fis = new FileInputStream(file);

            connection = DriverManager.getConnection(connurl, user, password);
            pstmt = connection.prepareStatement(sql);

            pstmt.setString(1, file.getName());
            pstmt.setLong(2, file.length());
            pstmt.setBinaryStream(3, fis);
            pstmt.setTimestamp(4, Timestamp.valueOf(LocalDateTime.now()));

            pstmt.executeUpdate();
        } catch (SQLException | FileNotFoundException e) {
            e.printStackTrace();
        }finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}