Unable to access lob stream for findByDescriptionContaining(String description)

There is a simple search by string parameter. The page consists of table of DB queries and simple form (input + button). The search slash filter is implemented with addon in Repository and Controller. Template engine is Freemarker.

But if the search input contains existing data it throws an exception nested exception is org.hibernate.HibernateException: Unable to access lob stream. Null and non-existent data works correct (showing no queries or all of them respectively).

Model

@Entity
@Table(name = "files")
public class DBFile {
    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid2")
    private String id;
    private String fileName;
    private String fileType;
    private String description;

    @Basic(fetch=FetchType.EAGER)
    @Lob
    private byte[] data;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User creator;

    @CreationTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "create_date")
    private Date createDate;

    public DBFile() {
    }

    public DBFile(String fileName, String fileType, String description, byte[] data, User user) {
        this.fileName = fileName;
        this.fileType = fileType;
        this.description = description;
        this.data = data;
        this.creator = user;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public String getFileType() {
        return fileType;
    }

    public void setFileType(String fileType) {
        this.fileType = fileType;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public byte[] getData() {
        return data;
    }

    public void setData(byte[] data) {
        this.data = data;
    }

    public User getCreator() {
        return creator;
    }

    public void setCreator(User creator) {
        this.creator = creator;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }
}

Repository

@Repository
public interface DBFileRepository extends JpaRepository<DBFile, String> {
    List<DBFile> findByDescriptionContaining(String description);
}

Controller

@Autowired
    private DBFileRepository dbFileRepository;

@GetMapping("/search")
    public String search(@RequestParam(required = false, defaultValue = "") String search, Model model) {
        Iterable<DBFile> dbFiles;
        if (search != null && !search.isEmpty()) {
            dbFiles = dbFileRepository.findByDescriptionContaining(search);
        } else {
            dbFiles = dbFileRepository.findAll();
        }
        model.addAttribute("dbFiles", dbFiles);
        return "search";
    }

Template

<@c.page>

    <form method="get">
        <div class="form-group">
            <input type="text" class="form-control" name="search" value="${search?ifExists}" />
        </div>
        <div class="form-group">
            <button type="submit" class="btn btn-primary">Search</button>
        </div>
    </form>
    <div class="table">
        <table class="table table-hover">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Description</th>
                </tr>
            </thead>
            <tbody>
            <#list dbFiles as dbFile>
                <tr>
                    <td>${dbFile.fileName}</td>
                    <td>${dbFile.description}</td>
                </tr>
            </#list>
            </tbody>
        </table>
    </div>

</@c.page>

Log

java.lang.reflect.InvocationTargetException: null
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_192]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_192]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_192]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_192]
    at org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl.useContextualLobCreation(LobCreatorBuilderImpl.java:113) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.engine.jdbc.env.internal.LobCreatorBuilderImpl.makeLobCreatorBuilder(LobCreatorBuilderImpl.java:54) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentImpl.<init>(JdbcEnvironmentImpl.java:271) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:114) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:259) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:233) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:242) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:861) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:888) [hibernate-core-5.2.14.Final.jar:5.2.14.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57) [spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:388) [spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377) [spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228) ~[spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) [spring-beans-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1085) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549) ~[spring-context-5.0.4.RELEASE.jar:5.0.4.RELEASE]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:140) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:752) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:388) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1246) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1234) ~[spring-boot-2.0.0.RELEASE.jar:2.0.0.RELEASE]
    at ru.tpu.file_java.Application.main(Application.java:9) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_192]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_192]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_192]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_192]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.0.0.RELEASE.jar:2.0.0.RELEASE]
Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.
    at org.postgresql.Driver.notImplemented(Driver.java:683) ~[postgresql-42.2.1.jar:42.2.1]
    at org.postgresql.jdbc.PgConnection.createClob(PgConnection.java:1252) ~[postgresql-42.2.1.jar:42.2.1]
    ... 49 common frames omitted

Adding @Transactional to your repository may solve the commit issue (worked for me)

@Repository
@Transactional
public interface DBFileRepository extends JpaRepository<DBFile, String> {
    List<DBFile> findByDescription(String description);
}

Unable to access lob stream error with posgres · Issue #32 · hipster , Overview of the issue and Motivation for or Use Case Can't fetch result from https://github.com/Column annotation on a String field gives "wrong deepu105 changed the title Unable to access lob stream Unable to access  4 Unable to access lob stream for findByDescriptionContaining(String description) Apr 24 '19 0 Set work_mem for a native query in Spring data Dec 11 '19 0 Selectively disable “Try it out” in swaggerUI Mar 1

When you use findBy match of JpaRepository, the suffix need to be the field that you are trying to get, e.g:

@Repository
public interface DBFileRepository extends JpaRepository<DBFile, String> {
    List<DBFile> findByDescription(String description);
}

[@Lob] [String] [PostgreSQL] Issue with Large Objects in auto , Overview of the issue When using PostgreSQL (at least), creating a String field with the annotation @Lob triggers a bug when getting the entity HibernateException: Unable to access lob stream' and exception The entity can not be retrieved for database, so can not be serialized and sent to the frontend. @Lob @Column (name = " description ") private String description; Complete stack trace Exception in com.mycompany.myapp.web.rest.PlaceResource.getAllPlaces() with cause = 'org.hibernate.HibernateException: Unable to access lob stream' and exception = 'Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to

Solution:

After adding spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true, I got another exception - org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.

But then I found a solution, which worked for me absolutely: org.hibernate.HibernateException [org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.]

[ENTESB-6038] Error loading situations when using Postgres, HibernateException: Unable to access lob stream ERROR [stderr] at org.​hibernate.type.descriptor.java.DataHelper.extractString(DataHelper.java:285) ERROR  Indicates whether or not the LOB stream can be read. CanSeek. Indicates whether or not forward and backward seek operations be performed. CanWrite. Indicates whether or not the LOB object supports writing. Connection. Indicates the OracleConnection that is used to retrieve and write BLOB data. IsEmpty. Indicates whether the BLOB is empty or not

[HHH-12393] Unable to access lob stream with Postgres for @lob , Description. I've just upgraded to 5.2.15, I come from 5.1.0. I have only one entity with only one field annotated with @lob. I'm not anymore able to get those  The most popular streaming platform for Twitch, YouTube, Mixer and Facebook. Cloud-based and used by 70% of Twitch. Grow with Streamlabs Open Broadcast Software (OBS), alerts, 1000+ overlays, analytics, chatbot, tipping, merch and more.

spring-projects/spring-data, JpaSystemException: Unable to access lob stream; nested exception is StringTypeDescriptor.wrap(StringTypeDescriptor.java:72) at  The value can be "-1" to disable LOB prefetch for this connection, "0" to enable LOB prefetch for meta-data only or any value greater than 0 which represents a number of bytes for BLOBs and chars for CLOBs to be prefetched along with the locator during fetch operations.

Chapter 10. Lob with Oracle and PostgreSQL, Lob columns can be used to store very long texts or binary files. There are two kind of lobs: Just add a @Lob annotation to a string field. That's all. Columns of type blob or binaryobject can read the lob as stream, once you access the data​. HHH-12396 Problem with mapping of the annotation @Enumerated(EnumType.STRING) HHH-12393 Unable to access lob stream with Postgres for @lob field HHH-12392 Caching SchemaResolver delegate with multiple data sources

Comments
  • Possible duplicate of Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented
  • @Compass none of those hints worked, unfortunately
  • this worked for me. Thank you.
  • Why? I needed incomplete match of query, so I used the official documentation on JPA - docs.spring.io/spring-data/jpa/docs/1.5.0.RELEASE/reference/…
  • But still, tried as you advised and got the same exception
  • The cause in the stacktrace is Caused by: java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createClob() is not yet implemented., apply this solution github Postgresql project github.com/pgjdbc/pgjdbc/issues/1102#issuecomment-398036270