'postgreSQL 이미지'에 해당되는 글 2건

* 이미지 파일 DB에 넣는 과정입니다.

https://minaminaworld.tistory.com/212

1. 테이블 구조

2. DB에서 데이터 읽어오는 부분과 이미지 정보 DTO 구성

* DB 에서 이미지 정보 읽어 오기

public void readImg() {
        String sql = "SELECT file_id, filename, filesize, file, inst_dt from public.img_test";
        Connection connection = null;
        PreparedStatement pstmt = null;

        try {
            connection = DriverManager.getConnection(connurl, user, password);
            pstmt = connection.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();

            ImgDto imgDto = new ImgDto();
            while (resultSet.next()) {
                imgDto.setFileId(resultSet.getInt("file_id"));
                imgDto.setFilename(resultSet.getString("filename"));
                imgDto.setFilesize(resultSet.getLong("filesize"));
                imgDto.setFile(resultSet.getBytes("file"));
                imgDto.setInsDt(resultSet.getTimestamp("inst_dt").toLocalDateTime());
            }
            System.out.println("imgDto.toString() = " + imgDto.toString());
            // 이미지 정보를 파일로 저장하는 함수 호출
            saveImg(imgDto);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

* DB에서 가져온 데이터 DTO로 변환

import java.time.LocalDateTime;

public class ImgDto {
    private int fileId;
    private String filename;
    private Long filesize;
    private byte[] file;
    private LocalDateTime insDt;

    public ImgDto() {
    }

    public int getFileId() {
        return fileId;
    }

    public void setFileId(int fileId) {
        this.fileId = fileId;
    }

    public String getFilename() {
        return filename;
    }

    public void setFilename(String filename) {
        this.filename = filename;
    }

    public Long getFilesize() {
        return filesize;
    }

    public void setFilesize(Long filesize) {
        this.filesize = filesize;
    }

    public byte[] getFile() {
        return file;
    }

    public void setFile(byte[] file) {
        this.file = file;
    }

    public LocalDateTime getInsDt() {
        return insDt;
    }

    public void setInsDt(LocalDateTime insDt) {
        this.insDt = insDt;
    }

    @Override
    public String toString() {
        return "ImgDto{" +
                "fileId=" + fileId +
                ", filename='" + filename + '\'' +
                ", filesize=" + filesize +
                ", insDt=" + insDt +
                '}';
    }
}

3. Img 파일로 저장

private void saveImg(ImgDto imgDto) {
		// 저장할 위치 
        String strPath = "D:/testFolder/aaa";
        Path path1 = Paths.get(strPath + "/" + imgDto.getFilename());
        try {
            Files.write(path1, imgDto.getFile());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

4. 결과화면

 * Console 로그

 * 저장된 파일 

public void readImg() {
    String sql = "SELECT file_id, filename, filesize, file, inst_dt from public.img_test";
    Connection connection = null;
    PreparedStatement pstmt = null;

    try {
        connection = DriverManager.getConnection(connurl, user, password);
        pstmt = connection.prepareStatement(sql);
        ResultSet resultSet = pstmt.executeQuery();

        ImgDto imgDto = new ImgDto();
        while (resultSet.next()) {
            imgDto.setFileId(resultSet.getInt("file_id"));
            imgDto.setFilename(resultSet.getString("filename"));
            imgDto.setFilesize(resultSet.getLong("filesize"));
            imgDto.setFile(resultSet.getBytes("file"));
            imgDto.setInsDt(resultSet.getTimestamp("inst_dt").toLocalDateTime());
        }
        System.out.println("imgDto.toString() = " + imgDto.toString());
        saveImg(imgDto);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

private void saveImg(ImgDto imgDto) {
    String strPath = "D:/testFolder/aaa";
    Path path1 = Paths.get(strPath + "/" + imgDto.getFilename());
    try {
        Files.write(path1, imgDto.getFile());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

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

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

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();
            }
        }
    }

}