CODEWITHSUNDEEP
javaspringspring-boothibernatejpa
user6090970
user6090970

Reputation: 199

Jpa Repository queries for multiple values in a column and many to one relationship for Spring boot

I am new to JPA repository implementation so seeking for help. The normal sql query to get the table items with the condition in column values will be:

select * from table t where t.column in('firstvalue','secondvalue','thirdvalue')

is there any JPA/CRUD repository equivalence for this?

For the Many to one relationship.

The subquery would be:

select * from child_foo where foo_id in(select foo_id from foo where foo_other_value="somevalue")

The hibernate implementation for this would be

select * from child_foo where child_foo.foo.foo_othervalue:"somevalue"

I was wondering what would JPA equivalence for these queries would be(Implementing this in spring boot)

Upvotes: 1

Views: 11210

Answers (2)

Damian Lisas
Damian Lisas

Reputation: 121

Spring provide you with 2 ways of defining queries in your repositories.

  1. Query Methods. Basically you can define queries by the name you put to your function.
  2. Named Queries. Here you annotate your method with @Query and you can either write a JPQL query like in coosta response or use Native SQL setting the nativeQuery to true in your annotation

For example for your query:

select * from table t where t.column in('firstvalue','secondvalue','thirdvalue')

You could use:

Query method

List<User> findAllByNameIn(List<String> names)

Here is the list of supported keywords

Named Query:

@Query("select u from User u where u.name in :names")
List<User> findUsersByName(List<String> names)

Native SQL

@Query(nativeQuery = true, value="SELECT * FROM USER WHERE NAME in ?0")
List<User> findUsersByName(List<String> names)

For the one to many mapping you can you the annotation @OneToMany. Here is a guide on how it works

Upvotes: 8

coosta
coosta

Reputation: 36

You could maybe annotate your jpa repository function with @Query and use your sql query. Something like this:

@Query("SELECT u FROM User u WHERE u.id= :id") User findUserById(string id);

Here's a thorough explanation: https://www.baeldung.com/spring-data-jpa-query

Upvotes: 0

Related Questions