I am using a projection interface to get data for a specific case. I cannot share the real code so I am sharing a similar example here. I have a Employee entity class and created a projection interface EmployeeProjection as shown below.
public interface EmployeeProjection {
@JsonProperty("employeeCode")
public String getEmployeeNumber();
@JsonProperty("employeeName")
public String getEmployeeName();
}
Below given Jpa query method I am using to get the data from the repo.
List<EmployeeProjection> employeeList = empRepo.findAllByOrderByEmployeeNumber();
Would like to know whether I can use Distinct and Order By together in this method. I want distinct employee number and it should be ordered by employee number descending.
Not sure if my understanding can help you.
JPQL
For repository layer, we can use JPQL. and Native SQL also will be sufficient.
// Custom JPQL query to achieve distinct and ordered results
@Query("SELECT DISTINCT e.employeeNumber AS employeeNumber, e.employeeName AS employeeName " +
"FROM Employee e ORDER BY e.employeeNumber DESC")
List<EmployeeProjection> findDistinctEmployeeOrderByEmployeeNumberDesc();
Lambda
if we put out the business logic to service layer, maybe java lambda can help.
public List<EmployeeProjection> getDistinctEmployeesOrderByEmployeeNumberDesc() {
List<EmployeeProjection> employees = employeeRepository.findAllByOrderByEmployeeNumberDesc();
return employees.stream()
.collect(Collectors.toMap(
EmployeeProjection::getEmployeeNumber,
e -> e,
(e1, e2) -> e1
))
.values()
.stream()
.sorted((e1, e2) -> e2.getEmployeeNumber().compareTo(e1.getEmployeeNumber()))
.collect(Collectors.toList());
}
niossoft is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3