一、模糊查询
- mapper接口中(SpecialMapper)
返回类型为List<User>
//根据用户名进行模糊查询
List<User> getUserByLike(@Param("mohu") String username);
- mapper映射文件中(SelectMapper.xml)
<!--List<User> getUserByLike(@Param("username") String username);-->
<select id="getUserByLike" resultType="User">
select * from t_user where username like "%"#{mohu}"%"
</select>
- test测试文件中(SelectMapperTest)
//根据用户名进行模糊查询
@Test
public void testGetUserByLike(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);
List<User> list = mapper.getUserByLike("admin");
System.out.println(list);
}
二、批量删除
只能使用${},如果使用#{},则解析后的sql语句为
delete from t_user where id in ('1,2,3')
,这样是将1,2,3
看做是一个整体,只有id为1,2,3
的数据会被删除。正确的语句应该是delete from t_user where id in (1,2,3)
,或者delete from t_user where id in ('1','2','3')
- mapper接口中(SpecialMapper)
返回类型为List<User>
//根据id批量删除
int deleteMore(@Param("ids") String ids);
- mapper映射文件中(SpecialMapper.xml)
<!--int deleteMore(@Param("ids") String ids);-->
<delete id="deleteMore">
delete from t_user where id in (${ids})
</delete>
- test测试文件中(SpecialMapperTest)
//根据id批量删除
@Test
public void deleteMore() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);
int result = mapper.deleteMore("4,6,8");
System.out.println(result);
}
三、动态设置表名
只能使用${},因为表名不能加单引号
- mapper接口中(SpecialMapper)
返回类型为List<User>
//查询指定表中数据
List<User> getUserByTable(@Param("tableName") String tableName);
- mapper映射文件中(SpecialMapper.xml)
<!--List<User> getUserByTable(@Param("tableName") String tableName);-->
<select id="getUserByTable" resultType="User">
select * from ${tableName}
</select>
- test测试文件中(SpecialMapperTest)
//查询指定表中数据
@Test
public void getUserByTable() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);
List<User> list = mapper.getUserByTable("t_user");
System.out.println(list);
}
四、添加功能获取自增的主键
- 在mapper.xml中设置两个属性
- useGeneratedKeys:设置使用自增的主键
- keyProperty:设置自增主键,因为增删改有统一的返回值是受影响的行数,因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
- mapper接口中(SpecialMapper)
传参类型为User
//添加用户信息
void insertUser(User user);
- mapper映射文件中(SpecialMapper.xml)
<!--void insertUser(User user);-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values (null,#{username},#{password},#{age},#{sex},#{email})
</insert>
- test测试文件中(SpecialMapperTest)
//添加用户信息
@Test
public void insertUser() {
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);
User user = new User(null, "ton", "123", 23, "男", "123@321.com");
mapper.insertUser(user);
System.out.println(user);
}
输出:
本文主要参考:
【尚硅谷】2022版MyBatis教程(细致全面,快速上手)