postgres存储二进制数据java

PostgreSQL™提供了两种不同的方式来存储二进制数据。二进制数据可以存储在一个表中使用的数据类型BYTEA或使用的大对象特性将二进制数据存储在一个单独的表在一个特殊的表的格式,是指你的表中存储一个值类型的OID。

为了确定哪种方法是合适你需要理解每个方法的局限性。BYTEA数据类型并不适合存储大量的二进制数据。而列的类型BYTEA可容纳1 GB的二进制数据,这需要大量的内存来处理如此大的价值。大对象的方法来存储二进制数据更适合存储非常大的值,但它有自己的局限性。具体删除一行,包含一个大型对象引用不删除大对象。删除大对象是一个单独的操作需要执行。大对象也有一些连接到数据库的安全问题,因为任何人都可以查看和/或修改任何大型对象,即使他们没有权限查看/更新包含大型对象引用的行。

7.2版本的第一个版本是JDBC驱动程序支持BYTEA数据类型。引入该功能在7.2中引入了行为与以前版本相比的变化。自7.2以来,getBytes方法(),setBytes(),getBinaryStream(),和setBinaryStream()操作BYTEA数据类型。在7.1和更早的,这些方法操作的OID与大型对象数据类型相关。可以回复司机回到旧的7.1行为通过设置属性的连接对象价值7.1兼容。连接属性的更多细节可在“连接参数”章节。

使用BYTEA数据类型应该简单地使用getBytes(),setBytes(),getBinaryStream(),或setBinaryStream()方法。

使用大对象功能您可以使用LargeObject类PostgreSQL™提供的JDBC驱动程序,或通过使用getBLOB()和setBLOB()方法。

重要的

您必须访问大型对象在一个SQL事务阻塞。你开始一个事务可以阻止通过调用setAutoCommit(假)。

7.1,“在JDBC中处理二进制数据”包含一些例子关于如何使用PostgreSQL™JDBC驱动程序处理二进制数据。

例7.1。在JDBC中处理二进制数据

例如,假设您有一个表包含图像的文件名,你还想将图像存储在一个BYTEA列:

CREATE TABLE images (imgname text, img bytea);

To insert an image, you would use:

File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = conn.prepareStatement("INSERT INTO images VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setBinaryStream(2, fis, (int)file.length());
ps.executeUpdate();
ps.close();
fis.close();

Here, setBinaryStream() transfers a set number of bytes from a stream into thecolumn of type BYTEA. This also could have been done using thesetBytes() methodif the contents of the image was already in a byte[].

Note

The length parameter tosetBinaryStream must be correct. There is no way toindicate that the stream is of unknown length. If you are in this situation, youmust read the stream yourself into temporary storage and determine the length.Now with the correct length you may send the data from temporary storage on tothe driver.

Retrieving an image is even easier. (We usePreparedStatement here, but theStatement class can equally be used.)

PreparedStatement ps = conn.prepareStatement("SELECT img FROM images WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
while (rs.next())
{
   byte[] imgBytes = rs.getBytes(1);
   // use the data in some way here
}
rs.close();
ps.close();

Here the binary data was retrieved as anbyte[]. You could have used aInputStream object instead.

Alternatively you could be storing a very large file and want to use theLargeObject API to store the file:

CREATE TABLE imageslo (imgname text, imgoid oid);

To insert an image, you would use:

// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);`

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

// Create a new large object
long oid = lobj.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);

// Open the large object for writing
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// Copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0){
   obj.write(buf, 0, s);
   tl += s;
}

// Close the large object
obj.close();

// Now insert the row into imageslo
PreparedStatement ps = conn.prepareStatement("INSERT INTO imageslo VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setLong(2, oid);
ps.executeUpdate();
ps.close();
fis.close();

// Finally, commit the transaction.
conn.commit();

Retrieving the image from the Large Object:

// All LargeObject API calls must be within a transaction block
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

PreparedStatement ps = conn.prepareStatement("SELECT imgoid FROM imageslo WHERE imgname = ?");
ps.setString(1, "myimage.gif");
ResultSet rs = ps.executeQuery();
while (rs.next())
{
   // Open the large object for reading
   long oid = rs.getLong(1);
   LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

   // Read the data
   byte buf[] = new byte[obj.size()];
   obj.read(buf, 0, obj.size());
   // Do something with the data read here

   // Close the object
   obj.close();
}
rs.close();
ps.close();

// Finally, commit the transaction.
conn.commit();