具有参数属性的 Spring 数据 JPA 查询

使用输入参数的属性作为查询参数来声明 Spring 数据 JPA 查询的最简单方法是什么？

例如，假设我有一个实体类:

public class Person {
@Id
private long id;


@Column
private String forename;


@Column
private String surname;
}

还有另一个班:

public class Name {
private String forename;
private String surname;


[constructor and getters]
}

然后我想编写一个 Spring 数据仓库，如下所示:

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

... 但 Spring data/JPA 不喜欢我在 ?1参数上指定属性名。

最好的选择是什么？

212145 次浏览
小开

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

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

小开
最佳答案

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

小开

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

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.

小开

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

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

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

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


提交答案