Mybatis学习之特殊SQL的执行

目录

模糊查询

批量删除

动态设置表名

获取自增的主键

---

模糊查询

 /**
     * 根据用户名模糊查询用户
     * @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, email
        from t_user where username like concat('%',#{username},'%')-->
        <!--方式二，使用#{username}
        select id, username, password, age, sex, email
        from t_user where username like '%${username}%'-->
        <!--方式三，也是最常用的方式-->
        select id, username, password, age, sex, email
        from t_user where username like "%"#{username}"%"
    </select>

  @Test
    public 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>

 @Test
    public 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, email
        from ${tableName}
    </select>

  @Test
    public 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>

  @Test
    public 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

    }