How to write java.sql.Blob to JPA entity?

Question

I have a JPA entity with java.sql.Blob:

@Entity public class LargeData {    @Lob   private java.sql.Blob data;    //getters/setters } 

How to create instance of this entity? I want to set Blob with setData() method, but how to get Blob from JPA? java.sql.Blob is only interface, there are different implementations for different databases, so I assume JPA should give me right implementation. How to get it?

Answer 1

Use a byte array:

@Lob @Column(length=100000) private byte[] data; 

If you want to use streams, create the blob using Hibernate.createBlob(..)

Answer 2

use a file stream. However, this appears to have various complications, depending on your database, driver, and JPA implementation. I built a generic solution, that was slow, and failed with large files, then found a Hibernate-specific solution that worked with Oracle 11.2.0.4

I'm using Spring Data / JPA, but the issue seems to be around Hibernate, not Spring.

Hibernate:

private void testLoadFile() throws SQLException, IOException {     File f = new File("//C:/tmp/6mb_file.wmv");   BufferedInputStream fstream = new BufferedInputStream(new FileInputStream(f));    Session session = entityManager.unwrap(Session.class);   Blob blob = Hibernate.getLobCreator(session).createBlob(fstream, f.length());    FileBLOBEntity file = new FileBLOBEntity();    file.setName("//C:/tmp/6mb_file.wmv");   file.setTheData(blob);   blobRepository.saveAndFlush(file); } 

Generic Spring/JPA:

private void testLoadFile() throws SQLException, IOException {    File f = new File("//C:/tmp/6mb_file.wmv");   BufferedInputStream fstream = new BufferedInputStream(new FileInputStream(f));    Blob blob = connection.getConnection().createBlob();   BufferedOutputStream bstream = new  BufferedOutputStream(blob.setBinaryStream(1));   // stream copy runs a high-speed upload across the network   StreamUtils.copy(fstream, bstream);    FileBLOBEntity file = new FileBLOBEntity();    file.setName("//C:/tmp/6mb_file.wmv");   file.setTheData(blob);   // save runs a low-speed download across the network.  this is where   // Spring does the SQL insert.  For a large file, I get an OutOfMemory exception here.   blobRepository.saveAndFlush(file); } 

and for retrieval:

public void unloadFile() throws SQLException, IOException {    File f = new File("//C:/tmp/6mb_file.wmv" + "_fromDb");    FileOutputStream fstream = new FileOutputStream(f);    FileBLOBEntity file = blobRepository.findByName("//C:/tmp/6mb_file.wmv");   Blob data = file.getTheData();    InputStream bstream = data.getBinaryStream();   StreamUtils.copy(bstream, fstream);  }