其实所谓mybatis批量更新,其实只不过是利用到了框架的动态sql拼接转化成的sql语句。
我这有两种方式,第一种方式需要在db连接url后面加上&allowMultiQueries=true这一参数,他的意思就是允许一次连接执行多条sql语句,不建议这种,因为它可能会给系统带来一些安全隐患。话不多说,直接上代码吧
//第一种批量更新
StringBuffer sb = new StringBuffer();
sb.append("<script> ");
sb.append(" <foreach collection=\"ccs\" item=\"commonContacts\" separator=\";\" >");
sb.append("update ffa_common_contacts ");
sb.append("<set>");
sb.append("<if test=\"commonContacts.tel != null\">");
sb.append("tel=#{commonContacts.tel},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.telephone != null\">");
sb.append("telephone=#{commonContacts.telephone},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.qq != null\">");
sb.append("qq=#{commonContacts.qq},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.email != null\">");
sb.append("email=#{commonContacts.email},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.webchat != null\">");
sb.append("webchat=#{commonContacts.webchat},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.companyName != null\">");
sb.append("company_name=#{commonContacts.companyName},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.shortName != null\">");
sb.append("short_name=#{commonContacts.shortName},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.address != null\">");
sb.append("address=#{commonContacts.address},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.departmentName != null\">");
sb.append("department_name=#{commonContacts.departmentName},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.position != null\">");
sb.append("position=#{commonContacts.position},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.createTime != null\">");
sb.append("create_time=#{commonContacts.createTime,jdbcType=TIMESTAMP},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.updateTime != null\">");
sb.append("update_time=#{commonContacts.updateTime,jdbcType=TIMESTAMP},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.status != null\">");
sb.append("status=#{commonContacts.status},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.flg != null\">");
sb.append("flg=#{commonContacts.flg},");
sb.append("</if>");
sb.append("<if test=\"commonContacts.pinYin != null\">");
sb.append("pinyin=#{commonContacts.pinYin},");
sb.append("</if>");
sb.append("</set>");
sb.append("where id= #{commonContacts.id}");
sb.append("</foreach>");
sb.append(" </script>");
第二种方式
StringBuffer sb = new StringBuffer();
sb.append("<script> ");
sb.append("update ffa_common_contacts ");
sb.append("<trim prefix=\"set\" prefixOverrides=\",\"> ");
sb.append("<trim prefix=\"contacts_name =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.contactsName != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.contactsName}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"tel =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.tel != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.tel}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"telephone =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.telephone != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.telephone}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"qq =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.qq != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.qq}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"email =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.email != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.email}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"webchat =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.webchat != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.webchat}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"company_name =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.companyName != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.companyName}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"short_name =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.shortName != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.shortName}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"address =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.address != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.address}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"department_name =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.departmentName != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.departmentName}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"position =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.position != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.position}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"create_time =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.createTime != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.createTime}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"update_time =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.updateTime != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.updateTime}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"status =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.status != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.status}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"flg =case\" suffix=\"end,\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.flg != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.flg}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("<trim prefix=\"pinyin =case\" suffix=\"end\"> ");
sb.append("<foreach collection=\"ccs\" item=\"commonContacts\" index=\"index\" > ");
sb.append("<if test=\"commonContacts.pinYin != null\">");
sb.append(" when id=#{commonContacts.id} then #{commonContacts.pinYin}");
sb.append("</if>");
sb.append("</foreach>");
sb.append("</trim>");
sb.append("</trim>");
sb.append(" where id in");
sb.append(" <foreach collection=\"ccs\" item=\"commonContacts\" separator=\",\" open=\"(\" close=\")\" index=\"index\">");
sb.append(" #{commonContacts.id}");
sb.append("</foreach>");
sb.append(" </script>");