読者です 読者をやめる 読者になる 読者になる

Doma で sql2o ぽい SELECT を発行するヘルパークラス

Sql2o - Easy database query library - sql2o というのを知りました。
で トップページを見てこれ位なら Doma でも出来るんじゃね?
と思い勢いで実装してみました。

コードを紹介する前に、Doma での動的な SQL を生成する機能と sql2o を比較しながら紹介します。

Doma のクエリビルダ

クエリビルダ — Doma 2.0 ドキュメント
元々 Doma には クエリビルダ があるので、SQL ファイルを利用出来ない動的なクエリも Doma で実装する事が可能。

ドキュメントでは、適度な間隔で builder.sql が呼ばれていますが、パラメータが出てこなければ、builder#sql は1回の呼び出しでもOK。

SelectBuilder builder = SelectBuilder.newInstance(config);
builder.sql("select id, name, salary from Emp order by name");
List<Emp> result = builder.getEntityResultList(Emp.class);

しかしパラメータを使う場合はこれが出来ません。

SelectBuilder builder = SelectBuilder.newInstance(config);
builder.sql("select id, name, salary from Emp where name like ")
  .param(String.class, "S%")
  .sql("and age > ")
  .param(int.class, 20);
Emp emp = builder.getEntitySingleResult(Emp.class);

パラメータが出てくると単なる文字列を渡すという方法が取れないのが難点。

sql2o を使った SELECT の発行

sql2o だとこんな感じ。

Sql2o sql2o = new Sql2o(dburl, user, pass);
try (Connection con = sql2o.open()) {
  List<Emp> result = con.createQuery(
    "select id, name, salary from Emp where name like :name and age > :age")
    .addParameter("name", "S%")
    .addParameter("age", 20)
    .executeAndFetch(Emp.class); // 1件検索なら、executeScalar(Emp.class) 
  ...
}

この中で出てくるクエリ

select id, name, salary from Emp where name like :name and age > :age

は、バインド変数に対応している SQL IDE では 2way SQL として動作する。
これはそこそこ便利な感じ。

この例なら DomaSQL ファイルを使った検索を使えばいい。

Doma クエリビルダの欠点

2way SQL 云々は個人的にはどうでもよくて、1個だけ欠点があります。
上でも述べたとおり、パラメータを含んだ文字列のケースです。
Doma で有効な SQL を文字列で受け取った際に、SQL 内でパラメータを使っているとクエリビルダ では上手く実行出来ません。

@Dao
public interface EmpDao {
  default List<Emp> findEmp(String domaValidQuery, ConditionBean cond) {
    // builder でクエリを発行したいけど、パラメータが絡むと上手くいかない
  }
}
// 呼び出し側 としてはこんな感じにしたい!
ConditionBean cond = new ConditionBean();
cond.age = 20;
List<Emp> result1 = dao.findEmp(
  "select id, name from Emp where age > /*cond.age*/1 order by name", cond);
cond.name = "aaa";
List<Emp> result2 = dao.findEmp(
  "select id, salary from Emp where name like /*@prefix(cond.name)*/'1' order by id", cond);

こんな感じの使い方はクエリビルダでは出来ません。*1
もちろん、クエリ毎に Dao のメソッドSQLファイルを用意するのが1番良いのですが、クエリが少ししか違わなくて大量にある場合や、クエリが短くて複雑で無い場合には若干面倒な感じもします。

または、ものすごいシンプルなクエリなのにわざわざ Dao/Entity/.sql 作った上で apt もしないといけないのは、人によってはしんどいと思うかもしれません。

Doma クエリビルダ以外の動的な SQL 作成

SQL ファイルでも
SQL — Doma 2.0 ドキュメント - 条件コメント
SQL — Doma 2.0 ドキュメント - 繰り返しコメント
があるので、動的な SQL を組み立てる事も出来ます。

Java 側で作成した動的 SQL を使う場合には、
SQL — Doma 2.0 ドキュメント - 埋め込み変数コメント
もあるので、こっちが利用できるならそれでもいいと思います。

select 
  /*#selectColumnList*/
from Emp
where
  age > /*cond.age*/1
order by
  /*#orderByList*/
@Dao
public interface EmpDao {
  List<Emp> findEmpCustom(String selectColumnList, String orderByList, ConditionBean cond);
}
// 呼び出し側 
ConditionBean cond = new ConditionBean();
cond.age = 20;
List<Emp> result1 = dao.findEmpCustom("id, name, salary", "name, id", cond);
List<Emp> result2 = dao.findEmpCustom("id, name", "name desc, id", cond);

埋め込み変数コメントでは、バインド変数コメントを使えません。埋め込み変数コメント内に自分で値を展開する必要があります。

String whereList = "name like '" + name + "%' and ..."; 

これは正直あまりやりたくない処理なので、埋め込み変数コメントを使う/使わないの切り分けの目安になると思います。
また埋め込み変数コメントに渡す値に以下があると例外を吐いてクエリを実行出来ません。

  • シングルクォテーション
  • セミコロン
  • 行コメント
  • ブロックコメント

つまり、上記の例では、name という変数に 「'」 や 「;」 が入ってると 実行時例外になります。
が、「'」 は普通に名前として入る可能性があります。なのでこの場合埋め込み変数は使えません。

個人的な比較

sql2o 全然触ってないけど、Doma と sql2o の検索を比べた時に、sql2o が良いと思うところは、

  • SQL が十分に短い場合は、実際の SQL が実コードと離れてなくて(同じファイル、出来れば近く)すぐ読める というのが利点
  • 文字列としてクエリを外部から受け取るパターンで、バインド変数を利用している場合。さらに バインド変数の接頭が ":" な DB*2

Commons DBUtils を使ってたケースでは、今後 sql2o に変えれそうな気がする。

sql2o はちゃんと見てないので実はもっと高機能かもしれない。時間が出来たら触ってみようかな。
ただ バインド変数の接頭を ":" にしているので 、SQL Server だと 2waySQL にならないのが欠点。*3

あと公式ページではパフォーマンス良いよと載っていますが自分では測定してません。
aaberg/sql2o · GitHub

でやっと本題

Doma でも sql2o っぽいのは doma/SelectBuilder.java at master · domaframework/doma · GitHub の getEntityResultList を参考にすると作れるので書いてみた。

package domasql2o;

import java.util.ArrayList;
import java.util.List;

import org.seasar.doma.internal.jdbc.command.EntityResultListHandler;
import org.seasar.doma.internal.jdbc.sql.SqlParser;
import org.seasar.doma.jdbc.Config;
import org.seasar.doma.jdbc.SqlLogType;
import org.seasar.doma.jdbc.SqlNode;
import org.seasar.doma.jdbc.command.SelectCommand;
import org.seasar.doma.jdbc.entity.EntityType;
import org.seasar.doma.jdbc.entity.EntityTypeFactory;
import org.seasar.doma.jdbc.query.SqlSelectQuery;

public class DomaSql2oQuery {
  private final Config config;
  private final SqlLogType logType;
  
  private final String query;
  private final List<Parameter<?>> parameters;
  
  public DomaSql2oQuery(Config config, String query) {
    this(config, SqlLogType.FORMATTED, query);
  }
  public DomaSql2oQuery(Config config, SqlLogType logType, String query) {
    // ちゃんとやるなら、AbstractDaoImpl#validateConfig でやってるようなチェックをやる必要あり。
    this.config = config;
    this.logType = logType;
    
    this.query = query;
    this.parameters = new ArrayList<>();
  }
  
  public <T> DomaSql2oQuery addParameter(String name, Class<T> type, T value) {
    parameters.add(new Parameter<T>(name, type, value));
    return this;
  }
  @SuppressWarnings("unchecked")
  public <T> DomaSql2oQuery addParameter(String name, T notNullValue) {
    parameters.add(new Parameter<T>(name, (Class<T>)notNullValue.getClass(), notNullValue));
    return this;
  }
  
  public <T> List<T> executeAndFetch(Class<T> returnEntityType) {
    SqlParser parser = new SqlParser(query);
    SqlNode node = parser.parse();
    
    SqlSelectQuery q = new SqlSelectQuery();
    q.setSqlNode(node);
    q.setConfig(config);
    q.setCallerClassName(this.getClass().getName());
    q.setCallerMethodName("executeAndFetch");
    q.setSqlLogType(logType);
    EntityType<T> entityType = EntityTypeFactory.getEntityType(returnEntityType, config.getClassHelper());
    EntityResultListHandler<T> handler = new EntityResultListHandler<T>(entityType);
    q.setEntityType(entityType);
    
    for (Parameter<?> param : parameters) {
      q.addParameter(param.name, param.type, param.value);
    }
    
    q.prepare();
    SelectCommand<List<T>> command = new SelectCommand<>(q, handler);
    List<T> result = command.execute();
    q.complete();
    
    return result;
  }
  
  static class Parameter<T> {
    public final String name;
    public final Class<T> type;
    public final T value;
    public Parameter(String name, Class<T> type, T value) {
      super();
      this.name = name;
      this.type = type;
      this.value = value;
    }
  }
}

こんなのを用意して

Config config = new DomaConfig();
List<Emp> result = new DomaSql2oQuery(config, 
    "select id, salary from Emp where name like /*@prefix(name)*/'1' and age > /*age*/1 order by id")
  .addParameter("name", "S")
  .addParameter("age", 20)
  .executeAndFetch(Emp.class);

sql2o と比べると doma のバインド変数コメントの都合で少し長くなるがそんな遜色ない気もする。

渡すクエリには、DomaSQL ファイルで使える構文は全部使えるし、パラメータも Bean にすることもできる。*4

コンパイル時にパラメータの渡し忘れとかをチェック出来ないので、Doma の利点は結構失われてると思う。
このやり方だけしかしないなら Doma じゃ無くて良いし、通常通り Doma を使った上で、どうしてもシンプルなクエリがだるい or クエリを SQL ファイルに出来ない理由があるならこれ使うっていうのが良いのかな?

今回は、Entity 型を返すのでこんな実装になったけど、sql2o と同じ様に executeAndFetch で基本型も指定出来るようにするともうちょっと複雑になりそう。

*1:一応バインド変数の名前を SelectBuilder が吐く名前にし、パラメータの型を Domain クラスにすると出来ない事もないが無理やり感がヤバい

*2:Oracle とか

*3:SQL Server の変数は 「@」 始まり

*4:/*cond.id*/1 とか