kagamihogeの日記

kagamihogeの日記です。

springのSimpleJdbcCallでストアドプロシージャを呼び出す

Java Oracle Spring Boot

springのDBアクセスにはJdbcTemplateが従来から存在しており、現在では後継のJdbcClientも利用可能である。ストアドプロシージャにはSimpleJdbcCallを使用する。

本エントリの注意点として、私はあまりOracleのストアドプロシージャに詳しくない。そのため用語を正しく使えていない可能性がある。

環境

  • Oracle Database 23ai Free Release 23.0.0.0.0
docker run --name oracle_queue -p 11521:1521 -e ORACLE_PWD="Oracle23" container-registry.oracle.com/database/free:23.5.0.0-lite

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.1'
    id 'io.spring.dependency-management' version '1.1.7'
}

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

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    compileOnly 'org.projectlombok:lombok'
    developmentOnly 'org.springframework.boot:spring-boot-devtools'
    runtimeOnly 'com.oracle.database.jdbc:ojdbc11'
    annotationProcessor 'org.projectlombok:lombok'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
    useJUnitPlatform()
}

以降のJavaのサンプルコードにはimportやクラス宣言などは省略するが、おおよそ以下のようになっている。

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

@Service
@RequiredArgsConstructor
public class StoredSample {

  final DataSource ds;

また、ストアドプロシージャは説明のための手抜きでSYSTEMで作成する。

説明

PROCEDURE - 引数無し・戻り値無し

動作確認用に以下のPROCEDUREを作成する。

CREATE OR REPLACE PROCEDURE simple_proc
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('');
END;

  public void simpleProc() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withProcedureName("simple_proc");

    Map<String, Object> result = jdbc.execute();
    System.out.println(result);
  }

実行結果は {} と出力される。

withSchemaNameでそのプロシージャが存在するスキーマを指定し、withProcedureNameでプロシージャの名前を指定し、executeで実行する。

SYSTEM.simple_procのようには指定出来ない。戻り値が何もない場合は単に空のmapが返される。

PROCEDURE - 引数有り・戻り値無し

以下は意味の無いコードだが、動作確認としてOracleのDBMS_OUTPUT.PUT_LINEを呼び出す。

  public void putLine() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("DBMS_OUTPUT")
        .withProcedureName("PUT_LINE")
        .withoutProcedureColumnMetaDataAccess()
        .declareParameters(
            new SqlParameter("inputString", Types.VARCHAR)
        );

    Map<String, Object> result = jdbc.execute("asdf");
    System.out.println(result);

    MapSqlParameterSource s = new MapSqlParameterSource();
    s.addValue("inputString", "asdfasdasd");

    String result1 = jdbc.executeFunction(String.class, s);
    System.out.println(result1);
  }

declareParametersで呼び出すストアドの引数を宣言する。SqlParameterのkeyはspring側での識別名なので必ずしもストアドと一致する必要は無いらしい。

executeで引数を指定して実行する。

または、executeFunctionとSqlParameterSourceの組み合わせも可能。こちらの場合keyはSqlParameterのと一致の必要がある。

実行結果は以下の通り。executeFunctionは戻り値型を指定可能だが、存在しなければnullになるようだ。

{}
null

FUNCTION - 引数有り・戻り値有り・複数の出力パラメータ

サンプルとして以下のfunctionを使用する。

CREATE OR REPLACE function sample_func3( 
    inStr1 IN nvarchar2
    , outStr OUT VARCHAR2
    , outInt OUT NUMBER
)
return VARCHAR2
AS
BEGIN
  outStr := inStr1 || 'hoge';
  outInt := 123;
  return 'returnStr';
END sample_func3;

ここでは「戻り値」はreturnの値、「出力パラメータ」はOUTを指すものとする（oracleの正確な用語かどうかは自身無し）。

  public void sampleFunc3() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withFunctionName("sample_func3")
        .declareParameters(
            new SqlParameter("inputString", Types.VARCHAR));

    Map<String, Object> result = jdbc.execute("asdf");
    System.out.println(result);

    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("INSTR1", "asdfasdasd");
    String result1 = jdbc.executeFunction(String.class, param);
    System.out.println(result1);
  }

実行結果は以下の通り。

{return=returnStr, OUTSTR=asdfhoge, OUTINT=123}
returnStr

functionなのでwithFunctionNameを使用する。

実行結果の通り、executeの戻り値Mapにはストアドの戻り値プラス出力パラメータの両方を含む。対してexecuteFunctionの戻り値はストアドの戻り値になるようだ。

execute

このメソッドのパラメータの型の違いによる違いはほぼ見たまま。

  public void sampleFunc3() {
    SimpleJdbcCall jdbc = new SimpleJdbcCall(ds)
        .withSchemaName("SYSTEM")
        .withFunctionName("sample_func3");

    jdbc.execute("asdf");
    jdbc.execute(Map.of("INSTR1", "asdf") );

    MapSqlParameterSource param = new MapSqlParameterSource();
    param.addValue("INSTR1", "asdfasdasd");
    jdbc.execute(param);
  • Object...はthe same order as the parameters are defined for the stored procedureとの事なのでストアドの定義順。
  • Map<String,?>はthe parameter values to be used in the callとの事なのでkey名で指定。
  • SqlParameterSourceは専用クラスなだけでMapとほぼ同等と思われる。

補足

自ユーザ以外所有のストアド実行にはwithoutProcedureColumnMetaDataAccessが必要

https://vkuzel.com/calling-oracle-function-with-spring-5-3-simple-jdbc-tools に解説がある。

If a function is located in another schema and only a synonym is available in local schema, we have to disable Spring's metadata resolution via the SimpleJdbcCall.withoutProcedureColumnMetaDataAccess() method.

The reason is, Spring resolves metadata of a called objects from all_procedures and all_arguments tables. While doing so, it checks whether owner of procedure or argument is equal to the current schema which is resolved from connection, e.g. all_arguments.owner = 'local_schema_name' AND all_procedures.owner = 'local_schema_name'.

Because the real owner is located elsewhere, the metadata resolution fails on the SQLException: Missing IN or OUT parameter at index:: 1 error.

https://vkuzel.com/calling-oracle-function-with-spring-5-3-simple-jdbc-tools Calling a function synonym より抜粋

springはRDBMSのメタデータを確認してストアド呼出を組み立てる。具体的にはOracleではall_argumentsや all_proceduresを参照するらしい。それで、自分がオーナーでは無いストアドだとメタデータ参照に失敗してしまい、エラーになるらしい。したがって、それを無視する設定withoutProcedureColumnMetaDataAccessが必要となる、とのこと。

declareParametersは必ずしも必要では無い？

ちょっと調べ切れなかったのだが、該当メソッドのjavadocには以下のような記述がある。多分だけど、メタデータだとか使い方だとか、その辺の兼ね合いによっては明示的なパラメータ宣言は不要なのだと思う。

public SimpleJdbcCall declareParameters(SqlParameter... sqlParameters)

Specify one or more parameters if desired. These parameters will be supplemented with any parameter information retrieved from the database meta-data. https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/simple/SimpleJdbcCall.html#declareParameters(org.springframework.jdbc.core.SqlParameter...)

OBJECT型とかは使えない？

調べた限りでは以下のようなOBJECT型をパラメータや戻り値に使用するストアドはSimpleJdbcCallからは呼び出せないように見える。呼び出し方も分からなかったし、OBJECT型とJavaのマッピング方法も分からなかった。

CREATE TYPE MYMESG AS OBJECT (
  ID    NUMBER(10,0),
  MESG  VARCHAR2(30)
);

CREATE OR REPLACE function sample_func5 return SYSTEM.MYMESG
IS
  msg SYSTEM.MYMESG;
BEGIN
 msg := SYSTEM.MYMESG(1, 'asd');
 return msg;
END sample_func5;

JdbcTemplateでは以下のように呼び出すらしい。ただ、呼び出せる事は出来たが構造体のマッピング方法は分からなかった。なので、以下のサンプルコードは構造体を直接ではなくその中身を取り出してから、にしている。

import java.sql.Types;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import lombok.RequiredArgsConstructor;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.CallableStatementCreatorFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.stereotype.Service;

@Service
@RequiredArgsConstructor
public class StoreadSample2 {

  final DataSource ds;

  public void sampleFunc5() {
    CallableStatementCreatorFactory factory = new CallableStatementCreatorFactory("""
        declare
         msg SYSTEM.MYMESG;
        begin
         ? := SYSTEM.sample_func5().MESG;
        end;
        """, List.of(new SqlOutParameter("result", Types.VARCHAR)));
    CallableStatementCreator creator = factory.newCallableStatementCreator(Map.of());
    JdbcTemplate jdbc = new JdbcTemplate(ds);

    Map<String, Object> result = jdbc.call(
        creator,
        List.of(new SqlOutParameter("result", Types.VARCHAR)));
    System.out.println(result);
  }
}

ハマった点

Unable to determine the correct call signature for 'SYSTEM.SIMPLE_PROC'

.withProcedureName("SYSTEM.simple_proc") のようにスキーマとプロシージャ名を一緒に指定するとこのエラーになる。

org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature for 'SYSTEM.SIMPLE_PROC' - package name should be specified separately using '.withCatalogName("SYSTEM")'

Missing IN or OUT parameter at index: 1とかwrong number or types of arguments in call to

withoutProcedureColumnMetaDataAccessが必要。詳細な理由は補足の項を参照。

以下のエラーログはDBMS_OUTPUT.PUT_LINEをwithProcedureNameで呼び出した場合。

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call DBMS_OUTPUT.PUT_LINE()}]
...
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

以下のエラーログはwithFunctionNameで呼び出した場合。おそらく、メタデータ参照に失敗し、さらにfunctionなので存在しない戻り値にプレースホルダを自動的に割り当ててしまっている。そのため、withProcedureNameとは異なるエラーになる、と推測する。

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call DBMS_OUTPUT.PUT_LINE()}]; SQL state [99999]; error code [17041]; ORA-17041: Missing IN or OUT parameter at index: 1
https://docs.oracle.com/error-help/db/ora-17041/
...
Caused by: java.sql.SQLException: ORA-17041: Missing IN or OUT parameter at index: 1

BadSqlGrammarException (略) bad SQL grammar [{call SYSTEM.SAMPLE_FUNC3(?, ?, ?, ?)}]

withFunctionNameとすべきところをwithProcedureNameにしてしまうと以下のようなエラーになる。

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call SYSTEM.SAMPLE_FUNC3(?, ?, ?, ?)}]
..
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SAMPLE_FUNC3'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

参考URL