mybatisでstring型を動的パラメータ(${ })で渡そうとしたらエラー MyBatisSystemException
事前説明
mybatis上でSQLにパラメータを動的パラメータを渡す方法は2通りあります。
#{ }
実行時にSQLに合わせてパラメータの型を変えてくれる。(”で囲むとか)
用途:where句の条件を動的にするとか。
mybatis上のSQL記述内容
(pogeはchar型だとする)
1 2 3 4 5 6 7 | SELECT hoge, poge, FROM hoge_table WHERE poge = #{pogeNumber} |
↓
pogeNumberに1を設定。
↓
実際に処理されるSQL文
1 2 3 4 5 6 7 | SELECT hoge, poge, FROM hoge_table WHERE poge = '1' |
${ }
指定されたパラメータの値をそのまま渡す。
用途:SQL実行対象のテーブルや取得対象のカラムを動的にするとか。
mybatis上のSQL記述内容
1 2 3 4 5 6 7 | SELECT hoge, poge, FROM ${tableName}_table WHERE poge = '1' |
↓
${tableName}にhogeを設定。
↓
実際に処理されるSQL文
1 2 3 4 5 6 7 | SELECT hoge, poge, FROM hoge_table WHERE poge = '1' |
事象
今回は、${}を使ってテーブルを動的に変えようとしていました。
mybatis用インタフェース
1 2 3 | public interface HogeRepository { HogeDTO searchHogeTable(String pogeNumber); } |
mybatisSQL用xml
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 | <? xml version = "1.0" encoding = "UTF-8" ?> <! DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" < 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実行
#{}と同じノリで実行させようとしていました。
1 2 | String tabelName = "hoge" ; HogeDTO hogeDto = repository.searchHogeTable(tabelName); |
エラー内容
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | [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用インタフェース
1 2 3 | public interface HogeRepository { HogeDTO searchHogeTable(Map map); } |
mybatisSQL用xml
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 | <? xml version = "1.0" encoding = "UTF-8" ?> <! DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" < 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実行
1 2 3 | 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’
コメントを書く