Postgresql之于Jdbc的大对象操作

最新推荐文章于 2024-03-30 12:02:37 发布

no_cross_no_crown

最新推荐文章于 2024-03-30 12:02:37 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： Java 文章标签： postgresql jdbc string file path buffer

本文链接：https://blog.csdn.net/no_cross_no_crown/article/details/6098222

Java 专栏收录该内容

24 篇文章

订阅专栏

本文介绍了一种利用PostgreSQL数据库处理大型文件的方法，包括如何将文件保存为BLOB格式到数据库中，以及如何从数据库中检索这些文件并将其保存到本地磁盘上。文章通过具体的Java代码示例，展示了通过流的方式读取和写入文件，以及利用PostgreSQL提供的Large Object API进行高效的大文件操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.io.BufferedInputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.postgresql.PGConnection; import org.postgresql.largeobject.LargeObject; import org.postgresql.largeobject.LargeObjectManager; import com.zznode.ext.json.Util; public class SQLOperater { public String fetchFileByBytes(String path) { String sql = "select * from blob_table"; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; File file = null; OutputStream fos = null; try { conn = Util.getConn(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); byte[] buffer = null; file = new File(path); fos = new FileOutputStream(file); while (rs.next()) { buffer = rs.getBytes("file"); fos.write(buffer); } fos.flush(); fos.close(); } catch (SQLException e) { e.printStackTrace(); return null; } catch (IOException e) { e.printStackTrace(); } finally { Util.close(null, ps, conn); } return null; } public String fetchFileByBlob(String path) { String sql = "select * from blob_table "; Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; File file = null; OutputStream fos = null; try { conn = Util.getConn(); conn.setAutoCommit(false); // 获取大对象管理器以便进行操作 LargeObjectManager lobj = ((org.postgresql.PGConnection) conn) .getLargeObjectAPI(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); byte[] buffer = null; file = new File(path); fos = new FileOutputStream(file); while (rs.next()) { Long oid = rs.getLong("file"); LargeObject obj = lobj.open(oid, LargeObjectManager.READ); buffer = new byte[obj.size()]; obj.read(buffer, 0, obj.size()); fos.write(buffer); obj.close(); } fos.flush(); fos.close(); } catch (SQLException e) { e.printStackTrace(); return null; } catch (IOException e) { e.printStackTrace(); } finally { Util.close(null, ps, conn); } return null; } public String saveFileByBlob(String path) { Connection conn = null; PreparedStatement ps = null; File file = null; FileInputStream fis = null; try { file = new File(path); fis = new FileInputStream(file); System.out.println(file.length()); String sql = "insert into blob_table (id,name,dsc,file) values(?,?,?,?)"; conn = Util.getConn(); conn.setAutoCommit(false); PGConnection pgCon = (PGConnection) conn; // 获取大对象管理器以便进行操作 LargeObjectManager lobj = pgCon.getLargeObjectAPI(); // LargeObjectManager lobj =new LargeObjectManager((BaseConnection) // conn); // // 创建一个新的大对象 int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE); System.out.println("oid:" + oid); // 打开一个大对象进行写 LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE); byte buf[] = new byte[(int) file.length()]; int s, tl = 0; while ((s = fis.read(buf, 0, (int) file.length())) > 0) { obj.write(buf, 0, s); tl += s; } obj.close(); ps = conn.prepareStatement(sql); ps.setInt(1, 1); ps.setString(2, file.getName()); ps.setString(3, file.getAbsolutePath()); ps.setInt(4, oid); ps.executeUpdate(); conn.commit(); System.out.println(ps.executeUpdate()); System.out.println("插入成功！"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { Util.close(null, ps, conn); try { fis.close(); } catch (IOException e) { e.printStackTrace(); } } return null; } public String saveFileByByteas(String path) { Connection conn = null; PreparedStatement ps = null; File file = null; FileInputStream fis = null; InputStream is = null; try { file = new File(path); fis = new FileInputStream(file); is = new BufferedInputStream(fis); System.out.println(file.length()); String sql = "insert into blob_table (id,name,dsc,file) values(?,?,?,?)"; conn = Util.getConn(); ps = conn.prepareStatement(sql); ps.setInt(1, 1); ps.setString(2, file.getName()); ps.setString(3, file.getAbsolutePath()); ps.setBinaryStream(4, fis, (int) file.length()); ps.executeUpdate(); System.out.println("插入成功！"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { Util.close(null, ps, conn); try { fis.close(); } catch (IOException e) { e.printStackTrace(); } } return null; } public static void main(String[] args) { SQLOperater op = new SQLOperater(); String path = "e://zip//myzip.zip"; String path2 = "e://zip//myzipfromdb.zip"; // op.saveFileByByteas(path); // op.fetchFileByBytes(path2); op.saveFileByBlob(path); op.fetchFileByBlob(path2); } }