特殊情况下SQL执行,需要注意 #
和 $
符号的使用,某些场景下只能用 $
,因为 #
有默认逗号隔开的作用
一、模糊查询
/**
* 根据用户名模糊查询用户信息
*/
List<User> getUserByLike(@Param("username") String username);
<!--List<User> getUserByLike(@Param("username") String username);-->
<select id="getUserByLike" resultType="User">
<!--select * from user where username like '%${username}%'-->
<!--select * from user where username like concat('%',#{username},'%')-->
select * from user where username like "%"#{username}"%"
</select>
推荐使用:
select * from user where username like "%"#{username}"%"
@Test
public void testGetUserByLike(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
List<User> list = mapper.getUserByLike("z");
System.out.println(list);
}
二、批量删除
因为 #
有默认逗号隔开的作用,所以只能使用$
\
/**
* 批量删除
*/
int deleteMore(@Param("ids") String ids);
<!--int deleteMore(@Param("ids") String ids);-->
<delete id="deleteMore">
delete from user where id in (${ids})
</delete>
@Test
public void testDeleteMore(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
int result = mapper.deleteMore("1,2");
System.out.println(result);
}
三、动态设置表名
/**
* 查询指定表中的数据
*/
List<User> getUserByTableName(@Param("tableName") String tableName);
这里只能采用 $
符号,因为#
默认有逗号功能
<!--List<User> getUserByTableName(@Param("tableName") String tableName);-->
<select id="getUserByTableName" resultType="User">
select * from ${tableName}
</select>
@Test
public void testGetUserByTableName(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
List<User> list = mapper.getUserByTableName("user");
System.out.println(list);
}
四、添加功能获取自增的主键
适用于处理中间表时,前提表中主键是自增
比如:为班级新增学生,符合原始表中id递增顺序,就可以采用
useGeneratedKeys:设置当前标签中的sql使用了自增的主键
keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性
/**
* 添加用户信息
*/
void insertUser(Yolo user);
<!--
void insertUser(User user);
useGeneratedKeys:设置当前标签中的sql使用了自增的主键
keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into yolo values(null,#{name},#{age})
</insert>
@Test
public void testInsertUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
Yolo user = new Yolo(null, "王五", 123);
mapper.insertUser(user);
System.out.println(user);
}