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

Question

I am using Spring , JPA, Hibernate, Postgresql. I can upload/insert a file to the database. But I got the error when tried to access the file.

EVERE: Servlet.service() for servlet default threw exception
org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
    at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:200)
    at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:172)
    at org.postgresql.jdbc2.AbstractJdbc2BlobClob.<init>(AbstractJdbc2BlobClob.java:47)
    at org.postgresql.jdbc2.AbstractJdbc2Blob.<init>(AbstractJdbc2Blob.java:21)
    at org.postgresql.jdbc3.AbstractJdbc3Blob.<init>(AbstractJdbc3Blob.java:19)
    at org.postgresql.jdbc4.AbstractJdbc4Blob.<init>(AbstractJdbc4Blob.java:20)
    at org.postgresql.jdbc4.Jdbc4Blob.<init>(Jdbc4Blob.java:20)
    at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(Jdbc4ResultSet.java:52)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:335)
    at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getBlob(DelegatingResultSet.java:527)
    at org.hibernate.type.ByteArrayBlobType.get(ByteArrayBlobType.java:112)
    at org.hibernate.type.AbstractLobType.nullSafeGet(AbstractLobType.java:68)
    at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2267)
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1423)
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1351)
    at org.hibernate.loader.Loader.getRow(Loader.java:1251)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:619)
    at org.hibernate.loader.Loader.doQuery(Loader.java:745)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
    at org.hibernate.loader.Loader.loadCollection(Loader.java:2062)
    at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:62)
    at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:628)
    at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:83)
    at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1853)
    at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:366)
    at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersistentCollection.java:108)
    at org.hibernate.collection.PersistentBag.iterator(PersistentBag.java:272)
    at org.apache.taglibs.standard.tag.common.core.ForEachSupport.toForEachIterator(ForEachSupport.java:382)
    at org.apache.taglibs.standard.tag.common.core.ForEachSupport.supportedTypeForEachIterator(ForEachSupport.java:258)
    at org.apache.taglibs.standard.tag.common.core.ForEachSupport.prepare(ForEachSupport.java:189)
    at javax.servlet.jsp.jstl.core.LoopTagSupport.doStartTag(LoopTagSupport.java:287)
    at org.apache.jsp.WEB_002dINF.jsp.purchaseOrder.editForm_jsp._jspx_meth_c_005fforEach_005f4(editForm_jsp.java:1884)
    at org.apache.jsp.WEB_002dINF.jsp.purchaseOrder.editForm_jsp._jspService(editForm_jsp.java:131)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
    at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:551)
    at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:488)
    at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:968)
    at org.apache.jasper.runtime.PageContextImpl.doInclude(PageContextImpl.java:650)
    at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:644)
    at org.apache.tiles.jsp.context.JspTilesRequestContext.include(JspTilesRequestContext.java:103)
    at org.apache.tiles.jsp.context.JspTilesRequestContext.dispatch(JspTilesRequestContext.java:96)
    at org.apache.tiles.renderer.impl.TemplateAttributeRenderer.write(TemplateAttributeRenderer.java:44)
    at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
    at org.apache.tiles.renderer.impl.ChainedDelegateAttributeRenderer.write(ChainedDelegateAttributeRenderer.java:76)
    at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:670)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:336)
    at org.apache.tiles.template.InsertAttributeModel.renderAttribute(InsertAttributeModel.java:210)
    at org.apache.tiles.template.InsertAttributeModel.end(InsertAttributeModel.java:126)
    at org.apache.tiles.jsp.taglib.InsertAttributeTag.doTag(InsertAttributeTag.java:311)
    at org.apache.jsp.WEB_002dINF.templates.main_jsp._jspx_meth_tiles_005finsertAttribute_005f2(main_jsp.java:619)
    at org.apache.jsp.WEB_002dINF.templates.main_jsp._jspService(main_jsp.java:178)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:377)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
    at org.apache.tiles.servlet.context.ServletTilesRequestContext.forward(ServletTilesRequestContext.java:241)
    at org.apache.tiles.servlet.context.ServletTilesRequestContext.dispatch(ServletTilesRequestContext.java:222)
    at org.apache.tiles.renderer.impl.TemplateAttributeRenderer.write(TemplateAttributeRenderer.java:44)
    at org.apache.tiles.renderer.impl.AbstractBaseAttributeRenderer.render(AbstractBaseAttributeRenderer.java:106)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:670)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:690)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:644)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:627)
    at org.apache.tiles.impl.BasicTilesContainer.render(BasicTilesContainer.java:321)
    at org.springbyexample.web.servlet.view.tiles2.DynamicTilesViewProcessor.renderMergedOutputModel(DynamicTilesViewProcessor.java:106)
    at org.springbyexample.web.servlet.view.tiles2.DynamicTilesView.renderMergedOutputModel(DynamicTilesView.java:104)
    at org.springframework.web.servlet.view.AbstractView.render(AbstractView.java:250)
    at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1060)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:798)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:552)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646)
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436)
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:374)
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302)
    at org.tuckey.web.filters.urlrewrite.NormalRewrittenUrl.doRewrite(NormalRewrittenUrl.java:213)
    at org.tuckey.web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:171)
    at org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:145)
    at org.tuckey.web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:92)
    at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:381)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:343)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:96)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.savedrequest.R

Answer 1

Since you have defined your Spring transactions via @Transactional, you are by default running inside of an auto-commit transaction. As per this other thread, you need to create a second session factory which runs in autocommit = false to retrieve the file.

Additionally, the DAO for the retrieval should be annotated with @Qualifier so that it knows which session factory to use. Example:

@Autowired
public MyDAOImpl(@Qualifier("someSessionFactory") SessionFactory sessionFactory) {
   setSessionFactory(sessionFactory);
}