Obtain InputStream from Blob by calling getBinaryStream

Question

I have a Spring Boot CRUD application that has the Document class, which is a DB entity with a Blob field. I also have the DocumentWrapper class, which is a class that is used for the transmission of the document and has a MultiPartFile field.

So, Document is the entity that I am storing in the DB, and has a dedicated JPA repository, while DocumentWrapper is the "helper" entity that the controller accepts and returns.

So in my service layer, I perform a transformation between DocumentWrapper and Document types as follow, in order to use the DocumentRepository and using this class as Bean to perform the conversion:

@Configuration
public class DocumentWrapperConverter implements AttributeConverter<DocumentWrapper, Document>, Serializable {

    @Autowired
    private LobService lobService;

    @Override
    public DocumentWrapper convertToEntityAttribute(Document document) {

        DocumentWrapper documentWrapper = new DocumentWrapper();

        documentWrapper.setName(document.getName());
        documentWrapper.setDescription(document.getDescription());
        documentWrapper.setId(document.getId());


        MultipartFile multipartFile = null;
        try {
            InputStream is = this.lobService.readBlob(document.getBlob());

            multipartFile = new MockMultipartFile(document.getName(), document.getOriginalFilename(), document.getContentType().toString(), is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        documentWrapper.setFile(multipartFile);

        return documentWrapper;
    }

    @Override
    public Document convertToDatabaseColumn(DocumentWrapper documentWrapper) {
        Document document = new Document();

        document.setName(documentWrapper.getName());
        document.setDescription(documentWrapper.getDescription());
        document.setId(documentWrapper.getId());
        document.setContentType(documentWrapper.getContentType());
        document.setFileSize(documentWrapper.getSize());
        document.setOriginalFilename(documentWrapper.getOriginalFilename());


        Blob blob = null;
        try {
            blob = this.lobService.createBlob(documentWrapper.getFile().getInputStream(), documentWrapper.getFile().getSize());
        } catch (IOException e) {
            e.printStackTrace();
        }
        document.setBlob(blob);


        return document;
    }
}

I encapsulated the logic to transform a Blob to an InputStream in the LobService and its implementor LobServiceImpl:

@Service
public class LobServiceImpl implements LobService {

    @Autowired
    private SessionFactory sessionFactory;

    @Autowired
    private DataSource dataSource;

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

    @Transactional
    @Override
    public InputStream readBlob(Blob blob) {
        Connection connection = null;
        try {
            connection = this.dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        InputStream is = null;
        try {
            is = blob.getBinaryStream();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            assert connection != null;
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return is;
    }
}

This is my generic JPARepository interface:

@NoRepositoryBean
public interface GenericRepository<T extends JPAEntityImpl, S extends Serializable> extends JpaRepository<T, S> {}

Which is extended in my Document class:

@Repository
@Transactional
public interface DocumentRepository<T extends JPAEntityImpl, S extends Serializable> extends GenericRepository<Document, UUID> {
}

Also, the Document entity class:

@Entity
@Table(uniqueConstraints = {
        @UniqueConstraint(columnNames = "id")
})
@JsonTypeInfo(
        use = JsonTypeInfo.Id.NAME,
        include = JsonTypeInfo.As.EXISTING_PROPERTY,
        property = "typeName",
        defaultImpl = Document.class)
public class Document extends JPAEntityImpl{

    @Id
    @Convert(converter = UUIDConverter.class)
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(
            name = "UUID",
            strategy = "org.hibernate.id.UUIDGenerator"
    )
    @Column(nullable = false, unique = true)
    protected UUID id;

    @Column(length = 1000, nullable = false)
    protected String name;

    @Column(length = 1000, nullable = false)
    protected String description;

    @Column(length = 1000, nullable = true)
    protected String originalFilename;

    @Column(length = 1000, nullable = false)
    protected MediaType contentType;

    @Column
    protected long fileSize;

    @Column
    @Lob
    protected Blob blob;

    public Document() {}

    // GETTERS, SETTERS, TOSTRING....

And the DocumentWrapper entity class:

@JsonTypeInfo(
        use = JsonTypeInfo.Id.NAME,
        include = JsonTypeInfo.As.EXISTING_PROPERTY,
        property = "typeName",
        defaultImpl = DocumentWrapper.class)
public class DocumentWrapper extends JPAEntityImpl {

    private UUID id;

    private String name;

    private String description;

    private MultipartFile file;

    public DocumentWrapper() {}

    // GETTERS, SETTERS, TOSTRING....

I am having problems in the method public InputStream readBlob(Blob blob). The relevant part of the error log is the following:

org.postgresql.util.PSQLException: This connection has been closed.
    at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:883)
    at org.postgresql.jdbc.PgConnection.getLargeObjectAPI(PgConnection.java:594)
    at org.postgresql.jdbc.AbstractBlobClob.getLo(AbstractBlobClob.java:270)
    at org.postgresql.jdbc.AbstractBlobClob.getBinaryStream(AbstractBlobClob.java:117)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.hibernate.engine.jdbc.SerializableBlobProxy.invoke(SerializableBlobProxy.java:60)
    at com.sun.proxy.$Proxy157.getBinaryStream(Unknown Source)
    at org.ICIQ.eChempad.services.LobServiceImpl.readBlob(LobServiceImpl.java:42)
...

This line is the one which produces the Exception:

            is = blob.getBinaryStream();

So, I would like to know how can I retrieve an InputStream for the Blob that I am receiving in the method public InputStream readBlob(Blob blob), so I can make the transformation between Document and DocumentWrapper. ¿How can I restore this connection that has been closed to retrieve the InputStream of the Blob? ¿Are there any workarounds?

Looking for answers on the Internet I saw many people performing this transformation using a ResultSet, but I am using a JPARepository interface to manipulate the records of Document in the database, not raw SQL queries; So I do not know how to proceed in that way.

I already tried using @Transactional annotations in this method but it did not work. I also tried setting the spring.jpa.properties.hibernate.current_session_context_class property (application.properties) to the values org.hibernate.context.ThreadLocalSessionContext, thread and org.hibernate.context.internal.ThreadLocalSessionContext but none of them worked.

I also tried to create a new connection, but it did not work.

I tried opening and closing a Session before and after calling blob.getBinaryStream(); but it did not work.

It seems to me that we need the same connection that was used to retrieve the Blob in the first place, but at some point Spring closes it and I do not know how to restore it or avoid that the connection is closed.

I also know that working with Byte[] arrays in my Document class could simplify things, but I do need to work with InputStream since I work with large files and is not convenient that whole files are loaded in memory.

If you need any other information about my code please do not hesitate to ask. I will add any required extra information.

Any tip, help or workaround is very welcome. Thank you.

score 0 · Answer 1 · answered Oct 04 '22 at 17:36

Whatever method calls your DocumentWrapperConverter#convertToEntityAttribute probably also loads the data from the database. It should be fine to annotate this caller method with @Transactional to keep the Connection through the transaction alive. Note though, that you have to keep the connection open until you close the input stream, so you should probably push the bytes of the input stream to some sink within the transaction. Not sure if your MockMultipartFile consumes the input stream and stores the data into a byte[] or a temporary file, but that's what you will probably have to do for this to work.

score 0 · Answer 2 · answered Oct 05 '22 at 13:53

Okay, so finally I have found the error by myself. Thanks for the help @Christian Beikov, you were actually hinting in the correct direction. Other Stack Overflow solutions did not work for me, but at least they gave me some idea of what is going on. Check this and this.

The problem is due to Hibernate and the implementation of BLOB types. The buffer inside the BLOB can only be read once. If we try to read it again (by using bob.getBinaryStream()), a database connection and session is needed to reload it.

So the first thing that you need to do if you have this error is check that your Datasource object and your database configuration is properly working and that your methods are correctly delimited by transactional boundaries by using the @Transactional annotation.

If that does not work for you and you still have problems regarding the database connection / session you can explicitly configure a SessionFactory in order to explicitly manage the session. To do so:

Define a bean that provides a SessionFactory


import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.Properties;

@org.springframework.context.annotation.Configuration
public class HibernateUtil {

    private static SessionFactory sessionFactory;

    private static HibernateUtil hibernateUtil;

    // Singleton
    @Autowired
    public HibernateUtil(Properties hibernateProperties)
    {

        try {
            Configuration configuration = new Configuration().setProperties(hibernateProperties);
            Properties properties = configuration.getProperties();
            StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().applySettings(properties);
            sessionFactory = configuration.buildSessionFactory(builder.build());
            HibernateUtil.hibernateUtil = this;  // singleton
        } catch (Throwable ex) {
            // Make sure you log the exception, as it might be swallowed
            System.err.println("Initial SessionFactory creation failed: " + ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public static HibernateUtil getInstance() {
        if (HibernateUtil.hibernateUtil == null)
        {
            // Init
            Logger.getGlobal().info("HIBERNATE UTIL INSTANCE HAS NOT BEEN INITIALIZED BY SPRING");
        }
        return HibernateUtil.hibernateUtil;
    }

    public static SessionFactory getSessionFactory()
    {
        return HibernateUtil.sessionFactory;
    }

    public void shutdown() {
        // Close caches and connection pools
        getSessionFactory().close();
    }

This class uses a singleton pattern, so everyone that request the HibernateUtil bean will have the same instance. The first initialization of this class is triggered by Spring Boot, so we can just use it from a global context by typing HibernateUtil.getSessionFactory() and receiving the same factory.

You can use that SessionFactory to delimit the boundaries of your transaction manually like this. I created a LobService class to wrap the logic to read and create BLOBs:

@Service
public class LobServiceImpl implements LobService {

    @Transactional
    @Override
    public Blob createBlob(InputStream content, long size) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();

        Blob blob = session.getLobHelper().createBlob(content, size);

        session.getTransaction().commit();
        session.close();
        return blob;
    }

    @Transactional
    @Override
    public InputStream readBlob(Blob blob) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();

        InputStream is = null;
        try {
            is = blob.getBinaryStream();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        session.getTransaction().commit();
        session.close();

        return is;
    }
}

If that does not work for you, in my project even if there was a database connection and session another error showed up in the same place as before: SQLException: could not reset reader. What you need to do is reload the database instance that contains the BLOB the second time that you try to access to it. So, you do an extra (useless) query, but in this case requerying for the same instance reloads the underlying BinaryStream and avoids that Exception. For example, in one of the methods of my service I receive DocumentWrapper, transform it to Document, forward call to DocumentService, and transform its return to a DocumentWrapper and returns it to the upper level of controllers):

    @Override
    public <S1 extends DocumentWrapper> S1 save(S1 entity) {
        Document document = this.documentWrapperConverter.convertToDatabaseColumn(entity);

        // Internally the BLOB is consumed, so if we use the same instance to return an exception will be thrown
        // java.sql.SQLException: could not reset reader
        Document documentDatabase = this.documentService.save(document);

        // This seems silly, but is necessary to update the Blob and its InputStream
        Optional<Document> documentDatabaseOptional = this.documentService.findById((UUID) documentDatabase.getId());

        return documentDatabaseOptional.map(value -> (S1) this.documentWrapperConverter.convertToEntityAttribute(value)).orElse(null);
    }

Please, notice that this line

        // This seems silly, but is necessary to update the Blob and its InputStream
        Optional<Document> documentDatabaseOptional = this.documentService.findById((UUID) documentDatabase.getId());

Fetches the same entity from the DB, but now the BinaryStream is reloaded and can be read again.

So, TL;DR reload the managed instance from the database if you already exhausted its BinaryStream buffer.

Obtain InputStream from Blob by calling getBinaryStream

2 Answers2