I coded a project by using SpringBoot simplify QueryDSl concept that is a framework which enables the construction of type-safe SQL, instead of writing queries as inline strings or externalizing them into XML files they are constructed via a fluent API, besid Projection concept is defined as taking a vertical subset from the columns of a single table that retains the unique rows. This kind of SELECT statement returns some of the columns and all the rows in a table.
- SpringBoot
- QueryDSL
- Procjection
- MySQL
- Controllers folder
- Models folder
- Projections
- Services folder
- Repositories folder
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<classifier>jakarta</classifier>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
<classifier>jakarta</classifier>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa-codegen</artifactId>
<version>${querydsl.version}</version>
<classifier>jakarta</classifier>
</dependency>
</dependencies>
spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://localhost:3312/db
spring.datasource.username=db
spring.datasource.password=nFLhPPKOnkW1FA1e
spring.jpa.show-sql=true
✏️ This query compares employee name that is sending with employees name which stored in database and then get employee information.
@Override
public Employee findByName(String employeeName) {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query.select(entityPath).from(entityPath).where(entityPath.employeeName.eq(employeeName))
.fetchOne();
return result;
}
@Override
public List<String> getUniqueJobName() {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query.select(entityPath.jopName).distinct().from(entityPath).fetch();
return result;
}
✏️ This query multiply current salary of employee with 15% and show employee names and new salary increasement.
@Override
public List<EmployeeProjection> getEmployeeNameWithIncreasedSalary() {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query
.select(Projections.bean(EmployeeProjection.class,
entityPath.employeeName.as("employeeName"),
entityPath.salary.multiply(Expressions.ONE.multiply(1.350))
.as("salary"))) // Increase 15%
.from(entityPath).fetch();
// --------------------------another way----------------------------
// var result = query.select(entityPath.employeeName,
// entityPath.salary).from(entityPath)
// .fetch().stream()
// .map(x -> new EmployeeProjection(x.get(entityPath.employeeName),
// x.get(entityPath.salary)))
// .collect(Collectors.toList());
return result;
}
✏️ This query gets employees who dosen't belong to this department number by comparing with other departments number which is sending and bringing them all, except employees who's under this department number.
@Override
public List<Employee> getEmployeeNotBelongToDepartment(int departmentNo) {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query.select(entityPath).from(entityPath)
.where(entityPath.department.departmentNo.notIn(departmentNo)).fetch();
return result;
}
@Override
public List<Employee> getEmployeeWhoJoinedBefore(int year) {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query.select(entityPath).from(entityPath)
.where(entityPath.hireDate.year().lt(year)).fetch();
return result;
}
✏️ This query fetches employee number, employee name, department number and department name where department number on employee equal department number on department.
@Override
public List<EmployeeProjection> getEmployeeWithDepartment() {
var employeeEntityPath = QEmployee.employee;
var departmentEntityPath = QDepartment.department;
var query = new JPAQuery<>(this.entityManager);
var result = query
.select(Projections.bean(EmployeeProjection.class,
employeeEntityPath.employeeNo.as("employeeNo"),
employeeEntityPath.employeeName.as("employeeName"),
departmentEntityPath.departmentNo.as("departmentNo"),
departmentEntityPath.departmentName.as("departmentName")))
.innerJoin(departmentEntityPath)
.on(employeeEntityPath.department.departmentNo.eq(departmentEntityPath.departmentNo));
return result.fetch();
}
✏️ This query calculates the employee average salary and then fetches employee name and job name which it's greater than this salary average.
@Override
public List<EmployeeProjection> getEmployeeWhoSalaryIsGreaterThanTheAverageSalaryOfAllEmployees() {
var entityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var subQuery = query.select(entityPath.salary.avg()).from(entityPath).fetchOne();
var result = query
.select(Projections.bean(EmployeeProjection.class,
entityPath.employeeName.as("employeeName"),
entityPath.jopName.as("jopName"), entityPath.salary.as("salary")))
.from(entityPath).where(entityPath.salary.gt(subQuery)).fetch();
return result;
}
✏️ This query counts every employees under each manager, where compare employee's manager id with employee number then arrange identical employee number and name without repetition, then show employee number, name, job name, and how many employees under this manager.
@Override
public List<EmployeeProjection> countAllEmployeesUnderEachManager() {
var entityPathManager = QEmployee.employee;
var entityPathEmployee = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query
.select(Projections.bean(EmployeeProjection.class,
entityPathManager.employeeNo.as("employeeNo"),
entityPathManager.employeeName.as("employeeName"),
entityPathManager.jopName.as("jopName"),
entityPathManager.managerId.as("managerId"),
entityPathEmployee.count().as("count")))
.from(entityPathEmployee)
.join(entityPathManager)
.on(entityPathEmployee.managerId.eq(entityPathManager.employeeNo))
.groupBy(entityPathManager.employeeName, entityPathManager.employeeNo, entityPathManager.jopName)
.fetch();
return result;
}
✏️ This query fetches employee number based on manager name that sending and then comparing this employee number which cames with managerId of employees and then show employees information under this manager.
@Override
public List<Employee> getEmployeesWhoseUnderManagerName(String managerName) {
var entityPathManager = QEmployee.employee;
var entityPathEmployee = QEmployee.employee;
var findIdManagerQuery = new JPAQuery<>(this.entityManager);
var findEmployeeQuery = new JPAQuery<>(this.entityManager);
var subQuery = findIdManagerQuery.select(entityPathManager.employeeNo).from(entityPathManager)
.where(entityPathManager.employeeName.like("%" + managerName + "%")).fetchOne();
var result = findEmployeeQuery.select(entityPathEmployee).from(entityPathEmployee)
.where(entityPathEmployee.managerId.eq(subQuery));
return result.fetch();
}
✏️ This query compares department name that is sending with department name which stored in database and then get department information.
@Override
public Department findByName(String departmentName) {
var entityPath = QDepartment.department;
var query = new JPAQuery<>(this.entityManager);
var result = query.select(entityPath).from(entityPath).where(entityPath.departmentName.eq(departmentName))
.fetchOne();
return result;
}
✏️ This query takes department number that is sending then filtering it with all department number that's stored in database and show department number, department name except this department number.
@Override
public List<DepartmentProjection> findAllDepartmentsThatIsNotIncludeThisDepartmentNo(int departmentNo) {
var entityPath = QDepartment.department;
var query = new JPAQuery<>(this.entityManager);
var result = query
.select(Projections.bean(DepartmentProjection.class, entityPath.departmentNo.as("departmentNo"),
entityPath.departmentName.as("departmentName")))
.where(entityPath.departmentNo.notIn(departmentNo)).from(entityPath).fetch();
return result;
}
✏️ This query brings departments which have no employee hired yet, where to get department number of employee in employee table and filter the result if the values that aren't mentioned as part of the department number of department table then get this department infomation.
@Override
public List<Department> findAllDepartmentsThatEmployeesNotIncluded() {
var departmentEntityPath = QDepartment.department;
var employeeEntityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var subQuery = query.select(employeeEntityPath.department.departmentNo).from(employeeEntityPath).fetch();
var result = query.select(departmentEntityPath).from(departmentEntityPath)
.where(departmentEntityPath.departmentNo.notIn(subQuery)).fetch();
return result;
}
✏️ This query gets departments which have at lest two employees, that compares department number that's in department table with department number of employee in employee table then arrange identical departments number, name without repetition, then count how many employee in this department which will be greater than or equal two.
@Override
public List<DepartmentProjection> findDepartmentsHaveAtLeastTwoEmployees() {
var entityPath = QDepartment.department;
var employeeEntityPath = QEmployee.employee;
var query = new JPAQuery<>(this.entityManager);
var result = query
.select(Projections.bean(DepartmentProjection.class, entityPath.departmentNo.as("departmentNo"),
entityPath.departmentName.as("departmentName")))
.from(entityPath, employeeEntityPath)
.where(entityPath.departmentNo.eq(employeeEntityPath.department.departmentNo))
.groupBy(entityPath.departmentName,entityPath.departmentNo)
.having(entityPath.count().goe(2))
.fetch();
return result;
}
