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

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

また、WITH句は、Oracle DBだけでなく、MySQL, PostgreSQL, SQL Serverの各DBにおいて利用できる。Oracle DBに接続した場合のWITH句の利用については、以下の記事を参照のこと。

MyBatisを使ってOracle DBに接続するアプリケーション上でWITH句を利用してみたSQLにおいてWITH句を利用すると、以下のサイトに記載されている通り、複数回書かれている副問合せを1つにまとめて記述することができるた...

なお、MySQLは、MySQL8.0でWITH句が利用できるようになっている。詳細は以下のサイトを参照のこと。
https://qiita.com/mzuk/items/78a0c94829832e0c4c7c

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

前提条件

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

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

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

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

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

各DBのIN句で1000件を超える項目を指定してみたOracleデータベースの場合、IN句で1000件を超える項目を指定するとエラーになってしまうが、他のDBではIN句に1001件の項目を...

MySQLの場合の実行結果

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

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

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

WITH TMP_BOSS AS (
   SELECT * 
   FROM EMPLOYEE
   WHERE BIRTHDAY = STR_TO_DATE('1965/05/15', '%Y/%m/%d') 
)
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 = STR_TO_DATE('1965/05/15', '%Y/%m/%d')

)

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

FROM EMPLOYEE EMP

INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID

PostgreSQLの場合の実行結果

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

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

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

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

SQL Serverの場合の実行結果

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

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

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

WITH TMP_BOSS AS (
   SELECT * 
   FROM EMPLOYEE
   WHERE BIRTHDAY = CONVERT(DATETIME, '1965/05/15')
)
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 = CONVERT(DATETIME, '1965/05/15')

)

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

FROM EMPLOYEE EMP

INNER JOIN TMP_BOSS ON EMP.BOSS_ID = TMP_BOSS.ID

「Envader」はLinuxコマンドやDatabase SQL等のスキルを、環境構築不要で習得できる学習サイトだった「Envader」は、ITエンジニアとしてよく使うLinuxコマンドやDatabase SQL等のスキルを、解説を読んだ上で、問題を解き...

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

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

上記の赤枠のうち、MySQLに接続するSQL「EmployeeMapperMs.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.mapper.ms.EmployeeMapperMs">
    <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="findByBossBirthdayWith" parameterType="java.lang.String" 
            resultMap="employeeResultMap">
        WITH TMP_BOSS AS (
            SELECT *
            FROM EMPLOYEE
            WHERE BIRTHDAY = STR_TO_DATE(#{birthday}, '%Y/%m/%d')
        )
        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="findByBossBirthdayWith" parameterType="java.lang.String"

resultMap="employeeResultMap">

WITH TMP_BOSS AS (

SELECT *

FROM EMPLOYEE

WHERE BIRTHDAY = STR_TO_DATE(#{birthday}, '%Y/%m/%d')

)

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>

上記の赤枠のうち、PostgreSQLに接続するSQL「EmployeeMapperPs.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.mapper.ps.EmployeeMapperPs">
    <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="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="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>

上記の赤枠のうち、SQL Serverに接続するSQL「EmployeeMapperSs.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.mapper.ss.EmployeeMapperSs">
    <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="findByBossBirthdayWith" parameterType="java.lang.String" 
            resultMap="employeeResultMap">
        WITH TMP_BOSS AS (
            SELECT *
            FROM EMPLOYEE
            WHERE BIRTHDAY = CONVERT(DATETIME, #{birthday})
        )
        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="findByBossBirthdayWith" parameterType="java.lang.String"

resultMap="employeeResultMap">

WITH TMP_BOSS AS (

SELECT *

FROM EMPLOYEE

WHERE BIRTHDAY = CONVERT(DATETIME, #{birthday})

)

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>

上記の赤枠のうち、各DBに接続しSQLを実行する「DemoApplication.java」の内容は以下の通り。

package com.example.demo;

import com.example.demo.mapper.ms.EmployeeMapperMs;
import com.example.demo.mapper.ora.EmployeeMapperOra;
import com.example.demo.mapper.ps.EmployeeMapperPs;
import com.example.demo.mapper.ss.EmployeeMapperSs;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

  /** 指定する上司の生年月日 */
  private static final String BOSS_BIRTHDAY = "1965/05/15";

  /** OracleのEmployeeテーブルにアクセスするMapper */
  @Autowired
  private EmployeeMapperOra employeeMapperOra;

  /** MySQLのEmployeeテーブルにアクセスするMapper */
  @Autowired
  private EmployeeMapperMs employeeMapperMs;

  /** PostgreSQLのEmployeeテーブルにアクセスするMapper */
  @Autowired
  private EmployeeMapperPs employeeMapperPs;

  /** SQL ServerのEmployeeテーブルにアクセスするMapper */
  @Autowired
  private EmployeeMapperSs employeeMapperSs;

  public static void main(String[] args) {
    SpringApplication.run(DemoApplication.class, args);
  }

  @Override
  public void run(String... args) {
    // Oracle上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す
    List<Employee> empListWithOra 
        = employeeMapperOra.findByBossBirthdayWith(BOSS_BIRTHDAY);

    // 指定した生年月日の上司をもつEmployeeテーブルのデータを表示
    System.out.println("*** Oracle EmployeeMapperOra.findByBossBirthdayWith" 
        + " 実行結果 START ***");
    for(Employee emp : empListWithOra){
      System.out.println(emp);
    }
    System.out.println("*** Oracle EmployeeMapperOra.findByBossBirthdayWith" 
        + " 実行結果 END ***");
    System.out.println();

    // MySQL上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す
    List<Employee> empListWithMs 
        = employeeMapperMs.findByBossBirthdayWith(BOSS_BIRTHDAY);

    // 指定した生年月日の上司をもつEmployeeテーブルのデータを表示
    System.out.println("*** MySQL EmployeeMapperMs.findByBossBirthdayWith" 
        + " 実行結果 START ***");
    for(Employee emp : empListWithMs){
      System.out.println(emp);
    }
    System.out.println("*** MySQL EmployeeMapperMs.findByBossBirthdayWith" 
        + " 実行結果 END ***");
    System.out.println();

    // PostgreSQL上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す
    List<Employee> empListWithPs 
        = employeeMapperPs.findByBossBirthdayWith(BOSS_BIRTHDAY);

    // 指定した生年月日の上司をもつEmployeeテーブルのデータを表示
    System.out.println("*** PostgreSQL EmployeeMapperPs.findByBossBirthdayWith" 
        + " 実行結果 START ***");
    for(Employee emp : empListWithPs){
      System.out.println(emp);
    }
    System.out.println("*** PostgreSQL EmployeeMapperPs.findByBossBirthdayWith" 
        + " 実行結果 END ***");
    System.out.println();

    // SQL Server上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す
    List<Employee> empListWithSs 
        = employeeMapperSs.findByBossBirthdayWith(BOSS_BIRTHDAY);

    // 指定した生年月日の上司をもつEmployeeテーブルのデータを表示
    System.out.println("*** SQL Server EmployeeMapperSs.findByBossBirthdayWith" 
        + " 実行結果 START ***");
    for(Employee emp : empListWithSs){
      System.out.println(emp);
    }
    System.out.println("*** SQL Server EmployeeMapperSs.findByBossBirthdayWith" 
        + " 実行結果 END ***");
    System.out.println();
  }

}

package com.example.demo;

import com.example.demo.mapper.ms.EmployeeMapperMs;

import com.example.demo.mapper.ora.EmployeeMapperOra;

import com.example.demo.mapper.ps.EmployeeMapperPs;

import com.example.demo.mapper.ss.EmployeeMapperSs;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import java.util.List;

@SpringBootApplication

public class DemoApplication implements CommandLineRunner {

/** 指定する上司の生年月日 */

private static final String BOSS_BIRTHDAY = "1965/05/15";

/** OracleのEmployeeテーブルにアクセスするMapper */

@Autowired

private EmployeeMapperOra employeeMapperOra;

/** MySQLのEmployeeテーブルにアクセスするMapper */

@Autowired

private EmployeeMapperMs employeeMapperMs;

/** PostgreSQLのEmployeeテーブルにアクセスするMapper */

@Autowired

private EmployeeMapperPs employeeMapperPs;

/** SQL ServerのEmployeeテーブルにアクセスするMapper */

@Autowired

private EmployeeMapperSs employeeMapperSs;

public static void main(String[] args) {

SpringApplication.run(DemoApplication.class, args);

}

@Override

public void run(String... args) {

// Oracle上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す

List<Employee> empListWithOra

= employeeMapperOra.findByBossBirthdayWith(BOSS_BIRTHDAY);

// 指定した生年月日の上司をもつEmployeeテーブルのデータを表示

System.out.println("*** Oracle EmployeeMapperOra.findByBossBirthdayWith"

+ " 実行結果 START ***");

for(Employee emp : empListWithOra){

System.out.println(emp);

}

System.out.println("*** Oracle EmployeeMapperOra.findByBossBirthdayWith"

+ " 実行結果 END ***");

System.out.println();

// MySQL上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す

List<Employee> empListWithMs

= employeeMapperMs.findByBossBirthdayWith(BOSS_BIRTHDAY);

// 指定した生年月日の上司をもつEmployeeテーブルのデータを表示

System.out.println("*** MySQL EmployeeMapperMs.findByBossBirthdayWith"

+ " 実行結果 START ***");

for(Employee emp : empListWithMs){

System.out.println(emp);

}

System.out.println("*** MySQL EmployeeMapperMs.findByBossBirthdayWith"

+ " 実行結果 END ***");

System.out.println();

// PostgreSQL上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す

List<Employee> empListWithPs

= employeeMapperPs.findByBossBirthdayWith(BOSS_BIRTHDAY);

// 指定した生年月日の上司をもつEmployeeテーブルのデータを表示

System.out.println("*** PostgreSQL EmployeeMapperPs.findByBossBirthdayWith"

+ " 実行結果 START ***");

for(Employee emp : empListWithPs){

System.out.println(emp);

}

System.out.println("*** PostgreSQL EmployeeMapperPs.findByBossBirthdayWith"

+ " 実行結果 END ***");

System.out.println();

// SQL Server上で指定した生年月日の上司をもつEmployeeテーブルのデータを取得する(WITH句)を呼び出す

List<Employee> empListWithSs

= employeeMapperSs.findByBossBirthdayWith(BOSS_BIRTHDAY);

// 指定した生年月日の上司をもつEmployeeテーブルのデータを表示

System.out.println("*** SQL Server EmployeeMapperSs.findByBossBirthdayWith"

+ " 実行結果 START ***");

for(Employee emp : empListWithSs){

System.out.println(emp);

}

System.out.println("*** SQL Server EmployeeMapperSs.findByBossBirthdayWith"

+ " 実行結果 END ***");

System.out.println();

}

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

「DemoApplication.java」を実行した結果は以下の通りで、A5M2によって各DBに接続した実行結果と同じ結果になっている。

https://www.purin-it.com/doctor-homenet

要点まとめ

SQLにおいてWITH句を利用すると、複数回書かれている副問合せを1つにまとめて記述することができるため、見通しのよいSQLを記載することができる。
WITH句はOracle DBだけでなく、MySQL(Ver8.0以上), PostgreSQL, SQL Serverの各DBにおいて利用できる。

前提条件

MySQLの場合の実行結果

PostgreSQLの場合の実行結果

SQL Serverの場合の実行結果

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

要点まとめ

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

Oracle上でMyBatisを利用して複数レコードをまとめて追加／更新してみた

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

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

Spring BootのWEB画面上でnull許可項目のDB更新機能を追加してみた(完成イメージと前提条件)

Spring BootのWEB画面上で、MyBatisによるOracle接続を実装してみた