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

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

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

前提条件

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

Spring Bootアプリケーションで定義ファイルから取得した数値(文字列型)を利用してSQLを実行してみたSpring Bootアプリケーションでは、application.propertiesの値を@Valueアノテーションを利用して取得で...

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

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

なお、上記の赤枠は、前提条件のプログラムから変更したプログラムである。

application.propertiesの内容は以下の通りで、SQL実行時に利用する数値を指定している。

server.port = 8084

# プロパティファイルの各設定値
test.integer.prop1=5
test.integer.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テーブルにアクセスするためのMapperの内容は以下の通りで、propNumをInteger型として、生年月日が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 プロパティから取得した数値(Integer型)
     * @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ
     */
    List<UserData> findByBirthdayPropInt(Integer 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="findByBirthdayPropInt" parameterType="java.lang.Integer" 
            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') - IsNull(#{propNum}, 0)
        AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
                 , FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))
            &lt;= CONVERT(DATETIME, '19900620') + IsNull(#{propNum}, 0)
    </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.integer.prop1の値を取得し設定
    // 設定値：5となる
    @Value("${test.integer.prop1}")
    private Integer prop1;

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

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

    @Autowired
    private UserDataMapper userDataMapper;

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

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

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

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

}

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

サラリーマン型フリーランスSEという働き方でお金の不安を解消しよう先日、「サラリーマン型フリーランスSE」という働き方を紹介するYouTube動画を視聴しましたので、その内容をご紹介します。「サ...

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

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

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のIsNullを除外する。

4) 3)の状態でDemoサービスクラスの「DemoApplication.java」を実行した結果は以下の通りで、prop1,prop3を利用した場合は2)と同じで、prop2を利用した場合は(propNumがNULL値のため)1件もレコードが検索されないことが確認できる。

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') - IsNull(5, 0)
AND CONVERT(DATETIME, CONCAT(FORMAT(birth_year,'0000')
        , FORMAT(birth_month,'00'), FORMAT(birth_day,'00')))
  <= CONVERT(DATETIME, '19900620') + IsNull(5, 0)

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') - NVL(5, 0)
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') + NVL(5, 0)

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') - IFNULL(5, 0)
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') + IFNULL(5, 0)

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) - COALESCE(5, 0)
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) + COALESCE(5, 0)