Java で クエリ(SELECT) を 安全に改変したい。jOOQ 編

Java で クエリ(SELECT) を 安全に改変したい。Doma 編 - お だ のスペース の続き

別のライブラリ jOOQ というのを試してみました。
jOOQ: The easiest way to write SQL in Java
jOOQ は初めて触りましたが、中々面白そうな感じでした。
SQL は直書きたい派なので、そんなに使うことは無さそうですが。

試した理由は、jOOQ のドキュメントに SQL 2 jOOQ というのがあったからです。
SQL 2 jOOQ Parser

Java の SQL パーサーは幾つか見つけたのですが、そこから組み立てる方が中々見つからず。
そんななか SQL 2 jOOQ とか書いてたのできたこれ！って感じです。

ドキュメント読むと分かるのですが、jOOQ 単体では出来ません。
sql2jooq という別ライブラリも必要です。
sqlparser/sql2jooq · GitHub
さらに sql2jooq が GENERAL SQL PARSER に依存しているという結構ややこしい感じです。
SQL Parse, Analyze, Transform and Format All In One

jOOQ は Maven Central に上がっているのですが、上がっているのは OPEN SOURCE DB 版で全ての機能が使えませんでした。
http://www.jooq.org/download/:title)

また sql2jooq は jar 形式では上がってないので、github から clone して自前でビルドします。
自分の環境では、
sql2jooq/pom.xml at master · sqlparser/sql2jooq · GitHub
の 69行目から254行目の mysql とか posgre 等々の箇所を取っ払わないと jar 作れませんでした。
GENERAL SQL PARSER も Download Free trial SQL Parser からトライアル版を落としました。

ここまでで結構めんどくさい！

で sql2jooq を試すとショックな事が！
sql2jooq は SQL を jOOQ で使えるソースコードに落とし込んでくれるツールのようです。
jOOQ の Object にしてくれるわけでは無いので、今回の用途では使えません。。

今回のコードは Xtend を使っていますが、Java な人なら伝わりますよね？
Xtend - Modernized Java

package sample

import gudusoft.gsqlparser.EDbVendor
import gudusoft.gsqlparser.sql2jooq.SQL2jOOQ
import org.jooq.Query
import org.jooq.SQLDialect
import org.jooq.SelectConditionStep
import org.jooq.conf.ParamType
import org.jooq.impl.DSL
import org.junit.Test

class JOOPTest {
  @Test
  def void sql2jooq() {
    val sql = '''
select
  id
  , name
  , hoge
from
  table_1
where
  id = ?
  and name = ?
order by 
  name desc
'''
    val convert = new SQL2jOOQ(EDbVendor.dbvoracle, sql)
    convert.convert
    if (convert.errorMessage != null) {
      println(convert.errorMessage)
      return
    }
    
    println(convert.convertResult) // Stringで返ってくる！！！
}

これがこんな文字列を出力しました。

DSLContext create = DSL.using(conn, SQLDialect.MYSQL);

Result result = create.select( ((Field)Table_1.TABLE_1.ID), ((Field)Table_1.TABLE_1.NAME), ((Field)Table_1.TABLE_1.HOGE) )
    .from( Table_1.TABLE_1 )
    .where( ((Field)Table_1.TABLE_1.ID).equal( ((Field)Table_1.TABLE_1.?) ).and( ((Field)Table_1.TABLE_1.NAME).equal( ((Field)Table_1.TABLE_1.?) ) ) )
    .orderBy( ((Field)Table_1.TABLE_1.NAME).desc( ) ).fetch( );

jOOQ のオブジェクトで返ってこないと改変出来ません。。

jOOQ を試すついでに jOOQ から クエリを吐いてみました。

val context = DSL.using(SQLDialect.MYSQL)

val object = context.select(
  DSL.field("table_1.id")
  , DSL.field("table_1.name")
  , DSL.field("table_1.hoge")
)
.from(DSL.table("table_1"))
.where(
  DSL.field("table_1.id").equal(1)
    .and(DSL.field("table_1.name").equal("aaa"))
)
.orderBy(DSL.field("table_1.name").desc);

// 後から条件を追加してみる
(object as SelectConditionStep)
  .and(DSL.field("table_1.additonalColumn").equal(5))
        
println(object.getSQL(ParamType.INDEXED))

これがこんな文字列を返します。

select table_1.id, table_1.name, table_1.hoge from table_1 where (table_1.id = ? and table_1.name = ? and table_1.additonalColumn = ?) order by table_1.name desc

これなら使えそうな感じですね。
sql2jooq が jOOQ オブジェクトを返せば何とかなりそうです。

というわけで、元のソースを真似しながら適当に書いてみました。
実装している途中で Maven で落とした jOOQ が join をサポートしていないという事に気付き、途中から投げやりな感じになりました。。

package sample

import gudusoft.gsqlparser.EDbVendor
import gudusoft.gsqlparser.TCustomSqlStatement
import gudusoft.gsqlparser.TGSqlParser
import gudusoft.gsqlparser.nodes.TTable
import gudusoft.gsqlparser.stmt.TSelectSqlStatement
import java.util.Objects
import org.eclipse.xtend.lib.annotations.Accessors
import org.jooq.DSLContext
import org.jooq.Record
import org.jooq.SelectGroupByStep
import org.jooq.SelectJoinStep
import org.jooq.SelectOrderByStep
import org.jooq.SelectSelectStep
import org.jooq.impl.DSL

class SQL2JOOQObject {
  TGSqlParser sqlparser
  @Accessors(PUBLIC_GETTER) String errorMessage
  @Accessors(PUBLIC_GETTER) Object convertedResult
  
  new(EDbVendor vendor, String sqlText) {
    sqlparser = new TGSqlParser(vendor) => [
      sqltext = sqlText
    ]   
  }
  
  def convert(DSLContext context) {
    convertedResult = null
    if (sqlparser.parse != 0) {
      errorMessage = sqlparser.errormessage
      return
    }
    
    val stmt = sqlparser.sqlstatements.get(0)
    convertStmt(context, stmt)
  }
  
  private def dispatch convertStmt(DSLContext context, TSelectSqlStatement stmt) {
    if (stmt.isCombinedQuery) {
      throw new UnsupportedOperationException("not support combined query")
    }
    
    var select = context.select()
    convertedResult = select.generateSelect(stmt)
      .generateFrom(stmt)
      .generateWhere(stmt)
      .generateGroupBy(stmt)
      .generateOrderBy(stmt)
  }
  private def generateSelect(SelectSelectStep<Record> select, TSelectSqlStatement stmt) {
    val collist = stmt.resultColumnList
    Objects.requireNonNull(collist)
    var result = select
    for (var i = 0; i < collist.size; i++) {
      val col = collist.getResultColumn(i)
      val str = col.expr.toString
      switch str {
        case str.indexOf('*') != -1,
        case str.trim.length == 1: {}
        default: result = result.select(DSL.field(str))
      }
    }
    result
  }
  private def generateFrom(SelectSelectStep<Record> select, TSelectSqlStatement stmt) {
    val joins = stmt.joins
    Objects.requireNonNull(joins)
    if (joins.size == 0) throw new UnsupportedOperationException("not exists from")
    if (joins.size > 1) throw new UnsupportedOperationException("not support join")
    val join = joins.getJoin(0)
    if (join.joinItems != null && join.joinItems.size > 0) throw new UnsupportedOperationException("not support join")
    
    select.from(join.table.convertJOOQTable)
    
    /* join サポートしてない！！
    for (var i = 0; i < joins.size; i++) {
      val join = joins.getJoin(i)
      var table = join.table.convertJOOQTable
      val joinItems = join.joinItems
      if (joinItems != null && joinItems.size > 0) {
        for (var j = 0; j < joinItems.size; j++) {
          val joinItem = joinItems.getJoinItem(j)
          switch joinItem.joinType {
            case EJoinType.leftouter, 
            case EJoinType.left: {
              table.leftOuterJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.rightouter,
            case EJoinType.right: {
              table.rightOuterJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.natural, 
            case EJoinType.natural_inner: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.natural_leftouter,
            case EJoinType.natural_left: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.natural_rightouter, 
            case EJoinType.natural_right: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.cross: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
            case EJoinType.fullouter, 
            case EJoinType.full: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
            default: {
              table.naturalJoin(joinItem.table.convertJOOQTable)
            }
          }
        }
      }
    } */
  }
  private def generateWhere(SelectJoinStep<Record> from, TSelectSqlStatement stmt) {
    if (stmt.whereClause == null) return from
    
    val expression = stmt.whereClause.condition
    from.where(expression.toString)
  }
  private def generateGroupBy(SelectGroupByStep<Record> groupby, TSelectSqlStatement stmt) {
    val groupByClause = stmt.groupByClause
    if (groupByClause == null) return groupby
    
    val groupItems = groupByClause.items
    val groupfields = newArrayList()
    for (var i = 0; i < groupItems.size; i++) {
      groupfields.add(DSL.field(groupItems.getGroupByItem(i).expr.toString))
    }
    var result = if (groupByClause.isRollupModifier) {
      groupby.groupBy(DSL.rollup(groupfields))
    } else {
      groupby.groupBy(groupfields)
    }
    
    if (groupByClause.havingClause != null) {
      result.having(groupByClause.havingClause.toString)
    } else {
      result
    }
  }
  private def generateOrderBy(SelectOrderByStep<Record> orderby, TSelectSqlStatement stmt) {
    val orderbyClause = stmt.orderbyClause
    if (orderbyClause == null) return orderby
    
    val fields = newArrayList
    val items = orderbyClause.items
    for (var i = 0; i < items.size; i++) {
      val item = items.getOrderByItem(i)
      val field = DSL.field(item.sortKey.toString)
      fields.add(switch item.sortType {
        case 1: field.asc
        case 2: field.desc
        default: field.asc
      })
    }
    
    orderby.orderBy(fields)
  }
  private def convertJOOQTable(TTable table) {
    val tabstr = table.name
    if (tabstr != null && tabstr.equalsIgnoreCase("DUAL")) {
        DSL.dual
      } else {
        DSL.table(tabstr)
      }
  }
  private def dispatch convertStmt(DSLContext context, TCustomSqlStatement stmt) {
    throw new UnsupportedOperationException("support select only")
  }
}

でこれを使うと…

  @Test
  def void obj() {
    val sql = '''
select
  id
  , name
  , hoge
from
  table_1
where
  id = ?
  and name = ?
order by 
  name desc
  , age
'''
    val convert = new SQL2JOOQObject(EDbVendor.dbvoracle, sql)
    convert.convert(DSL.using(SQLDialect.MYSQL))
    if (convert.errorMessage != null) {
      println(convert.errorMessage)
      return
    }
    
    val object = convert.getConvertedResult()
    (object as SelectConditionStep).and(DSL.field("table_1.additonalColumn").equal(5))
    
    println((object as Query).getSQL(ParamType.INDEXED))
  }

こんな感じになりました。

select id, name, hoge from table_1 where ((id = ?
  and name = ?) and table_1.additonalColumn = ?) order by name desc, age asc

なんかいけそうですね！
あとは sql2jooq が jOOQ オブジェクトを返すようになってくれれば…という感じでしょうか。

という訳で、jOOQ を使ったやり方も、まだ実環境では試しにくいなぁという感じです。

やっぱ公式から出てる SQL Server ScriptDom サイコー！