[jOOQ] How to CASE WHEN in the WHERE / AND / OR clause

You can derive what you want to do (code you want to write) from the SQL you want to execute based on the jOOQ manual. I was thinking about it, but for some reason I took a lot of time, so make a note so that I can remember it.

environment

jOOQ manual

https://www.jooq.org/doc/3.9/manual/sql-building/column-expressions/case-expressions/

Thing you want to do

Expressing the following CASE WHEN statement in jOOQ

-- (Suitable example)
--Specific person under 30 years old(ID)If the monthly salary is the same, other people double it and it does not reach 500,000 yen, it will be a hit
SELECT *
FROM person
WHERE person.age < 30
AND (
  CASE
    WHEN person.id IN (100, 1000)
    THEN person.salary
    ELSE person.salary * 2
  END
) < 500000;

jOOQ Like this

//Omitted before WHERE
  .and(
    DSL.when(
      person.getId().in(100, 1000),
      person.getSalary()
    ).otherwise(
      person.getSalary().mul(2)
    ).lt(500000)
  )

Recommended Posts

[jOOQ] How to CASE WHEN in the WHERE / AND / OR clause
[jOOQ] How to CASE WHEN in the WHERE / AND / OR clause
How to convert A to a and a to A using AND and OR in Java
How to set chrony when the time shifts in CentOS7
How to create a placeholder part to use in the IN clause
How to solve the problem when the value is not sent when the form is disabled in rails and sent
How to output the value when there is an array in the array
How to install the language used in Ubuntu and how to build the environment
How to solve the unknown error when using slf4j in Java
How to write the view when Vue is introduced in Rails?
How to find the tens and ones
How to get the date in java
Method definition location Summary of how to check When defined in the project and Rails / Gem
I summarized the points to note when using resources and resources in combination
What to do and how to install when an error occurs in DXRuby 1.4.7
How to change the maximum and maximum number of POST data in Spark
How to find the total number of pages when paging in Java
How to constrain the action of the transition destination when not logged in
[Java improvement case] How to reach the limit of self-study and beyond
[Ruby] How to prevent errors when nil is included in the operation
How to reference a column when overriding the column name method in ActiveRecord
How to create your own annotation in Java and get the value
Resolved the error that occurred when trying to use Spark in an environment where Java 8 and Java 11 coexist.
Getting Started with Doma-Using Logical Operators such as AND and OR in the WHERE Clause of the Criteria API
How to add sound in the app (swift)
How to delete the database when recreating the application
How to find the total score and average score
How to build the simplest blockchain in Ruby
How to log in automatically when Ubuntu restarts
How to check Rails commands in the terminal
graphql-ruby: How to get the name of query or mutation in controller Note
[Ruby] Learn how to use odd? Even? And count the even and odd numbers in the array!
How to set when "The constructor Empty () is not visible" occurs in junit
About the case where "Docker" freeter tried to put Docker in the existing Rails application
Be absolutely careful when putting the result of and / or in a variable!
How to set the display time to Japan time in Rails
[Java] How to omit the private constructor in Lombok
N things to keep in mind when reading "Introduction to Spring" and "Introduction to Spring" in the Reiwa era
A memo about the types of Java O/R mappers and how to select them
[Java] How to get the key and value stored in Map by iterative processing
How to get the class name of the argument of LoggerFactory.getLogger when using SLF4J in Java
Organized how to interact with the JDK in stages
How to remove Ubuntu when dual booting Windows and Ubuntu
Java classes and instances to understand in the figure
Make the where clause variable in Spring Data JPA
How to specify the resource path in HTML import
How to handle TSV files and CSV files in Ruby
[Swift] How to display the entered characters in Widget via UserDefaults when using WidgetKit
How to debug the generated jar file in Eclipse
[Ruby] How to get the tens place and the ones place
[Swift5] How to round off, round down, and round up to the second or third decimal place
How to launch Swagger UI and Swagger Editor in Docker
How to identify the cause when GC occurs frequently and CPU usage is high
[Rails] How to display an image in the view
Summary of how to use the proxy set in IE when connecting with Java
How to solve the problem that line breaks in cells are amplified when outputting EXCEL using SXSSF Workbook (3.16 or older)
How to write comments in the schema definition file in GraphQL Java and reflect them in GraphQL and GraphQL Playground
In Swift, when JSON purge, it is passed in the snake case and troublesome coping method
Check how to set the timeout when connecting with Spring + HikariCP + MySQL and executing SQL
How to mark up search keywords regardless of case and without changing the search source word
How to change Java version and execute in an environment where Java cannot be installed freely