mybatis动态查询字段、动态更新字段

一、动态查询字段并返回list

1. 接收的对象

   @Data
   public class TableFieldOutput implements Serializable {
       private static final long serialVersionUID = 6374628679868679057L;
       @ApiModelProperty(value = "主键ID")
       private Long primaryKey;
       @ApiModelProperty(value = "oldField1")
       private String oldField1;
       @ApiModelProperty(value = "oldField2")
       private String oldField2;
       @ApiModelProperty(value = "oldField3")
       private String oldField3;
       @ApiModelProperty(value = "oldField4")
       private String oldField4;
       @ApiModelProperty(value = "oldField5")
       private String oldField5;
       @ApiModelProperty(value = "oldField6")
       private String oldField6;
       @ApiModelProperty(value = "oldField7")
       private String oldField7;
       @ApiModelProperty(value = "oldField8")
       private String oldField8;
       @ApiModelProperty(value = "oldField9")
       private String oldField9;
       @ApiModelProperty(value = "oldField10")
       private String oldField10;
     }
   

2. 拼接要查询的sql语句。

   @Resoure
   Querymapper querymapper;
   
   //要查询的字段 id, name,age,
   String tableName="school"
   String primaryKey="id";
   String oldFields="name,age";
   String condition=" where age>18"
   
   StringBuilder sqlStr = new StringBuilder("select ").append(primaryKey).append(" primaryKey,");
   String[] split = oldFields.split(",");
   for (int i = 0; i < split.length; i++) {
     String oldFiled = split[i];
     sqlStr.append(oldFiled).append(" oldField").append(i + 1);
     if (i != split.length - 1) {
       sqlStr.append(",");
     }
   }
   sqlStr.append(" from ").append(tableName).append(" ").append(condition);
   //查询数据
   List<TableFieldOutput> outputs = querymapper.selectTableFieldListBySql(sqlStr.toString());
   

3. mybatis 配置

   <select id="selectTableFieldListBySql" resultType="com.ctfojt.synchfile.vo.TableFieldOutput"
           statementType="STATEMENT">
       ${sqlStr}
   </select>
   

二、动态更新字段

1. 动态获取对象中值

TableFieldOutput out = new TableFieldOutput();
out.setOldField1("1");
out.setOldField2(null);
out.setOldField3("3");
out.setOldField4("4");
//比如我们要获取 oldField3的值：
String value = getItemByReflect(out,3);

//通过反射来动态获取对象的值 
public String getItemByReflect(TableFieldOutput dto, int num) {
  try {
    Method m1 = TableFieldOutput.class.getDeclaredMethod(String.format("getOldField%s", num));
    return (String) m1.invoke(dto);
  } catch (NoSuchMethodException | SecurityException | IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
    return null;
  }
}

2. 拼接sql

   String tableName="school"
   String primaryKey="id";
   String newFields="name,age";
   String primaryValue=" 3"
     
   List<String> newFieldContent = new ArrayList<>();
   newFieldContent.add("小明,18");
   //拼接要更新的字段的sql
   StringBuilder sqlStr = new StringBuilder("update ").append(tableName).append(" set ");
   for (int i = 0; i < newFieldContent.size(); i++) {
       if (!StringUtils.isEmpty(newFieldContent.get(i))) {
           sqlStr.append(newFields.split(",")[i]).append("='").append(newFieldContent.get(i)).append("'");
           if (i != newFieldContent.size() - 1) {
               sqlStr.append(",");
           }
       }
   }
   sqlStr.append(" where ").append(primaryKey).append("=").append(output.getPrimaryKey());
   

3. 更新

<update id="updateNewImage" statementType="STATEMENT">
 ${sqlStr}
</update>

三、知识点

statementType：标记操作SQL的对象
取值说明：
1、STATEMENT:直接操作sql，如上所用。
2、PREPARED:预处理，参数？，进行预编译
3、CALLABLE:执行存储过程