mybatisでstring型を動的パラメータ(${ })で渡そうとしたらエラー MyBatisSystemException

mybatisでstring型を動的パラメータ(${  })で渡そうとしたらエラー MyBatisSystemException

mybatisでstring型を動的パラメータ(${ })で渡そうとしたらエラー MyBatisSystemException

事前説明

mybatis上でSQLにパラメータを動的パラメータを渡す方法は2通りあります。

#{ }

実行時にSQLに合わせてパラメータの型を変えてくれる。(”で囲むとか)
用途:where句の条件を動的にするとか。

mybatis上のSQL記述内容
(pogeはchar型だとする)

SELECT
    hoge,
    poge,
FROM
    hoge_table
WHERE
    poge = #{pogeNumber}


pogeNumberに1を設定。

実際に処理されるSQL文

SELECT
    hoge,
    poge,
FROM
    hoge_table
WHERE
    poge = '1'

${ }

指定されたパラメータの値をそのまま渡す。
用途:SQL実行対象のテーブルや取得対象のカラムを動的にするとか。

mybatis上のSQL記述内容

SELECT
    hoge,
    poge,
FROM
    ${tableName}_table
WHERE
    poge = '1'


${tableName}にhogeを設定。

実際に処理されるSQL文

SELECT
    hoge,
    poge,
FROM
    hoge_table
WHERE
    poge = '1'

事象

今回は、${}を使ってテーブルを動的に変えようとしていました。

mybatis用インタフェース

public interface HogeRepository {
    HogeDTO searchHogeTable(String pogeNumber);
}

mybatisSQL用xml

<?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="hoge.repository.HogeRepository">
    <select id="searchHogeTable" resultMap="HogeDTO" parameterType="string">
    <![CDATA[
        SELECT
            hoge,
            poge,
        FROM
            ${tableName}_table
        WHERE
            poge = '1'
    ]]>
    </select>
</mapper>

SQL実行
#{}と同じノリで実行させようとしていました。

String tabelName = "hoge";
HogeDTO hogeDto = repository.searchHogeTable(tabelName);

エラー内容

[2018/02/09 14:20:39] [main] [o.s.b.c.s.AbstractStep] [ERROR] Encountered an error executing step hoge.step01 in job hoge
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'tableName' in 'class java.lang.String'
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy17.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy19.getKinkoInfo(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
	at com.sun.proxy.$Proxy9.execute(Unknown Source)
	at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:406)
	at org.springframework.batch.core.step.tasklet.TaskletStep$ChunkTransactionCallback.doInTransaction(TaskletStep.java:330)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
	at org.springframework.batch.core.step.tasklet.TaskletStep$2.doInChunkContext(TaskletStep.java:271)
	at org.springframework.batch.core.scope.context.StepContextRepeatCallback.doInIteration(StepContextRepeatCallback.java:81)
	at org.springframework.batch.repeat.support.RepeatTemplate.getNextResult(RepeatTemplate.java:374)
	at org.springframework.batch.repeat.support.RepeatTemplate.executeInternal(RepeatTemplate.java:215)
	at org.springframework.batch.repeat.support.RepeatTemplate.iterate(RepeatTemplate.java:144)
	at org.springframework.batch.core.step.tasklet.TaskletStep.doExecute(TaskletStep.java:257)
	at org.springframework.batch.core.step.AbstractStep.execute(AbstractStep.java:200)
	at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:148)
	at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
	at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67)
	at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
	at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
	at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134)
	at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
	at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:50)
	at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:128)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:362)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:592)
Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'tableName' in 'class java.lang.String'
	at org.apache.ibatis.reflection.Reflector.getGetInvoker(Reflector.java:422)
	at org.apache.ibatis.reflection.MetaClass.getGetInvoker(MetaClass.java:164)
	at org.apache.ibatis.reflection.wrapper.BeanWrapper.getBeanProperty(BeanWrapper.java:162)
	at org.apache.ibatis.reflection.wrapper.BeanWrapper.get(BeanWrapper.java:49)
	at org.apache.ibatis.reflection.MetaObject.getValue(MetaObject.java:122)
	at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextMap.get(DynamicContext.java:94)
	at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:108)
	at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:2666)
	at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:114)
	at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
	at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
	at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:467)
	at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:431)
	at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:44)
	at org.apache.ibatis.scripting.xmltags.TextSqlNode$BindingTokenParser.handleToken(TextSqlNode.java:77)
	at org.apache.ibatis.parsing.GenericTokenParser.parse(GenericTokenParser.java:78)
	at org.apache.ibatis.scripting.xmltags.TextSqlNode.apply(TextSqlNode.java:51)
	at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:33)
	at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:41)
	at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:292)
	at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 41 common frames omitted

原因

どうやらString型は#{}は使えるけど、${}に入れるパラメータとしては使えないらしい。
${}を使用する場合、Map型を使う。
またはパラメータ用クラスを作って渡す。

解決策

parameterTypeをMapに変更。

mybatis用インタフェース

public interface HogeRepository {
    HogeDTO searchHogeTable(Map map);
}

mybatisSQL用xml

<?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="hoge.repository.HogeRepository">
    <select id="searchHogeTable" resultMap="HogeDTO" parameterType="Map">
    <![CDATA[
        SELECT
            hoge,
            poge,
        FROM
            ${tableName}_table
        WHERE
            poge = '1'
    ]]>
    </select>
</mapper>

SQL実行

Map map = null;
map.put("tableName", "hoge");
HogeDTO hogeDto = repository.searchHogeTable(map);

無事SQLを実行することができました。

参考

iBatisの動的パラメータ「$」と「#」の違いについて
SpringBootMyBatisのエラー:There is no getter for property named ‘〇〇’ in ‘class java.lang.String’が解決できない
There is no getter for property named ‘tablename’ in ‘class java.lang.String’

mybatisカテゴリの最新記事