@NamedNativeQuery with @SqlResultSetMapping for non-entity
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.
Related videos on Youtube
![Micho Rizo](https://i.stack.imgur.com/liUII.gif?s=256&g=1)
Micho Rizo
Updated on June 04, 2022Comments
-
Micho Rizo about 2 years
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!