Mybatis --- 自定义映射、特殊和动态SQL

一、特殊SQL的执行

1.1、模糊查询

    /**
     * 测试模糊查询
     * @param mohu
     * @return
     */
    List<User> getUserByLike(@Param("mohu") String mohu);

    <!--List<User> getUserByLike(@Param("mohu") String mohu);-->
    <select id="getUserByLike" resultType="User">
        <!--select * from t_user where username like '%${mohu}%'-->
        <!--select * from t_user where username like concat('%',#{mohu},'%')-->
        select * from t_user where username like "%"#{mohu}"%"
    </select>

    @Test
    public void testGetUserByLike() {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);

        List<User> userList = mapper.getUserByLike("六");
        for (User user : userList) {
            System.out.println(user);
        }
    }

 

1.2、批量删除

    /**
     * 批量删除
     * @param ids
     * @return
     */
     void deleteMoreUser(@Param("ids") String ids);

    <!--int deleteMoreUser(@Param("ids") String ids);-->
    <delete id="deleteMoreUser">
        delete from t_user where id in (${ids})
    </delete>

    @Test
    public void testDeleteMoreUser() {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        
        mapper.deleteMoreUser("4,5,6,7,8,9,10");
    }

 

1.3、动态设置表名

    /**
     * 动态设置表名，查询所有的用户信息
     * @param tableName
     * @return
     */
    List<User> getUserList(@Param("tableName") String tableName);

    <!--List<User> getUserList(@Param("tableName") String tableName);-->
    <select id="getUserList" resultType="User">
        select * from ${tableName}
    </select>

    @Test
    public void testGetUserList() {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);

        List<User> list = mapper.getUserList("t_user");
        for (User user : list) {
            System.out.println(user);
        }
    }

 

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

场景模拟：

t_clazz(clazz_id,clazz_name)

t_student(student_id,student_name,clazz_id)

1、添加班级信息

2、获取新添加的班级的id

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

    /**
     * 添加用户信息
     * @param user
     */
    void insertUser(User user);

    <!--int insertUser(User user);-->
    <!--
        useGeneratedKeys：设置使用自增的主键
        keyProperty：因为增删改有统一的返回值是受影响的行数，
        因此只能将获取的自增的主键放在传输的参数user对象的某个属性中
    -->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
    </insert>

    @Test
    public void testInsertUser() {
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);

        User user = new User(null,"李四", "123456", 10, "男", "lisi@qq.com");
        mapper.insertUser(user);
        System.out.println(user);
        //User{id=11, username='李四', password='123456', age=10, gender='男', email='lisi@qq.com'}
    }

 

 

二、自定义映射resultMap

2.1、resultMap处理字段和属性的映射关系

若字段名和实体类中的属性名不一致，但是