37

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?

Manuel Drieschmanns
  • 704
  • 5
  • 26
amorfis
  • 15,390
  • 15
  • 77
  • 125

2 Answers2

59

Use a byte array:

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

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

Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
  • 6
    Additional info to the accepted answer. Confirmed by personal experience in 2016. http://stackoverflow.com/a/16599953/102658 tl;dr: > length attribute is used to define the > column length of **String** fields (it is **ignored for other types**) – Rope Aug 25 '16 at 07:53
  • This uses a clob for me, not a blob. – Nic Oct 12 '18 at 18:11
  • I don't specify the `length` attribute. I also use java nio to read from a file : `myEntity.data = Files.readAllBytes(myPath)`. – Julien Kronegg Dec 27 '18 at 15:46
5

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

}
Phil Horder
  • 402
  • 6
  • 13
  • works but is Hibernate specific, if you use EclipseLink it would break – wutzebaer Jan 21 '20 at 11:39
  • ... which I stated at the top of the post. I don't like to be implementation-specific, but this was the only solution I found that was both stable and fast enough. – Phil Horder Jan 22 '20 at 14:08