1、模糊查询

SQLMapper

     /* @description:根据用户名模糊查询用户信息* @author: Hey * @date: 2022/7/3 21:45* @param: [username]* @return: java.util.List<com.ir.mybatis.pojo.User>/List<User> getUserByLike(@Param("username") String username);

SelectMapper.xml

<select id="getUserByLike" resultType="user"><!--方式一：select * from t_user where username like '%${username}%'--><!--方式二：select * from t_user where username like concat('%',#{username},'%')--><!--方式三：-->select * from t_user where username like "%"#{username}"%"</select>

SQLMapperTest

/* @description:根据用户名模糊查询用户信息* @author: Hey* @date: 2022/7/3 21:48* @param: []* @return: void/@Testpublic void testGetUserByLike(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);List<User> list = mapper.getUserByLike("a");System.out.println(list);}

注意：使用#{}的方式，要使用上面的方式二与方式三，否则会报错，因为此时占位符？在双引号""中，所以无法识别占位符？

2、批量删除

SQLMapper

/* @description:批量删除* @author: Hey* @date: 2022/7/3 21:58* @param: [ids]* @return: int/int deleteMore(@Param("ids") String ids);

SelectMapper.xml

 <delete id="deleteMore">delete from t_user where id in (${ids})</delete>

SQLMapperTest

 /* @description:批量删除* @author: Hey* @date: 2022/7/3 21:59* @param: []* @return: void/@Testpublic void testDeleteMore(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);int result = mapper.deleteMore("1,2,3");System.out.println(result);}

注意：不可以使用#{}的方式，因为DELETE FROM t_user WHERE id IN("1,2,3")此sql语句会报错，正确的语句应该是DELETE FROM t_user WHERE id IN(1,2,3)。

3、动态设置表名

SQLMapper

/* @description:查询指定表中的数据* @author: Hey* @date: 2022/7/3 22:08* @param: [tableName]* @return: java.util.List<com.ir.mybatis.pojo.User>/List<User> getUserByTableName(@Param("tableName") String tableName);

SelectMapper.xml

 <select id="getUserByTableName" resultType="User">select * from ${tableName}</select>

SQLMapperTest

 /* @description:查询指定表中的数据* @author: Hey * @date: 2022/7/3 22:09* @param: []* @return: void/@Testpublic void testGetUserByTableName(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);List<User> list = mapper.getUserByTableName("t_user");System.out.println(list);}

注意：不能使用#{}的方式，因为表名是不可以加上单引号的

4、添加功能获取自增的主键

场景：
表1： t_clazz(clazz_id,clazz_name)
表2：t_student(student_id,student_name,clazz_id)

1. 添加班级信息
2. 获取新添加的班级的id
3. 为班级分配学生，即将某学的班级id修改为新添加的班级的id

SQLMapper

void insertUser(User user);

SelectMapper.xml

      <!--void insertUser(User user);useGeneratedKeys:设置当前标签中的sql使用了自增的主键keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性--><insert id="insertUser" useGeneratedKeys="true" keyProperty="id">insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})</insert>

SQLMapperTest

/* @description:添加用户* @author: Hey * @date: 2022/7/3 22:55* @param: []* @return: void/@Testpublic void testInsertUser(){SqlSession sqlSession = SqlSessionUtils.getSqlSession();SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);User user = new User(null, "王五", "123", 23, "男", "123@163.com");mapper.insertUser(user);System.out.println(user);//User{id=8, username='王五', password='123', age=23, sex='男', email='123@163.com'}}/添加的用户信息是：new User(null, "王五", "123", 23, "男", "123@163.com")输出用户的信息是：User{id=8, username='王五', password='123', age=23, sex='男', email='123@163.com'}id由 null--> 8，这是因为SelectMapper.xml中的设置*/