怎么在ibatis使用$往in里面传String参数

码拜

10年 ago

ibatis sql oracle 我将参数设置成 bizCode=”””BIZ-NATIONAL-SOUTUDI””,””BIZ-NATIONAL-DEFUALT”””; 在ibatis里面<select id=”queryListTblBcsShare” parameterType=”com.ums.bcs.front.model.Share” resultMap=”ShareResultMap”> select BIZ_ID, BIZ_CODE, DETAIL_ID, IS_CUS_URL, BIZ_CLIENT_VERSION, BIZ_STATUS, SHARE_URL, SHARE_TYPE from TBL_BCS_SHARE where (BIZ_CODE in (${bizCode} $) and SHARE_TYPE=#{shareType,jdbcType=VARCHAR}) or (BIZ_CODE is null and SHARE_TYPE=#{shareType,jdbcType=VARCHAR}) or(BIZ_CODE=#{bizCode,jdbcType=VARCHAR} and SHARE_TYPE is null) or(BIZ_CODE is null and SHARE_TYPE is null) order by BIZ_CODE nulls last,SHARE_TYPE nulls last </select> 然后在控制台报错： BIZ_CODE, DETAIL_ID, IS_CUS_URL, BIZ_CLIENT_VERSION, BIZ_STATUS, SHARE_URL, SHARE_TYPE from TBL_BCS_SHARE where (BIZ_CODE in (“”BIZ-NATIONAL-SOUTUDI””,””BIZ-NATIONAL-DEFUALT”” $) and SHARE_TYPE=?) or (BIZ_CODE is null and SHARE_TYPE=?) or(BIZ_CODE=? and SHARE_TYPE is null) or(BIZ_CODE is null and SHARE_TYPE is null) order by BIZ_CODE nulls last,SHARE_TYPE nulls last 17:44:32.706 INFO [bio-8080-exec-3] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – —-pra:com.ums.bcs.front.model.Share@bc3ed6 17:44:32.706 INFO [bio-8080-exec-3] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – ——————————————————— 17:44:32.846 INFO [bio-8080-exec-3] o.s.b.f.xml.XmlBeanDefinitionReader – Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 17:44:32.906 INFO [bio-8080-exec-3] o.s.j.support.SQLErrorCodesFactory – SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: java.sql.SQLException: ORA-00911: 无效字符如果用 select BIZ_ID, BIZ_CODE, DETAIL_ID, IS_CUS_URL, BIZ_CLIENT_VERSION, BIZ_STATUS, SHARE_URL, SHARE_TYPE from TBL_BCS_SHARE where (BIZ_CODE in (${bizCode}) and SHARE_TYPE=#{shareType,jdbcType=VARCHAR}) or (BIZ_CODE is null and SHARE_TYPE=#{shareType,jdbcType=VARCHAR}) or(BIZ_CODE=#{bizCode,jdbcType=VARCHAR} and SHARE_TYPE is null) or(BIZ_CODE is null and SHARE_TYPE is null) order by BIZ_CODE nulls last,SHARE_TYPE nulls last 这样就只有一个值了，在控制台输出: BIZ_CODE, DETAIL_ID, IS_CUS_URL, BIZ_CLIENT_VERSION, BIZ_STATUS, SHARE_URL, SHARE_TYPE from TBL_BCS_SHARE where (BIZ_CODE in (BIZ-NATIONAL-SOUTUDI) and SHARE_TYPE=?) or (BIZ_CODE is null and SHARE_TYPE=?) or(BIZ_CODE=? and SHARE_TYPE is null) or(BIZ_CODE is null and SHARE_TYPE is null) order by BIZ_CODE nulls last,SHARE_TYPE nulls last 17:47:13.774 INFO [bio-8080-exec-3] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – —-pra:com.ums.bcs.front.model.Share@129efd0 17:47:13.774 INFO [bio-8080-exec-3] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – ——————————————————— 17:47:13.824 INFO [bio-8080-exec-3] o.s.b.f.xml.XmlBeanDefinitionReader – Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 17:47:13.885 INFO [bio-8080-exec-3] o.s.j.support.SQLErrorCodesFactory – SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: java.sql.SQLException: ORA-00904: “SOUTUDI”: 标识符无效 ### The error may involve com.ums.bcs.front.dao.ShareDao.queryListTblBcsShare-Inline ### The error occurred while setting parameters ### Cause: java.sql.SQLException: ORA-00904: “SOUTUDI”: 标识符无效应该怎么写啊？求大神指教
	求大神指教啊
	有木有大神在啊
	木有大神不幸福
5分	为什么不用#{aaa}
5分	在参数外面加上””试试，比如说BIZ_CODE in (“${bizCode} $”)
	引用 4 楼 yiran_ming 的回复: 为什么不用#{aaa} 不能用#{aaa}，#是带””的，在in里面会报错的
	引用 5 楼 smile_to_life 的回复: 在参数外面加上””试试，比如说BIZ_CODE in (“${bizCode} $”) 那这样和用#{aaa}有什么区别呢
	楼主没办法，后面就改用Iterator，但是用Iterator也报错，很奇怪，最后实在没办法了，就用的foreach….
5分	引用 6 楼 licasion 的回复: Quote: 引用 4 楼 yiran_ming 的回复: 为什么不用#{aaa} 不能用#{aaa}，#是带””的，在in里面会报错的 “”${aaa}”” 加单引号
	引用 9 楼 yiran_ming 的回复: Quote: 引用 6 楼 licasion 的回复: Quote: 引用 4 楼 yiran_ming 的回复: 为什么不用#{aaa} 不能用#{aaa}，#是带””的，在in里面会报错的 “”${aaa}”” 加单引号也不行，不能在外面家引号， —-sql:select BIZ_ID, BIZ_CODE, DETAIL_ID, IS_CUS_URL, BIZ_CLIENT_VERSION, BIZ_STATUS, SHARE_URL, SHARE_TYPE from TBL_BCS_SHARE where BIZ_CODE in (“”one,two,three””) and share_type =? 14:26:27.422 INFO [io-8080-exec-10] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – —-pra:com.ums.bcs.front.model.Share@12a1062 14:26:27.422 INFO [io-8080-exec-10] c.h.c.m.i.p.DiclectStatementHandlerInterceptor – ———————————————————
5分	实在不行就用foreach吧
5分	直接 in ( $xxx$ ) 可以吧。我以前项目就这么做。
5分	foreach用#{}不要用${}。
5分	用$肯定可以的，ibatis根本不会修改你传入的值，只可能是你传值出错
5分	from TBL_BCS_SHARE where (BIZ_CODE in (“”BIZ-NATIONAL-SOUTUDI””,””BIZ-NATIONAL-DEFUALT”” $) and SHARE_TYPE=?) 多有个$号，后面的$号不要改成 where (BIZ_CODE in (“”BIZ-NATIONAL-SOUTUDI””,””BIZ-NATIONAL-DEFUALT”” )
	虽然结贴了，但是该问题还是木有解决额，楼主还是用的foreach，如果有知道怎么做的大神还请告诉下哈。