Retrieving only a specific column by specifying it in the SELECT clause is called projection, but when it is inconvenient to retrieve all columns (there are too many columns, there are columns with a large capacity such as LOB). This is a convenient method.
This time, I will explain how to express a projection with the Criteria API of Doma.
There are two ways to achieve projection with the Criteria API.
The version of Doma used here is 2.43.0.
For an overview of Doma and the Criteria API, read the other articles in Introduction to Doma.
I will explain it with a simplified code. See the project below for the complete sample code.
The database has an employee table that represents employees.
schema.sql
create table employee (
id integer not null primary key,
name varchar(255) not null,
age integer not null,
salary integer,
job_type varchar(20),
hiredate timestamp,
department_id integer,
version integer not null,
insertTimestamp timestamp,
updateTimestamp timestamp);
Prepare the Employee class corresponding to the employee table.
Employee.java
@Entity(metamodel = @Metamodel)
public class Employee {
@Id
Integer id;
String name;
Age age;
Salary salary;
@Column(name = "JOB_TYPE")
JobType jobType;
LocalDate hiredate;
@Column(name = "DEPARTMENT_ID")
Integer departmentId;
@Version
@Column(name = "VERSION")
Integer version;
LocalDateTime insertTimestamp;
LocalDateTime updateTimestamp;
// getter, setter
}
Prepare a DTO (ValueObject) that holds the projected result.
NameAndSalaryDto.java
public class NameAndSalaryDto {
private final String name;
private final Salary salary;
public NameAndSalaryDto(String name, Salary salary) {
this.name = name;
this.salary = salary;
}
// getter
}
We will prepare ʻEmployeeRepository` and add some methods to this class to show an example.
EmployeeRepository.java
public class EmployeeRepository {
private final Entityql entityql;
private final NativeSql nativeSql;
public EmployeeRepository(Config config) {
this.entityql = new Entityql(config);
this.nativeSql = new NativeSql(config);
}
}
This is a very easy way in the sense that you can reuse existing data structures.
You can use the selectTo method to set the projected value to a particular property of the entity (other properties are set to null or initial values).
public List<Employee> selectNameAndSalaryAsEntityList() {
Employee_ e = new Employee_();
return entityql.from(e).selectTo(e, e.name, e.salary).fetch();
}
The SQL issued is as follows:
select t0_.id, t0_.name, t0_.salary from Employee t0_
The two columns, name and salary, are the SQL specified in the SELECT clause. It contains an unspecified primary key, which is by design and is used to ensure the uniqueness of the entity within the result set.
Doma provides classes in the ʻorg.seasar.doma.jdbc.criteria.tuplepackage that represent combinations of values such asTuple2, Tuple3, ..., Tuple9`.
The number at the end of the class name indicates how many values the class can handle.
The following is an example of projecting two columns using the Tuple2 class.
public List<Tuple2<String, Salary>> selectNameAndSalary() {
Employee_ e = new Employee_();
return nativeSql.from(e).select(e.name, e.salary).fetch();
}
Note that we are using nativeSql instead of ʻentityql. If you don't need to deal with entities with individual identifiers (such as getting results without a primary key or using aggregate functions), nativeSql` is a good choice.
The SQL issued is as follows:
select t0_.name, t0_.salary from Employee t0_
Only two columns, name and salary, are the SQL specified in the SELECT clause.
It is up to you how to use the Tuple2-9 class in which layer, but it is a beautiful design to use it as a temporary data structure until it is converted to the DTO (or ValueObject) of the application as shown below. think.
public List<NameAndSalaryDto> selectNameAndSalaryAsNameAndSalaryDtoList() {
Employee_ e = new Employee_();
return nativeSql.from(e).select(e.name, e.salary).stream()
.map(tuple -> new NameAndSalaryDto(tuple.getItem1(), tuple.getItem2()))
.collect(Collectors.toList());
}
If you specify 10 or more columns and receive it, you can express it with ʻorg.seasar.doma.jdbc.criteria.tuple.Row` interface, but if there are many columns, read-only entity class It will be easier to understand if you create it.
I explained how to represent a projection with the Criteria API.
If there is something like the Tuple class in the Java standard, I would like to use it, but it's annoying that it doesn't actually exist.
In addition, Doma's Tuple 2-9 class is also aware that it will be used in Kotlin, so it corresponds to the destructuring declaration. Therefore, Kotlin can handle it as follows.
val (number, string) = Tuple2(1, "a")
Recommended Posts