Java Community | Help. Code. Learn.•12mo ago

how to get records from DB on multiple conditions?

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);
}

@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?

7 Replies

JavaBot•12mo ago

⌛ This post has been reserved for your question.

Hey @bambyzas! 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.

imp_o_rt•12mo ago

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

i hate SQL so much its unrealOP•12mo ago

oh, okay. didnt know thats possible. 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.]

 "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?

imp_o_rt•12mo ago

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

i hate SQL so much its unrealOP•12mo ago

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

imp_o_rt•12mo ago

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')")

@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);

@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)

JavaBot•12mo ago

💤 Post marked as dormant

This post has been inactive for over 300 minutes, thus, it has been archived. If your question was not answered yet, feel free to re-open this post or create a new one. In case your post is not getting any attention, you can try to use /help ping. Warning: abusing this will result in moderative actions taken against you.

Gaming

Programming

how to get records from DB on multiple conditions?

Did you find this page helpful?