目录

模糊查询

批量删除

动态设置表名

获取自增的主键

---

模糊查询

 /*** 根据用户名模糊查询用户* @param username* @return*/List<User> getUserByNameLike(@Param("username") String username);

  <select id="getUserByNameLike" resultType="com.csdn.beans.User"><!--方式一，通过#{username} 来实现，select id, username, password, age, sex, emailfrom t_user where username like concat('%',#{username},'%')--><!--方式二，使用#{username}select id, username, password, age, sex, emailfrom t_user where username like '%${username}%'--><!--方式三，也是最常用的方式-->select id, username, password, age, sex, emailfrom t_user where username like "%"#{username}"%"</select>

  @Testpublic void test1(){SqlSession sqlSession= SqlSessionUtil.getSqlSession();SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);List<User> users = mapper.getUserByNameLike("张");users.forEach(user -> System.out.println(user));}结果：
User(id=6, username=张三1, password=123, age=23, sex=女, email=abc@123)
User(id=7, username=张三, password=123, age=23, sex=女, email=abc@123)

批量删除

只能使用${}，如果使用#{}，则解析后的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')

  /*** 根据ids进行批量删除* @param ids* @return*/int deleteBatch(@Param("ids") String ids);

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

 @Testpublic void test2(){SqlSession sqlSession= SqlSessionUtil.getSqlSession();SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);int batch = mapper.deleteBatch("9,10,11");System.out.println(batch);//1,返回被删除的条数。因为数据库中只包含有9的主键值，所以返回1}

动态设置表名

只能使用${}

  /*** 查询指定表中的所有的用户信息* @param tableName* @return*/List<User> getUserFromTable(@Param("tableName") String tableName);

 <select id="getUserFromTable" resultType="com.csdn.beans.User">select id, username, password, age, sex, emailfrom ${tableName}</select>

  @Testpublic void test3(){SqlSession sqlSession= SqlSessionUtil.getSqlSession();SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);List<User> users = mapper.getUserFromTable("t_user");users.forEach(user -> System.out.println(user));}
结果：User(id=6, username=张三1, password=123, age=23, sex=女, email=abc@123)
User(id=7, username=张三, password=123, age=23, sex=女, email=abc@123)
User(id=8, username=admin, password=123456, age=22, sex=男, email=123@123)

获取自增的主键

在mapper.xml中设置两个属性

useGeneratedKeys：设置使用自增的主键

keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参数user对象的某个属性中

  /*** 插入新的用户记录并返回主键值* @param user*/void insertUser(User user);

  <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})</insert>

  @Testpublic void test4(){SqlSession sqlSession= SqlSessionUtil.getSqlSession();SpecialMapper mapper = sqlSession.getMapper(SpecialMapper.class);User user=new User(null,"zhangsan","123",45,"女","email@123.com");mapper.insertUser(user);System.out.println(user.getId());//10}