Issue with projection when using @Lob and @Query

Question

Entity:

@Entity
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    
    private Integer price;

    @Lob
    private String description;
}

Interface for Projection:

public interface NameAndDesc {

    String getAlias();
    String getDesc();
}

Repository:

public interface ItemRepository extends JpaRepository<Item, Long> {

    @Query(value = "SELECT NAME AS ALIAS, DESCRIPTION AS DESC FROM ITEM WHERE ID IS :#{#id}",nativeQuery = true)
    NameAndDesc findNameAndDesc(@Param("id") Long id);
}

When I try to call .getDesc() on the query above, I get this exception:

java.lang.IllegalArgumentException: Projection type must be an interface!

at org.springframework.util.Assert.isTrue(Assert.java:118)
at org.springframework.data.projection.ProxyProjectionFactory.createProjection(ProxyProjectionFactory.java:100)
at org.springframework.data.projection.SpelAwareProxyProjectionFactory.createProjection(SpelAwareProxyProjectionFactory.java:45)
at org.springframework.data.projection.ProjectingMethodInterceptor.getProjection(ProjectingMethodInterceptor.java:131)
at org.springframework.data.projection.ProjectingMethodInterceptor.invoke(ProjectingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.ProxyProjectionFactory$TargetAwareMethodInterceptor.invoke(ProxyProjectionFactory.java:245)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy105.getDesc(Unknown Source)
at com.example.demo.DemoApplicationTests.contextLoads(DemoApplicationTests.java:18)
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:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)

When I remove the "@Lob" annotation from "description" the projection is working without any problem. It seems that the problem is the CLOB what returns from the DB. When I change the projection interface method to clob "java.sql.Clob getDesc();" it seems to start working again, but not the best solution.

Is it right behaviour when using projections, like this?

I found a somewhat similar issue when it was a bug in ProxyProjectionFactory: Issue with projection in SpringDataRest and @Lob attribute

Could you add the full stack trace, please? Please format it as code, so the formatting is preserved. — Jens Schauder, Jan 29 '19 at 06:37
I updated my question. I only get this single line of warn message. I don't know how can I see more about this message. — Tamas P., Jan 29 '19 at 09:49
This seems to be because you are running this as part of a web application and the `DefaultHandlerExecptionResolver` just logs a warning. Execute the method directly in a test and you'll get a stack trace in all its beauty. — Jens Schauder, Jan 29 '19 at 10:13
Thank you! I updated the question. Yes, now I can see the full stack trace. — Tamas P., Jan 29 '19 at 12:20

score 1 · Answer 1 · answered Jan 29 '19 at 12:45

The idea behind a projection is to limit the columns returned and (ideally requested) from the database. There isn't much conversion support build in because this is normally handled by JPA but this doesn't happen because you are using a native query.

I therefore see two options how to solve the issue:

Convert the LOB into a VARCHAR2 or similar in the database. How this is done depends on your database. This answer seems to work for SQL Server. I'm sure you'll find an alternative for whatever database you are using.
Get JPA back in the game by using a JPQL query. That should be database independent but I assume you had a reason for using a native query, to begin with.

Paul Warren · Answer 2 · 2019-01-31T07:05:09.170

One way around this is to use the Spring Content community project. This project allows you to associate content with Spring Data entities. The content is managed separately leaving only "managed" content-related metadata on the Entity. This won't mess your projections. Think Spring Data but for Content (or Unstructured data).

This is pretty easy to add to your existing projects. I am not sure if you are using Spring Boot, or not. I'll give a non-spring boot example:

pom.xml

   <!-- Java API -->
   <dependency>
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-jpa</artifactId>
      <version>0.5.0</version>
   </dependency>
   <!-- REST API (if desired)-->
   <dependency>
      <groupId>com.github.paulcwarren</groupId>
      <artifactId>spring-content-rest</artifactId>
      <version>0.5.0</version>
   </dependency>

Configuration

@Configuration
@EnableJpaStores
@Import("org.springframework.content.rest.config.RestConfiguration.class")
public class ContentConfig {

    // schema management
    // 
    @Value("/org/springframework/content/jpa/schema-drop-mysql.sql")
    private Resource dropContentTables;

    @Value("/org/springframework/content/jpa/schema-mysql.sql")
    private Resource createContentTables;

    @Bean
    DataSourceInitializer datasourceInitializer() {
        ResourceDatabasePopulator databasePopulator =
                new ResourceDatabasePopulator();

        databasePopulator.addScript(dropContentTables);
        databasePopulator.addScript(createContentTables);
        databasePopulator.setIgnoreFailedDrops(true);

        DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource());
        initializer.setDatabasePopulator(databasePopulator);

        return initializer;
    }
}

To associate content, add Spring Content annotations to your account entity.

Item.java

@Entity
public class Item {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private Integer price;

    // replace @Lob field with
    @ContentId
    private String contentId;

    @ContentLength
    private long contentLength = 0L;

    // if you have rest endpoints
    @MimeType
    private String mimeType = "text/plain";
}

Create a "store":

ItemContentStore.java

@StoreRestResource(path="itemsContent)
public interface ItemContentStore extends ContentStore<Item, String> {
}

This is all you need to create REST endpoints @ /itemsContent. When your application starts, Spring Content will look at your dependencies (seeing Spring Content JPA/REST), look at your ItemContentStore interface and inject an implementation of that interface for JPA. It will also inject a @Controller that forwards http requests to that implementation. This saves you having to implement any of this yourself whch I think is what you are after.

So...

For to access content through a Java API, auto-wire ItemContentStore and use its methods.

Or to access content through a REST API:

curl -X POST /itemsContent/{itemId}

with a multipart/form-data request will store the image in the database and associate it with the account entity whose id is itemId.

curl /itemsContent/{itemId}

will fetch it again and so on...supports full CRUD.

There are a couple of getting started guides here. The reference guide is here. And there is a tutorial video here. The coding bit starts about 1/2 way through.

HTH

Issue with projection when using @Lob and @Query

Entity:

Interface for Projection:

Repository:

2 Answers2