1. #{}
解析为SQL时,会将形参变量的值取出,并自动给其添加引号''
例如:
<select id="getUserList" resultType="user">
select * from user where username = #{username}
</select>
当前sql语句经过解析结果为:
select * from user where username = 'jack'
2. ${}
解析为SQL时,将形参变量的值直接取出,直接拼接显示在SQL中
例如:
<select id="getUserList" resultType="user">
select * from user where username = #{username}
</select>
解析结果为:
select * from user where username = jack
3. ${} 和 #{} 的区别
#{}可以防止sql注入,但是${}不行
${}方式是将形参和SQL语句直接拼接形成完整的SQL命令后,再进行编译,所以可以通过精心设计的形参变量的值,来改变原SQL语句的使用意图从而产生安全隐患,即为SQL注入攻击。
实际开发中我们常用的是#{}
但是在某些情况不得不使用${} 例如:查询某一个表中数据
4. 模糊查询
模糊查询sql语句
select * from user where username like '%a%'
Mapper层
/**
* 通过用户名模糊查询用户
* @param mohu
* @return
*/
List<User> getUserByLike(@Param("mohu") String mohu);
mapper映射
总共三种写法:
<select id="getUserByLike" resultType="user">
select * from t_user where username like "%"#{mohu}"%"
</select>
<select id="getUserByLike" resultType="user">
select * from t_user where username like '%${mohu}%'
</select>
<select id="getUserByLike" resultType="user">
select * from t_user where username like concat('%',#{mohu},'%')
</select>
使用Test类进行测试
@Test
public void testGetUserByLike(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSqlMapper mapper = sqlSession.getMapper(SpecialSqlMapper.class);
List<User> list = mapper.getUserByLike("a");
list.forEach(System.out::println );
}
5.批量删除
mapper层
/**
* 批量删除
* @param ids
*/
void deleteMoreUser(@Param("ids") String ids);
mapper.xml
<!--void deleteMoreUser(@Param("ids") String ids);-->
<delete id="deleteMoreUser">
delete from t_user where id in (${ids})
</delete>
测试:
@Test
public void testDeleteMoreUser(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSqlMapper mapper = sqlSession.getMapper(SpecialSqlMapper.class);
mapper.deleteMoreUser("4,5");
}
6.动态设置表名
mapper层
/**
* 动态设置表名
* @param tableName
* @return
*/
List<User> getUserList(@Param("tableName") String tableName);
mapper.xml
<!--List<User> getUserList(@Param("tableName") String tableName);-->
<select id="getUserList" resultType="user">
select * from ${tableName}
</select>
测试:
@Test
public void testGetUserList(){
SqlSession sqlSession = SqlSessionUtil.getSqlSession();
SpecialSqlMapper mapper = sqlSession.getMapper(SpecialSqlMapper.class);
List<User> list = mapper.getUserList("t_user");
list.forEach(System.out::println);
}