@NamedNativeQuery with @SqlResultSetMapping for non-entity

Question

I have been using this post as an example. I have a complex join query (simplified here). It returns a subset of values from two tables (and a derived column using CASE). I don't think I need to use an entity annotation because the object returned from my result set is not an actual table in my schema.

My non-entity object that I want to hold results from my join query:

@SqlResultSetMapping(
           name="myMapping",
           classes={
              @ConstructorResult(
                   targetClass=CarLimitDelta.class,
                     columns={
                        @ColumnResult(name="caseCol"),
                        @ColumnResult(name="colA"),
                        @ColumnResult(name="colB"),
                        }
              )
           }
)
@NamedNativeQuery(name="CarLimitDelta.getCarLimitDelta", 
        resultSetMapping="myMapping", 
        query="SELECT CASE "+ 
           "WHEN t.foo IS NULL THEN 'INS' "+
           "WHEN s.foo IS NULL THEN 'DEL' "+
           "ELSE 'UPD' "+
        "END caseCol "+
     ", T.bar colA "+
     ", S.bar ColB "+
     "FROM tableA S "+
     "FULL JOIN TableB ON S.bar= T.bar")

public class CarLimitDelta {
        private String caseCol;
        private String colA;
        private String colB;


    //getters/setters/etc
    }

My repo:

@Repository
public interface CarLimitRepository extends CrudRepository<CarLimitDelta, String> {
    List<CarLimitDelta> getCarLimitDelta();
}

my Service:

@Service
public class CarLimitService {

    @Autowired
    CarLimitRepository carLimitRepository ;

    public void deleteAll() {
        carLimitRepository.deleteAll();
    }

    public List<CarLimitDelta> getCarLimitDelta() {
        return carLimitRepository.getCarLimitDelta();
    }


}

I'm not sure how to get my repo to see my new CarLimitDelta.getCarLimitDelta native query method defined in my NamedNativeQuery. I get the following error:

Invalid derived query! No property getCarLimitDelta found for type CarLimitDelta!

I ended up getting around this by just creating a view in my db and using the standard entity/repo/service structure instead of calling a native query. I will look at the native query solution later. — Micho Rizo, Mar 12 '18 at 14:35

score 16 · Answer 1 · answered Mar 28 '18 at 13:40

The @SqlResultSetMapping and @NamedNativeQuery annotations need to be on an @Entity, not on the non-entity POJO.

If the entity is Foo, then add the annotations as follows:

@SqlResultSetMapping(
           name="myMapping",
           classes={
              @ConstructorResult(
                   targetClass=CarLimitDelta.class,
                     columns={
                        @ColumnResult(name="caseCol"),
                        @ColumnResult(name="colA"),
                        @ColumnResult(name="colB"),
                        }
              )
           }
)
@NamedNativeQuery(name="Foo.getCarLimitDelta", 
        resultSetMapping="myMapping", 
        query="...")
@Entity
public class Foo {
  ...
}

Note that the @NamedNativeQuery name is prefixed with the entity name, e.g. Foo.getCarLimitDelta.

Then add the method to the Foo repository:

@Repository
public interface FooRepository extends CrudRepository<Foo, String> {
    List<CarLimitDelta> getCarLimitDelta();
}

Note that the method name, getCarLimitDelta, matches the @NamedNativeQuery name, minus the prefix.

It is a bit silly: You want to create a query that puts data into a DTO, not an Entity, and you get to define it in conjunction with an entity class. To express that clearly, I usually define the SqlResultSetMapping on a "dummy class": ```class DummyEntity() { @Id var id: Long? = null }``` (Kotlin), especially when I generate native SQL with JOOQ. — raoulsson, Jan 22 '23 at 17:17

@NamedNativeQuery with @SqlResultSetMapping for non-entity

1 Answers1

Linked