QueryDSL

Introduction

Since version 1.6.0, Query DSL is newly added. It’s much readable and type-safe DSL.

val id = 123 val ( m , g ) = ( GroupMember . syntax ( "m" ), Group . syntax ( "g" )) val groupMember = sql """ select ${m.result.*}, ${g.result.*} from ${GroupMember.as(m)} left join ${Group.as(g)} on ${m.groupId} = ${g.id} where ${m.id} = ${id} """ . map ( GroupMember ( m , g )). single . apply ()

You can re-write this SQLInterpolation code by using QueryDSL as follow.

We believe this code is much simpler and quite understandable.

val id = 123 val ( m , g ) = ( GroupMember . syntax ( "m" ), Group . syntax ( "g" )) val groupMember = withSQL { select . from ( GroupMember as m ). leftJoin ( Group as g ). on ( m . groupId , g . id ) . where . eq ( m . id , id ) }. map ( GroupMember ( m . resultName , g . resultName )). single . apply ()

More examples are

val m = Member . syntax ( "m" ) val ids : List [ Long ] = sql "select ${m.result.id} from ${Member.as(m)} where ${m.groupId} = 1" val members = sql "select ${m.result.*} from ${Member.as(m)}" . map ( Member ( m )). list . apply ()

will be like this:

val m = Member . syntax ( "m" ) val ids : List [ Long ] = withSQL { select ( m . result . id ). from ( Member as m ). where . eq ( m . groupId , 1 ) } . map ( rs => rs . long ( m . resultName . id )). list . apply () val members = withSQL { select . from ( Member as m ) }. map ( Member ( m . resultName )). list . apply () // or select.all(m).from(Member as m)

QueryDSL Reference

FYI: You can find some example in QueryDSL’s test code:

scalikejdbc-interpolation/src/test/scala/scalikejdbc/QueryInterfaceSpec.scala

sqls is alias for SQLSyntax object. Methods that are defined on object SQLSyntax is available everywhere.

scalikejdbc-core/src/main/scala/scalikejdbc/interpolation/SQLSyntax.scala

And/Or conditions

val o = Order . syntax ( "o" ) val orders = withSQL { select . from ( Order as o ) . where . eq ( o . productId , 123 ) . and . isNotNull ( o . orderedAt ) }. map ( Order ( o )). list . apply () // select o.id as i_on_o, o.customer_id ci_on_o, o.product_id as pi_on_o, o.ordered_at as oa_on_o // from orders o where o.product_id = ? and o.ordered_at is not null

val orders = withSQL { select ( o . result . id ). from ( Order as o ) . where . eq ( o . productId , 123 ) . or . isNull ( o . customerId ) }. map ( Order ( o )). list . apply () // select o.id as i_on_o from orders o where o.product_id = ? and o.customer_id is null

Adding round bracket is like this:

val ids = withSQL { select ( o . result . id ). from ( Order as o ) . where . isNotNull ( o . accountId ) . and . withRoundBracket { _ . eq ( o . productId , 1 ). or . eq ( o . accountId , 2 ) } }. map ( _ . int ( 1 )). list . apply () // select o.id as i_on_o from orders o // where o.account_id is not null // and (o.product_id = ? or o.account_id = ?)

or simply using #append :

select(o.result.id).from(Order as o).where.isNotNull(o.accountId) .and.append(sqls"(${o.productId} = ${pid} or ${o.accountId} = ${aid})")

Join queries

val orders : List [ Order ] = withSQL { select . from ( Order as o ) . innerJoin ( Product as p ). on ( o . productId , p . id ) . leftJoin ( Account as a ). on ( o . accountId , a . id ) . where . eq ( o . productId , 123 ) . orderBy ( o . id ). desc . limit ( 4 ) . offset ( 0 ) }. map ( Order ( o , p , a )). list . apply () // select o.*, p.*, a.* from orders o inner join products p on o.product_id = p.id // left join accounts a on o.account_id = a.id // where o.product_id = ? order by o.id desc limit ? offset ?

.on(o.productId, p.id) is simply converted to sqls"${o.productId} = ${p.id}" . If you need to write more complex condition to join tables, use sqls directly.

. innerJoin ( Product as p ). on ( sqls "${o.proudctId} = ${p.id} and ${o.deletedAt} is not null" ) // inner join products p on o.product_id = p.id and o.deleted_at is not null

Dynamic query building

def findOrder ( id : Long , accountRequired : Boolean ) = withSQL { select . from [ Order ]( Order as o ) . innerJoin ( Product as p ). on ( o . productId , p . id ) . map { sql => if ( accountRequired ) sql . leftJoin ( Account as a ). on ( o . accountId , a . id ) else sql }. where . eq ( o . id , 13 ) }. map { rs => if ( accountRequired ) Order ( o , p , a )( rs ) else Order ( o , p )( rs ) }. single . apply () // or def findOrder ( id : Long , accountRequired : Boolean ) = withSQL { select . from [ Order ]( Order as o ) . innerJoin ( Product as p ). on ( o . productId , p . id ) . leftJoin ( if ( accountRequired ) Some ( Account as a ) else None ). on ( o . accountId , a . id ) . where . eq ( o . id , 13 ) }. map { rs => if ( accountRequired ) Order ( o , p , a )( rs ) else Order ( o , p )( rs ) }. single . apply () // select o.*, p.*(, a.*) from orders o inner join products p on o.product_id = p.id // (left join accounts a on o.account_id = a.id) // where o.id = ?

If you just need to append optional conditions, toAndConditionOpt , and toOrConditionOpt are useful.

val ( productId , accountId ) = ( Some ( 1 ), None ) val ids = withSQL { select ( o . result . id ). from ( Order as o ) . where ( sqls . toAndConditionOpt ( productId . map ( id => sqls . eq ( o . productId , id )), accountId . map ( id => sqls . eq ( o . accountId , id )) )) . orderBy ( o . id ) }. map ( _ . int ( 1 )). list . apply () // select o.id as i_on_o from orders o // where o.product_id = ? (and o.account_id = ?) // order by o.id // productId: Some, accountId: None -> where o.product_id = ? // productId: None, accountId: Some -> where o.account_id = ?

in clause

val inClauseResults = withSQL { select . from ( Order as o ). where . in ( o . id , Seq ( 1 , 2 , 3 )) }. map ( Order ( o )). list . apply () // select * from orders o where o.id in (?, ?, ?)

exists/notExists clause

withSQL { select ( a . id ). from ( Account as a ) . where . exists ( select . from ( Order as o ). where . eq ( o . accountId , a . id )) }. map ( _ . int ( 1 )). list . apply () // select a.id from accounts a // where exists (select * from orders o where o.account_id = a.id)

It’s also possible to pass sqls values.

withSQL { select ( a . id ). from ( Account as a ) . where . notExists ( sqls "select ${o.id} from ${Order as o} where ${o.accountId} = ${a.id}" ) }. map ( _ . int ( 1 )). list . apply () // select a.id from accounts a // where not exists (select o.id from orders o where o.account_id = a.id)

between

withSQL { select ( o . result . id ). from ( Order as o ). where . between ( o . id , 13 , 22 ) }. map ( _ . int ( 1 )). list . apply () // select o.id as i_on_o from orders o where o.id between ? and ?

distinct count

import sqls. { distinct , count } val productCount = withSQL { select ( count ( distinct ( o . productId ))). from ( Order as o ) }. map ( _ . int ( 1 )). single . apply (). get // select count(distinct o.product_id) from orders o

group by queries

import sqls.count withSQL { select ( o . accountId , count ). from ( Order as o ) . where . isNotNull ( o . accountId ) . groupBy ( o . accountId ) }. map ( rs => ( rs . int ( 1 ), rs . int ( 2 ))). list . apply () // select o.account_id, count(1) from orders o // where o.account_id is not null // group by o.account_id

union, unionAll queries

withSQL { select ( a . id ). from ( Account as a ) . union ( select ( p . id ). from ( Product as p )) //.unionAll(select(p.id).from(Product as p)) }. map ( _ . int ( 1 )). list . apply () // select a.id from accounts a // union // select p.id from products p

Sub-queries

import SQLSyntax. { sum , gt } val x = SubQuery . syntax ( "x" ). include ( o , p ) val preferredClients : List [( Int , Int )] = withSQL { select ( sqls "${x(o).accountId} id" , sqls "${sum(x(p).price)} as amount" ) . from { select . all ( o , p ). from ( Order as o ). innerJoin ( Product as p ). on ( o . productId , p . id ). as ( x ) } . groupBy ( x ( o ). accountId ) . having ( gt ( sum ( x ( p ). price ), 300 )) . orderBy ( sqls "amount" ) }. map ( rs => ( rs . int ( "id" ), rs . int ( "amount" ))). list . apply () // select x.account_id id, sum(x.price) as amount // from (select o.*, p.* from orders o inner join products p on o.product_id = p.id) x // group by x.account_id // having sum(x.price) > ? // order by amount

Insert

import java.time.ZonedDateTime withSQL { insert . into ( Member ). values ( 1 , "Alice" , ZonedDateTime . now ) }. update . apply () // insert into members values (?, ?, ?) withSQL { val m = Member . column insert . into ( Member ). namedValues ( m . id -> 1 , m . name -> "Alice" , m . createdAt -> ZonedDateTime . now ) }. update . apply () // insert into members (id, name, created_at) values (?, ?, ?)

Or applyUpdate is much simpler. But in some cases, applyUpdate causes compilation errors since Scala 2.10.1. This is not an issue of ScalikeJDBC. If you suffered it, use withSQL { }.update.apply() instead.

import java.time.ZonedDateTime applyUpdate { insert . into ( Member ). values ( 2 , "Bob" , ZonedDateTime . now ) } // insert into members values (?, ?, ?) val c = Member . column applyUpdate { insert . into ( Member ). columns ( c . id , c . name , c . createdAt ). values ( 2 , "Bob" , ZonedDateTime . now ) } // insert into members (id, name, created_at) values (?, ?, ?)

Insert select

case class Product ( id : Int , name : Option [ String ], price : Int ) object Product extends SQLSyntaxSupport [ Product ] case class LegacyProduct ( id : Option [ Int ], name : Option [ String ], price : Int ) object LegacyProduct extends SQLSyntaxSupport [ LegacyProduct ] val lp = LegacyProduct . syntax ( "lp" ) withSQL { insert . into ( Product ). select ( _ . from ( LegacyProduct as lp ). where . isNotNull ( lp . id )) }. update . apply () // insert into products select lp.* from legacy_products lp where lp.id is not null

Delete

withSQL { delete . from ( Member ). where . eq ( Member . column . id , 123 ) }. update . apply () // delete from members where id = ?

import java.time.ZonedDateTime withSQL { update ( Member ). set ( Member . column . name -> "Chris" , Member . column . updatedAt -> ZonedDateTime . now ). where . eq ( Member . column . id , 2 ) }. update . apply () // update members set name = ?, updated_at = ? // where id = ?

Avoiding Method Name Conflict

For example, if your code already uses select , insert or update as method name, name confliction occurs.

def insert ( e : Member )( implicit session : DBSession ) { withSQL { insert // compilation error . into ( Member ). values ( e . id , e . name ) }. update . apply () }

Use QueryDSL prefix or insertInto , deleteFrom .

def insert ( e : Member )( implicit session : DBSession ) { withSQL { QueryDSL . insert . into ( Member ). values ( e . id , e . name ) }. update . apply () } def insert ( e : Member )( implicit session : DBSession ) { withSQL { insertInto ( Member ). values ( e . id , e . name ) }. update . apply () } def delete ( id : Long )( implicit session : DBSession ) { withSQL { deleteFrom ( Member ). where . eq ( Member . column . id , id ) }. update . apply () }