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

我会带着你远行 2023-09-29 10:34 1阅读 0赞

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