近日，在编写Spring Mvc的时候，出现一个特别怪的事情，我正常使用
<select id="findListWithIdsString" resultType="OrderProduct" parameterType="java.util.List">
SELECT
<include refid="orderProductOwnColumns"/>
a.del_flag AS "delFlag"
FROM tbl_order_product a
<where>
a.del_flag = '0'
AND a.id in ('XXXXX','XXXX')
</where>
</select>
以上这个这个是** 没问题的**，但是，当我改为从java**传值List**的时候，就出问题了，xml代码：
<select id="findListWithIdsString" resultType="OrderProduct" parameterType="java.util.List">
SELECT
<include refid="orderProductOwnColumns"/>
a.del_flag AS "delFlag"
FROM tbl_order_product a
<where>
a.del_flag = '0'
<choose>
<when test="inComingIds != null and inComingIds.size()>0">
AND a.id in
<foreach collection="inComingIds" item="item" index="index" open="(" close=")" separator="," >
#{item}
</foreach>
</when>
<otherwise>
AND a.id='0'
</otherwise>
</choose>
</where>
</select>
然后我的前java端代码是：
List<String> idList=new ArrayList<>();//传入的List
String opIds=mp.getRelatedOrderProductIds();//前端获取的String
Collections.addAll(idList,StringUtils.split(opIds, ","));//转化为List
OrderProduct opForSearch=new OrderProduct();
opForSearch.setInComingIds(idList);
orderProductService.findListWithIdsString(opForSearch);
数据库配置如下：
?useUnicode=true&characterEncoding=utf-8
出错信息：
2019-05-01 15:28:43,034 DEBUG [modules.sys.dao.OrderProductDao.findListWithIdsString] - ==> Preparing: SELECT a.id AS "id", a.order_id AS "order.id", a.actual_flow_id AS "actualFlowId", a.category_id AS "companyCategory.id", a.parent_order_product_id as "parentOrderProductId", a.price AS "price", a.amount AS "amount", a.product_name AS "productName", a.a_product_name AS "aProductName", a.product_description AS "productDescription", a.delivery_time AS "deliveryTime", a.rate AS "rate", a.is_a_confirm AS "isAConfirm", a.confirm_userid AS "confirmUserid", a.A_confirm_time AS "aConfirmTime", a.order_product_type AS "orderProductType", a.inner_delivery_time AS "innerDeliveryTime", a.companyB_priority AS "companyBPriority", a.companyA_priority AS "companyAPriority", a.order_product_status AS "orderProductStatus", a.product_sn AS "productSn", a.a_product_sn AS "aProductSn", a.source AS "source", a.A_company_id AS "aCompany.id", a.B_company_id AS "bCompany.id", a.take_materiel_status AS "takeMaterielStatus", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", "ORDER_PRODUCT" AS "resourceType", a.remarks AS "remarks", a.down_order_status AS "downOrderStatus", a.batch_number AS "batchNumber", a.closure AS "closure", a.closure_money AS "closureMoney", a.del_flag AS "delFlag" FROM tbl_order_product a WHERE a.del_flag = '0' AND a.id in ( ???????????? ??? ? ???????? ??? , ???????????? ??? ? ???????? ??? )
2019-05-01 15:28:43,034 DEBUG [modules.sys.dao.OrderProductDao.findListWithIdsString] - ==> Parameters: 54eca8e0409a4f5b81919ecbf7b85d44(String), 4fda20df078945aeaae3960f265795c1(String)
2019-05-01 15:28:43,094 ERROR [500.jsp] -
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
### The error may exist in D:\apache-tomcat-8.0.53-ft\webapps\blanink-web\WEB-INF\classes\mappings\modules\sys\OrderProductDao.xml
### The error may involve com.blanink.business.modules.sys.dao.OrderProductDao.findListWithIdsString-Inline
### The error occurred while setting parameters
### SQL: SELECT a.id AS "id", a.order_id AS "order.id", a.actual_flow_id AS "actualFlowId", a.category_id AS "companyCategory.id", a.parent_order_product_id as "parentOrderProductId", a.price AS "price", a.amount AS "amount", a.product_name AS "productName", a.a_product_name AS "aProductName", a.product_description AS "productDescription", a.delivery_time AS "deliveryTime", a.rate AS "rate", a.is_a_confirm AS "isAConfirm", a.confirm_userid AS "confirmUserid", a.A_confirm_time AS "aConfirmTime", a.order_product_type AS "orderProductType", a.inner_delivery_time AS "innerDeliveryTime", a.companyB_priority AS "companyBPriority", a.companyA_priority AS "companyAPriority", a.order_product_status AS "orderProductStatus", a.product_sn AS "productSn", a.a_product_sn AS "aProductSn", a.source AS "source", a.A_company_id AS "aCompany.id", a.B_company_id AS "bCompany.id", a.take_materiel_status AS "takeMaterielStatus", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", "ORDER_PRODUCT" AS "resourceType", a.remarks AS "remarks", a.down_order_status AS "downOrderStatus", a.batch_number AS "batchNumber", a.closure AS "closure", a.closure_money AS "closureMoney", a.del_flalag AS "delFlag" FROM tbl_order_product a WHERE a.del_flag = '0' AND a.id in ( ???????????? ??? ? ???????? ??? , ???????????? ??? ? ???????? ??? )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
### The error may exist in D:\apache-tomcat-8.0.53-ft\webapps\blanink-web\WEB-INF\classes\mappings\modules\sys\OrderProductDao.xml
### The error may involve com.blanink.business.modules.sys.dao.OrderProductDao.findListWithIdsString-Inline
### The error occurred while setting parameters
### SQL: SELECT a.id AS "id", a.order_id AS "order.id", a.actual_flow_id AS "actualFlowId", a.category_id AS "companyCategory.id", a.parent_order_product_id as "parentOrderProductId", a.price AS "price", a.amount AS "amount", a.product_name AS "productName", a.a_product_name AS "aProductName", a.product_description AS "productDescription", a.delivery_time AS "deliveryTime", a.rate AS "rate", a.is_a_confirm AS "isAConfirm", a.confirm_userid AS "confirmUserid", a.A_confirm_time AS "aConfirmTime", a.order_product_type AS "orderProductType", a.inner_delivery_time AS "innerDeliveryTime", a.companyB_priority AS "companyBPriority", a.companyA_priority AS "companyAPriority", a.order_product_status AS "orderProductStatus", a.product_sn AS "productSn", a.a_product_sn AS "aProductSn", a.source AS "source", a.A_company_id AS "aCompany.id", a.B_company_id AS "bCompany.id", a.take_materiel_status AS "takeMaterielStatus", a.create_by AS "createBy.id", a.create_date AS "createDate", a.update_by AS "updateBy.id", a.update_date AS "updateDate", "ORDER_PRODUCT" AS "resourceType", a.remarks AS "remarks", a.down_order_status AS "downOrderStatus", a.batch_number AS "batchNumber", a.closure AS "closure", a.closure_money AS "closureMoney", a.del_flag AS "delFlag", a.del_flag AS "delFlag" FROM tbl_order_product a WHERE a.del_flag = '0' AND a.id in ( ???????????? ??? ? ???????? ??? , ???????????? ??? ? ???????? ??? )
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??? '54eca8e0409a4f5b81919ecbf7b85d44'
???????? ??? ,
?????' at line 47
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
at com.sun.proxy.$Proxy27.selectList(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)
at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
```
