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?
This question is related to
spring
jpa
spring-data
spring-data-jpa
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());
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...
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
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);
}
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);
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();
}
Source: Stackoverflow.com