Spring-data-jpa storing blob

Question

What is "best" or canonical way to store entity with blob using spring-data-jpa?

@Entity
public class Entity {
  @Id
  private Long id;
  @Lob()
  private Blob blob;
}

public interface Repository extends CrudRepository<Entity,  Long> {
}

Tyika

Did you solve this problem? I have problem with this situation too. And I am searching for answer. – Wendalyn

Not with Spring-data. If I did, I would share with you. ;) – Tyika

Answer 1

TL; DR

You can see sample project on my github. The project shows how you stream data to/from database.

Problem

All advices about mapping the @Lob as byte[] defeats (IMO) the main advantage of blobs - streaming. With byte[] everything gets loaded in memory. May be ok but if you go with LargeObject you likely want to stream.

Solution

Mapping

@Entity
public class MyEntity {

    @Lob
    private Blob data;

    ...

}

Configuration

Expose hibernate SessionFactory and CurrentSession so you can get hold of the LobCreator. In application.properties:

spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate5.SpringSessionContext

Expose session factory as bean:

@Bean // Need to expose SessionFactory to be able to work with BLOBs
public SessionFactory sessionFactory(HibernateEntityManagerFactory hemf) {
    return hemf.getSessionFactory();
}

Create blob

@Service
public class LobHelper {

    private final SessionFactory sessionFactory;

    @Autowired
    public LobHelper(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }

    public Blob createBlob(InputStream content, long size) {
        return sessionFactory.getCurrentSession().getLobHelper().createBlob(content, size);
    }

    public Clob createClob(InputStream content, long size, Charset charset) {
        return sessionFactory.getCurrentSession().getLobHelper().createClob(new InputStreamReader(content, charset), size);
    }
}

Also - as pointed out in comments - as long as you work with the @Blob including the stream you get you need to be within transaction. Just mark the working part @Transactional.

Amputate

It won't work in hibernate 5.2. HibernateEntityManagerFactory is now deprecated. – Skeptical

It will probably still work but if you read the deprecated javadoc it's pretty clear what needs to be done in order to get rid of the deprecated class ... – Amputate

What about reading the Blob? Reading the Blob requires the underlying JDBC connection to be opened, but JPA does not give any guarantee on that unless your code runs in a transaction – Say

Right, it will need to be called from within transaction. Is it a problem? Good point though, I should have mentioned it ... – Amputate

All works fine for PostgreSQL. Even when I upgraded Spring Boot to the latest version 3.2.1-RELEASE (with some modifications). For MariaDB I couldn't get it working (in neither versions). Writing to DB does work, but reading gives OutOfMemoryError. Apparently the blob is loaded from DB into memory completely at once. – Earplug

@BartWeber: I'm not sure about MariaDB but chances are MariaDB doesn't implement BLOBs. Some older question suggests that MariDB driver doesn't support streaming: #42556152 – Amputate

Answer 2

Autowire your repository interface and call the save method passing your entity object.

I have a similar setup which works pretty well:

@Autowired
Repository repository;

repository.save(entity);

@Entity
@Table(name = "something")
public class Message {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @Lob
    @Column
    private byte[] data;

Upright

And how you created The Blob? – Tyika

Please find my modified answer – Upright

This defeats the purpose of streaming, you are likely to run into OOM with bigger data. – Amputate

Why will you run into OOM(Out of Memory) with byte[] and not String – Volney

Answer 3

Spring Data does not handle BLOBs but Spring Content does. Specifically, Spring Content JPA stores content as BLOBs in the database and associates that content with an Entity through annotations.

pom.xml

   <!-- Java API -->
   <dependency>
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-jpa-boot-starter</artifactId>
      <version>0.0.11</version>
   </dependency>
   <!-- REST API -->
   <dependency>
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-rest-boot-starter</artifactId>
      <version>0.0.11</version>
   </dependency>

Entity.java

@Entity
public class Entity {
   @Id
   @GeneratedValue
   private long id;

   @ContentId
   private String contentId;

   @ContentLength
   private long contentLength = 0L;

   // if you have rest endpoints
   @MimeType
   private String mimeType = "text/plain";

DataContentStore.java

@StoreRestResource(path="data")
public interface DataContentStore extends ContentStore<Data, String> {
}

The advantage of this approach over the accepted answer is that the Developer doesn't need to worry about any of the boilerplate code (the "service" in the accepted answer). The BLOB is also exposed as Spring Resource giving a natural programming interface. Or can be automatically exported via a REST interface. But none of this requires any coding, on behalf of the Developer, besides java config and the store interface.

Cathee

So this works great for storing 1 hour long videos with issues ? @Paul Warren, Doesnt it become slow as the content becomes larger ? I am talking of retrieving it after storage. – Glasscock

It shouldn't @vilak. Spring Content REST streams the video through the memory space of the app server so the intent is to connect the client to the video stream and have the client pull the stream out of the database and not store it anywhere on the way. However, esp. when it comes to relational databases, how well this works usually depends on how well the underlying database client supports BLOB streaming. Happy to look into optimizing (where possible) if you have a specific database/client in mind. – Cathee

Answer 4

You can do it with a single statement (4 below) using Hibernate.getLobCreator and passing the session that EntityManager can unwrap to you:

// 1. Get entity manager and repository
EntityManager em = .... // get/inject someway the EntityManager
EntityRepository repository = ...// get/inject your Entity repository

// 2. Instantiate your Entity
Entity entity = new Entity();

// 3. Get an input stream (you shall also know its length)
File inFile = new File("/somepath/somefile");
InputStream inStream = new FileInputStream(inFile);

// 4. Now copy to the BLOB
Blob blob =
  Hibernate.getLobCreator(em.unwrap(Session.class))
           .createBlob(inStream, inFile.length());

// 5. And finally save the BLOB
entity.setBlob(blob);
entityRepository.save(f);

Berneta

Sure, but you did not used spring-data-jpa at all in your answer ;) – Tyika

Not at all? What EntityManager and Repository are? The only call to Hibernate is to provide an adequate Blob that is implementation dependent. To stay 100% independent you can inject this single statement through Spring. – Berneta

Answer 5

I had a little problems to get current session factory like in answers above (got for example errors like: Could not obtain transaction-synchronized Session for current thread or no transaction is in progress) . Finally (in Spring Boot application, currently 2.3.1.RELEASE version, Hibernate 5.4.1) i'm using approach like below and my problems has solved.

@Component
public class SomeService {

    /**
     * inject entity manager
     */
    @PersistenceContext 
    private EntityManager entityManager;

    @Transactional
    public void storeMethod(File file) {
       // ...
       FileInputStream in = new FileInputStream(file);

       Session session = entityManager.unwrap(Session.class);
       Blob blob = session.getLobHelper().createBlob(in, file.length());
       // ...
       entity.setData(blob);
       repo.save(entity);
    }
}

LobHelper could be like that:

@Service
public class LobHelper {

    @PersistenceContext
    private EntityManager entityManager;

    public Blob createBlob(InputStream content, long size) {
        return ((Session)entityManager).getLobHelper().createBlob(content, size);
    }

    // ...

}

Winterkill

Answer 6

You can also create Blob right from DataSource:

@Component
public class LobHelper {

    private final DataSource ds;

    public LobHelper(@Autowired DataSource ds){
         this.ds = ds;
    }

    public Blob createBlob(byte[] content) {
        try (Connection conn = ds.getConnection()) {
            Blob b = conn.createBlob();
            try (OutputStream os = b.setBinaryStream(1);
                 InputStream is = new ByteArrayInputStream(content)) {
                byte[] buffer = new byte[500000];
                int len;
                while ((len = is.read(buffer)) > 0) {
                    os.write(buffer, 0, len);
                }
                return b;
            }
        } catch (Exception e) {
            log.error("Error while creating blob.", e);
        }
        return null;
    }

}

Unific

Why requiring byte array and not Stream? Why not use try-with-resources? – Tyika

@Tyika feel free to refactor this code. In my case I needed to use byte[] not a stream but it's pretty straightforward to change it. I didn't want to nest try-with-resources statements, but here it is - I refactored this code. – Skeptical