Expressions
Kotlin JDSL์ JPQL์ expression๋ฅผ ํํํ๊ธฐ ์ํด Expression
์ธํฐํ์ด์ค๋ฅผ ๊ฐ์ง๊ณ ์์ต๋๋ค.
Alias
Expression
์ as()
๋ฅผ ํธ์ถํ๋ ๊ฒ์ผ๋ก Expression
์ alias๋ฅผ ๊ฑธ ์ ์์ต๋๋ค. expression()
์ ์ด์ฉํ๋ฉด Expression
์ ์ฐธ์กฐ๋ฅผ ๋ง๋ค ์ ์์ต๋๋ค. ์ฐธ์กฐ๋ alias๋ฅผ ํตํด์ ๊ตฌ๋ถ๋๋ฉฐ ๋์ผํ alias๋ฅผ ๊ฐ์ง๊ณ ์๋ Expression
์ ์ฐธ์กฐํ ์ ์์ต๋๋ค. ์ด๋ฅผ ํตํด Expression
์ alias๋ฅผ ๊ฑธ๊ณ alias๊ฐ ๊ฑธ๋ฆฐ Expression
์ ๋ค๋ฅธ clause์์ ์ฐธ์กฐํ ์ ์์ต๋๋ค.
val bookPrice = expression(BigDecimal::class, "price")
select(
path(Book::price)(BookPrice::value).`as`(bookPrice)
).from(
entity(Book::class)
).where(
bookPrice.eq(BigDecimal.valueOf(100))
)
// OR
select(
path(Book::price)(BookPrice::value).`as`(expression("price"))
).from(
entity(Book::class)
).where(
expression(BigDecimal::class, "price").eq(BigDecimal.valueOf(100))
)
Type Cast
์ด๋ค ๊ฒฝ์ฐ์๋ Expression
์ ํ์
์ ์ํ๋ ํ์
์ผ๋ก ๋ณ๊ฒฝํ๊ณ ์ถ์ ๋๊ฐ ์์ ๊ฒ์
๋๋ค. ์ด๋ฅผ ์ํด Kotlin JDSL์ as()
๋ฅผ ํตํด์ unsafe type casting์ ์ง์ํฉ๋๋ค.
avg(path(FullTimeEmployee::annualSalary)(EmployeeSalary::value)).`as`(BigDecimal::class)
Arithmetic operations
์ฐ์ ์ฐ์ฐ์๋ฅผ ๋ง๋ค๊ธฐ ์ํด์๋ ๋ค์ ํจ์๋ค์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
+ (plus)
- (minus)
* (times)
/ (div)
path(Book::price).plus(path(Book::salePrice))
plus(path(Book::price), path(Book::salePrice))
path(Book::price).minus(path(Book::salePrice))
minus(path(Book::price), path(Book::salePrice))
path(Book::price).times(path(Book::salePrice))
times(path(Book::price), path(Book::salePrice))
path(Book::price).div(path(Book::salePrice))
div(path(Book::price), path(Book::salePrice))
Parentheses
ํ์ฅ ํจ์๊ฐ ์๋ ์ผ๋ฐ ํจ์๋ฅผ ํธ์ถํ๋ ๊ฒ์ผ๋ก ์ฐ์ ์ฐ์ฐ์์ ์ฐ์ฐ ์์๋ฅผ ์ํ ์๊ดํธ๋ฅผ ์ถ๊ฐํ ์ ์์ต๋๋ค. ํ์ฅ ํจ์์ ๊ฒฝ์ฐ ์ฐ์ฐ ์์๊ฐ ๋ชจํธํด์ ์๊ดํธ๋ฅผ ์ถ๊ฐํ ์ ์์ต๋๋ค.
// ์ผ๋ฐ ํจ์: (Book.price - Book.salePrice) * (100)
times(
path(Book::price).minus(path(Book::salePrice)),
BigDecimal.valueOf(100),
)
// ํ์ฅ ํจ์: Book.price - Book.salePrice * 100
path(Book::price).minus(path(Book::salePrice)).times(BigDecimal.valueOf(100))
Values
๊ฐ์ ๋ง๋ค๊ธฐ ์ํด, value()
๋ฅผ ์ฌ์ฉํ ์ ์์ต๋๋ค. ๋ชจ๋ ๊ฐ์ ์ฟผ๋ฆฌ ํ๋ผ๋ฏธํฐ๋ก ์นํ๋๋ฉฐ, ์ด ํ๋ผ๋ฏธํฐ๋ ๋ณ๊ฒฝํ ์ ์์ต๋๋ค.
// SELECT Book.isbn FROM Book as Book WHERE Book.price = ?
select(
path(Book::isbn)
).from(
entity(Book::class)
).where(
path(Book::price).eq(value(BigDecimal.valueOf(100)))
)
Params
์ฟผ๋ฆฌ ํ๋ผ๋ฏธํฐ๋ฅผ ๋ง๋ค๊ธฐ ์ํด, value()
๋์ param()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค. param()
์ผ๋ก ๋ง๋ค์ด์ง ํ๋ผ๋ฏธํฐ๋ ๋ณ๊ฒฝ์ด ๊ฐ๋ฅํฉ๋๋ค.
val context = JpqlRenderContext()
val query = jpql {
select(
path(Book::isbn)
).from(
entity(Book::class)
).where(
path(Book::price).eq(param("price"))
)
}
val queryParams = mapOf(
"price" to BigDecimal.valueOf(100)
)
entityManager.createQuery(query, queryParams, context)
Literals
literal์ ๋ง๋ค๊ธฐ ์ํด, value()
๋์ xxxLiteral()
์ ์ด์ฉํ ์ ์์ต๋๋ค.
Int
intLiteral
{value}
Long
longLiteral
{value}L
Float
floatLiteral
{value}F
Double
doubleLiteral
{value}
Boolean
booleanLiteral
TRUE or FALSE
Char
charLiteral
'{value}'
String
stringLiteral
'{value}'
Enum
enumLiteral
{qualified name}.{enum name}
Aggregation functions
์งํฉ ํจ์๋ฅผ ๋ง๋ค๊ธฐ ์ํด, ๋ค์ ํจ์๋ค์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
COUNT (count)
MIN (min)
MAX (max)
AVG (avg)
SUM (sum)
count(path(Book::price))
countDistinct(path(Book::price))
max(path(Book::price))
maxDistinct(path(Book::price))
min(path(Book::price))
minDistinct(path(Book::price))
sum(path(Book::price))
sumDistinct(path(Book::price))
Sum
sum()
์ ํ๋ผ๋ฏธํฐ์ ๋ฐ๋ผ ๋ค๋ฅธ ๋ฐํ ํ์
์ ๊ฐ์ง๊ฒ ๋ฉ๋๋ค.
Int
Long
Long
Long
Float
Double
Double
Double
BigInteger
BigInteger
BigDecimal
BigDecimal
Functions
Kotlin JDSL์ JPA์์ ์ ๊ณตํ๋ ์ฌ๋ฌ ํจ์๋ค์ ์ง์ํ๊ธฐ ์ํ ํจ์๋ค์ ์ ๊ณตํฉ๋๋ค.
String functions
CONCAT (concat)
SUBSTRING (substring)
TRIM (trim)
LOWER (lower)
UPPER (upper)
LENGTH (length)
LOCATE (locate)
concat(path(Book::title), literal(":"), path(Book::imageUrl))
substring(path(Book::title), 4)
trim(path(Book::title))
trim('B').from(path(Book::title))
lower(path(Book::title))
upper(path(Book::title))
length(path(Book::title))
locate("Book", path(Book::title))
Arithmetic functions
ABS (abs)
CEILING (ceiling)
EXP (exp)
FLOOR (floor)
INDEX (index)
LN (ln)
MOD (mod)
POWER (power)
SIGN (sign)
SQRT (sqrt)
ROUND (round)
SIZE (size)
abs(path(Book::price))
ceiling(path(Book::price))
exp(path(Book::price))
floor(path(Book::price))
index(BookAuthor::class)
ln(path(Book::price))
mod(path(Employee::age), 3)
power(path(Employee::age), 2)
sign(path(Book::price))
sqrt(path(Book::price))
round(path(Book::price), 2)
size(path(Book::authors))
Datetime functions
CURRENT_DATE (currentDate)
CURRENT_TIME (currentTime)
CURRENT_TIMESTAMP (currentTimestamp)
LOCAL DATE (localDate)
LOCAL TIME (localTime)
LOCAL DATETIME (localDateTime)
currentDate()
currentTime()
currentTimestamp()
localDate()
localTime()
localDateTime()
Database function
DB ํจ์๋ ์ฌ์ฉ์ ์ ์ ํจ์๋ฅผ ๋ง๋ค๊ธฐ ์ํด, function()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
function(String::class, "myFunction", path(Book::isbn))
Cases
case๋ฅผ ๋ง๋ค๊ธฐ ์ํด, caseWhen()
๊ณผ caseValue()
๋ฅผ ์ฌ์ฉํ ์ ์์ต๋๋ค.
caseWhen(path(Book::price).lt(BigDecimal.valueOf(100))).then("0")
.`when`(path(Book::price).lt(BigDecimal.valueOf(200))).then("100")
.`when`(path(Book::price).lt(BigDecimal.valueOf(300))).then("200")
.`else`("300")
caseValue(path(Book::price))
.`when`(BigDecimal.valueOf("100")).then("10")
.`when`(BigDecimal.valueOf("200")).then("20")
.`when`(BigDecimal.valueOf("300")).then("30")
.`else`(path(Book::price))
Coalesce
coalesce๋ฅผ ๋ง๋ค๊ธฐ ์ํด, coalesce()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
coalesce(path(Employee::nickname), path(Employee::name))
NullIf
nullIf๋ฅผ ๋ง๋ค๊ธฐ ์ํด, nullIf()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
nullIf(path(Book::price), BigDecimal.ZERO)
New
DTO ํ๋ก์ ์
์ ๋ง๋ค๊ธฐ ์ํด, new()
๋ฅผ ์ฌ์ฉํ ์ ์์ต๋๋ค.
data class Row(
val departmentId: Long,
val count: Long,
)
new(
Row::class
path(EmployeeDepartment::departmentId),
count(Employee::employeeId),
)
Type
type ์ฐ์ฐ์๋ฅผ ๋ง๋ค๊ธฐ ์ํด, type()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
select(
path(Employee::id)
).from(
entity(Employee::class)
).where(
type(entity(Employee::class)).eq(FullTimeEmployee::class)
)
Custom expression
์ปค์คํ
expression์ ๋ง๋ค๊ธฐ ์ํด, customExpression()
์ ์ฌ์ฉํ ์ ์์ต๋๋ค.
customExpression(String::class, "CAST({0} AS VARCHAR)", path(Book::price))
๋ง์ฝ customExpression()
์ ๋ง์ด ์ฌ์ฉํ๋ค๋ฉด ๋๋ง์ DSL์ ๋ง๋๋ ๊ฒ์ ๊ณ ๋ คํด๋ณด์ธ์.
Last updated