mapper有时写的sql不够规范出现java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0)
可能原因:包含多余引号,比如 name like ‘%#{name}%’
出错案例
@Select({
"SELECT * ",
"FROM users",
"WHERE name like '%#{name}%' ",
"ORDER BY id desc ",
"LIMIT 100"
})
List<User> selectUsersLikeName(@Param("name") String name);
因为#{name}本身是带引号的,可以用${name}替换
解决办法1:用${name}替换#{name}
@Select({
"SELECT * ",
"FROM users",
"WHERE name like '%${name}%' ",
"ORDER BY id desc ",
"LIMIT 100"
})
List<User> selectUsersLikeName(@Param("name") String name);
解决办法2:用concat连接字符串
@Select({
"SELECT * ",
"FROM users",
"WHERE name like concat('%',#{name},'%') ",
"ORDER BY id desc ",
"LIMIT 100"
})
List<User> selectUsersLikeName(@Param("name") String name);
解决办法3:使用instr代替like
@Select({
"SELECT * ",
"FROM users",
"WHERE instr(name,#{name})>0 ",
"ORDER BY id desc ",
"LIMIT 100"
})
List<User> selectUsersLikeName(@Param("name") String name);