SQL ServerのIN句で2100件程度の項目を指定してみた｜ITエンジニアとして経験・学習したこと

SQL Serverの場合、SQLのIN句で1,001件の項目を指定してもエラーにならないが、2,100件程度の項目を指定すると、「着信要求のパラメーターが多すぎます。サーバーがサポートするパラメーターは最大2100個です。」というエラーが発生してしまう。

今回は、SQL Serverで、SQLのIN句で2,100件程度の項目を指定した場合の動作を確認してみたので、共有する。

前提条件

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

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

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

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

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

Spring Bootのメインクラスの内容は以下の通りで、SQL Serverに接続し、WHERE句内のIN句で2098件・2099件のデータを指定したSQLを実行している。

package com.example.demo;

import com.example.demo.mapper.ss.UserDataMapperSs;
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.ArrayList;

@SpringBootApplication
public class DemoApplication implements CommandLineRunner {

    /**
     * SQL ServerのUSER_DATAテーブルにアクセスするMapper
     */
    @Autowired
    private UserDataMapperSs userDataMapperSs;

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

    @Override
    public void run(String... args) {
        // 引数のIDリストに2098件のデータを指定した場合(SQLServer)
        ArrayList<Long> idList = getIdListNoErrorMax();
        System.out.println("*** 引数のIDリストに2098件のデータを指定した場合(SQLServer) ***");
        System.out.println("*** idList size : " + idList.size());

        ArrayList<UserData> userDataList = userDataMapperSs.findByIdList(idList);
        System.out.println("*** userDataList size : " + userDataList.size());
        System.out.println();

        // 引数のIDリストに2099件のデータを指定した場合(SQLServer)
        idList = getIdListErrorMin();
        System.out.println("*** 引数のIDリストに2099件のデータを指定した場合(SQLServer) ***");
        System.out.println("*** idList size : " + idList.size());

        try {
            userDataMapperSs.findByIdList(idList);
        } catch (Exception ex) {
            System.out.println("*** " + ex.getCause());
        }
    }

    /**
     * 1～2098までのLong型のリストを返却する
     * @return 1～2098までのLong型のリスト
     */
    private ArrayList<Long> getIdListNoErrorMax() {
        ArrayList<Long> idList = new ArrayList<>();
        for (int i = 1; i <= 2098; i++) {
            idList.add(Long.valueOf(i));
        }
        return idList;
    }

    /**
     * 1～2099までのLong型のリストを返却する
     * @return 1～2099までのLong型のリスト
     */
    private ArrayList<Long> getIdListErrorMin() {
        ArrayList<Long> idList = getIdListNoErrorMax();
        idList.add(Long.valueOf(2099));
        return idList;
    }
}

package com.example.demo;

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

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.ArrayList;

@SpringBootApplication

public class DemoApplication implements CommandLineRunner {

/**

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

@Autowired

private UserDataMapperSs userDataMapperSs;

public static void main(String[] args) {

SpringApplication.run(DemoApplication.class, args);

}

@Override

public void run(String... args) {

// 引数のIDリストに2098件のデータを指定した場合(SQLServer)

ArrayList<Long> idList = getIdListNoErrorMax();

System.out.println("*** 引数のIDリストに2098件のデータを指定した場合(SQLServer) ***");

System.out.println("*** idList size : " + idList.size());

ArrayList<UserData> userDataList = userDataMapperSs.findByIdList(idList);

System.out.println("*** userDataList size : " + userDataList.size());

System.out.println();

// 引数のIDリストに2099件のデータを指定した場合(SQLServer)

idList = getIdListErrorMin();

System.out.println("*** 引数のIDリストに2099件のデータを指定した場合(SQLServer) ***");

System.out.println("*** idList size : " + idList.size());

try {

userDataMapperSs.findByIdList(idList);

} catch (Exception ex) {

System.out.println("*** " + ex.getCause());

}

/**

* 1～2098までのLong型のリストを返却する

* @return 1～2098までのLong型のリスト

private ArrayList<Long> getIdListNoErrorMax() {

ArrayList<Long> idList = new ArrayList<>();

for (int i = 1; i <= 2098; i++) {

idList.add(Long.valueOf(i));

}

return idList;

}

/**

* 1～2099までのLong型のリストを返却する

* @return 1～2099までのLong型のリスト

private ArrayList<Long> getIdListErrorMin() {

ArrayList<Long> idList = getIdListNoErrorMax();

idList.add(Long.valueOf(2099));

return idList;

}

フリエン(furien)は多くの案件を保有しフリーランス向けサービスも充実しているエージェントだったフリエン(furien)は、ITフリーランス（個人事業主）エンジニア専門のエージェントであるアン・コンサルティング株式会社が運営する業界...

また、今回は修正していないが、Spring Bootのメインクラス内で実行するSQLのMapperインタフェース・Mapper XMLの内容は以下の通り。

package com.example.demo.mapper.ss;

import com.example.demo.UserData;
import org.apache.ibatis.annotations.Mapper;
import java.util.ArrayList;

@Mapper
public interface UserDataMapperSs {

    /**
     * SQL Serverで指定したIDリストをもつユーザーデータテーブル(user_data)のデータを取得する
     * @param idList IDリスト
     * @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ
     */
    ArrayList<UserData> findByIdList(ArrayList<Long> idList);
}

package com.example.demo.mapper.ss;

import com.example.demo.UserData;

import org.apache.ibatis.annotations.Mapper;

import java.util.ArrayList;

@Mapper

public interface UserDataMapperSs {

/**

* SQL Serverで指定したIDリストをもつユーザーデータテーブル(user_data)のデータを取得する

* @param idList IDリスト

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

ArrayList<UserData> findByIdList(ArrayList<Long> idList);

}

<?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.UserDataMapperSs">
    <select id="findByIdList" parameterType="java.util.List" 
        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
        FROM dbo.USER_DATA
        <where>
            <if test="list != null and list.size() > 0">
                id in
                <foreach item="item" open="(" close=")" 
                    collection="list" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </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="findByIdList" parameterType="java.util.List"

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

FROM dbo.USER_DATA

<where>

id in

<foreach item="item" open="(" close=")"

collection="list" separator=",">

#{item}

</foreach>

</if>

</where>

</select>

</mapper>

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

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

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

サンプルプログラムの実行結果は以下の通りで、SQLのIN句で2,100件程度の項目を指定するとエラーになることが確認できる。

1) 以下を実行し、USER_DATAテーブルに2,100件のデータを追加する。

truncate table dbo.USER_DATA;
declare @i int;
declare @ins_sql nvarchar(256);
begin
   set @i = 1;
   while @i <= 2100
   begin
      set @ins_sql = 'insert into dbo.USER_DATA values (' + STR(@i) 
          + ', concat(N''テストプリン'', ' + STR(@i) + '), 2012, 1, 20, ' 
          + STR(@i % 2 + 1) + ', N''テスト'');'
      execute(@ins_sql);
      set @i = @i + 1;
   end
end