How to define custom query using JPA @Query annotation

Question

How do you use the JPA @Query notation? I wish to run the query:

SELECT region, winner_count
FROM awards_regions
WHERE award_id = ?
ORDER BY region

I have the repository:

public interface AwardsRegionsRepository extends JpaRepository<AwardRegion,AwardRegionPk>{

    List<AwardRegion> findAllByOrderByAwardRegionPk_RegionAscSortKeyAsc();
    List<AwardRegion> findByAwardRegionPk_RegionOrderBySortKeyAsc(String region);

    @Query("select a.region, a.winner_count from awards_regions a "
            + "where a.award_id = :awardId order by a.region")
    List<AwardRegion> findByAwardRegionPk_AwardId(@Param("awardId") Long awardId);

}

My entity Java beans are

@Entity
public class AwardRegion implements Serializable{

    @EmbeddedId
    private AwardRegionPk awardRegionPk;

    @Column(name = "award")
    private String award;

    @Column(name = "sort_key")
    private String sortKey;

    @Column(name = "winner_count")
    private String winnerCount;

}

...and embedded PK

@Embeddable
public class AwardRegionPk implements Serializable{

    @Column(name = "region")
    private String region;

    @Column(name = "award_id")
    private Long awardId;

}

You can check bellow answer https://stackoverflow.com/a/50365522/3073945 — Md. Sajedul Karim, Jun 30 '18 at 12:12

score 0 · Accepted Answer · answered May 19 '16 at 20:01

It looks as though you are using the database table column names in your JPA query. Use the Java bean names.

public interface AwardsRegionsRepository extends JpaRepository<AwardRegion,AwardRegionPk>{

    List<AwardRegion> findAllByOrderByAwardRegionPk_RegionAscSortKeyAsc();
    List<AwardRegion> findByAwardRegionPk_RegionOrderBySortKeyAsc(String region);

    @Query("select a.awardRegionPk.region, a.winnerCount from AwardRegion a "
            + "where a.awardRegionPk.awardId = :awardId order by a.awardRegionPk.region")
    List<AwardRegion> findByAwardRegionPk_AwardId(@Param("awardId") Long awardId);

}

This is coming out as a list of strings, not a list of objects — , May 19 '16 at 20:09
If you want objects query for Objects and not individual fields! — Alan Hay, May 20 '16 at 07:46

How to define custom query using JPA @Query annotation

1 Answers1