Zenn
🐕

【MyBatis】SQLの書き方 - oracle

2022/04/09に公開
Java
MyBatis
Oracle
tech

業務で初めてMyBatisを使い、慣れるまで大変だったので書き方をメモ。

目次
  • 準備
  • 基本的な構文
    • mapper.javaとmapper.xmlのidが紐づく
    • 変数名の指定には#{}
    • parameterType
    • resultType
    • 条件分岐はwhereかif
      -if
      choose where
    • listや配列を渡したいとき
  • 応用
    • resultMap
    • 最初と最後に連続でSQL実行したいとき
    • まとめてinesertしたい
      • foreachでindex（連番）を使用したいとき
    • シーケンスを連続で採番したいとき
    • 大量データを取得したいとき（速度改善）
  • 最後に

準備

このようなPROFILEテーブルがあると仮定して記載していきます。

id cd_name nm_name
1 100 yamada
2 101 miyazaki

実行環境

  • Java17
  • MyBatis3
  • oracle (ojdbc8)

bean

Profile.java
public class Profile {
	private int id;
	private String cdName;
	private String nmName;

	public int getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getCdName() {
		return cdName;
	}
	public void setCdName(String cdName) {
		this.cdName = cdName;
	}
	
	public String getNmName() {
		return nmName;
	}

	public void setNmName(String nmName) {
		this.nmName = nmName;
	}
}

インターフェース

ProfileMapper.java
@Mapper
public interface ProfileMapper {
	Profile selectProfile(@Param("pr") Profile profile);
}

SQL

ProfileMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="(ProfileMapper.javaのパス)">
	<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
		SELECT
			PROFILE.id,
			PROFILE.cd_name,
			PROFILE.nm_name
		FROM
			PROFILE
		ORDER BY
			PROFILE.id
		WHERE
		<if test="pr.cdName != null">
			PROFILE.cd_name = #{pr.cdName}
		</if>
	</select>
</mapper>

※javaファイルとxmlファイルの名称は合わせます。

基本的な構文

mapper.javaとmapper.xmlのidが紐づく

Profile selectProfile(@Param("pr") Profile profile);

変数名を違う名前で渡したいとき@Paramをつけるとその名前でmapper.xmlに記載できます。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="pr.cd_name != null">
		PROFILE.cd_name = #{pr.cdName}
	</if>
</select>

パラメータが１つの場合は、以下でもOKです。(cdNameだけ書く)

Profile selectProfile(Profile profile);

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="cdName != null">
		PROFILE.cd_name = #{cdName}
	</if>
</select>

変数名の指定には#{}

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="cdName != null">
		PROFILE.cd_name = #{cdName}
	</if>
</select>

パラメータに渡したい変数名を#{変数名}の形で指定します。

「test=""」に指定したい条件式を書きます。
このとき#{}では括らずそのまま変数名だけを書きます。

parameterType

selectタグやinsertタグに、パラメータのタイプを指定します。
この指定は、メソッドの引数が１つの場合のみします。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">

resultType

selectタグの場合は、resultTypeを指定します。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">

条件分岐はwhereかif

nullではないときや、指定の値のときなどで条件分岐したいとき。

if

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="cdName != null">
		PROFILE.cd_name = #{cdName}
	</if>
</select>

このように何個もつなげて書いてもOKです。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="cdName == 100">
		PROFILE.cd_name = 'yamada'
	</if>
	<if test="cdName == null">
		PROFILE.cd_name = #{cdName}
	</if>
</select>

choose where

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<choose>
		<when test="cdName == 100">
			PROFILE.cd_name = 'yamada'
		</when>
		<when test="cdName != null">
			PROFILE.cd_name = #{cdName}
		</when>
		<otherwise>
			<!-- if文のelseの代わりになります -->
		</otherwise>	
	</choose>
</select>

条件が多いときは、chooseを使った方がわかりやすく、
「otherwise」で指定条件以外のときの条件を書くことができ便利です。

listを渡したいとき

foreachでこう書きます。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
		PROFILE.cd_name in
	 <foreach item="cd"  collection="cdName" open="(" close=")" separator="," >
		 #{cd}
	 </foreach>  
</select>

open：foreachの始めに追加したい値
close:foreachの終わりに追加したい値
separator:きぎりに追加したい値

こう変換されます。

SELECT
	PROFILE.id,
	PROFILE.cd_name,
	PROFILE.nm_name
FROM
	PROFILE
ORDER BY
	PROFILE.id
WHERE
	PROFILE.cd_name in ('100', '101')

おそらくですが変数を囲う''は自動で付与されます。

like句を使ってorで使いたいときはこうなります。

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	 <foreach item="cd"  collection="cdName" open="PROFILE.cd_name like'%" close="%'" separator="or" >
		 #{cd}
	 </foreach>  
</select>

こう変換されます。

SELECT
	PROFILE.id AS,
	PROFILE.cd_name,
	PROFILE.nm_name
FROM
	PROFILE
ORDER BY
	PROFILE.id
WHERE
PROFILE.cd_name like'%100%' or PROFILE.cd_name like'%101%'

配列を渡したいとき

listと違う点は、以下２つです。

  • collectionとitemを同じ名称にする。
  • resultMapに「typeHandler」のArrayTypeHandlerを追加する。
<resultMap id="BaseResultMap" type="(Profileのパス名）">
	<id column="id" jdbcType="NUMERIC" property="id" />
	<result column="cd_name" typeHandler="org.apache.ibatis.type.ArrayTypeHandler" jdbcType="NUMERIC" property="cdName" />
</resultMap>

<select id="selectProfile" parameterType="java.util.List" resultType="Profile">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
		PROFILE.cd_name in
	 <foreach item="cdName"  collection="cdName" open="(" close=")" separator="," >
		 #{cdName}
	 </foreach>  
</select>

応用

個人的に応用っぽい使い方です。

resultMap

公式の説明では、
「データベースから取得した結果セットを Java オブジェクトにマッピングするための情報を記述する、最も複雑で強力な要素です。」
とあります。

受け取りたい変数名が、SQLと違うときに便利です（と思ってます）。
ここで変数名が違う値を受け取りたい場合は、
SELECT文にASをつけて受け取る必要があります。
ASをつけずに受け取ることができるので、
受け取る値が増えたときに効果を発揮するのがresultMapです。

使い方としては、
select文や受け取りたい値があるとき、タグにresultType、または、このresultMapを追加します。
※このときresultType と resultMap は、どちらか一方のみ指定可能です。

<resultMap id="BaseResultMap" type="(Profileのパス名）">
	<id column="id" jdbcType="NUMERIC" property="id" />
	<result column="cd_name" jdbcType="NUMERIC" property="cdName" />
	<result column="nm_name" jdbcType="NUMERIC" property="nmName" />
</resultMap>

<select id="selectProfile" parameterType="java.util.List" resultMap="BaseResultMap">
	SELECT
		PROFILE.id,
		PROFILE.cd_name,
		PROFILE.nm_name
	FROM
		PROFILE
	ORDER BY
		PROFILE.id
	WHERE
	<if test="cdName != null">
		PROFILE.cd_name = #{cdName}
	</if>
</select>

例えば、cd_nameはcdNameのように自動で変換して認識してくれますが、
テーブルのカラムがcd_name、javaでの変数名はcdNamaeのようなときに
ASをつけないとエラーになります。

このような変数が多いときには、resultMapを使って受け取った方が記述量が減ります。

最初と最後に連続でSQL実行したいとき

自動採番のidをinsert文の後にselectして取得したい、というようなときは
selectkeyタグを使います。

<insert id="insertProfile" parameterType="java.util.List">
  insert into Profile
    (id, nm_name, cd_name)
  values
    (#{id}, #{nmName}, #{cdName})
<selectKey keyProperty="id" resultType="int" order="AFTER">
    <!-- 自動採番されたidを取得 -->
        SELECT
	    id
	FROM
	    Profile
	where
	    id = (select max(id) from Profile)
</selectKey>
</insert>

order="BEFORE"にすればinsert文の前に実行したいクエリを書けます。

用意したテーブルが情報少なすぎてSQLも微妙ですが、
自動採番されたidを取得するSELECT文のwhere句に追加日時のカラムがあればそれを指定すれば最新が取れます。
（これもちょっと無理矢理かもしれませんが、、、）

まとめてinesertしたい

<insert id="insertProfile" parameterType="java.util.List">
	INSERT ALL
	<foreach collection="searchList" item="item">
	into Profile
	(id, nm_name, cd_name)
	values
    	(#{id}, #{nmName}, #{cdName})
    	</foreach>
    	SELECT * FROM dual
</insert>

これはそのままです。
oracle以外は違う書き方かもしれません。

foreachでindex（連番）を使用したいとき

<insert id="insertProfileIndex" parameterType="java.util.List">
	INSERT ALL
	<foreach collection="searchList" item="item" index="index">
	into Profile
	(id, nm_name, cd_name)
	values
    	(#{index} = 1, #{nmName}, #{cdName})
    	</foreach>
	SELECT * FROM dual;
</insert>

連番を使いたいときは、foreachのindexを使います。
indexは0から始まります。

例のような使い方はしないと思いますが、
Listでもらったidを使わずにindexを用いて1から振ることができます。

シーケンスを連続で採番したいとき

例えば、「PROFILE_CODE_SEQ」というシーケンスがあるとします。

 <select id="getSeq" resultType="short" flushCache="true">
	select PROFILE_CODE_SEQ.nextval from dual
  </select>

「flushCache="true"」をつけるとローカルキャッシュがクリアされて正常にシーケンスが採番できます。

最初はこれをつけていなくて、
for文で毎回シーケンス採番してそれをlistに詰めて、処理をしたい。
というときに、同じ値が取れてしまってました。

大量データを取得したいとき（速度改善）

Cursorを使うと早くなります。
List<>の形式で返却したい場合が多いと思うのでServiceで処理します。

ポイントは2つです。

  • 「@Transactional(readOnly = true)」のアノテーション付与
  • mapperでfetchSizeを指定
ProfileService.java
@Service
public class ProfileService {

	@Autowired
	private ProfileMapper profileMapper;

	@Transactional(readOnly = true)
	public List<String> getProfileList() {
		
		List<String> res = new ArrayList<>();
		try (Cursor<String> profileList = profileMapper.getProfileList()) {
			for (String nmName : profileList) {
				res.add(nmName);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		return res;
	}
}
ProfileMapper.java
@Mapper
public interface ProfileMapper {
	Cursor<String> getProfileList();
}
ProfileMapper.xml
<select id="getProfileList" fetchSize="100000" resultType="String">
SELECT nm_name as nmName FROM PROFILE
</select>

最後に

mybatisが、というよりそもそも簡単なSQLしか書いたことがなかったため、最初はかなり苦戦しました。
慣れると便利さを実感できました。

個人開発では、MySQLを使う予定なので違う点があればまた書きたいと思います。
まだ勉強不足でわからない点や間違ってる点あると思うので
気づかれた際はコメントいただけると嬉しいです。

参考サイト：
https://mybatis.org/mybatis-3/ja/sqlmap-xml.html
https://ykhr-kokko.hatenadiary.org/entry/20111115/1321338207

Discussion