ibatis 批量提交sql

我项目的持久层用的是ibatis:

 

我项目接口被人调用后返回 jsonArray字符串，然后换成List<Object>对象，insert是，循环List<Object>对象批量插入，代码图如下：

 

Action层：

@RequestMapping(value = "/test.htm", method = RequestMethod.POST)
    public String init(HttpServletRequest request ) throws IOException {

        //接收接口返回数据
        Map<String, String> map = new HashMap<String, String>();
        Enumeration<?> fields = request.getParameterNames();

        while(fields.hasMoreElements())
        {
            String field = (String) fields.nextElement();
            String[] values = request.getParameterValues(field);
            if (values.length > 1)
                map.put(field, values.toString());
            else
map.put(field, values[0]);
        }
        System.out.println(map.get("jsonArray"));


        //自定义测试数据
//        String arrayStr ="[{\"amount\":\"9.83\",\"fundCategory\":\"A\",\"postStaus\":\"I\",\"fundChannelCode\":\"WEIXINPAY11601\",\"instOrder\":\"FI116011001082400080288\",\"happenDate\":\"20170824\",\"remarks\":\"20170824扣除入款手续费9.83元\",\"creater\":\"Arvin\",\"createTime\":\"20170907\",\"auditor\":\"\",\"auditorTime\":\"\",\"deleteFlag\":\"N\"},{\"amount\":\"15.77\",\"fundCategory\":\"A\",\"postStaus\":\"I\",\"fundChannelCode\":\"WEIXINPAY11601\",\"instOrder\":\"FI116011001082400080301\",\"happenDate\":\"20170824\",\"remarks\":\"20170824扣除入款手续费15.77元\",\"creater\":\"Arvin\",\"createTime\":\"20170907\",\"auditor\":\"\",\"auditorTime\":\"\",\"deleteFlag\":\"N\"}]";
if(map.get("jsonArray")!=null && map.get("jsonArray").length()>0){
            String arrayStr = map.get("jsonArray");
            logger.info("接口返回jsonArray："+arrayStr);
            List<PostAccountVO> postAccountVOList = new ArrayList<PostAccountVO>();

            List<Map<String,String>> listObjectSec = JSONArray.parseObject(arrayStr,List.class);
            Map<String,String> mapList = new HashMap<String, String>();
            for(int i=0;i<listObjectSec.size();i++){
                mapList = listObjectSec.get(i);
                JSONObject jsonObject = JSONObject.fromObject(mapList);
                PostAccountVO postAccountVO=(PostAccountVO)JSONObject.toBean(jsonObject, PostAccountVO.class);
                postAccountVOList.add(postAccountVO);
            }
            System.out.println( postAccountVOList.size());

            //接收的数据转list对象后，存入数据库
            GlideSyncResult result = postAccountFacade.getBankHandlingCharge(postAccountVOList);

        }else{
            System.out.println("接口返回为空！");
            logger.info("接口返回为空！");
        }

        return "";
    }

 

service层：

public GlideSyncResult getBankHandlingCharge(List<PostAccountVO> vo);

 

service  impl实现层：

我的情况是：service层实体类都为VO，dao层实体类为DO，所以想要调用dao层方法，想要把对象转为dao层对象

(VO和DO实体类是一样的，但是这个项目都是按照这个格式走的，我也分别建了VO和DO两个实体类，你们应该也许建一个实体类就好了，这样不用转换了)

 

@Override
public GlideSyncResult getBankHandlingCharge(List<PostAccountVO> vo) {



    List<PostAccountInfoDO> infoDOList = new ArrayList<PostAccountInfoDO>();
    int status=0;

    if(vo!=null && vo.size()>0){

            for(int i=0;i<vo.size();i++){
                PostAccountInfoDO infoDO = new PostAccountInfoDO();
                    infoDO.setAccountSeqNo(postAccountInfoDAO.getAccountNo());
                    infoDO.setAmount(vo.get(i).getAmount());
                    infoDO.setFundCategory(vo.get(i).getFundCategory());
                    infoDO.setPostStaus(vo.get(i).getPostStaus());
                    infoDO.setFundChannelCode(vo.get(i).getFundChannelCode());
                    infoDO.setHappenDate(vo.get(i).getHappenDate());
                    infoDO.setRemarks(vo.get(i).getRemarks());
                    infoDO.setCreater(vo.get(i).getCreater());
                    infoDO.setCreateTime(vo.get(i).getCreateTime());
                    infoDO.setAuditor(vo.get(i).getCreater());
                    infoDO.setAuditorTime(vo.get(i).getCreateTime());
                    infoDO.setDeleteFlag(vo.get(i).getDeleteFlag());
                    infoDO.setInstOrder(vo.get(i).getInstOrder());

                    infoDOList.add(infoDO);
            }

        status = postAccountInfoDAO.insert(infoDOList);
    }

    if (status> 0) {
        return new GlideSyncResult(GlideSyncResultCode.SUCCESS);
    } else {
        return new GlideSyncResult(GlideSyncResultCode.FAILED);
    }
}

 

dao层：

public int insert(List<PostAccountInfoDO> infoDOList ) throws DataAccessException;

 

dao  impl实现层：

@Override
public int insert(List<PostAccountInfoDO> infoDOList ) throws DataAccessException {
    if(infoDOList==null){
        throw new IllegalArgumentException("Can't insert a null data object into db.");
    }
    Integer nums =  (Integer)getSqlMapClientTemplate().insert("MS-POST-ACCOUNT-INFO-INSERT", infoDOList);

    if(nums==null){
        return 1;
    }

    return 0;
}

 

mapper.xml:

<insert id="MS-POST-ACCOUNT-INFO-INSERT" parameterClass="java.util.List">
      insert all
      <iterate  conjunction="" >
         <![CDATA[
         into TB_POST_ACCOUNT_INFO
         (ACCOUNT_SEQ_NO,AMOUNT,FUND_CATEGORY,POST_STAUS,FUND_CHANNEL_CODE,HAPPEN_DATE,REMARKS,CREATER,CREATE_TIME,AUDITOR,AUDITOR_TIME,DELETE_FLAG,INST_ORDER)
         values
            (#infoDOList[].accountSeqNo#,#infoDOList[].amount#,#infoDOList[].fundCategory#,#infoDOList[].postStaus#,
            #infoDOList[].fundChannelCode#,#infoDOList[].happenDate#,#infoDOList[].remarks#,#infoDOList[].creater#,
#infoDOList[].createTime#,#infoDOList[].auditor#,#infoDOList[].auditorTime#,#infoDOList[].deleteFlag#,
#infoDOList[].instOrder#)
        ]]>
      </iterate>
      select 1 from dual
   </insert>

 

注意 ：

如果添加数据的时候，如果有自动生成参数，譬如：

<select id="ACCOUNT-INFO-NO" resultClass="String">
   <![CDATA[
       select seq_post_account_info.nextval from dual
   ]]>
</select>

 

建议，单独写sql查询，查出之后往对象里边塞值，如下图：

 

 

方法比较笨。。。

并且，这个方法批量提交数量要小于 一千条（size() < 1000）

 

如果发现有更好的方法请提出来，我也学习一下。