码库记事本

码库记事本

mybatis注解动态sql注入map和list方式(防sql注入攻击)

小诸哥 0

网上的教程

  • 配置xml
  • [email protected]()
  • 使用Java类中的Java方法拼写sql语句(不防sql注入攻击的纯字符串拼接)

我的教程(防sql注入攻击)

注入Map

Mapper层代码

@Repository
public interface ManageMapper { 
 @SelectProvider(type = ManageProvider.class, method = "queryDevices")
 List<Map<String, Object>> queryDevices(@Param("devicetypeno") String devicetypeno, @Param("map") Map<String, Object> map);
}

Service层代码

@Service("manageService")
public class ManageServiceImpl implements ManageService {
 
 @Resource
 private ManageMapper manageMapper;
 @Override
 public List<Map<String, Object>> queryDevices(String devicetypeno) {
  HashMap<String, Object> map = new HashMap<>();
  map.put("1-1", "1800");
  map.put("1-2", "1854");
  return manageMapper.queryDevices(devicetypeno, map);
 }
}

SqlProvider代码

public class ManageProvider {
 
 public String queryDevices() {
  String sql = new SQL()
    .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
    .FROM("S_DEVICE_INFO")
    .WHERE("DEVICETYPENO = #{devicetypeno}")
    .WHERE("ORGCODE IN (#{map.1-1}, #{map.1-2})")
    .toString();
  return sql;
 }
}

注入List

Mapper层代码

@Repository
public interface ManageMapper {
 @SelectProvider(type = ManageProvider.class, method = "queryDevices")
 List<Map<String, Object>> queryDevices(@Param("devicetypeno") String devicetypeno, @Param("list") List<Object> list);
}

Service层代码

@Service("manageService")
public class ManageServiceImpl implements ManageService { 
 @Resource
 private ManageMapper manageMapper;
 @Override
 public List<Map<String, Object>> queryDevices(String devicetypeno) {
  ArrayList<Object> list = new ArrayList<>();
  list.add("1800");
  list.add("1854");
  return manageMapper.queryDevices(devicetypeno, list);
 }
}

SqlProvider代码

public class ManageProvider {
 public String queryDevices(Map<String, Object> params) {
//  String sql = new SQL()
//    .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
//    .FROM("S_DEVICE_INFO")
//    .WHERE("DEVICETYPENO = #{devicetypeno}")
//    .WHERE("ORGCODE IN (#{list[0]}, #{list[1]})")
//    .toString();
//  return sql;
 
  @SuppressWarnings("unchecked")
  List<Object> list = (List<Object>) params.get("list");
  StringBuilder inBuilder = new StringBuilder();
  for (int i = 0, size = list.size(); i < size; i++) {
   if (i == 0) {
    inBuilder.append("(").append("#{list[").append(i).append("]}");
   } else if (i == size - 1) {
    inBuilder.append(", ").append("#{list[").append(i).append("]}").append(")");
   } else {
    inBuilder.append(", ").append("#{list[").append(i).append("]}");
   }
  }
  SQL sql = new SQL()
     .SELECT("TERMINALNUM, ORGCODE, DEVICETYPENO, DEVICENAME")
     .FROM("S_DEVICE_INFO")
     .WHERE("DEVICETYPENO = #{devicetypeno}");
  if (inBuilder.length() > 0) {
   sql.WHERE("ORGCODE IN " + inBuilder);
  }
  return sql.toString();
 }
}

封装foreach

像xml foreach标签一样使用foreach方法

请看mybatis注解动态sql中foreach工具方法

mybatis防止sql注入的循环map写法

<foreach collection="condition.keys" item="k" separator="and"> 
<if test="null != condition[k]"> 
${k} = #{condition[${k}]}
</if>
</foreach>

标签: list 动态 mybatis