Spring Framework 入門記 JDBCその7 StoredProcedure前編

Java Spring

遅い時間になってしまいましたが，一応は平日の日課なので，自宅からの入門記です．
SqlQuery，SqlUpdateに続いて，今回は

  • StoredProcedure

です．StoredProcedureというくらいですから，ストアドプロシジャ／ファンクションを実行するためのクラスです．で，内部ではJDBCのCallableStatementを使っているようです．
恥ずかしながら私，CallableStatementを使ったことがありません．心より恥じる．というのもですね，Oracleだとかなり昔(JDBC2.0になる前)からPreparedStatementでストアドが呼び出せていたからなんですね．うーみゅ，だからといってさぼってちゃいかんなぁ．
そんなわけでStoredProcedureですが，例によって

    • dataSource
    • sql
    • types

といったプロパティを持っています．
ただし，sqlプロパティに設定するのはストアドの名前だけです．CallableStatementを使う場合，

{?= call <procedure-name>[<arg1>,<arg2>, ...]}

みたいなエスケープ構文が必要っぽいですが，このような文字列を組み立てるのはStoredProcedureがやってくれます．なので，sqlプロパティには上記の<procedure-name>の部分だけを渡せばいいようです．
上記プロパティに加えて，

    • function

というbooleanのプロパティが用意されています．ストアドファンクションから戻り値を得る場合はこのプロパティにtrueを設定する必要があります．デフォルトはfalseです．
それから，RdbmsOperationから継承したtypesプロパティですが，ストアドのパラメータにOUTなものがある場合は使えません．そんな場合は，

    • void declareParameter(SqlParameter param)

を使ってパラメータの情報を与える必要があるみたい．っていうか，setTypes(int[])は中でdeclareParameter(SqlParameter)を呼び出しているんですね．ということは，前回作ったJdbcTypeやそのPropertyEditorなんて必要なくて，SqlParameterのPropertyEditorを作るべきだったようです．心より恥じる．
それはともかくですね，ストアドのパラメータの数分だけ，declareParameter(SqlParameter)を呼び出すわけですが，ストアドの引数がINかOUTかによって渡すインスタンスの型を変える必要があるようです．

INの場合
SqlParameterのインスタンスを渡します．
OUTの場合
SqlOutParameterのインスタンスを渡します．

それからもしかすると，結果セットを返すOUTパラメータの場合はSqlReturnResultSetのインスタンスを渡す必要があるかもしれません．いや，SqlOutParameterも結果セット扱えるみたいだが．．． よくわかりません．心より恥じる．
さらに，INOUTの場合はどうるんだろう？ それもよくわかりません．．． 心より恥じる．恥じりまくりだなぁ．
ということで

  • SqlParameter

ですが，これは，次のプロパティを持っています．

    • name
    • sqlType
    • typeName

SqlParameterはイミュータブルなので，これらはコンストラクタで設定します．
次に

  • SqlOutParameter

ですが，これはSqlParameterの派生クラスなので，上記のプロパティも持っています．それに加えて，コンストラクタで

  • RowCallbackHandler
  • RowMapper

のいずれかを設定することができます．でも，たいていは意識しなくていいみたい．たぶん．．． 無念だ．
という具合になんかとっても大変そうなのですが，要はsqlプロパティにストアドの名前を設定して，戻り値があればfunctionプロパティをtrueにして，ストアドのパラメータに応じてdeclareParameter(SqlParameter)を呼び出せば，準備完了です．．． 完了なのは準備だけですか．無念だ．
ということで，準備ができたらストアドの呼び出しです．それには，

    • Map execute(Map inParams)
    • Map execute(ParameterMapper inParamMapper)

のいずれかを使います．
execute(Map)の場合，declareParameter(SqlParameter)で設定したSqlParameterのnameプロパティの値をキーとしてinParamsから取得した値がパラメータの値になります．うーむ，今の文，日本語としてどうよ？ 後で呼んだらわけわかだよなぁ．
execute(ParameterMapper)の場合は，ParameterMapperのcreateMap(Connection)が返すMapが使われるだけで，execute(Map)とあまり変わらないような？ Map作るのにConnectionが必要な場合って？ 想像が付きません．引数にCallableStatementが渡されて，好きに設定できるのなら分かるんですが．うーみゅ．


いかんなぁ，今日は書いていてイマイチ理解が進まない感じ．いいんだい，そんな場合は手を動かすのさ！ ということで，お試しタぁイム．
「Reference Documentation」の「9.4.4. StoredProcedure」では，OracleのSYSDATEを呼び出すようなStoredProcedureの派生クラス(しかも内部クラスだ)を作っています．そう，またしてもStoredProcedureは抽象クラスなんですねぇ．でも，その派生クラスでやっていることって，プロパティの設定とdeclareParameter(SqlParameter)の呼び出しくらいなんですよね．それくらいだったら，箱から出してすぐ使えるようにしておいてくれよぉ〜．
ということで，前回同様すぐに使えるStoredProcedureの派生クラスを作ってみましょう．ということでまずは，SqlParameterおよびSqlOutParameterのPropertyEditorです．ちょっと悩んだのですが，これらの文字列表現を

IN,name,type
OUT,name,type

ということにして，前者ならSqlParameter，後者ならSqlOutParameterのインスタンスを返すことにします．
ということで，こんな感じ．

package study;
import java.beans.PropertyEditorSupport;
import java.lang.reflect.Field;
import java.sql.Types;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;

public class SqlParameterEditor extends PropertyEditorSupport {
    public void setAsText(String text) throws IllegalArgumentException {
        String[] tokens = text.split(",");
        if (tokens.length != 3) {
            throw new IllegalArgumentException("Could not parse SqlParameter : " + text);
        }
        String kind = tokens[0];
        String name = tokens[1];
        String typeName = tokens[2];

        try {
            Field field = Types.class.getField(typeName);
            int type = field.getInt(null);
            if ("IN".equalsIgnoreCase(kind)) {
                setValue(new SqlParameter(name, type, typeName));
            }
            else if ("OUT".equalsIgnoreCase(kind)) {
                setValue(new SqlOutParameter(name, type, typeName));
            }
            else {
                throw new IllegalArgumentException("Illegal IN/OUT type : " + kind);
            }
        }
        catch (Exception e) {
            throw (IllegalArgumentException)
                new IllegalArgumentException("Illegal jdbc type name : " + typeName).initCause(e);
        }
    }
    public String getAsText() {
        SqlParameter sqlParameter = (SqlParameter) getValue();
        if (sqlParameter instanceof SqlOutParameter) {
            return "OUT" + "," + sqlParameter.getTypeName() + "," + sqlParameter.getName();
        }
        else {
            return "IN" + "," + sqlParameter.getTypeName() + "," + sqlParameter.getName();
        }
    }
}

このPropertyEditorをfactory.xmlに組み込みます．このあたりの話はまたしても「Property Editorだよ」を参照．
そして，StoredProcedureの派生クラス．

package study;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class MyStoredProcedure extends StoredProcedure {
    public MyStoredProcedure() {
        super();
    }
    public MyStoredProcedure(DataSource ds, String name) {
        super(ds, name);
    }
    public MyStoredProcedure(JdbcTemplate jdbcTemplate, String name) {
        super(jdbcTemplate, name);
    }
    public void setSqlParameters(SqlParameter[] parameters) {
        for (int i = 0; i < parameters.length; ++i) {
            declareParameter(parameters[i]);
        }
    }
}

うーみゅ，またしてもMy〜にしてしまった．心より恥じる．←もはや句読点並大安売り．
そして定義ファイル．

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans
    PUBLIC "-//SPRING//DTD BEAN//EN"
    "http://www.springframework.org/dtd/spring-beans.dtd"
>
<beans>
    <bean id="autoProxyCreator" 
        class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
        <property name="beanNames">
            <value>foo</value>
        </property>
        <property name="interceptorNames">
            <value>transactionInterceptor,trace</value>
        </property>
    </bean>

    <bean id="storedProcedure" class="study.MyStoredProcedure">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
        <property name="sql">
            <value>CURRENT_DATE</value>
        </property>
        <property name="function">
            <value>true</value>
        </property>
        <property name="sqlParameters">
            <list>
                <value>OUT,date,DATE</value>
            </list>
        </property>
    </bean>
</beans>

HSQLDBなのでSYSDATEではなくCURRENT_DATEを使ってみました．
で，実行用のクラス．

package study;
import java.util.HashMap;
import java.util.Map;
import org.springframework.beans.factory.access.BeanFactoryLocator;
import org.springframework.beans.factory.access.BeanFactoryReference;
import org.springframework.context.ApplicationContext;
import org.springframework.context.access.ContextSingletonBeanFactoryLocator;
import org.springframework.jdbc.object.StoredProcedure;

public class Main {
    public static void main(String[] args) {
        try {
            BeanFactoryLocator locator = ContextSingletonBeanFactoryLocator.getInstance();
            BeanFactoryReference ref = locator.useBeanFactory("context");
            ApplicationContext context = (ApplicationContext) ref.getFactory();

            StoredProcedure storedProcedure = (StoredProcedure) context.getBean("storedProcedure");
            Map result = storedProcedure.execute(new HashMap());
            System.out.println(result);

            ref.release();
        }
        catch (Throwable e) {
            e.printStackTrace();
        }
    }
}

これを実行！！

java.sql.SQLException: This function is not supported
    at org.hsqldb.Trace.getError(Unknown Source)
    at org.hsqldb.Trace.error(Unknown Source)
    at org.hsqldb.jdbcPreparedStatement.getNotSupported(Unknown Source)
    at org.hsqldb.jdbcPreparedStatement.registerOutParameter(Unknown Source)
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:183)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:520)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:549)
    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
    at study.Main.main(Main.java:19)

ぐはぁっ，何が起きたわけ？
うーみゅ，どうやらHSQLDBの1.7.1ではCallableStatementはまともに実装されていないっぽい．
しょうがないので1.7.2RC5で再度実行！！！！

java.sql.SQLException: Unknown JDBC escape sequence: {
    at org.hsqldb.jdbc.jdbcConnection.nativeSQL(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.(Unknown Source)
    at org.hsqldb.jdbc.jdbcCallableStatement.(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareCall(Unknown Source)
    at org.seasar.extension.dbcp.impl.ConnectionWrapperImpl.prepareCall(ConnectionWrapperImpl.java:115)
    at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:150)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:520)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:549)
    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:102)
    at study.Main.main(Main.java:19)

ぐはぁっ，今度は何だっていうわけっ！？
うーみゅ，StoredProcedureは

{? = call CURRENT_DATE()}

というSQL文字列を組み立ててprepareCall(String)を呼び出すのですが，HSQLDBはそれを理解できないらしい．．． 使えないじゃん！
それとも自分が間違っているのかなぁ？
よくわからないので，明日職場のOracleで再度挑戦することにします．なので今日は前編，明日の後編に続きます．
心より恥じる．


2004/05/13 追記
Oracleで試してみたところ，SqlParameterEditorに問題があることが分かりました．無念だ．
SqlParameterのtypeNameって，オブジェクト型など構造化型の場合の型名を示すものなのですね．なので，普通のTIMESTAMPなどの場合はnullにしておかなければいけないようで，見事にはまってしまいました．心より恥じる．
そこを修正してsqlプロパティを"SYSDATE"にしたところ，すんなり動きました．
とはいえSYSDATEだけではイマイチな感じもするわけで，大して変わらないけれど

    <bean id="storedProcedure" class="study.MyStoredProcedure">
        <property name="dataSource">
            <ref bean="dataSource"/>
        </property>
        <property name="sql">
            <value>DBMS_DEBUG.PROBE_VERSION</value>
        </property>
        <property name="function">
            <value>false</value>
        </property>
        <property name="sqlParameters">
            <list>
                <value>OUT,major,INTEGER</value>
                <value>OUT,minor,INTEGER</value>
            </list>
        </property>
    </bean>

として実行すると

{minor=4, major=2}

という感じになりました．
おしまい．