Between methods
Definition
The Between methods allow you to add the BETWEEN operator to the query.
The methods available in Normal method name and the Short method name versions are:
| Normal method name | Short method name | SQL to generate |
|---|---|---|
| between | bt | leftOp BETWEEN rightOp1 AND rightOp2 |
| iBetween | ibt | LOWER(leftOp) BETWEEN LOWER(rightOp1) AND LOWER(rightOp2) |
| notBetween | nbt | NOT (leftOp BETWEEN rightOp1 AND rightOp2) |
| notIBetween | nibt | NOT (LOWER(leftOp) BETWEEN LOWER(rightOp1) AND LOWER(rightOp2)) |
For all cases, the object that calls Between methods will be placed as the operand on the left side of the BETWEEN operator and the objects or values received by parameter will be placed on the right side of the BETWEEN operator.
1. between | bt
leftOp BETWEEN rightOp1 AND rightOp2
This method takes two parameters and the possible values are:
KTableColumn, KColumn, Number, String, LocalDate, LocalDateTime, KValNumberField, KValTextField.
The two parameters must always be of the same type.
Example: bt(KColumn, KColumn)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.ID.bt(APP_USER.ROLE_ID, APP_USER.ROLE_ID.add(5)))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE au.id BETWEEN au.role_id AND (au.role_id + ?1)
Parameters:
- ?1: 5
Example: bt(Number, Number)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.ID.bt(5, 10))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE au.id BETWEEN ?1 AND ?2
Parameters:
- ?1: 5
- ?2: 10
2. iBetween | ibt
LOWER(leftOp) BETWEEN LOWER(rightOp1) AND LOWER(rightOp2)
This method takes two parameters and the possible values are:
KTableColumn, KColumn, String, KValTextField.
The two parameters must always be of the same type.
Example: ibt(KColumn, KColumn)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.EMAIL.ibt(APP_USER.FIRST_NAME, APP_USER.LAST_NAME))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE LOWER(au.email) BETWEEN LOWER(au.first_name) AND LOWER(au.last_name)
Parameters:
- None
Example: ibt(String, String)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.EMAIL.ibt("A", "B"))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE LOWER(au.email) BETWEEN ?1 AND ?2
Parameters:
- ?1: "a"
- ?1: "b"
3. notBetween | nbt
NOT (leftOp BETWEEN rightOp1 AND rightOp2)
This method takes two parameters and the possible values are:
KTableColumn, KColumn, Number, String, LocalDate, LocalDateTime, KValNumberField, KValTextField.
The two parameters must always be of the same type.
Example: nbt(KColumn, KColumn)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.ID.nbt(APP_USER.ROLE_ID, APP_USER.ROLE_ID.add(5)))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE NOT (au.id BETWEEN au.role_id AND (au.role_id + ?1))
Parameters:
- ?1: 5
Example: nbt(Number, Number)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.ID.nbt(5, 10))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE NOT(au.id BETWEEN ?1 AND ?2)
Parameters:
- ?1: 5
- ?2: 10
4. notIBetween | nibt
NOT (LOWER(leftOp) BETWEEN LOWER(rightOp1) AND LOWER(rightOp2))
This method takes two parameters and the possible values are:
KTableColumn, KColumn, String, KValTextField.
The two parameters must always be of the same type.
Example: nibt(KColumn, KColumn)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.EMAIL.nibt(APP_USER.FIRST_NAME, APP_USER.LAST_NAME))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE NOT (LOWER(au.email) BETWEEN LOWER(au.first_name) AND LOWER(au.last_name))
Parameters:
- None
Example: nibt(String, String)
Java code:
k
.select(APP_USER.ID)
.from(APP_USER)
.where(APP_USER.EMAIL.nibt("A", "B"))
.multiple();
SQL generated:
SELECT au.id
FROM app_user au
WHERE NOT (LOWER(au.email) BETWEEN ?1 AND ?2)
Parameters:
- ?1: "a"
- ?1: "b"