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.
CodePudding user response:
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.
CodePudding user response:
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 aLobService
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 underlyingBinaryStream
and avoids that Exception. For example, in one of the methods of my service I receiveDocumentWrapper
, transform it toDocument
, forward call toDocumentService
, and transform its return to aDocumentWrapper
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.