这里写一个查询的sql语句
第一种方式
首先看 mapper
@SelectProvider(type = SqlModel.class, method = "listDeviceIndex")
@Results(value = {
@Result(column = "index_id",property = "userIndex"),
@Result(column = "device_id",property = "deviceId")
})
List<DeviceIndex> selectByDeviceIdList(List<Integer> deviceIds);
@SelectProvider是生产动态sql的
type : 对应的类
method : 方法
SqlModel.java
public String listDeviceIndex(Map map) {
List<Integer> list = (List<Integer>) map.get("list");
StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM device_index WHERE device_id IN(");
MessageFormat mf = new MessageFormat("#'{'list[{0}]}");
for (int i = 0; i < list.size(); i++) {
sb.append(mf.format(new Object[]{i}));
if (i < list.size() - 1) {
sb.append(",");
}
}
sb.append(")");
return sb.toString();
}
第二种方式
@Select("<script> " +
"select count(id) from training_history where uid = #{uid} and finish_type in " +
"<foreach item='item' index='index' collection='types' open='(' separator=',' close=')'> " +
" #{item} " +
"</foreach>" +
"</script> "
)
fun findCountByUidAndFinishType(@Param("uid") uid: Int,@Param("types") types: List<Int>): Int
注意加上 script 标签
collection 遍历的类型,(集合为list,数组为array,如果方法参数是对象的某个属性,而这个属性是list,或array类型,就可以写形参的名字)
open 条件的开始
close 条件的结束
item 遍历集合时候定义的临时变量,存储当前遍历的每一个值
separator 多个值之间用逗号拼接
#{item} 获取遍历的每一个值,与item定义的临时变量一致
提示,在 rest 服务中,传参数 type = 0 & type=1 参数名一样即可