MyBatis-特殊SQL的执行

1.  通过用户名模糊查询用户信息

SpecialSQLMapper.java

public interface SpecialSQLMapper {
    List<User> getUserByLike(@Param("mohu") String mohu);
}

SpecialSQLMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.atguigu.mybatis.mapper.SpecialSQLMapper">

    <!--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},'%')  (sql中的字符串拼接方法)-->
        select * from t_user where username like "%"#{mohu}"%"
    </select>
</mapper>

测试：

public class SpecialSQLMapperTest {
    @Test
    public void testGetUserByLike(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        List<User> list = mapper.getUserByLike("a");
        list.forEach(System.out::println);
    }
}

2. 批量删除

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

3. 动态设置表名查询数据

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

4. 获取自增的主键

   useGeneratedKeys:表示当前添加功能使用自增的主键
        keyProperty:将添加的数据的自增主键为实体类类型的参数的属性赋值

<!--void insertUser(User user);-->
    <!--
        useGeneratedKeys:表示当前添加功能使用自增的主键
        keyProperty:将添加的数据的自增主键为实体类类型的参数的属性赋值
    -->
    <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,"xiaoming","123456",23,"男","[email protected]");
        mapper.insertUser(user);
        System.out.println(user);
    }