Spring Bootアプリケーションで定義ファイルから取得した数値(文字列型)を利用してSQLを実行してみた

Spring Bootアプリケーションでは、application.propertiesの値を@Valueアノテーションを利用して取得できるが、その値(数値)をSQLで利用することもできる。

今回は、SQL Serverに接続するSQLにおいて、定義ファイルから取得した数値(文字列型)を利用してみたので、そのサンプルプログラムを共有する。

前提条件

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

IntelliJ IDEA上でGradleを使ってWeb画面のSpring Bootプロジェクトを作成してみたSpring Bootのプロジェクトを新規作成を「IntelliJ IDEA」のメニューから実施しようとしたところ、無料の「Commun...

サンプルプログラムの構成

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

なお、上記の赤枠は、今回作成・変更したプログラムである。

build.gradleの内容は以下の通りで、Lombok・SQL Server接続・MyBatisを利用するための定義を追加している。

plugins {
    id 'org.springframework.boot' version '2.1.7.RELEASE'
    id 'java'
}

apply plugin: 'io.spring.dependency-management'

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    compileOnly 'org.projectlombok:lombok:1.18.10'
    annotationProcessor 'org.projectlombok:lombok:1.18.10'
    //SQL Serverに接続するための設定
    compile group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '8.4.1.jre11'
    //MyBatisを利用するための設定
    implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'
}

plugins {

id 'org.springframework.boot' version '2.1.7.RELEASE'

id 'java'

}

apply plugin: 'io.spring.dependency-management'

group = 'com.example'

version = '0.0.1-SNAPSHOT'

sourceCompatibility = '1.8'

repositories {

mavenCentral()

}

dependencies {

implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'

implementation 'org.springframework.boot:spring-boot-starter-web'

testImplementation 'org.springframework.boot:spring-boot-starter-test'

compileOnly 'org.projectlombok:lombok:1.18.10'

annotationProcessor 'org.projectlombok:lombok:1.18.10'

//SQL Serverに接続するための設定

compile group: 'com.microsoft.sqlserver', name: 'mssql-jdbc', version: '8.4.1.jre11'

//MyBatisを利用するための設定

implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'

}

また、application.propertiesの内容は以下の通りで、SQL実行時に利用する数値(文字列型)や、DB接続先の定義を追加している。

server.port = 8084

# プロパティファイルの各設定値
test.string.prop1=5
test.string.prop2=

# DB接続先(SQLServer)
spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=master
spring.datasource.username=USER01
spring.datasource.password=USER01
spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

server.port = 8084

# プロパティファイルの各設定値

test.string.prop1=5

test.string.prop2=

# DB接続先(SQLServer)

spring.datasource.url=jdbc:sqlserver://localhost:1433;databaseName=master

spring.datasource.username=USER01

spring.datasource.password=USER01

spring.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

さらに、USER_DATAテーブルにアクセスするためのエンティティクラスの内容は、以下の通り。

package com.example.demo;

import lombok.Data;

/**
 * ユーザーデータテーブル(user_data)アクセス用エンティティ
 */
@Data
public class UserData {

    /** ID */
    private long id;

    /** 名前 */
    private String name;

    /** 生年月日_年 */
    private int birthY;

    /** 生年月日_月 */
    private int birthM;

    /** 生年月日_日 */
    private int birthD;

    /** 性別 */
    private String sex;

    /** 性別(文字列) */
    private String sex_value;

    /** メモ */
    private String memo;

}

package com.example.demo;

import lombok.Data;

/**

* ユーザーデータテーブル(user_data)アクセス用エンティティ

@Data

public class UserData {

/** ID */

private long id;

/** 名前 */

private String name;

/** 生年月日_年 */

private int birthY;

/** 生年月日_月 */

private int birthM;

/** 生年月日_日 */

private int birthD;

/** 性別 */

private String sex;

/** 性別(文字列) */

private String sex_value;

/** メモ */

private String memo;

}

また、USER_DATAテーブルにアクセスするためのMapperの内容は以下の通りで、生年月日が1990年6月20日のpropNum日前～1990年6月20日のpropNum日後までのデータを取得するようにしている。

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserDataMapper {

    /**
     * SQL Serverで、生年月日が1990年6月20日のpropNum日前以降、
     * 1990年6月20日のpropNum日後以前であるユーザーデータテーブル(user_data)の
     * データを取得する
     * @param propNum プロパティから取得した数値(String型)
     * @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ
     */
    List<UserData> findByBirthdayPropStr(String propNum);

}

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper

public interface UserDataMapper {

/**

* SQL Serverで、生年月日が1990年6月20日のpropNum日前以降、

* 1990年6月20日のpropNum日後以前であるユーザーデータテーブル(user_data)の

* データを取得する

* @param propNum プロパティから取得した数値(String型)

* @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ

List<UserData> findByBirthdayPropStr(String propNum);

}

<?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.UserDataMapper">
    <select id="findByBirthdayPropStr" parameterType="java.lang.String" 
            resultType="com.example.demo.UserData">
        SELECT
              id
            , name
            , birth_year as birthY
            , birth_month as birthM
            , birth_day as birthD
            , sex
            , CASE sex
              WHEN '1' THEN '男'
              WHEN '2' THEN '女'
              ELSE ''
              END AS sex_value
            , memo
        FROM dbo.USER_DATA
        WHERE CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
                , FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))
            &gt;= CONVERT(DATETIME, '19900620') - CONVERT(int, #{propNum})
        AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
                , FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))
            &lt;= CONVERT(DATETIME, '19900620') + CONVERT(int, #{propNum})
    </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">

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

resultType="com.example.demo.UserData">

SELECT

, name

, birth_year as birthY

, birth_month as birthM

, birth_day as birthD

, sex

, CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE ''

END AS sex_value

, memo

FROM dbo.USER_DATA

WHERE CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')

, FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))

>= CONVERT(DATETIME, '19900620') - CONVERT(int, #{propNum})

AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')

, FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))

<= CONVERT(DATETIME, '19900620') + CONVERT(int, #{propNum})

</select>

</mapper>

さらに、Demoサービスクラスの内容は以下の通りで、SQL実行時に利用する数値(文字列型)の取得や、USER_DATAテーブルにアクセスするMapperの呼び出しを行っている。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class DemoService {

    // プロパティファイルからtest.string.prop1の値を取得し設定
    // 設定値：5となる
    @Value("${test.string.prop1}")
    private String prop1;

    // プロパティファイルからtest.string.prop2の値を取得し設定
    // 設定値：空文字となる
    @Value("${test.string.prop2}")
    private String prop2;

    // プロパティファイルに存在しないtest.string.prop3の値を設定
    // 設定値：0となる
    @Value("${test.string.prop3:0}")
    private String prop3;

    @Autowired
    private UserDataMapper userDataMapper;

    /**
     * 生年月日を検索条件にユーザーデータテーブル(user_data)のデータを取得する
     */
    public void getUserDataByBirthday(){
        System.out.println("prop1の値 : " + prop1);
        System.out.println("prop2の値 : " + prop2);
        System.out.println("prop2は空文字か? : " + "".equals(prop2));
        System.out.println("prop3の値 : " + prop3);

        List<UserData> userDataList = userDataMapper.findByBirthdayPropStr(prop1);
        System.out.println();
        System.out.println("*** prop1の値を利用して検索した結果 ***");
        for(UserData userData : userDataList){
            System.out.println(userData);
        }
        System.out.println();

        List<UserData> userDataList2 = userDataMapper.findByBirthdayPropStr(prop2);
        System.out.println("*** prop2の値を利用して検索した結果 ***");
        for(UserData userData : userDataList2){
            System.out.println(userData);
        }
        System.out.println();

        List<UserData> userDataList3 = userDataMapper.findByBirthdayPropStr(prop3);
        System.out.println("*** prop3の値を利用して検索した結果 ***");
        for(UserData userData : userDataList3){
            System.out.println(userData);
        }
        System.out.println();
    }

}

package com.example.demo;

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

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

import org.springframework.stereotype.Service;

import java.util.List;

@Service

public class DemoService {

// プロパティファイルからtest.string.prop1の値を取得し設定

// 設定値：5となる

@Value("${test.string.prop1}")

private String prop1;

// プロパティファイルからtest.string.prop2の値を取得し設定

// 設定値：空文字となる

@Value("${test.string.prop2}")

private String prop2;

// プロパティファイルに存在しないtest.string.prop3の値を設定

// 設定値：0となる

@Value("${test.string.prop3:0}")

private String prop3;

@Autowired

private UserDataMapper userDataMapper;

/**

* 生年月日を検索条件にユーザーデータテーブル(user_data)のデータを取得する

public void getUserDataByBirthday(){

System.out.println("prop1の値 : " + prop1);

System.out.println("prop2の値 : " + prop2);

System.out.println("prop2は空文字か? : " + "".equals(prop2));

System.out.println("prop3の値 : " + prop3);

List<UserData> userDataList = userDataMapper.findByBirthdayPropStr(prop1);

System.out.println();

System.out.println("*** prop1の値を利用して検索した結果 ***");

for(UserData userData : userDataList){

System.out.println(userData);

}

System.out.println();

List<UserData> userDataList2 = userDataMapper.findByBirthdayPropStr(prop2);

System.out.println("*** prop2の値を利用して検索した結果 ***");

for(UserData userData : userDataList2){

System.out.println(userData);

}

System.out.println();

List<UserData> userDataList3 = userDataMapper.findByBirthdayPropStr(prop3);

System.out.println("*** prop3の値を利用して検索した結果 ***");

for(UserData userData : userDataList3){

System.out.println(userData);

}

System.out.println();

}

また、Spring Bootのメインクラスの内容は以下の通りで、Demoサービスクラスを呼び出している。

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

    /**
     * 生年月日を検索条件にユーザーデータテーブル(user_data)の
     * データを取得するサービス
     */
    @Autowired
    private DemoService demoService;

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

    @Override
    public void run(String... args) {
        // 生年月日を検索条件にユーザーデータテーブル(user_data)の
        // データを取得する
        demoService.getUserDataByBirthday();
    }

}

package com.example.demo;

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

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication

public class DemoApplication implements CommandLineRunner {

/**

* 生年月日を検索条件にユーザーデータテーブル(user_data)の

* データを取得するサービス

@Autowired

private DemoService demoService;

public static void main(String[] args) {

SpringApplication.run(DemoApplication.class, args);

}

@Override

public void run(String... args) {

// 生年月日を検索条件にユーザーデータテーブル(user_data)の

// データを取得する

demoService.getUserDataByBirthday();

}

これらのソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-property-int-string-sql/demo

「CODE×CODE」は、需要の高い技術(AWS, Python等)を習得できるプログラミングスクールスクールだった近年、さまざまな会社でクラウド（特にIaaSやPaaSのパブリッククラウド）の需要が非常に高まっていて、クラウドサービスによるシステム開...

サンプルプログラムの実行結果

サンプルプログラムの実行結果は、以下の通り。

1) SQL ServerのUSER_DATAテーブルには、以下のデータを入れておく。

2) 1)の状態でDemoサービスクラスの「DemoApplication.java」を実行した結果は以下の通りで、prop1を利用した場合は生年月日が1990年6月15日～1990年6月25日(propNum=5)のデータが、prop2, prop3を利用した場合は生年月日が1990年6月20日(propNum=0)のデータが、それぞれ取得できることが確認できる。

3) UserDataMapper.xmlで、以下の赤枠のように、propNumのCONVERTを除外する。

4) 3)の状態でDemoサービスクラスの「DemoApplication.java」を実行した結果は以下の通りで、propNumの変換に失敗し、赤枠のSQLエラーが発生したことが確認できる。

5) A5M2を利用して、SQL Server上で、UserDataMapper.xmlに記載したWHERE句の条件でSQL実行した結果は、以下の通り。

SELECT * FROM dbo.user_data 
WHERE CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
                              , FORMAT(birth_month,'00')
                              , FORMAT(birth_day,'00'))) 
      >= CONVERT(DATETIME, '19900620') - CONVERT(int, '5')
AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
                           , FORMAT(birth_month,'00')
                           , FORMAT(birth_day,'00')))
      <= CONVERT(DATETIME, '19900620') + CONVERT(int, '5')

SELECT * FROM dbo.user_data

WHERE CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')

, FORMAT(birth_month,'00')

, FORMAT(birth_day,'00')))

>= CONVERT(DATETIME, '19900620') - CONVERT(int, '5')

AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')

, FORMAT(birth_month,'00')

, FORMAT(birth_day,'00')))

<= CONVERT(DATETIME, '19900620') + CONVERT(int, '5')

6) A5M2を利用して、Oracle上で、UserDataMapper.xmlに記載したWHERE句の条件でSQL実行した結果は、以下の通り。

SELECT * FROM user_data 
WHERE TO_DATE(TO_CHAR(birth_year) 
           || LPAD(TO_CHAR(birth_month), 2, '0') 
           || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd')
  >= TO_DATE('19900620', 'yyyyMMdd') - TO_NUMBER('5')
AND TO_DATE(TO_CHAR(birth_year) 
         || LPAD(TO_CHAR(birth_month), 2, '0') 
         || LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd')
  <= TO_DATE('19900620', 'yyyyMMdd') + TO_NUMBER('5')

SELECT * FROM user_data

WHERE TO_DATE(TO_CHAR(birth_year)

|| LPAD(TO_CHAR(birth_month), 2, '0')

|| LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd')

>= TO_DATE('19900620', 'yyyyMMdd') - TO_NUMBER('5')

AND TO_DATE(TO_CHAR(birth_year)

|| LPAD(TO_CHAR(birth_month), 2, '0')

|| LPAD(TO_CHAR(birth_day), 2, '0'), 'yyyyMMdd')

<= TO_DATE('19900620', 'yyyyMMdd') + TO_NUMBER('5')

7) A5M2を利用して、MySQL上で、UserDataMapper.xmlに記載したWHERE句の条件でSQL実行した結果は、以下の通り。

SELECT * FROM user_data 
WHERE str_to_date(CONCAT(CONVERT(birth_year, CHAR(4))
                       , LPAD(CONVERT(birth_month, CHAR(2)), 2, '0')
                       , LPAD(CONVERT(birth_day, CHAR(2)), 2, '0')), '%Y%m%d')
  >= str_to_date('19900620', '%Y%m%d') - CAST('5' AS SIGNED)
AND str_to_date(CONCAT(CONVERT(birth_year, CHAR(4))
                     , LPAD(CONVERT(birth_month, CHAR(2)), 2, '0')
                     , LPAD(CONVERT(birth_day, CHAR(2)), 2, '0')), '%Y%m%d')
  <= str_to_date('19900620', '%Y%m%d') + CAST('5' AS SIGNED)

SELECT * FROM user_data

WHERE str_to_date(CONCAT(CONVERT(birth_year, CHAR(4))

, LPAD(CONVERT(birth_month, CHAR(2)), 2, '0')

, LPAD(CONVERT(birth_day, CHAR(2)), 2, '0')), '%Y%m%d')

>= str_to_date('19900620', '%Y%m%d') - CAST('5' AS SIGNED)

AND str_to_date(CONCAT(CONVERT(birth_year, CHAR(4))

, LPAD(CONVERT(birth_month, CHAR(2)), 2, '0')

, LPAD(CONVERT(birth_day, CHAR(2)), 2, '0')), '%Y%m%d')

<= str_to_date('19900620', '%Y%m%d') + CAST('5' AS SIGNED)

8) A5M2を利用して、PostgreSQL上で、UserDataMapper.xmlに記載したWHERE句の条件でSQL実行した結果は、以下の通り。

SELECT * FROM user_data 
WHERE CAST(CONCAT(CAST(birth_year AS VARCHAR(4))
                , LPAD(CAST(birth_month AS VARCHAR(2)), 2, '0')
                , LPAD(CAST(birth_day AS VARCHAR(2)), 2, '0')) AS DATE)
  >= CAST('19900620' AS DATE) - CAST('5' AS INTEGER)
AND CAST(CONCAT(CAST(birth_year AS VARCHAR(4))
              , LPAD(CAST(birth_month AS VARCHAR(2)), 2, '0')
              , LPAD(CAST(birth_day AS VARCHAR(2)), 2, '0')) AS DATE)
  <= CAST('19900620' AS DATE) + CAST('5' AS INTEGER)

SELECT * FROM user_data

WHERE CAST(CONCAT(CAST(birth_year AS VARCHAR(4))