Java
MyBatis

MyBatis で生SQLを叩きたい

More than 1 year has passed since last update.

いや、MyBatis はいつだってネイティブ SQL を使えることが売りなのだが、そういうことではなくて、単純に Java コードから SQL 文を DB に投げたい。
MyBatis の API には当然そのためのインターフェースもありそうなものだが、なぜか見当たらない。
結局、定義した Mapper を介さなければならない決まりのようだ。

デバッグやテストでちょっとした SQL 文を投げたいことはよくあるのだが、そのために interface や XML をわざわざ仕込むのは気が進まないし、そもそも許されない環境もある。

妥協案として、SQL文を注入できる汎用 Mapper を一つ用意する。

public interface DbToolMapper {
    @Select("${sql}")
    List<Map<String, Object>> query(@Param("sql") String sql);
}

query() の引数に渡した文字列は丸ごと変数展開され、SQL として DB に渡される。
ちょっとミもフタもないが、これを session にねじ込めば任意の SQL を動的に実行し、結果行を Map の List で取得できる。

    // 既存の SqlSession の大元の Configuration に DbToolMapper を追加する。
    session.getConfiguration().addMapper(DbToolMapper.class);
    DbToolMapper db = session.getMapper(DbToolMapper.class);

    List<Map<String, Object>> rset = db.query("select * from COMMENT where id = 1");

    System.out.println(rset);

上記 Mapper interface は public である必要もなく、使いたい既存クラスファイル内に(インナークラスでも可)コピー&ペーストして一時的に付け足すだけで使え、他のコードへの影響も少ない。

ただし、Mapper の削除はできないので、本当にテストやデバッグ時の使用に限定してほしい。

プリペアドステートメントとしてパラメータ(#{param})を渡すようにもできる。

public interface DbToolMapper {
    ...

    @Select("${sql}")
    Map<String, Object> find(@Param("sql") String sql, @Param("id") Integer id);

    @Select("${sql}")
    List<Map<String, Object>> list(@Param("sql")  String sql, @Param("params") Map<String, Object> params);

}
    // パラメータ名で参照
    Map<String, Object> row = db.find("select * from COMMENT where id = #{id}", 1);
    //==>  Preparing: select * from COMMENT where id = ? 
    //==> Parameters: 1(Integer)

    // Map は #{map.key} 形式で参照、配列やListは ${arr[n]} 形式で参照
    String sql = "select * from COMMENT where blog_id = #{params.blog_id} and created_at > #{params.since}";
    Map<String, Object> params = new HashMap<>();
    params.put("blog_id", 1);
    params.put("since", LocalDate.now().minusWeeks(1).atStartOfDay());

    List<Map<String, Object>> rset = db.list(sql, params);
    //==>  Preparing: select * from COMMENT where blog_id = ? and created_at > ? 
    //==> Parameters: 1(Integer), 2016-02-04T00:00(LocalDateTime)

基本的にこれで更新系も含めて一応どんな SQL も叩けるはずだ。

ただちょっとクエリが複雑になると手作業で SQL を構築するのはもう面倒くさい。
残念ながらこのやりかたでは動的に SQL 構築すための XML タグ(<foreach ... >など)は使用できない。

MyBatis の SQL プロバイダアノーテーションや SQL ビルダークラスを駆使すれば、決まり切ったものだけは少しは楽することができる。

public interface DbToolMapper {
    ...

    /** COUNT */    
    @Select("select count(*) from (${sql}) as t") @Options(useCache=false)
    Long count(@Param("sql") String sql);

    /** DELETE */
    @Delete("delete from ${table} where id = #{id}")
    public int delete(@Param("table") String table, @Param("id") Integer id);

    /**
     * INSERT
     *  
     * @param table テーブル名
     * @param data  挿入データ組(可変長)。カラム名と値を交互に指定する。
     * @return      
     */
    @InsertProvider(type=EasyInsertProvider.class, method="toInsert")
    public int insert(@Param("table") String table, @Param("data") Object... data);

    static class EasyInsertProvider extends SQL {
        public String toInsert(Map<String, Object> params) {
            INSERT_INTO((String)params.get("table"));
            Object[] data = (Object[]) params.get("data");
            for (int i=0; i<data.length -1; i+=2) {
                VALUES((String)data[i], "#{data[" + (i+1) + "]}");
            }
            return toString();
        }
    }

    /**
     * UPDATE 
     * 
     * @param table テーブル名
     * @param set   更新データ組(可変長)。カラム名と値を交互に指定する。
     *              最初の組は更新レコードのIDとみなす。
     * @return      
     */
    @UpdateProvider(type=EasyUpdateProvider.class, method="toUpdate")
    public int update(@Param("table") String table, @Param("set") Object... set);

    static class EasyUpdateProvider extends SQL {
        public String toUpdate(Map<String, Object> params) {
            UPDATE((String)params.get("table"));
            Object[] set = (Object[]) params.get("set");
            WHERE("" + set[0] + " = #{set[1]}"); // 最初のカラムはIDとみなす
            for (int i=2; i<set.length -1; i+=2) {
                SET(String.format("%s = #{set[%d]}", set[i], i+1));
            }
            return toString();
        }
    }
    Long count = db.count("select * from COMMENT");
    //==>  Preparing: select count(*) from (select * from hoge) as t 
    //==> Parameters: 

    db.delete("COMMENT", 1);
    //==>  Preparing: delete from COMMENT where id = ? 
    //==> Parameters: 1(Integer)

    for (int i=1; i<=1000; i++) {
        db.insert("COMMENT", "blog_id", 1, "author", "ac_" + i, "content", "test " + i, "point", i);
    }
    //==>  Preparing: INSERT INTO COMMENT (blog_id, author, content, point) VALUES (?, ?, ?, ?) 
    //==> Parameters: 1(Integer), ac_1000(String), test 1000(String), 1000(Integer)

    db.update("COMMENT", "id", 1, "point", 1000);
    //==>  Preparing: UPDATE COMMENT SET point = ? WHERE (id = ?) 
    //==> Parameters: 1000(Integer), 1(Integer)

SQL プロバイダに関数を渡せればラムダ式などで自由に SQL を組めるようになるが、それはもう完全に趣旨が違う。

public interface DbToolMapper {
    ...

    @SelectProvider(type=EasySelectProvider.class, method="fromFunciton")
    public List<Map<String, Object>> queryFunc(@Param("func") Function<Map<String, Object>, String> func,
            @Param("params") Map<String, Object> params);

    static class EasySelectProvider {
        @SuppressWarnings("unchecked")
        public String fromFunciton(Map<String, Object> args) {
            Function<Map<String, Object>, String> func = (Function<Map<String, Object>, String>) args.get("func");
            Map<String, Object> params = (Map<String, Object>) args.get("params");
            return func.apply(params);
        }
    }
    // 条件置換
    Map<String, Object> params = new LinkedHashMap<>();
    params.put("blog_id", 1);
    params.put("title", null);

    List<Map<String, Object>> rset = db.queryFunc(p -> {
        return new SQL() {{
            SELECT("*");
            FROM("COMMENT");
            p.forEach((col, val) -> {
                if (val == null) {
                    WHERE( col + " IS NULL");
                } else {
                    WHERE( col + " = #{params." + col + "}");
                }
            });
        }}.toString();
    }, params);
    //==>  Preparing: SELECT * FROM COMMENT WHERE (blog_id = ? AND title IS NULL) 
    //==> Parameters: 1(Integer)


    // IN句展開
    Integer[] ids = {2,4,6,1};
    System.out.println(Arrays.toString(ids));
    String idList = Arrays.toString(ids).replaceAll("^\\[|]$", "");

    List<Map<String, Object>> results = db.queryFunc(p -> {
        return new SQL()
                .SELECT("*").FROM("PRODUCT")
                .WHERE( "id IN (" + idList + ")").ORDER_BY("FIELD(id, " + idList  +")")
                .toString();
    }, null);
    //==>  Preparing: SELECT * FROM PRODUCT WHERE (id IN (2, 4, 6, 1)) ORDER BY FIELD(id, 2, 4, 6, 1) 
    //==> Parameters: