CODEWITHSUNDEEP
javahibernatejpa
georgiana_e
georgiana_e

Reputation: 1869

CRudRepository native query could not return Result Set

I have a simple test query inside a CrudRepository interface that should return a List of entities.

public interface TestRepository extends CrudRepository<Test, TestId> {
    @Query(value = "SELECT p FROM test p ", nativeQuery = true)
    public List<Test> getTests();
}

When I test this I get the exception:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet

If I don't use native query it works, but I want to use native query because I want to extend the select.

Upvotes: 1

Views: 7373

Answers (3)

karim mohsen
karim mohsen

Reputation: 2254

In order to make your query work :-

@Query(value = "SELECT * FROM TEST ", nativeQuery = true)
public List<Test> getTests();

The reason is simply because you are writing native query."SELECT p FROM test p" is not a native query

Upvotes: 4

Ofer Skulsky
Ofer Skulsky

Reputation: 743

2 problems

in native SQL use native SQL :)
@Query(value = "SELECT p.* FROM test p ", nativeQuery = true)

your native Query returns an Object[] or a List of Object[].
You can change that, if you provide additional mapping information to the EntityManager.
By doing this you can tell the EntityManager to map the result into managed entities, scalar values of specific types or POJOs.
The simplest way to map the result of a native query into a managed entity is to select all properties of the entity and provide its as a parameter to the createNativeQuery method.

(sorry for using other examples)

Query q = em.createNativeQuery("SELECT a.id, a.version, a.firstname, a.lastname FROM Author a", Author.class);
List<Author> authors = q.getResultList();

All other mappings, like the following one which maps the query result into a POJO, need to be defined as SQLResultSetMappings.

@SqlResultSetMapping(
        name = "AuthorValueMapping",
        classes = @ConstructorResult(
                targetClass = AuthorValue.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "firstname"),
                    @ColumnResult(name = "lastname"),
@ColumnResult(name = "numBooks", type = Long.class)}))

To use this mapping, you need to provide the name of the mapping as a parameter to the createNativeQuery method.

Query q = em.createNativeQuery("SELECT a.id, a.firstname, a.lastname, count(b.id) as numBooks FROM Author a JOIN BookAuthor ba on a.id = ba.authorid JOIN Book b ON b.id = ba.bookid GROUP BY a.id", "AuthorValueMapping");
List<AuthorValue> authors = q.getResultList();

Upvotes: 1

georgiana_e
georgiana_e

Reputation: 1869

@Query(value = "SELECT * FROM test p ", nativeQuery = true)

Upvotes: 0

Related Questions