n/a
https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html
https://www.baeldung.com/spring-data-query-by-example
<!-- Persist data in SQL stores with Java Persistence API using Spring Data and Hibernate -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<!-- Fixes error: jakarta.validation.NoProviderFoundException -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
The interface JpaRepository exteds the interface QueryByExampleExecutor:
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T>
The interface QueryByExampleExecutor add more find methods with Example as argument:
<S extends T> Optional<S> findOne(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example);
<S extends T> Iterable<S> findAll(Example<S> example, Sort sort);
<S extends T> Page<S> findAll(Example<S> example, Pageable pageable);
<S extends T> long count(Example<S> example);
<S extends T> boolean exists(Example<S> example);
The interface Example provides methods for accessing the probe (instance of our entity) and the ExampleMatcher:
static <T> Example<T> of(T probe)
static <T> Example<T> of(T probe, ExampleMatcher matcher)
T getProbe()
ExampleMatcher getMatcher()
default Class<T> getProbeType()
Note: Our probe and out ExampleMatcher together specify our query.
Nesting and grouping statements are not supported, for example: (firstName = ?0 and lastName = ?1) or seatNumber = ?2 (for this, you can use JpaSpecificationExecutor or JPQL)
String matching only includes exact, case-insensitive, starts, ends, contains, and regex
All types other than String are exact-match only
Entity:
@Entity
public class Employee {
@Id
@GeneratedValue
private Long id;
private String name;
private String dept;
}
Probe:
Employee employee = new Employee();
employee.setName("Erika");
Example<Employee> employeeExample = Example.of(employee);
Remark: By default, fields having null values are ignored in the underlying query, so above Example will be equivalent to the following JPQL:
SELECT t from Employee t where t.name = 'Erika';
Warning: As Query by Example technique relies on null values to be omitted in the query generation, we should not use primitive identity (like int, long) in our Entity (as in above example) because it will default to 0 instead of null.
System.out.println("-- finding employees with name Tim --");
Employee employee = new Employee();
employee.setName("Tim");
Example<Employee> employeeExample = Example.of(employee);
//calling QueryByExampleExecutor#findAll(Example)
Iterable<Employee> employees = repo.findAll(employeeExample);
System.out.println("-- finding employees with name Jack and dept IT --");
Employee employee = new Employee();
employee.setName("Jack");
employee.setDept("IT");
Example<Employee> employeeExample = Example.of(employee);
//calling QueryByExampleExecutor#findAll(Example)
Iterable<Employee> employees = repo.findAll(employeeExample);
Reference article > https://www.baeldung.com/spring-data-jpa-projections
An entity class may have many projections. The reference article demonstrates using repositories with:
Entities
Interface view (closed projection)
Interface view (open projection)
Dto class (projection)
Dynamic( all of the above)
Eg: generic cat
Custom Repo Interface
package gendata.repo;
import java.util.List;
import gendata.dto.SubjectGatRecord;
/**
* Repo for table 'subjects' w/ entityManager
*/
public interface SubjectsRepoCustom {
List<SubjectGatRecord> getAllSubjectsByCalendarAndInitiative(String calendarCode, String initiativeCode);
}
Custom Repo Implementation
package gendata.repo;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import org.springframework.stereotype.Repository;
import gendata.dto.SubjectCatRecord;
@Repository
public class SubjectsRepoCustomImpl implements SubjectsRepoCustom {
@jakarta.persistence.PersistenceContext //Injected
jakarta.persistence.EntityManager entityManager;
@Override
public List<SubjectCatRecord> getAllSubjectsByCalendarAndInitiative(String calendarCode,
String initiativeCode) {
String sql =
"""
SELECT distinct a.*
from subjects a
join p_o_d pod
on a.subject_code= pod.cod_subject
where pod.calendar = :calendarCode
and a.t_d_t = :initiativeCode
and NOT EXISTS (
select 1
from eq_subjects ae
where ae.eq_type = 'E'
and ae.c_a_h = a.cod_subject
and a_e_s.existRelationshipSubj(ae.c_a_m, ae.c_a_h, pod.calendar) = 't'
)
order by a.cod_subject
""";
jakarta.persistence.Query query = entityManager.createNativeQuery(sql);
query.setParameter("calendarCode", calendarCode);
query.setParameter("initiativeCode", initiativeCode);
// Hibernate propietary AliasToBeanResultTransformer
@SuppressWarnings("unchecked")
List<SubjectGatRecord> results = query.unwrap(org.hibernate.query.NativeQuery.class)
.setTupleTransformer((tuple, aliases) -> new SubjectCatRecord(//
(String) tuple[0],
(String) tuple[1],
(Long) tuple[2],
(BigDecimal) tuple[3],
(BigDecimal) tuple[4],
(BigDecimal) tuple[5],
(String) tuple[6],
(String) tuple[7],
(String) tuple[8],
(String) tuple[9],
(String) tuple[10],
(String) tuple[11],
(String) tuple[12],
(String) tuple[13],
(Long) tuple[14],
(String) tuple[15],
(String) tuple[16],
(String) tuple[17],
(String) tuple[18],
(String) tuple[19],
(String) tuple[20],
(String) tuple[21],
(String) tuple[22],
(String) tuple[23],
(Long) tuple[24],
(String) tuple[25],
(String) tuple[26],
(String) tuple[27],
(LocalDate) tuple[28],
(String) tuple[29],
(String) tuple[30],
(Long) tuple[31]
)).getResultList();
return results;
}
}
Spring Data JPA Repository also extends the custom one
package gendata.repo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import gendata.entity.Subjects;
@Repository
public interface AsignaturasRepo extends JpaRepository<Subjects, String>, SubjectsRepoCustom {
...
}