Spring data JPA query with parameter properties
SpringJpaSpring DataSpring Data-JpaSpring Problem Overview
What is the simplest way of declaring a Spring data JPA query that uses properties of an input parameter as query parameters?
For example, suppose I have an entity class:
public class Person {
@Id
private long id;
@Column
private String forename;
@Column
private String surname;
}
and another class:
public class Name {
private String forename;
private String surname;
[constructor and getters]
}
... then I would like to write a Spring data repository as follows:
public interface PersonRepository extends CrudRepository<Person, Long> {
@Query("select p from Person p where p.forename = ?1.forename and p.surname = ?1.surname")
findByName(Name name);
}
... but Spring data / JPA doesn't like me specifying property names on the ?1
parameter.
What is the neatest alternative?
Spring Solutions
Solution 1 - Spring
This link will help you: Spring Data JPA M1 with SpEL expressions supported. The similar example would be:
@Query("select u from User u where u.firstname = :#{#customer.firstname}")
List<User> findUsersByCustomersFirstname(@Param("customer") Customer customer);
https://spring.io/blog/2014/07/15/spel-support-in-spring-data-jpa-query-definitions
Solution 2 - Spring
Define the query method with signatures as follows.
@Query(select p from Person p where p.forename = :forename and p.surname = :surname)
User findByForenameAndSurname(@Param("surname") String lastname,
@Param("forename") String firstname);
}
For further details, check the Spring Data JPA reference
Solution 3 - Spring
What you want is not possible. You have to create two parameters, and bind them separately:
select p from Person p where p.forename = :forename and p.surname = :surname
...
query.setParameter("forename", name.getForename());
query.setParameter("surname", name.getSurname());
Solution 4 - Spring
You could also solve it with an interface default method:
@Query(select p from Person p where p.forename = :forename and p.surname = :surname)
User findByForenameAndSurname(@Param("surname") String lastname,
@Param("forename") String firstname);
default User findByName(Name name) {
return findByForenameAndSurname(name.getLastname(), name.getFirstname());
}
Of course you'd still have the actual repository function publicly visible...
Solution 5 - Spring
You can try something like this:
public interface PersonRepository extends CrudRepository<Person, Long> {
@Query("select p from Person AS p"
+ " ,Name AS n"
+ " where p.forename = n.forename "
+ " and p.surname = n.surname"
+ " and n = :name")
Set<Person>findByName(@Param("name") Name name);
}
Solution 6 - Spring
if we are using JpaRepository then it will internally created the queries.
Sample
> findByLastnameAndFirstname(String lastname,String firstname) > > findByLastnameOrFirstname(String lastname,String firstname) > > findByStartDateBetween(Date date1,Date2) > > findById(int id)
Note
if suppose we need complex queries then we need to write manual queries like
@Query("SELECT salesOrder FROM SalesOrder salesOrder WHERE salesOrder.clientId=:clientId AND salesOrder.driver_username=:driver_username AND salesOrder.date>=:fdate AND salesOrder.date<=:tdate ")
@Transactional(readOnly=true)
List<SalesOrder> findAllSalesByDriver(@Param("clientId")Integer clientId, @Param("driver_username")String driver_username, @Param("fdate") Date fDate, @Param("tdate") Date tdate);
Solution 7 - Spring
Are you working with a @Service
too? Because if you are, then you can @Autowired
your PersonRepository
to the @Service
and then in the service just invoke the Name
class and use the form that @CuriosMind... proposed:
@Query(select p from Person p where p.forename = :forename and p.surname = :surname)
User findByForenameAndSurname(@Param("surname") String lastname,
@Param("forename") String firstname);
}
and when invoking the method from the repository in the service, you can then pass those parameters.
Solution 8 - Spring
The simplicity of Spring Data JPA is that it tries to interpret from the name of the function in repository without specifying any additional @Query or @Param annotations. If you are supplying the complete name, try to break it down as firstname and lastname and then use something like this -
HotelEntity findByName(String name);
My HotelEntity does contain the field name so JPA tries to interpret on its own to infer the name of the field I am trying to query on and create a subsequent query internally. Some more evidence from JPA documentation -
Further details - here
Solution 9 - Spring
for using this, you can create a Repository for example this one:
Member findByEmail(String email);
List<Member> findByDate(Date date);
// custom query example and return a member
@Query("select m from Member m where m.username = :username and m.password=:password")
Member findByUsernameAndPassword(@Param("username") String username, @Param("password") String password);
Solution 10 - Spring
@Autowired
private EntityManager entityManager;
@RequestMapping("/authors/{fname}/{lname}")
public List actionAutherMulti(@PathVariable("fname") String fname, @PathVariable("lname") String lname) {
return entityManager.createQuery("select A from Auther A WHERE A.firstName = ?1 AND A.lastName=?2")
.setParameter(1, fname)
.setParameter(2, lname)
.getResultList();
}