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:执行存储过程