How to Use Criteria Queries in Spring Boot Data Jpa Application
I have an application that uses Spring Boot Data jpa. As long as I use storage like this
public interface StudentRepository extends CrudRepository<StudentEntity, Integer>{
@Query(value = ""
+ "SELECT s.studentname "
+ "FROM studententity s, "
+ " courseentity c "
+ "WHERE s.courseid = c.courseid "
+ " AND s.courseid IN (SELECT c.courseid "
+ " FROM courseentity c "
+ " WHERE c.coursename = ?1)")
List<String> nameByCourse(String coursename);
}
How can I use the Criteria Query that Hibernate provides for such cases in a Spring Boot application
source to share
Of docs
To enrich the repository with custom functionality, you first define the interface and implementation for the custom functionality. Use the repository interface you provided to extend the user interface.
Define such an interface
public interface StudentRepositoryCustom {
List<String> nameByCourse(String coursename);
}
Then define a custom implementation of this interface like so:
@Service
class StudentRepositoryImpl implements StudentRepositoryCustom {
@PersistenceContext
private EntityManager em;
public List<String> nameByCourse(String coursename) {
CriteriaBuilder cb = em.getCriteriaBuilder();
//Using criteria builder you can build your criteria queries.
}
}
Now you can extend this custom repository implementation in your JPA repository like this.
public interface StudentRepository extends CrudRepository<StudentEntity, Integer>, StudentRepositoryCustom {
}
Learn more about Query Criteria and Criteria Builder here
source to share
With, Spring-boot-jpa
you can use it entityManager
almost everywhere. The most convenient way is to create your own interface
for custom methods.
public interface StudentCustomRepository {
void anyCustomMethod();
Student getStudentByName(String name);
}
Then we implement this interface for the service class, where you can autwire and use entityManager
:
@Service
public class StudentCustomRepositoryServiceImpl implements StudentCustomRepository {
@PersistenceContext
private EntityManager em;
@Override
public void anyCustomMethod(){
//here use the entityManager
}
@Override
StudentEntity getStudentByName(String name){
Criteria crit = em.unwrap(Session.class).createCriteria(StudentEntity.class);
crit.add(Restrictions.eq("name", name));
List<StudentEntity> students = crit.list();
return students.get(0);
}
}
You can also implement StudentRepository
yours in your new class StudentCustomRepositoryServiceImpl
.
source to share
JPA 2 introduces a criteria API that you can use to create queries programmatically.
You can extend the new interface with JpaSpecificationExecutor
public interface CustomerRepository extends CrudRepository<Customer, Long>, JpaSpecificationExecutor {
default List<Customer> findCustomers() {
return findAll(CustomerSpecs.findCustomers());
}
Then create specifications for clients
public final class CustomerSpecs {
public static Specification<Customer> findCustomers() {
return new Specification<Customer>() {
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query,
CriteriaBuilder builder) {
LocalDate date = new LocalDate().minusYears(2);
return builder.lessThan(root.get("birthday"), date);
}
};
}
See spring doc here for details
https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications
source to share
According to Spring doc HibernateTemplate :
NOTE. Hibernate access code can also be encoded in simple hibernate style. Hence, for newly started projects, consider adopting the standard Hibernate style for coding data access objects instead of on SessionFactory.getCurrentSession (). This HibernateTemplate primarily exists as a migration helper for data based Hibernate 3 access code to take advantage of bug fixes in Hibernate 4.x.
According to the Hibernate doc:
New development should focus on the JPA javax.persistence.criteria.CriteriaQuery API. Eventually, Hibernate Specific Criteria will be ported as extensions to JPA javax.persistence.criteria.CriteriaQuery.
So, it is better to use JPQL criteria: JPA Query API Query
Example:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Country> q = cb.createQuery(Country.class);
Root<Country> c = q.from(Country.class);
q.select(c);
where entityManager should be @Autowired. See the link above for more information.
source to share
you can refer to Create Query in JPA docs for spring docs and take a look at the table, JPA gives multiple queries from method names where you can avoid using string query
source to share
It is very difficult to create specifications using JPA criteria because the API is very verbose and intrusive.
With Lambda JDK 8, you can create highly typed queries using predicates.
@Test
public void testSimpleSpec() {
String expected =
"select e0.id, e0.name "
+ "from Customer e0 "
+ "where (e0.regionCode = :p0)";
Consumer<Query<Customer>> regionCode1 =
q -> q.where(i -> i.getRegionCode()).eq(1L);
NativeSQLResult result = new QueryBuilder()
.from(Customer.class)
.whereSpec(regionCode1)
.select(i -> i.getId())
.select(i -> i.getName())
.to(new NativeSQL())
;
String actual = result.sql();
Assert.assertEquals(expected, actual);
Assert.assertEquals(result.params().get("p0"), 1L);
}
You can isolate conditions and reuse other queries, i.e. specifications.
source to share