特殊SQL的执行

 1、模糊查询
  select * from users where name like '%${name}%'
  select * from users where name like concat('%',#{name},'%')
  select * from users where name like '%'#{name}'%'

/*
     * 根据用户名模糊查询用户信息
     * */
    List<Users> getUserByLike(@Param("name") String name);



<!--
        select * from users where name like '%${name}%'
        select * from users where name like concat('%',#{name},'%')
 -->

    <select id="getUserByLike" resultType="Users">
        select * from users where name like '%${name}%'
    </select>


//模糊查询
    @Test
    public void  testGetUserByLike(){
        SqlSession  sqLsession = SQLsession.getSqlsession();
        SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
        List<Users> users = mapper.getUserByLike("T");
        System.out.println(users);
    }

2、批量删除 不能使用#{} delete from users where name in(${name})

/*
    * 批量删除
    * */
    int deleteUserByName(@Param("id") String id);



<delete id="deleteUserByName">
        delete from users where id in(${id})
</delete>




//批量删除
    @Test
    public void  testdeleteUserByName(){
        SqlSession  sqLsession = SQLsession.getSqlsession();
        SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
         int re = mapper.deleteUserByName("1548237766262165505,3");
        System.out.println(re);
    }

3、动态查询表名 select * from ${tablename}

/*
    * 动态获取表名
    * */
    List<Users> getUserByTableName(@Param("tablename")String tablename);



<select id="getUserByTableName" resultType="Users">
        select * from  ${tablename}
</select>




//动态查询表名
    @Test
    public void  testgetUserByTableName(){
        SqlSession  sqLsession = SQLsession.getSqlsession();
        SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
        List<Users> re = mapper.getUserByTableName("Users");
        System.out.println(re);
    }

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

        应用场景：

                添加班级信息，

                获取新加的班级的id，

                为班级分配学生，即将某学的班级id修改为新添加的班级的id

/*
    主键自增测试
    * 添加用户信息
    * */
    int insertUser(Users users);


<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into  users values ('2022-08-16 12:15:23',null ,18,'小双双',22,'helen@sina.com',1,0)
</insert>


 @Test
    public void testinsertUser(){
        SqlSession sqlsession = SQLsession.getSqlsession();
        UserMapper mapper = sqlsession.getMapper(UserMapper.class);
        int user = mapper.insertUser(new Users(null,null ,0,"shang",12,"12233",0,0));
        System.out.println(user);

    }