mybatis的对于动态sql的几种用法

在表名、字段名及个数不确定的情况下使用动态sql语句会实现你想要的结果

<!--执行传入的sql语句 -->

<insert id="insertTaskBySql" flushCache="false" parameterType="java.lang.String" statementType="PREPARED">
  ${insertSqlStr}
 </insert>

@Test
 public void testInsertSelective() throws Exception {
  long startTime = System.currentTimeMillis();
  String sqlStr = "insert into test1(id,name) values(1,'ww')";
  for (int i = 0; i < 100000; i++) {
   clientTaskImpMapper.insertTaskBySql(sqlStr);
  }
  System.out.println(System.currentTimeMillis() - startTime);
 }
 

<!--通过传入Map<String,Object>类型的参数进行sql语句的拼接：insert into tableName(col1,col2) values(?,?)-->
 <insert id="dynamicInsertSql1" parameterType="java.util.Map" statementType="PREPARED">
  INSERT INTO ${paramsMap.tableName}
  <foreach collection="paramsMap.columnsList" index="index" item="columnName" open="(" separator="," close=")">
   ${columnName}
  </foreach>
  <foreach collection="paramsMap.datasList" index="index" item="dataValue" open="VALUES(" separator="," close=")">
   #{dataValue}
  </foreach>
 </insert>

@Test
 public void testDynamicInsertSql() throws Exception {
  long startTime = System.currentTimeMillis();
  for (int i = 0; i < 100000; i++) {//虽然用到了预编译，没起到好的效果
   Map<String, Object> paramsMap = new HashMap<String, Object>();
   paramsMap.put("tableName", "test1");
   List<String> columnList = new ArrayList<String>();
   columnList.add("id");
   columnList.add("name");
   paramsMap.put("columnsList", columnList);
   List<Object> dataList = new ArrayList<Object>();
   dataList.add(Integer.valueOf(1));
   dataList.add("ww");
   paramsMap.put("datasList", dataList);
   clientTaskImpMapper.dynamicInsertSql(paramsMap);
  }
  System.out.println(System.currentTimeMillis() - startTime);
 }
 

<!--通过传入Map<String,Object>类型的参数进行sql语句的拼接：insert into tableName(col1,col2) select ?,? from dual union all select ?,? from dual ... -->
 <insert id="dynamicInsertSql" parameterType="java.util.Map" statementType="PREPARED">
  INSERT INTO ${paramsMap.tableName}
  <foreach collection="paramsMap.columnsList" index="index" item="columnName" open="(" separator="," close=")">
   ${columnName}
  </foreach>
  <foreach collection="paramsMap.datasList" index="index" item="dataList" separator=" union all " >
   <foreach collection="dataList" index="index1" item="selectName" separator="," open="select " close=" from dual ">
    #{selectName}
   </foreach>
  </foreach>
 </insert>

@Test
 public void testDynamicInsertSql() throws Exception {
  long startTime = System.currentTimeMillis();

  Map<String, Object> paramsMap = new HashMap<String, Object>();
  paramsMap.put("tableName", "test1");
  List<String> columnList = new ArrayList<String>();
  columnList.add("id");
  columnList.add("name");
  paramsMap.put("columnsList", columnList);
  List<Object> dataList = new ArrayList<Object>();
  for (int i = 0; i < 10; i++) {//所有数据一次提交，注意数据量
   List<Object> tmpList = new ArrayList<Object>();
   tmpList.add(Integer.valueOf(1));
   tmpList.add("ww");
   dataList.add(tmpList);   
  }
  paramsMap.put("datasList", dataList);
  clientTaskImpMapper.dynamicInsertSql(paramsMap);

  System.out.println(System.currentTimeMillis() - startTime);
 }

 

注：对于这种动态sql要提高insert执行效率，还是得活动SqlSession进行原生态的jdbc编程(或许mybatis自身有更优的做法，请告之，谢谢)