正确写法:
dao
public Map saveWxcircleList_one(List<WxcircleDTO> wdto){
Map<String,Object> map = new HashMap<String,Object>();
List<WxcircleDTO> error = new ArrayList<WxcircleDTO>();
try {
try {
sqlMapClient.insert("wxcircle_sqlmap.insert_data", wdto);
} catch (Exception e) {
e.printStackTrace();
}
map.put("error_list", error);
return map;
} catch (Exception e) {
e.printStackTrace();
}
return map;
}
sql-map.xml
<insert id="insert_data" parameterClass="java.util.List">
insert into wx_circle
(id,username,createtime,private,contentDesc,sourceUserName,
sourceNickName,publicUserName,statisticsData,title,description,
mediaid,mediaurl,mediathumb,sourceNickName2,description2,contenturl,
city,longitude,latitude,poiAddress,poiName)values
<iterate conjunction="," >
<![CDATA[
(#wdto[].id:int#, #wdto[].username:varchar#, #wdto[].createtime:datetime#,
#wdto[].privates:int#, #wdto[].contentDesc:text#, #wdto[].sourceUserName:text#, #wdto[].sourceNickName:text#,
#wdto[].publicUserName:varchar#, #wdto[].statisticsData:text#, #wdto[].title:text#,
#wdto[].description:text#, #wdto[].mediaid:int#, #wdto[].mediaurl:text#, #wdto[].mediathumb:text#,
#wdto[].sourceNickName2:varchar#, #wdto[].description2:text#, #wdto[].contenturl:text#, #wdto[].city:varchar#,
#wdto[].longitude:float#, #wdto[].latitude:float#, #wdto[].poiAddress:text#, #wdto[].poiName:text#)
]]>
</iterate>
<!--下面这句必须加,不然会提示找不到SELECT select * from dual
<![CDATA[
SELECT * FROM dual
]]>-->
</insert>
说明:
wdto 为dao中list参数名,
<![CDATA[
SELECT * FROM dual
]]>-->
不需要加,加了会报错,不知道是不是因为mysql不支持,还有 insert all 貌似也不支持
其中要着重说明的就是
<iterate conjunction="," >
用示例来说明吧
<insert id="insert_data" parameterClass="java.util.List">
insert into wx_circle
(id,username,createtime,private,contentDesc,sourceUserName,
sourceNickName,publicUserName,statisticsData,title,description,
mediaid,mediaurl,mediathumb,sourceNickName2,description2,contenturl,
city,longitude,latitude,poiAddress,poiName)values
<iterate conjunction="," open="(" close=")">
<![CDATA[
#wdto[].id:int#, #wdto[].username:varchar#, #wdto[].createtime:datetime#,
#wdto[].privates:int#, #wdto[].contentDesc:text#, #wdto[].sourceUserName:text#, #wdto[].sourceNickName:text#,
#wdto[].publicUserName:varchar#, #wdto[].statisticsData:text#, #wdto[].title:text#,
#wdto[].description:text#, #wdto[].mediaid:int#, #wdto[].mediaurl:text#, #wdto[].mediathumb:text#,
#wdto[].sourceNickName2:varchar#, #wdto[].description2:text#, #wdto[].contenturl:text#, #wdto[].city:varchar#,
#wdto[].longitude:float#, #wdto[].latitude:float#, #wdto[].poiAddress:text#, #wdto[].poiName:text#
]]>
</iterate>
<!--下面这句必须加,不然会提示找不到SELECT select * from dual
<![CDATA[
SELECT * FROM dual
]]>-->
</insert>
注意: <iterate conjunction="," open="(" close=")">
以前没用过,不理解。但是经过打印出来的sql,消化掉了
Connection
DEBUG 2015-11-17 16:35:53,758 http-bio-8555-exec-3: {conn-100000} Preparing Statement: insert into wx_circle (id,username,createtime,private,contentDesc,sourceUserName, sourceNickName,publicUserName,statisticsData,title,description, mediaid,mediaurl,mediathumb,sourceNickName2,description2,contenturl, city,longitude,latitude,poiAddress,poiName)values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
DEBUG 2015-11-17 16:35:55,985 http-bio-8555-exec-3: {pstm-100001} Executing Statement: insert into wx_circle (id,username,createtime,private,contentDesc,sourceUserName, sourceNickName,publicUserName,statisticsData,title,description, mediaid,mediaurl,mediathumb,sourceNickName2,description2,contenturl, city,longitude,latitude,poiAddress,poiName)values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
DEBUG 2015-11-17 16:35:55,985 http-bio-8555-exec-3: {pstm-100001} Parameters: [5, null, null, 0, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, 0.0, 0.0, null, null, 6, null, null, 0, null, null, null, null, null, null, null, 0, null, null, null, null, null, fdsa, 0.0, 0.0, null, null]
DEBUG 2015-11-17 16:35:55,986 http-bio-8555-exec-3: {pstm-100001} Types: [java.lang.Integer, null, null, java.lang.Integer, null, null, null, null, null, null, null, java.lang.Integer, null, null, null, null, null, null, java.lang.Float, java.lang.Float, null, null, java.lang.Integer, null, null, java.lang.Integer, null, null, null, null, null, null, null, java.lang.Integer, null, null, null, null, null, java.lang.String, java.lang.Float, java.lang.Float, null, null]
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred while applying a parameter map.
--- Check the wxcircle_sqlmap.insert_data-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: Column count doesn't match value count at row 1
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:447)
从打印中来看 conjunction 是在每个参数后添加的, open是开头添加,close则是最后了。
要批量插入的数据,结果在values()一个括号里面了,肯定不行了!!!
所以就修改下<iterate conjunction="," open="(" close=")"> ,需要按照插入的格式来insert into tables values (,,,,) (,,,,,)
<iterate conjunction="," > 改成这样就变成预想的了,数据也插入成功了。
遇到很多错,没看懂。。。。。
例如 :
<insert id="insert_data" parameterClass="java.util.ArrayList">
insert into wx_circle
(id,username,createtime,private,contentDesc,sourceUserName,
sourceNickName,publicUserName,statisticsData,title,description,
mediaid,mediaurl,mediathumb,sourceNickName2,description2,contenturl,
city,longitude,latitude,poiAddress,poiName)values
<iterate conjunction="">
(#[].id:int#, #[].username:varchar#, #[].createtime:datetime#,
#[].privates:int#, #[].contentDesc:text#, #[].sourceUserName:text#, #[].sourceNickName:text#,
#[].publicUserName:varchar#, #[].statisticsData:text#, #[].title:text#,
#[].description:text#, #[].mediaid:int#, #[].mediaurl:text#, #[].mediathumb:text#,
#[].sourceNickName2:varchar#, #description2:text#, #[].contenturl:text#, #[].city:varchar#,
#[].longitude:float#, #[].latitude:float#, #[].poiAddress:text#, #[].poiName:text#)
</iterate>
</insert>
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/paic/crawler/wx_friend_conent/dao/Wxcircle_sqlmap.xml.
--- The error occurred while preparing the mapped statement for execution.
--- Check the wxcircle_sqlmap.insert_data.
--- Check the parameter map.
--- Cause: com.ibatis.common.beans.ProbeException: Error getting ordinal list from JavaBean. Cause java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:94)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDelegate.java:447)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.java:82)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:59)
at com.paic.crawler.wx_friend_conent.dao.WxcircleDao.saveWxcircleList_one(WxcircleDao.java:45)
at com.paic.crawler.wx_friend_conent.action.WxCricleSpringAction.test(WxCricleSpringAction.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:950)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:859)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:883)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:781)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:83)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
{error_list=[]}
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibatis.common.beans.ProbeException: Error getting ordinal list from JavaBean. Cause java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at com.ibatis.common.beans.BaseProbe.getIndexedProperty(BaseProbe.java:86)
at com.ibatis.common.beans.GenericProbe.getObject(GenericProbe.java:54)
at com.ibatis.sqlmap.engine.exchange.ListDataExchange.getData(ListDataExchange.java:63)
at com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.getParameterObjectValues(BasicParameterMap.java:132)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:71)
... 38 more
Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1937)
at com.ibatis.common.beans.BaseProbe.getIndexedProperty(BaseProbe.java:50)
... 42 more
连sql都没打印出来。。。。。。。
错误中很难看出来,后面修改的时候才发现,值中的参数少了#description2:text# ,每个参数前一定要加上 []. !!!!!,不然会报上述错误。粗心啊!!!!
好了,就到这里了。