Getting Started with Doma-Using Projection with the Criteira API

Introduction

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.

Sample code used in this article

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);
  }
}

Get using entity class

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.

Get using tuple class

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.

in conclusion

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

Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Joins with the Criteira API
Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Doma-Introduction to the Criteria API
Getting Started with Doma-Dynamicly construct WHERE clauses with the Criteria API
Getting Started with Reactive Streams and the JDK 9 Flow API
Getting Started with Doma-Criteria API Cheat Sheet
Getting started with the JVM's GC mechanism
Getting Started with DBUnit
Getting Started with Ruby
Getting Started with Swift
Getting Started with Docker
Getting Started with Doma-Transactions
Getting Started with Doma-Using Logical Operators such as AND and OR in the WHERE Clause of the Criteria API
Getting Started with Doma-Annotation Processing
Getting Started with JSP & Servlet
Getting Started with Java Basics
Getting Started with Spring Boot
Getting Started with Ruby Modules
Now is the time to get started with the Stream API
Getting Started with Java_Chapter 5_Practice Exercises 5_4
[Google Cloud] Getting Started with Docker
Getting started with Java lambda expressions
Getting Started with Docker with VS Code
Returning to the beginning, getting started with Java ② Control statements, loop statements
Summarize the main points of getting started with JPA learned with Hibernate
Getting Started with Ruby for Java Engineers
Getting Started with Docker for Mac (Installation)
Getting Started with Parameterization Testing in JUnit
Getting Started with Java Starting from 0 Part 1
Getting Started with Ratpack (4)-Routing & Static Content
Getting Started with Language Server Protocol with LSP4J
Getting Started with Creating Resource Bundles with ListResoueceBundle
Getting Started with Java_Chapter 8_About Instances and Classes
Links & memos for getting started with Java (for myself)
Getting Started with Java 1 Putting together similar things
Getting started with Kotlin to send to Java developers
I tried Getting Started with Gradle on Heroku
Going back to the beginning and getting started with Java ① Data types and access modifiers
Getting started with Java programs using Visual Studio Code
"Experience" reactive programming with NoSQL (touch the Couchbase Reactive API)
Getting Started with Legacy Java Engineers (Stream + Lambda Expression)
Get started with serverless Java with the lightweight framework Micronaut!