@Query annotation is not reading native sql select * spring data

Question

public interface ConsumerRepository extends CrudRepository<Consumer, Long>  {

    List<Consumer> findByLastName(String lastName);
    List<Consumer> findByFirstName(String firstName);

    //List<Consumer> readExistsFirstName(String firstName);
    //List<Consumer> getByFirstNameAndLastNameOrderByFirstName();
    //Collection<Consumer> findById(Long id);
    @Query("select c from Consumer as c where c.firstName like '%?1%'")
    Collection<Consumer> findByNameContains(String word);

    @Query(value = "SELECT ppc_consumer FROM ppc_consumer WHERE lastname = ?1",
            countQuery = "SELECT count(*) FROM ppc_consumer WHERE lastname = ?1",
            nativeQuery = true)
    Page<Consumer> findByLastname(String lastname, Pageable pageable);
 }

But is shows error " Distinc expected " , got * because i have used * inside @Query annotation ? what is wrong please ?

Why do you want to use native query here ? You can actually fetch the ppc_consumer records directly, right ? Could you please elaborate ? — Vasu, Jul 19 '16 at 10:23

score 2 · Answer 1 · edited May 23 '17 at 12:14

2

You can't use * in spring jpa you have to use aliases. Try this way:

@Query(value = "SELECT ppc FROM ppc_consumer ppc WHERE ppc.lastname = ?1",
            countQuery = "SELECT count(ppc) FROM ppc_consumer ppc WHERE ppc.lastname = ?1")

See this question for more information

Does Spring Data JPA have any way to count entites using method name resolving?

edited May 23 '17 at 12:14

Community

1
1

answered Jul 19 '16 at 11:30

amicoderozer

2,046
6
28
44

it is because of the `nativeQuery=true` command. I edit the answer, i tried removing that and it works for me. – amicoderozer Jul 19 '16 at 13:08
your example is also perfectly working but you could also add * example :) – Develop4Life Jul 19 '16 at 13:11

score 2 · Accepted Answer · edited Jun 28 '18 at 04:11

Let's do it as below example-

@Query(value = "SELECT * FROM consumer WHERE lastname = :lastName  LIMIT :maxRecord", nativeQuery = true)
StickerProduct findWalletProduct(@Param("lastName") String lastName, @Param("maxRecord") Long maxRecord);

Another way to do same instead of * :

@Query(value = "SELECT c FROM consumer c WHERE lastname = :lastName  LIMIT :maxRecord", nativeQuery = true)
StickerProduct findWalletProduct(@Param("lastName") String lastName, @Param("maxRecord") Long maxRecord);

I Hope it work. Thanks

@Query annotation is not reading native sql select * spring data

2 Answers2