MyBatisを使ってOracle DBに接続するアプリケーション上でWITH句を利用してみた

SQLにおいてWITH句を利用すると、以下のサイトに記載されている通り、複数回書かれている副問合せを1つにまとめて記述することができるため、見通しのよいSQLを記載することができる。

今回は、MyBatisを使ってOracle DBに接続するアプリケーション上でWITH句を利用してみたので、そのサンプルプログラムを共有する。

なお、WITH句については以下のサイトを参照のこと。
https://oreno-it.info/archives/698

前提条件

下記記事の「EMPLOYEEテーブル」のテーブル・データ作成が完了していること。

自己結合を含むSQLのIN句とEXISTS句を試してみた同じテーブル同士を結合することを「自己結合」という。自己結合を含むSQLでは、結合する2テーブルの区別を付かないことによる不具合が発生し...

また、下記記事に記載した方法で、A5M2を利用してOracleに接続できていること。

A5M2を利用して各DBに接続してみたA5M2(A5:SQL Mk-2)は、SQL文の入力支援やER図作成などの機能を備えていて、Oracle、MySQL、PostgreSQ...

さらに、下記記事の実装が完了していること。

MyBatisフレームワーク内で引数のリストからIN句を自動生成してみたSpring Bootアプリケーション内でDBアクセス処理を含む場合に、MyBatisフレームワークを利用することがあるが、foreac...

WITH句を利用したSQLの実行結果

A5M2によってOracle DBに接続し、WITH句を利用したSQLの実行結果は、以下の通り。

1) 前提条件の記事で作成したEMPLOYEEテーブルの中身は、以下の通り。

2) EMPLOYEEテーブルから、生年月日が1965年5月15日の上司をもつデータを取得するSQLを、IN句を用いて記載した結果は、以下の通り。

SELECT ID, NAME, BIRTHDAY, BOSS_ID 
FROM EMPLOYEE
WHERE BOSS_ID IN ( 
  SELECT ID 
  FROM EMPLOYEE 
  WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
ORDER BY ID ASC

SELECT ID, NAME, BIRTHDAY, BOSS_ID

FROM EMPLOYEE

WHERE BOSS_ID IN (

SELECT ID

FROM EMPLOYEE

WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD')

)

ORDER BY ID ASC

3) EMPLOYEEテーブルから、生年月日が1965年5月15日の上司をもつデータを取得するSQLを、WITH句を用いて記載した結果は以下の通りで、IN句を利用した場合と同じ結果になっている。

WITH TMP_BOSS AS (
   SELECT * 
   FROM EMPLOYEE
   WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD') 
)
SELECT EMP.ID, EMP.NAME, EMP.BIRTHDAY, EMP.BOSS_ID 
FROM EMPLOYEE EMP
INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID

WITH TMP_BOSS AS (

SELECT *

FROM EMPLOYEE

WHERE BIRTHDAY = TO_DATE('1965/05/15', 'YYYY/MM/DD')

)

SELECT EMP.ID, EMP.NAME, EMP.BIRTHDAY, EMP.BOSS_ID

FROM EMPLOYEE EMP

INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID

削除または保存していないWordドキュメントの復元方法【4DDiG Windowsデータ復元】ワード(Word)データ等のファイルを誤って削除してしまった場合は、通常はデータの復元ができませんが、4DDiGというソフトウェアを利用...

MyBatisを利用したサンプルプログラムの作成と実行結果

MyBatisを利用したサンプルプログラムの作成と実行結果は、以下の通り。

1) 作成したサンプルプログラムの構成は、以下の通り。

2) 1)のうち、赤枠の「EmployeeMapper.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="com.example.demo.EmployeeMapper">
    <resultMap id="employeeResultMap" type="com.example.demo.Employee" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="birthday" property="birthDay" jdbcType="DATE" />
        <result column="boss_id" property="bossId" jdbcType="INTEGER" />
    </resultMap>
    <select id="findByBossBirthdayIn" parameterType="java.lang.String" 
            resultMap="employeeResultMap">
        SELECT ID, NAME, BIRTHDAY, BOSS_ID
        FROM EMPLOYEE
        WHERE BOSS_ID IN (
            SELECT ID
            FROM EMPLOYEE
            WHERE BIRTHDAY = TO_DATE(#{birthday}, 'YYYY/MM/DD')
        )
        ORDER BY ID ASC
    </select>
    <select id="findByBossBirthdayWith" parameterType="java.lang.String" 
            resultMap="employeeResultMap">
        WITH TMP_BOSS AS (
            SELECT *
            FROM EMPLOYEE
            WHERE BIRTHDAY = TO_DATE(#{birthday}, 'YYYY/MM/DD')
        )
        SELECT EMP.ID, EMP.NAME, EMP.BIRTHDAY, EMP.BOSS_ID
        FROM EMPLOYEE EMP
        INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID
    </select>
</mapper>

<?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">

</resultMap>

<select id="findByBossBirthdayIn" parameterType="java.lang.String"

resultMap="employeeResultMap">

SELECT ID, NAME, BIRTHDAY, BOSS_ID

FROM EMPLOYEE

WHERE BOSS_ID IN (

SELECT ID

FROM EMPLOYEE

WHERE BIRTHDAY = TO_DATE(#{birthday}, 'YYYY/MM/DD')

)

ORDER BY ID ASC

</select>

<select id="findByBossBirthdayWith" parameterType="java.lang.String"

resultMap="employeeResultMap">

WITH TMP_BOSS AS (

SELECT *

FROM EMPLOYEE

WHERE BIRTHDAY = TO_DATE(#{birthday}, 'YYYY/MM/DD')

)

SELECT EMP.ID, EMP.NAME, EMP.BIRTHDAY, EMP.BOSS_ID

FROM EMPLOYEE EMP

INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID

</select>

</mapper>

その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-oracle-with/demo

3)「EmployeeMapper.xml」に記載したSQLを順次実行した結果は以下の通りで、A5M2によってOracle DBに接続した実行結果と同じ結果になっている。

要点まとめ

SQLにおいてWITH句を利用すると、複数回書かれている副問合せを1つにまとめて記述することができるため、見通しのよいSQLを記載することができる。
MyBatisを利用したプログラムにおいても、WITH句を利用できる。

前提条件

WITH句を利用したSQLの実行結果

MyBatisを利用したサンプルプログラムの作成と実行結果

要点まとめ

OracleのIN句で1000件を超える項目を指定してみた

Oracle上で@Transactionalアノテーションをネストして利用してみた

インメモリ型のH2DBに接続しMyBatisを利用してみた

Spring Bootで、OracleのテーブルにBLOB,CLOBのカラムを含む場合のOracle接続を実装してみた

Spring BootでJNDIによるDBアクセスを実装してみた

Oracle上でSpring Bootの@Transactionalアノテーションの挙動を調べてみた