ibatis批量插入对象

原创 2015年11月17日 16:54:14

正确写法:

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



好了,就到这里了。

ibatis批量insert

这是dao @DAOAction(action = DAOActionType.INSERT) public int batchInsertGroupShoup(@DAOParam(val...

ibatis批量插入、修改、删除

批量插入 insert into files (fileID,title,brief,content,url) values (#files[].fileId#,#file...

ibatis批量插入

项目开发中在很多地方可能会遇到同时插入多条记录到数据库的业务场景,如果业务级别循环单条插入数据会不断建立连接且有多个事务,这个时候如果业务的事务执行频率相当较高的话(高并发),对数据库的性能影响是比较...

关于oracle+ibatis批量insert的写法

现在编写代码,总时刻要求自己,不只是实现功能,而且要质量更高。 最近使用ibatis框架,其灵活的sql编写,能够颗粒度的优化,蛮不错的。 另外使用批量insert时,找了好久,终于被我找到一...

点评 ibatis+oracle 批量插入的三种方法.

第一种                insert into sj_test( col1 , col2 , col3 ) values       ]]>            ...

利用Ibatis执行批量插入更新数据库操作

刚开始接触springmvc+ibatis架构时遇到批量插入更新数据库的问题。 y
  • weifeib
  • weifeib
  • 2016年06月24日 15:31
  • 4932

ibatis批量插入数据-iterate标签详解及应用

Sql代码   insert into       tb_name(col1, col2, col3)       values       (col1_v, col2_v, col3_v), ...

Ibatis批量插入数据

一:实体类 public class Group implements Serializable { private static final long serialVersionUID =...

ibatis批量处理插入实例

getSqlMapClientTemplate().execute(new SqlMapClientCallback() {     public Object doInSqlMapClient(S...

ibatis 批量插入,批量更新,批量删除,xml与Java代码实现方式

ibatis批量操作
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ibatis批量插入对象
举报原因:
原因补充:

(最多只允许输入30个字)