Oracleデータベースの場合、IN句で1000件を超える項目を指定するとエラーになってしまうが、他のDBではIN句に1001件の項目を指定してもエラーにならない。
今回は、MyBatisフレームワークを利用したSpring Bootアプリケーション内で、Oracle以外のデータベース(MySQL, PostgreSQL, SQL Server)上でIN句に1001件の項目を指定し実行するサンプルプログラムを作成してみたので、共有する。
前提条件
下記記事の実装が完了していること。
OracleのIN句で1000件を超える項目を指定してみたOracleデータベースの場合、IN句で1000件を超える項目を指定するとエラーになってしまうので、注意が必要である。 今回は、S...
また、下記記事の前提条件を満たしていること。
DB上でソートして上位n件のデータを取得してみた前回、Oracle上でソートして上位n件のデータを取得するSQLを作成したが、今回は他のDB上で同様のSQLを作成してみたので、共有する...
ストアドプログラムによるデータ追加
Oracle以外のデータベースについても、ストアドプログラムによってデータ追加が行える。ストアドプログラムによるデータ作成方法については、以下の記事を参照のこと。
各DB上でストアドプログラムを試してみた今回は、Oracle・MySQL・PostgreSQL・SQL Serverの各DB上でストアドプログラムを試してみたので、共有する。ス...
今回データ作成を行うために実行したSQLとSQL実行後のデータ件数はそれぞれ以下の通りで、実行後に各DB上に1001件のデータが作成されていることが確認できる。
●MySQL
truncate table USER_DATA;
DELIMITER $$
drop procedure if exists testproc$$
create procedure testproc()
begin
DECLARE i INT;
set i = 1;
while i <= 1001 do
INSERT INTO USER_DATA VALUES (i, concat('テスト プリン', i), 2012, 1, 20, MOD(i, 2) + 1, 'テスト');
set i = i + 1;
end while;
commit;
end$$
DELIMITER ;
call testproc();＜SQL実行後のデータ件数＞
●PostgreSQL
TRUNCATE TABLE USER_DATA;
do $$
begin
for I in 1..1001 loop
INSERT INTO USER_DATA VALUES (i, concat('テスト プリン', i), 2012, 1, 20, MOD(i, 2) + 1, 'テスト');
end loop;
end$$;＜SQL実行後のデータ件数＞
●SQL Server
truncate table dbo.USER_DATA;
declare @i int;
declare @ins_sql nvarchar(256);
begin
set @i = 1;
while @i <= 1001
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＜SQL実行後のデータ件数＞
「CODE×CODE」は、需要の高い技術(AWS, Python等)を習得できるプログラミングスクールスクールだった近年、さまざまな会社でクラウド（特にIaaSやPaaSのパブリッククラウド）の需要が非常に高まっていて、クラウドサービスによるシステム開...
サンプルプログラムの作成
作成したサンプルプログラムの構成は以下の通り。
なお、上記の赤枠は、前提条件のプログラムから追加／変更したプログラムである。
build.gradleの内容は以下の通りで、各DBに接続するためのJDBCライブラリを追加している。
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'
//Oracleに接続するための設定
compile files('lib/ojdbc6.jar')
//MySQLに接続するための設定
compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.21'
//PostgreSqlに接続するための設定
compile group: 'org.postgresql', name: 'postgresql', version: '42.2.16'
//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の内容は以下の通りで、各DBに接続するための接続設定を追加している。なお、Oracleの接続先については、前提条件の記事と同じ内容となっている。
server.port = 8084 # DB接続先(Oracle) spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe spring.datasource.username=USER01 spring.datasource.password=USER01 spring.datasource.driverClassName=oracle.jdbc.driver.OracleDriver # DB接続先(MySQL) spring.datasourcems.url=jdbc:mysql://localhost:3306/user01?serverTimezone=JST spring.datasourcems.username=USER01 spring.datasourcems.password=USER01 spring.datasourcems.driverClassName=com.mysql.cj.jdbc.Driver # DB接続情報(PostgreSQL) spring.datasourceps.url=jdbc:postgresql://localhost:5432/USER01 spring.datasourceps.username=USER01 spring.datasourceps.password=USER01 spring.datasourceps.driverClassName=org.postgresql.Driver # DB接続先(SQLServer) spring.datasourcess.url=jdbc:sqlserver://localhost:1433;databaseName=master spring.datasourcess.username=USER01 spring.datasourcess.password=USER01 spring.datasourcess.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
「EaseUS Todo Backup」は様々な形でバックアップ取得が行える便利ツールだったパソコン内のデータを、ファイル/パーティション/ディスク等の様々な単位でバックアップしたり、バックアップのスケジュール設定や暗号化設定も...
また、1つのSpring Bootアプリケーションから、複数DBに接続できるようにするには、Configクラスを作成する必要がある。
OracleのConfigクラスの内容は以下の通りで、DB接続に必要なデータソースプロパティ、データソース、トランザクションマネージャ、セッションファクトリを生成している。
package com.example.demo.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.example.demo.mapper.ora"}, sqlSessionFactoryRef = "sqlSessionFactoryOra")
public class DemoOraDataSourceConfig {
/**
* Oracleのデータソースプロパティを生成する
* @return Oracleのデータソースプロパティ
*/
@Bean(name = {"datasourceOraProperties"})
@Primary
@ConfigurationProperties(prefix = "spring.datasource")
public DataSourceProperties datasourceOraProperties() {
return new DataSourceProperties();
}
/**
* Oracleのデータソースを生成する
* @param properties Oracleのデータソースプロパティ
* @return Oracleのデータソース
*/
@Bean(name = {"dataSourceOra"})
@Primary
public DataSource datasourceOra(@Qualifier("datasourceOraProperties") DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
/**
* Oracleのトランザクションマネージャを生成する
* @param dataSourceOra Oracleのデータソース
* @return Oracleのトランザクションマネージャ
*/
@Bean(name = {"txManagerOra"})
@Primary
public PlatformTransactionManager txManagerOra(@Qualifier("dataSourceOra") DataSource dataSourceOra) {
return new DataSourceTransactionManager(dataSourceOra);
}
/**
* OracleのSQLセッションファクトリを生成する
* @param dataSourceOra Oracleのデータソース
* @return OracleのSQLセッションファクトリ
* @throws Exception 任意例外
*/
@Bean(name = {"sqlSessionFactoryOra"})
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceOra") DataSource dataSourceOra)
throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSourceOra);
return sqlSessionFactory.getObject();
}
}
「AOMEI Partition Assistant Standard(無料)版」は便利なパーティション管理ツールだったハードディスクの記憶領域を論理的に分割し、分割された個々の領域のことを、パーティションといいます。 例えば、以下の図の場合、C/D...
データソースプロパティは、application.propertiesに指定した「spring.datasource」から取得するようにしている。また、1つのDB接続定義には@Primaryアノテーションを付与する必要があるため、OracleのConfigクラスの各メソッドに@Primaryアノテーションを付与している。
Oracle以外のConfigクラスでは、@Primaryアノテーションを付与しない状態で、Oracleの場合と同様に、DB接続に必要なリソースを生成している。以下はMySQLの場合のソースコードであるが、他のDBも同じような内容となる。
package com.example.demo.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = {"com.example.demo.mapper.ms"}, sqlSessionFactoryRef = "sqlSessionFactoryMs")
public class DemoMsDataSourceConfig {
/**
* MySQLのデータソースプロパティを生成する
* @return MySQLのデータソースプロパティ
*/
@Bean(name = {"datasourceMsProperties"})
@ConfigurationProperties(prefix = "spring.datasourcems")
public DataSourceProperties datasourceSqlProperties() {
return new DataSourceProperties();
}
/**
* MySQLのデータソースを生成する
* @param properties MySQLのデータソースプロパティ
* @return MySQLのデータソース
*/
@Bean(name = {"dataSourceMs"})
public DataSource datasourceMs(@Qualifier("datasourceMsProperties") DataSourceProperties properties) {
return properties.initializeDataSourceBuilder().build();
}
/**
* MySQLのトランザクションマネージャを生成する
* @param dataSourceMs MySQLのデータソース
* @return MySQLのトランザクションマネージャ
*/
@Bean(name = {"txManagerMs"})
public PlatformTransactionManager txManagerSql(@Qualifier("dataSourceMs") DataSource dataSourceMs) {
return new DataSourceTransactionManager(dataSourceMs);
}
/**
* MySQLのSQLセッションファクトリを生成する
* @param dataSourceMs MySQLのデータソース
* @return MySQLのSQLセッションファクトリ
* @throws Exception 任意例外
*/
@Bean(name = {"sqlSessionFactoryMs"})
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceMs") DataSource dataSourceMs)
throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSourceMs);
return sqlSessionFactory.getObject();
}
}
Mapperインタフェースは、Configクラスの@MapperScanアノテーション内のbasePackagesで指定したパッケージ内に、それぞれ作成している。以下はOracleとMySQLの場合のソースコードであるが、他のDBも同じような内容となる。
package com.example.demo.mapper.ora;
import com.example.demo.UserData;
import org.apache.ibatis.annotations.Mapper;
import java.util.ArrayList;
@Mapper
public interface UserDataMapperOra {
/**
* Oracleで指定したIDリストをもつユーザーデータテーブル(user_data)のデータを取得する
* @param idList IDリスト
* @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ
*/
ArrayList<UserData> findByIdList(ArrayList<Long> idList);
}package com.example.demo.mapper.ms;
import com.example.demo.UserData;
import org.apache.ibatis.annotations.Mapper;
import java.util.ArrayList;
@Mapper
public interface UserDataMapperMs {
/**
* MySQLで指定したIDリストをもつユーザーデータテーブル(user_data)のデータを取得する
* @param idList IDリスト
* @return ユーザーデータテーブル(user_data)の指定したIDリストのデータ
*/
ArrayList<UserData> findByIdList(ArrayList<Long> idList);
}また、Mapperインタフェースから呼ばれるXMLファイルで、IN句を指定したSQLを記載している。以下はOracleとMySQLの場合のソースコードであるが、他のDBも同じような内容となる。
<?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.ora.UserDataMapperOra">
<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 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">
<mapper namespace="com.example.demo.mapper.ms.UserDataMapperMs">
<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 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>
サラリーマン型フリーランスSEという働き方でお金の不安を解消しよう先日、「サラリーマン型フリーランスSE」という働き方を紹介するYouTube動画を視聴しましたので、その内容をご紹介します。 「サ...
さらに、Spring Bootのメインクラスの内容は以下の通りで、各DB上で定義したSQLを実行できるようにしている。
package com.example.demo;
import com.example.demo.mapper.ms.UserDataMapperMs;
import com.example.demo.mapper.ora.UserDataMapperOra;
import com.example.demo.mapper.ps.UserDataMapperPs;
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 {
/** OracleのUSER_DATAテーブルにアクセスするMapper */
@Autowired
private UserDataMapperOra userDataMapperOra;
/** MySQLのUSER_DATAテーブルにアクセスするMapper */
@Autowired
private UserDataMapperMs userDataMapperMs;
/** PostgreSQLのUSER_DATAテーブルにアクセスするMapper */
@Autowired
private UserDataMapperPs userDataMapperPs;
/** 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) {
// IN句を指定したSQLの実行結果を確認する
// 引数のIDリストに1000件のデータを指定した場合(Oracle)
ArrayList<Long> idList = getIdListOneThousand();
System.out.println("*** 引数のIDリストに1000件のデータを指定した場合(Oracle) ***");
System.out.println("*** idList size : " + idList.size());
ArrayList<UserData> userDataList = userDataMapperOra.findByIdList(idList);
System.out.println("*** userDataList size : " + userDataList.size());
System.out.println();
// 引数のIDリストに1001件のデータを指定した場合(Oracle)
idList = getIdListOneThousandOne();
System.out.println("*** 引数のIDリストに1001件のデータを指定した場合(Oracle) ***");
System.out.println("*** idList size : " + idList.size());
try{
userDataMapperOra.findByIdList(idList);
}catch(Exception ex){
System.out.println("*** " + ex.getCause());
}
// 引数のIDリストに1001件のデータを指定した場合(MySQL)
System.out.println("*** 引数のIDリストに1001件のデータを指定した場合(MySQL) ***");
System.out.println("*** idList size : " + idList.size());
userDataList = userDataMapperMs.findByIdList(idList);
System.out.println("*** userDataList size : " + userDataList.size());
System.out.println();
// 引数のIDリストに1001件のデータを指定した場合(PostgreSQL)
System.out.println("*** 引数のIDリストに1001件のデータを指定した場合(PostgreSQL) ***");
System.out.println("*** idList size : " + idList.size());
userDataList = userDataMapperPs.findByIdList(idList);
System.out.println("*** userDataList size : " + userDataList.size());
System.out.println();
// 引数のIDリストに1001件のデータを指定した場合(SQLServer)
System.out.println("*** 引数のIDリストに1001件のデータを指定した場合(SQLServer) ***");
System.out.println("*** idList size : " + idList.size());
userDataList = userDataMapperSs.findByIdList(idList);
System.out.println("*** userDataList size : " + userDataList.size());
System.out.println();
}
/**
* 1～1000までのLong型のリストを返却する
* @return 1～1000までのLong型のリスト
*/
private ArrayList<Long> getIdListOneThousand(){
ArrayList<Long> idList = new ArrayList<>();
for(int i = 1; i <= 1000; i++){
idList.add(Long.valueOf(i));
}
return idList;
}
/**
* 1～1001までのLong型のリストを返却する
* @return 1～1001までのLong型のリスト
*/
private ArrayList<Long> getIdListOneThousandOne(){
ArrayList<Long> idList = getIdListOneThousand();
idList.add(Long.valueOf(1001));
return idList;
}
}その他のソースコード内容は、以下のサイトを参照のこと。
https://github.com/purin-it/java/tree/master/spring-boot-db-in-over1000/demo
【PR】「Filmora」は初心者でも本格的な動画編集ができる大変便利なツールだった「Filmora」は初心者でも使いやすい動画編集ツールで、テンプレートとして利用できるテキスト・動画・音楽などが充実していると共に、複数...
サンプルプログラムの実行結果
Spring Bootのメインクラス(DemoApplication.java)を実行した結果、コンソールログに出力される内容は以下の通り。
上記実行結果は、青枠部分が前提条件の記事のOracleの場合の実行結果となっていて、赤枠部分がMySQL, PostgreSQL, SQLServerの実行結果となっている。Oracle以外については、IN句が1001件の場合も正常に実行できることが確認できる。
「DesignEvo」は多くのテンプレートからロゴを簡単に作成できるツールだった多くのテンプレートが用意されていてロゴを簡単に作成できるツールの一つに、「DesignEvo」があります。今回は、「DesignEvo」...
ただし、以下のサイトの情報を見ると、各DB上でIN句の上限はそれぞれあるようだ。
●MySQL, PostgreSQLの場合
https://workmemo.techblog.jp/archives/36467399.html
●SQL Serverの場合
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14171212004
要点まとめ
- Oracle以外のデータベース(MySQL, PostgreSQL, SQL Server)では、IN句に1001件の項目を指定してもエラーにならない。ただし上限はある。
- 1つのSpring Bootアプリケーションから、複数DBに接続できるようにするには、Configクラスを作成し、DB接続に必要なデータソースプロパティ、データソース、トランザクションマネージャ、セッションファクトリを生成する処理を追加する。また、そのうちの1つのDB接続定義には@Primaryアノテーションを付与する必要がある。
