1. kumazo

    Posted

    kumazo
Changes in title
+MyBatis で生SQLを叩きたい
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,208 @@
+いや、MyBatis はいつだってネイティブ SQL を使えることが売りなのだが、そういうことではなくて、単純に Java コードから SQL 文を DB に投げたい。
+MyBatis の API には当然そのためのインターフェースもありそうなものだが、なぜか見当たらない。
+結局、定義した Mapper を介さなければならない決まりのようだ。
+
+デバッグやテストでちょっとした SQL 文を投げたいことはよくあるのだが、そのために interface や XML をわざわざ仕込むのは気が進まない。
+
+妥協案として、SQL文を注入できる汎用 Mapper を一つ用意する。
+
+```java
+public interface DbToolMapper {
+ @Select("${sql}")
+ List<Map<String, Object>> query(@Param("sql") String sql);
+}
+```
+
+query() の引数に渡した文字列は丸ごと変数展開され、SQL として DB に渡される。
+ちょっとミもフタもないが、これを session にねじ込めば任意の SQL を動的に実行し、結果行を Map の List で取得できる。
+
+```java
+ // 既存の SqlSession に 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}`)を渡すようにもできる。
+
+```java
+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);
+
+}
+```
+```java
+
+ // パラメータ名で参照
+ 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 ビルダークラスを駆使すれば、決まり切ったものだけは少しは楽することができる。
+
+```java
+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();
+ }
+ }
+```
+
+```java
+ 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 を組めるようになるが、それはもう完全に趣旨が違う。
+
+```java
+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);
+ }
+ }
+```
+
+
+```java
+ // 条件置換
+ 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:
+```
+