关闭

ibatis批量插入对象

标签: javaibatis
2497人阅读 评论(0) 收藏 举报
分类:

正确写法:

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#   ,每个参数前一定要加上  [].      !!!!!,不然会报上述错误。粗心啊!!!!



好了,就到这里了。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:26024次
    • 积分:523
    • 等级:
    • 排名:千里之外
    • 原创:27篇
    • 转载:1篇
    • 译文:0篇
    • 评论:4条
    最新评论