how to get records from DB on multiple conditions?

[storm crane]

hey guys. so i have this repo class:

@Repository
@EnableJpaRepositories
public interface ItemRepository extends JpaRepository<Item,Long> {
    public List<Item> getAllByDistanceAndSeason(double distance, String season);
}

So i want to be able to get records where season in db is the same as in the request AND get all records from the where season is all. and also combine with db records, where db records has less or equal distance than distance in the request. is it possible? how can i do that?

[torpid hedge]

⌛ This post has been reserved for your question.

Hey @storm crane! Please use /close or the Close Post button above when your problem is solved. Please remember to follow the help guidelines. This post will be automatically closed after 300 minutes of inactivity.

TIP: Narrow down your issue to simple and precise questions to maximize the chance that others will reply in here.

[loud gorge]

i would just write a @Query: https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html#jpa.query-methods.at-query

[storm crane]

oh, okay. didnt know thats possible.

[storm crane]

if i have @Query(nativeQuery = true,value = "SELECT name FROM items WHERE distance<=?1 AND season IN (?2, 'all')"), and do a request, i get

 "timestamp": "2024-02-06T13:41:26.992+00:00",
    "status": 500,
    "error": "Internal Server Error",
    "trace": "org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: id [The column name id was not found in this ResultSet.] 

but if i do select * ... it works. but i dont want to select all the columns. what can i do?

[loud gorge]

you can just do @Query("[insert JPA query here]") not a native query

unless if you actually want to run a native query of course

[storm crane]

jqpl queries make no sense to me. their syntax are too complicated for me

[loud gorge]

the JPA version of your query i think would be:

@Query("select i from Item i where i.distance <= ?1 and i.season in (?2, 'all')")

well the full method would be:

@Query("select i from Item i where i.distance <= ?1 and i.season in (?2, 'all')")
List<Item> getByDistanceAndSeasonOrAllSeasons(double distance, String season);

i don't think it's too different from your native query:

• replaced name with i in the select
• referring to the JPA entity name Item in the from
• using dot notation to access the members in the where e.g. (i.season instead of just season)