0

In mybatis.xml

<select id="muwbQueryCityAll" parameterType="String" resultType="MonCity">
    SELECT CITY_CODE cityCode, REGION_CODE regionCode, CITY_NAME cityName, ORDER_ID orderId
FROM TB_MON_CITY_DICT where region_code in(#{regionCode}) group by REGION_CODE
</select>

TB_MON_CITY_DICT.region_code is int(11) String regionCode = "1,2,3,4,5,6,7,8,9,10";

When I use 'in' and 'group by' at the same time, the size of returning list is always 1. But when I use '1,2,3,4,5,6,7,8,9,10' instead of #{regionCode}, the size of returning list is real size.

Libraries and environment: Spring 3.1.3, MyBatis 3.1.1, MyBatis-Spring 1.1.1, MySQL 5.5

Thanks in advance.

LOG: DEBUG [http-80-4] - Returning JDBC Connection to DataSource DEBUG [http-80-4] - Creating a new SqlSession DEBUG [http-80-4] - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4349816e] was not registered for synchronization because synchronization is not active DEBUG [http-80-4] - Fetching JDBC Connection from DataSource DEBUG [http-80-4] - JDBC Connection [jdbc:mysql://115.28.11.23:3306/envmonitor, UserName=monitor@183.187.94.213, MySQL-AB JDBC Driver] will not be managed by Spring DEBUG [http-80-4] - ooo Using Connection [jdbc:mysql://115.28.11.23:3306/envmonitor, UserName=monitor@183.187.94.213, MySQL-AB JDBC Driver] DEBUG [http-80-4] - ==> Preparing: select a.COLL_ID as collId,a.ITEM_VALUE1 as itemValue1,a.ITEM_VALUE2 as itemValue2,a.ITEM_VALUE3 as itemValue3, a.ITEM_VALUE4 as itemValue4,a.ITEM_VALUE5 as itemValue5,a.ITEM_VALUE6 as itemValue6,a.ITEM_VALUE7 as itemValue7, a.ITEM_VALUE8 as itemValue8,a.ITEM_VALUE9 as itemValue9,a.ITEM_VALUE10 as itemValue10 from TB_MON_NUMERIC_REC a inner join ( select max(rec_Id) id from TB_MON_NUMERIC_REC where coll_Id in(?) group by coll_Id) b on a.rec_id=b.id DEBUG [http-80-4] - ==> Parameters: 1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013(String) DEBUG [http-80-4] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4349816e] DEBUG [http-80-4] - Returning JDBC Connection to DataSource muwb----------5-------------resultList.size()=1

muwb
  • 1
  • 1
  • 1
    Check what is the query in your log? What is the query in your log? – Karthik Prasad Jan 30 '14 at 13:22
  • Thanks for your comment. actually I find the tag 'foreach' can help me to carry out my task instead, but i still dont know why use 'in(#{param})' and 'group by' in same time that cant work. I extremely confused. – muwb Feb 02 '14 at 01:39

1 Answers1

0

It doesn't work, because of JDBC's SQL Security injection mechanism. Under the hood mybatis queries boils down to JDBC prepared statement. JDBC prepared statement replaces place holder with only one value, hence only first value would be injected. Please refer SO link for further detail.

Community
  • 1
  • 1
Karthik Prasad
  • 9,662
  • 10
  • 64
  • 112