记录下一个简单的scala sql DSL

记录下一个简单的Scala sql DSL

一个日本人写的DSL 原网址:https://github.com/yuroyoro/Squire

总共两个类,基于twitter的querulous框架

package com.yuroyoro.squire
import java.sql.ResultSet
import scala.collection.mutable.ArrayBuffer
import com.twitter.querulous.evaluator.QueryEvaluator
trait SqlCommand {
val head:Option[SqlCommand]
val command:String
def arg:String
val pms = new ArrayBuffer[Any]
def constract = command format arg
def query:String = head match {
case None => constract
case Some(cmd) => cmd.query + " " + constract
}
def params:ArrayBuffer[Any] = head match{
case None => pms
case Some(cmd) => cmd.params ++ pms
}
override def toString = query
}
trait Selectable extends SqlCommand{
def apply[A](f: ResultSet => A)( implicit qe:QueryEvaluator ):Seq[A] = select( f )( qe )
def select[A](f: ResultSet => A)( implicit qe:QueryEvaluator ): Seq[A] =
qe.select( query , params: _* )(f)
def selectOne[A](f: ResultSet => A)( implicit qe:QueryEvaluator ): Option[A] =
qe.selectOne( query , params: _* )(f)
def count( implicit qe:QueryEvaluator ): Int =
qe.count( query , params: _* )
}
trait Executable extends SqlCommand{
def apply( implicit qe:QueryEvaluator ): Int = execute( qe )
def execute( implicit qe:QueryEvaluator ): Int = qe.execute( query ,  params: _* )
}
trait SelectConditionale extends Selectable {
def where( cond:Condition ) = new Where( this , cond ) with Selectable with Groupable with Sortable
}
trait ExecuteConditionale extends Executable {
def where( cond:Condition ) = new Where( this , cond ) with Executable
}
trait Groupable extends Selectable{
def group( cond:String ) = new Group( this, cond ) with Sortable
}
trait Sortable extends Selectable {
def order( cond:String ) = new Order( this, cond ) with Selectable
}
case class By( columns:String ) {
def asc = columns + " ASC"
def desc = columns + " DESC"
}
class Select ( val arg:String ) extends SqlCommand {
val head = None
val command = "SELECT %s"
def from( table:String ) = new From( this , table ) with SelectConditionale
}
class From( val h:SqlCommand, val arg:String ) extends SqlCommand {
val head = Some(h)
val command = "FROM %s"
}
class Where( val h:SqlCommand, cond:Condition ) extends SqlCommand{
val head = Some(h)
def arg = cond.condition
pms ++= cond.params
val command = "WHERE %s"
}
class Group( val h:SqlCommand, val arg:String ) extends SqlCommand {
val head = Some(h)
val command = "GROUP BY %s"
}
class Order( val h:SqlCommand, val arg:String ) extends SqlCommand {
val head = Some(h)
val command = "ORDER BY %s"
}
class Insert extends SqlCommand{
val head = None
def arg:String = ""
val command = "INSERT"
def into( table:String ) = new Into( this, table ) with SelectConditionale
def values( values:Any* ) = new Values( this , values: _* ) with Executable
}
class Into( val h:SqlCommand, table:String ) extends SqlCommand{
val head = Some(h)
val command = "INTO %s"
var cols:String = ""
override def arg = table + " " + cols
def apply( columns:String* ) = {
cols = columns.mkString( "(", ",", ")" )
this
}
def values( values:Any* ) = new Values( this , values: _* ) with Executable
}
class Values( val h:SqlCommand, values:Any* ) extends SqlCommand {
val head = Some(h)
val command = "VALUES %s"
pms ++= values
override def arg = Array.make( values.size, "?").mkString( "(", ",", ")" )
}
class Update( val arg:String ) extends SqlCommand{
val head = None
val command = "UPDATE %s"
def set( sets:SetVal* ) = new SetValues( this, sets: _* ) with ExecuteConditionale
}
class SetValues( val h:SqlCommand, sets:SetVal* ) extends SqlCommand {
val head = Some(h)
val command = "SET %s"
var cols:String = ""
pms ++= sets.map{ _.param }
override def arg = sets.mkString(",")
}
case class SetVal( column:String ){
var param:Any = null
def =/( v:Any ) = param = v
override def toString = "%s = ?".format( column )
}

condition类

package com.yuroyoro.squire
import scala.collection.mutable.ArrayBuffer
sealed trait Condition{
val params = new ArrayBuffer[Any]
def condition:String
def and( left:Condition ) = And( this, left )
def or( left:Condition ) = Or( this, left )
}
case class Not ( val cond:Condition ) extends Condition{
params ++= cond.params
override def condition = "NOT " + cond.condition
}
case class And( val right:Condition,val left:Condition ) extends Condition{
params ++= right.params
params ++= left.params
override def condition = right.condition + " AND " + left.condition
}
case class Or( val right:Condition, val left:Condition ) extends Condition{
params ++= right.params
params ++= left.params
override def condition = right.condition + " OR " + left.condition
}
sealed trait Criteria extends Condition{
val col:String
val op:String
val paramPrefix = ""
val paramSuffix = ""
val delimitor = ","
def addParam( v:Any ) = {
params += v
this
}
override def condition =
"%s %s %s %s %s".format( col, op,
Array.make( params.size, "?").mkString( paramPrefix,  delimitor,  paramSuffix) )
override def toString = "%s %s %s ".format( col, op, params.mkString( paramPrefix, delimitor, paramSuffix) )
}
case class Eq(col:String) extends Criteria{
val op = "="
def =/( v:Any ) = addParam( v )
}
case class NotEq(col:String) extends Criteria{
val op = "<>"
def <>( v:Any ) = addParam( v )
}
case class Lt(col:String) extends Criteria{
val op = "<"
def <( v:Any ) = addParam( v )
}
case class Le(col:String) extends Criteria{
val op = "<="
def <=( v:Any ) = addParam( v )
}
case class Gt(col:String) extends Criteria{
val op = ">"
def >( v:Any ) = addParam( v )
}
case class Ge(col:String) extends Criteria{
val op = ">="
def >=( v:Any ) = addParam( v )
}
case class Is( col:String ) extends Criteria{
val op = "IS"
def is( v:Null ) = addParam( "null" )
}
case class Like( col:String ) extends Criteria{
val op = "LIKE"
def like( v:Any ) = addParam( v )
}
case class In( col:String) extends Criteria{
val op = "IN"
override val paramPrefix = "("
override val paramSuffix = ")"
def in( v:Any* ) = {
params ++= v
this
}
}
object Implicits {
val * = "*"
def select = new Select("*")
def select( columns:String* ) = new Select( columns.mkString(",") )
def not( op:Criteria ) = Not( op )
def by( columns:String*) = columns.mkString(",")
implicit def string2by( columns:String) = By( columns )
implicit def string2Eq( col:String ) = Eq( col )
implicit def string2NotEq( col:String ) = NotEq( col )
implicit def string2Lt( col:String ) = Lt( col )
implicit def string2Le( col:String ) = Le( col )
implicit def string2Gt( col:String ) = Gt( col )
implicit def string2Ge( col:String ) = Ge( col )
implicit def string2Is( col:String ) = Is( col )
implicit def string2Like( col:String ) = Like( col )
implicit def string2In( col:String ) = In( col )
implicit def string2SetVal( col:String ) = SetVal( col )
}

使用方法：

在需要用到的地方import 下 Implicits._

implicit val queryEvaluator = QueryEvaluator("localhost:3306/dd", "root", "123456")
var ct=select("count(*)").from("scm_sale_order_entity").count
println(ct)
select(*).from("scm_sale_order_entity soe")
.where( " is_invalid " =? 2 and " soe.sale_api_id" =? 5 ){row => println(row.getString("order_no"))}