いや、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: