Spring data JPA query with parameter properties

Question

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?

Answer 1

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

For more details see: SpEL support in Spring Data JPA @Query definitions

Answer 2

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

Answer 3

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...

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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