org.postgresql.util.PSQLException：大对象不能在自动提交模式下使用

Question

我正在使用 Spring 、 JPA 、 Hibernate 、 Postgresql 。我可以将文件上传/插入到数据库中。但当我尝试访问该文件时出现错误。

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.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:177)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:355)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:149)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:113)
    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.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
    at java.lang.Thread.run(Unknown Source)

当我从 JSP 文件中删除这段代码时，就没有问题了。

  <c:forEach items="${purchaseOrder.purchaseOrderQuotes}" var="document">
                        <tr>
                            <td>
                                ${document.name}
                            </td>
                            <td>
                                ${document.description}
                            </td>
                            <td align="left">
                                ${document.filename}
                            </td>
                            <td>
                                ${document.created}
                            </td>

                            </td>
                        </tr>
                    </c:forEach>

我检索purchaseOrder的方法：

@Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
    public PurchaseOrder findById(Integer id)
    {
        log.debug((new StringBuilder("getting PurchaseOrder instance with id: ")).append(id).toString());
        try
        {
            PurchaseOrder instance = entityManager.find(PurchaseOrder.class, id);
            //            if(instance.getProjectFundingYears() != null)
            //                log.debug("get successful");
            //            log.debug("get successful");
            return instance;
        }
        catch(RuntimeException re)
        {
            log.error("get failed", re);
            throw re;
        }
    }

这是两个实体的关系：

PurchaseOrder

@Entity
@Table(name = "purchase_order", schema =  "pta")
public class PurchaseOrder implements java.io.Serializable {

    ............

    private List<PurchaseOrderQuotes> purchaseOrderQuotes = ShrinkableLazyList
    .decorate(new ArrayList(), FactoryUtils
            .instantiateFactory(PurchaseOrderQuotes.class));

  .............................................................

    public PurchaseOrder() {
    }

....................................................................    

    @OneToMany(cascade = CascadeType.REFRESH,fetch=FetchType.LAZY, mappedBy="purchaseOrder")
    public List<PurchaseOrderQuotes> getPurchaseOrderQuotes() {
        return purchaseOrderQuotes;
    }

    public void setPurchaseOrderQuotes(List<PurchaseOrderQuotes> purchaseOrderQuotes) {
        this.purchaseOrderQuotes = purchaseOrderQuotes;
    }

............................................................................
}

PurchaseOrderQuotes

@Entity
@Table(name = "purchase_order_quotes", schema =  "pta")
public class PurchaseOrderQuotes implements java.io.Serializable {
    private Integer id;
    private String name;
    private String description;
    private String filename;
    private byte[] content;
    private String contentType;
    private Date created;
    private PurchaseOrder purchaseOrder;
    public PurchaseOrderQuotes() {
    }
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "purchase_order_quotes_id", unique = true, nullable = false)
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    @Column(name="description")
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }

    @Column(name="filename")
    public String getFilename() {
        return filename;
    }
    public void setFilename(String filename) {
        this.filename = filename;
    }

    @Lob
    @Type(type="org.hibernate.type.PrimitiveByteArrayBlobType")
    @Basic(fetch = FetchType.LAZY)
    @Column(name="content")
    public byte[] getContent() {
        return content;
    }
    public void setContent(byte[] content) {
        this.content = content;
    }
    @Column(name="content_type")
    public String getContentType() {
        return contentType;
    }
    public void setContentType(String contentType) {
        this.contentType = contentType;
    }


    @Temporal(TemporalType.TIMESTAMP)
    @Column(name="created")
    public Date getCreated() {
        return created;
    }
    public void setCreated(Date created) {
        this.created = created;
    }

    @ManyToOne
    @JoinColumn(name = "purchase_order_id", referencedColumnName = "purchase_order_id")
    public PurchaseOrder getPurchaseOrder() {
        return purchaseOrder;
    }
    public void setPurchaseOrder(PurchaseOrder purchaseOrder) {
        this.purchaseOrder = purchaseOrder;
    }

---

我不清楚。对不起。 这里我不直接访问 BLOB 文件，而是获取它的父对象。

我能够上传文件并检索一份购买订单，没有任何问题。 仅当我在 JSP 文件中添加这段代码时才报告错误。但它不应该尝试访问 BLOB 字段，因为我设置了 FetchType.LAZY。我很困惑。

<c:forEach items="${purchaseOrder.purchaseOrderQuotes}" var="document">
                        <tr>
                            <td>
                                ${document.name}
                            </td>
                            <td>
                                ${document.description}
                            </td>
                            <td align="left">
                                ${document.filename}
                            </td>
                            <td>
                                ${document.created}
                            </td>

                            </td>
                        </tr>
      </c:forEach>

Answer 1

由于您已经通过 @Transactional 定义了 Spring 事务，因此默认情况下您在自动提交事务中运行。根据 另一个线程，您需要创建第二个会话工厂，该工厂在 autocommit = false 中运行以检索文件。

此外，用于检索的 DAO 应该使用 @Qualifier 进行注释，以便它知道要使用哪个会话工厂。例子：

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