Getting Started with Doma-Using Subqueries with the Criteria API

Introduction

Doma Shows how the Criteria API in 2.43.0 can represent SQL subqueries.

For an overview of Doma and the Criteria API, read the other articles in Introduction to Doma.

Sample code used in this article

Here, the simplified code is used. See the project below for the complete sample code.

The database has a department table that represents departments and an employee table that represents employees.

schema.sql


create table department (
    id integer not null primary key,
    name varchar(255) not null);

create table employee (
    id integer not null primary key,
    name varchar(255) not null,
    department_id integer);

Prepare the Department class corresponding to the department table and the Employee class corresponding to the employee table.

Department.java


@Entity(metamodel = @Metamodel)
public class Department {
  @Id Integer id;
  String name;
}

Employee.java


@Entity(metamodel = @Metamodel)
public class Employee {
  @Id Integer id;
  String name;
  @Column(name = "DEPARTMENT_ID")
  Integer departmentId;
}

We will prepare ʻEmployeeRepository` and add some methods to this class to show an example.

public class EmployeeRepository {

  private final Entityql entityql;

  public EmployeeRepository(Config config) {
    this.entityql = new Entityql(config);
  }
}

Subquery using IN predicate

Call the ʻin` method.

Note that the lambda expression you pass to the where in the subquery uses the c2 instance, which is passed as a parameter, instead of the outer c instance.

  public List<Employee> selectByDepartmentName_in(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .from(e)
        .where(
            c ->
                c.in(
                    e.departmentId,
                    c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id)))
        .fetch();
  }

If the departmentName parameter is" SALES ", the generated SQL will look like this: (Hereafter, the SQL with the bound value embedded is shown, but in reality, the SQL using the bind variable ? Is issued.)

select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID
from
    Employee t0_ 
where 
    t0_.DEPARTMENT_ID in (
        select 
            t1_.id
        from
            Department t1_
        where
            t1_.name = 'SALES'
    )

If the department table has a compound key, you can also search by key combination by using Tuple2 as follows (however, the database to be used specifies multiple columns in the IN predicate format. Must be supported).

    c.in(
        new Tuple2<>(e.departmentId1, e.departmentId2),
        c.from(d).where(c2 -> c2.eq(d.name, departmentName)).select(d.id1, d.id2)))

The notIn method, which is the equivalent of the NOT IN predicate, is also provided.

Subquery using EXISTS predicate

Call the ʻexists` method.

The precautions are the same as when using the IN predicate. Note that the lambda expression you pass to the where in the subquery uses the c2 instance, which is passed as a parameter, instead of the outer c instance.

  public List<Employee> selectByDepartmentName_exists(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .from(e)
        .where(
            c ->
                c.exists(
                    c.from(d)
                        .where(
                            c2 -> {
                              c2.eq(e.departmentId, d.id);
                              c2.eq(d.name, departmentName);
                            })))
        .fetch();
  }

If the departmentName parameter is" SALES ", the generated SQL will look like this:

select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID
from
    Employee t0_
where
    exists (
        select
            t1_.id,
            t1_.name
        from
            Department t1_
        where
            t0_.DEPARTMENT_ID = t1_.id 
            and
            t1_.name = 'SALES'
    )

The notExists method, which is the equivalent of the NOT EXISTS predicate, is also provided.

Achieve equivalent search results using JOIN clauses

Queries that are not subqueries but get equivalent search results can also be represented using joins. You don't have to worry about the precautions (lambda expression parameters) required when using IN predicates and EXISTS predicates, so if the results are the same, this method is recommended. This example calls the ʻinnerJoin` method.

  public List<Employee> selectByDepartmentName_join(String departmentName) {
    Employee_ e = new Employee_();
    Department_ d = new Department_();
    return entityql
        .from(e)
        .innerJoin(d, on -> on.eq(e.departmentId, d.id))
        .where(c -> c.eq(d.name, departmentName))
        .fetch();
  }

If the departmentName parameter is" SALES ", the generated SQL will look like this:

select
    t0_.id,
    t0_.name,
    t0_.DEPARTMENT_ID 
from
    Employee t0_
inner join
    Department t1_
on 
    (t0_.DEPARTMENT_ID = t1_.id)
where
    t1_.name = 'SALES'

When written in Kotlin

Especially for subqueries, the code tends to get a little messy. If you are concerned about that point, you may consider writing in Kotlin. (Doma provides Criteria API for Kotlin) If you write a method equivalent to the above in Kotlin, it will be refreshing overall, and you will not have to worry about the parameters that are passed in the lambda expression.

Below is a code example when written in Kotlin, but if you are interested, please also refer to this project. I have a more complete working sample code.

Example using IN predicate

    fun selectByDepartmentName_in(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .where {
                `in`(e.departmentId, from(d).where { eq(d.name, departmentName) }.select(d.id))
            }
            .fetch()
    }

Example using EXISTS predicate

    fun selectByDepartmentName_exists(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .where {
                exists(
                    from(d).where {
                        eq(e.departmentId, d.id)
                        eq(d.name, departmentName)
                    }
                )
            }
            .fetch()
    }

Example using JOIN clause

    fun selectByDepartmentName_join(departmentName: String): List<Employee> {
        val e = Employee_()
        val d = Department_()
        return entityql
            .from(e)
            .innerJoin(d) { eq(e.departmentId, d.id) }
            .where {
                eq(d.name, departmentName)
            }
            .fetch()
    }

in conclusion

Demonstrated how to represent subqueries in the Criteria API of Doma 2.43.0.

In this article, the Java code is formatted with google-java-format and the Kotlin code is formatted with ktlint, but you may want to customize the format a bit to improve the readability of the code. Personally, I have the impression that google-java-format formats a little more line breaks around lambda expressions.

Recommended Posts

Getting Started with Doma-Using Subqueries with the Criteria API
Getting Started with Doma-Using Projection with the Criteira API
Getting Started with Doma-Using Joins with the Criteira 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 Doma-Using Logical Operators such as AND and OR in the WHERE Clause of the Criteria 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-Annotation Processing
Getting Started with Java Collection
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 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!