Java 如何使用 JPA 从 PostgreSQL 读取 bytea 图像数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35505424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to read bytea image data from PostgreSQL with JPA?
提问by Bhushan
I have PostgreSQL database and there is column 'image' with datatype 'bytea'. I cannot modify columns or database configurations. JPA annotated POJO contains followign mapping
我有 PostgreSQL 数据库，并且有数据类型为“bytea”的“image”列。我无法修改列或数据库配置。JPA 注释的 POJO 包含 followign 映射
@Column(name="image")
private byte[] image;
The returned data is in following format (this is just a sample)
返回的数据格式如下（这只是一个示例）
WF5ClN6RlpLZ0hJTUdNQ1FJWmkwcFVGSUdNQ0lDWUE5TUEvanRFeElwK2x0M2tBQUFBQVNVVk9SSzVDWUlJPQo=
When I write this data to file (.jpeg) photo viewer says "this is corrupted file". I also understand that actual image byte data looks different from above sample. I read some blogs which mentioned that PostgreSQL applies hexadecimal conversion to bytea data. How to restore it to original data with or without JPA ?
当我将此数据写入文件 (.jpeg) 时，照片查看器显示“这是损坏的文件”。我也明白实际的图像字节数据看起来与上面的示例不同。我读了一些博客，其中提到 PostgreSQL 将十六进制转换应用于 bytea 数据。如何使用或不使用 JPA 将其恢复为原始数据？
Database - PostgresSQL Version 9.5.1
数据库 - PostgresSQL 版本 9.5.1
Driver
司机
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4-1205-jdbc41</version>
</dependency>
采纳答案by Neothorn
回答by P S M
insert an image, you would use:
插入图像，您将使用：
//Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();
// Create a new large object
int oid = lobj.create(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.setInt(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 = ((org.postgresql.PGConnection)conn).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
int oid = rs.getInt(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();
回答by josivan
Try to annotate you entity with @Lob
尝试用 @Lob
@Lob
@Column(name="image")
private byte[] image;
If you are using hibernate implementation you can add @Type(type="org.hibernate.type.BinaryType")in column too.
如果您正在使用休眠实现，您也可以添加@Type(type="org.hibernate.type.BinaryType")列。
@Lob
@Column(name="image")
@Type(type="org.hibernate.type.BinaryType")
private byte[] image;
回答by Mike Shauneu
ImageEntity
图像实体
package com.example;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class ImageEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name="image")
private byte[] image;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public byte[] getImage() {
return image;
}
public void setImage(byte[] image) {
this.image = image;
}
}
ImageRepository
图像库
package com.example;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface ImageRepository extends JpaRepository<ImageEntity, Long> {
}
Test
测试
package com.example;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import javax.annotation.Resource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import junit.framework.TestCase;
@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = TestApplication.class)
public class ImageDaoTest {
@Resource
private ImageRepository imageRepository;
@Test
public void testImage() throws IOException {
// Read an image from disk. Assume test.png exists
ByteArrayOutputStream out = new ByteArrayOutputStream();
try (InputStream in = getClass().getResourceAsStream("test.png")) {
int length;
byte[] buffer = new byte[1024];
while ((length = in.read(buffer)) != -1) out.write(buffer, 0, length);
}
byte[] image = out.toByteArray();
// Store image to DB
ImageEntity imageEntiry = new ImageEntity();
imageEntiry.setImage(image);
long imageEntiryId = imageRepository.save(imageEntiry).getId();
// Retrieve image from DB
ImageEntity resultImageEntiry = imageRepository.findOne(imageEntiryId);
byte[] resultImage = resultImageEntiry.getImage();
// Compare retrieved image with source image by byte to byte comparison
for (int i = 0; i < resultImage.length; i++) {
TestCase.assertEquals(image[i], resultImage[i]);
}
}
}
It works against Postgres 9.5.0-1 with 9.4.1207.jre7 jdbc driver.
它适用于带有 9.4.1207.jre7 jdbc 驱动程序的 Postgres 9.5.0-1。
回答by Bhushan
I am adding complete code which may be useful for others (skipping try/catch),
我正在添加可能对其他人有用的完整代码（跳过 try/catch），
String base64EncryptedImage = new String(image);
decoded = org.apache.commons.codec.binary.Base64.decodeBase64(base64EncryptedImage);
ImageOutputStream out = new FileImageOutputStream(new File("D://abc.png"));
out.write(decoded);
out.close();
