CODEWITHSUNDEEP
springjpaspring-dataspring-data-jpa
Kkkev
Kkkev

Reputation: 4925

Spring data JPA query with parameter properties

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?

Upvotes: 141

Views: 307256

Answers (11)

mmey
mmey

Reputation: 1685

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.

Upvotes: 14

CuriosMind...
CuriosMind...

Reputation: 517

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

Upvotes: 37

quangdang
quangdang

Reputation: 384

In JPQL (Java Persistent Query Language) - You can try as this example

@Query("SELECT p FROM Product as p " +
    "WHERE (:search IS null OR p.title LIKE %:#{#filter.search}%) " +
    "AND (:companyId IS null OR p.company.id = :#{#filter.companyId}) " +
    "AND (:categoryId IS null OR p.category.id = :#{#filter.categoryId}) "+
    "AND (:colorId IS null OR p.color.id = :#{#filter.colorId}) "+
    "AND (:maxPrice IS null OR p.newPrice < :#{#filter.maxPrice}) " +
    "AND (:minPrice IS null OR p.newPrice >= :#{#filter.minPrice}) ")
 List<Product> filterProduct(@Param("filter") Filter filter);

Upvotes: 0

sunday
sunday

Reputation: 2993

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

Upvotes: 279

Anvita Shukla
Anvita Shukla

Reputation: 449

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 - enter image description here

Further details - here

Upvotes: 3

KIBOU Hassan
KIBOU Hassan

Reputation: 389

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

Upvotes: -3

Casi
Casi

Reputation: 111

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

Upvotes: 10

Ram Pukar
Ram Pukar

Reputation: 1621

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

Upvotes: -5

Mohammad Adil
Mohammad Adil

Reputation: 533

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

Upvotes: 3

Leo
Leo

Reputation: 482

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.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 692081

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

Upvotes: 11

Related Questions