Large Objects may not be used in auto-commit mode

Question

I have a Spring application which uses Hibernate on a PostgreSQL database. I'm trying to store files in a table of the database. It seems it stores the row with the file (I just use persist method on EntityManager), but when the object is loaded from the database I get the following exception:

org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode. 

To load the data I'm using a MultipartFile transient atribute and in its setter I'm setting the information I want to persist (byte[], fileName, size). The entity I'm persisting looks like this one (I've ommitted the rest of getters/setters):

@Entity @Table(name="myobjects") public class MyClass {      @Id     @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequence")     @SequenceGenerator(name="sequence", sequenceName="myobjects_pk_seq", allocationSize=1)     @Column(name="id")     private Integer id;      @Lob     private String description;      @Temporal(TemporalType.TIMESTAMP)     private Date creationDate;      @Transient     private MultipartFile multipartFile;      @Lob     @Basic(fetch=FetchType.LAZY, optional=true)     byte[] file;      String fileName;      String fileContentType;      Integer fileSize;      public void setMultipartFile(MultipartFile multipartFile) {         this.multipartFile = multipartFile;         try {             this.file = this.multipartFile.getBytes();             this.fileName = this.multipartFile.getOriginalFilename();             this.fileContentType = this.multipartFile.getContentType();             this.fileSize = ((Long) this.multipartFile.getSize()).intValue();         } catch (IOException e) {             logger.error(e.getStackTrace());         }     } } 

I can see that when it is persisted I have the data in the row but when I call this method it fails:

public List<MyClass> findByDescription(String text) {     Query query = getEntityManager().createQuery("from MyClass WHERE UPPER(description) like :query ORDER BY creationDate DESC");     query.setParameter("query", "%" + text.toUpperCase() + "%");     return query.getResultList(); } 

This method only fails when the result has objects with files. I've tried to set in my persistence.xml

<property name="hibernate.connection.autocommit" value="false" /> 

but it doesn't solve the problem.

In general the application works well it only commit the data when the transaction is finished and it performs a rollback if something fails, so I don't understand why is this happening.

Any idea?

Thanks.

UPDATE

Looking at the link given by Shekhar it is suggested to include the call in a transation, so I've set the service call inside a transaction an it works (I've added @Transactional annotation).

@Transactional public List<myClass> find(String text) {     return myClassDAO.findByDescription(text); } 

the problem is that I don't want to persist any data so I don't understand why it should be include inside a transaction. Does it make any sense to make a commit when I've only loaded some data form the database?

Thanks.

Answer 1

A large object can be stored in several records, that's why you have to use a transaction. All records are correct or nothing at all.

https://www.postgresql.org/docs/current/static/largeobjects.html

Answer 2

Instead of using @Transactional, all I did for this issue was to update this column in PostgreSQL DB from oid type to bytea type and added @Type for the @Lob field.

i.e.

ALTER TABLE person DROP COLUMN image; ALTER TABLE person ADD COLUMN image bytea; 

And changed

@Lob private byte[] image; 

To

@Lob @Type(type = "org.hibernate.type.ImageType") private byte[] image;