Java JDBC将一个文件夹的文件装载到PostgreSQL数据库BLOB列,并将BLOB列下载到另一个文件夹

最新推荐文章于 2024-07-21 20:59:31 发布

allway2

最新推荐文章于 2024-07-21 20:59:31 发布

阅读量864

点赞数

文章标签： java

本文链接：https://blog.csdn.net/allway2/article/details/124221666

版权

DROP TABLE public.bindata;

CREATE TABLE public.bindata (
	id serial4 NOT NULL,
	"name" varchar NULL,
	"data" bytea NULL
);

package com.infotech.client;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import java.sql.DriverManager;

/**
 * @author KK JavaTutorials JDBC program to write or save binary data/BLOB data
 * in database
 */
public class SaveBinaryFilesInDBClientTest {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String DB_DRIVER_CLASS = "org.postgresql.Driver";
        String DB_USERNAME = "postgres";
        String DB_PASSWORD = "postgres";
        String DB_URL = "jdbc:postgresql://127.0.0.1:5432/testdb";

        Class.forName(DB_DRIVER_CLASS);

        String SQL = "INSERT INTO bindata (name,data)VALUES(?,?)";
        Path dir = Paths.get("InputFiles");
        try ( Stream<Path> list = Files.list(dir);  Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);  PreparedStatement ps = connection.prepareStatement(SQL)) {
            /*
			 *Below line belongs to JDK 1.8 API so make sure you are running this code on JDK 1.8.
			 *And you IDE pointing to compiler version 1.8
             */
            List<Path> pathList = list.collect(Collectors.toList());
            System.out.println("Following files are saved in database..");
            for (Path path : pathList) {
                System.out.println(path.getFileName());
                File file = path.toFile();
                String fileName = file.getName();
                long fileLength = file.length();

                ps.setString(1, fileName);

                FileInputStream fis = new FileInputStream(file);
                ps.setBinaryStream(2, fis, fileLength);

                ps.addBatch();
            }
            System.out.println("----------------------------------------");
            int[] executeBatch = ps.executeBatch();
            for (int i : executeBatch) {
                System.out.println(i);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

package com.infotech.client;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.DriverManager;

/**
 * @author KK JavaTutorials JDBC program to read binary data/BLOB data from
 * database and write into local disk.
 */
public class DownloadBinaryFilesFromDBClientTest {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String DB_DRIVER_CLASS = "org.postgresql.Driver";
        String DB_USERNAME = "postgres";
        String DB_PASSWORD = "postgres";
        String DB_URL = "jdbc:postgresql://127.0.0.1:5432/testdb";

        Class.forName(DB_DRIVER_CLASS);
        String SQL = "SELECT * FROM bindata";
        try ( Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);  PreparedStatement ps = connection.prepareStatement(SQL);  ResultSet rs = ps.executeQuery()) {
            System.out.println("Following flies are downloaded from database..");
            while (rs.next()) {
                int fileId = rs.getInt("id");
                String fileName = rs.getString("name");
                System.out.println("File Id:" + fileId);
                System.out.println("File Name:" + fileName);

//                Blob blob = rs.getBlob("data");
//                InputStream inputStream = blob.getBinaryStream();
//
//                System.out.println("-----------------------------------");
//                Files.copy(inputStream, Paths.get("DownLoadFiles/" + fileName));
                File myFile = new File("DownLoadFiles/" + fileName);

                try ( FileOutputStream fos = new FileOutputStream(myFile)) {

                    byte[] buf = rs.getBytes("data");
                    int len = buf.length;
                    fos.write(buf, 0, len);
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}