6.mybatis_sql(传智播客)

需求1:根据用户名称模糊查询用户信息

动态sql:mybatis核心对sql语句进行灵活操作，通过表达式进行判断，对sql进行灵活拼接、组装。
sql片段:将动态sql判断代码块抽取出来，组成一个sql片段。其它的statement中就可以引用sql片段，方便程序员进行开发。
1.mapper接口

public interface UserMapper {
    //根据用户姓名模糊匹配
    User findUserList(User user) throws Exception;
}

2.sql映射文件

<?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="mapper.UserMapper">
	<!--if和where-->
	<select id="findUserList" parameterType="user" resultType="user">
	    select * from user
	    <where>
	        <include refid="findUserByIdAndUsername"/>
	    </where>
	</select>
	
	<!--sql片段-->
	<sql id="findUserByIdAndUsername">
	    <if test="id!=null and id!=''">
	        and id=#{id}
	    </if>
	    <if test="username!=null and username!=''">
	        and username like '%${username}%'
	    </if>
	</sql>
</mapper>

注意：如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，如: include refid="namespace.sql片段id”

3.pojo类编写

public class User {
    //属性名要和数据库表的字段对应
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private String birthday;// 生日
    private String address;// 地址
    //get和set方法......
}

4.测试

public class Test {
    private static final Logger log = Logger.getLogger(Test.class.getName());
    public static void main(String[] args) throws Exception {
        // 1.获取mybatis配置文件的路径
        String resource = "config/mybatis.xml";
        // 2.得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 3.根据mybatis配置文件的信息创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        //根据用户姓名模糊匹配
        User user = new User();
        user.setId(1);
        user.setUsername("en");
        User user1= (User)userMapper.findUserList(user);
        System.out.println(user1);
    }
}

运行测试结果如下

需求2:根据用户的ids批量查询用户信息

向sql传递数组或List，mybatis使用foreach解析。在用户查询列表和查询总数的statement中增加多个id输入查询。即实现SELECT * FROM USER WHERE id=1 OR id=10 OR id=16。
1.mapper接口

List<User> findUserListByIds(QueryVo queryVo) throws Exception;

2.sql映射文件

<select id="findUserListByIds" parameterType="QueryVo" resultType="user">
    select * from user
    <where>
        <if test="ids!=null and ids.size>0">
             <!--
                collection:指定输入对象集合中的属性
                item：每次遍历生成的对象名
                open：开始遍历拼接的串
                close：结束时拼接的串
                separator：每个对象的中间拼接的串
             -->
            <foreach collection="ids" item="id" open=" and id in(" close=")" separator="," >
                #{id}
            </foreach>
        </if>
    </where>
</select>

3.po类编写

public class QueryVo {
    private User user;
    private List<Integer> ids;
    //get和set方法......
}

4.测试

public class Test {
    private static final Logger log = Logger.getLogger(Test.class.getName());
    public static void main(String[] args) throws Exception {
        // 1.获取mybatis配置文件的路径
        String resource = "config/mybatis.xml";
        // 2.得到配置文件流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 3.根据mybatis配置文件的信息创建会话工厂
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        
        //根据用户id批量查找用户
        QueryVo queryVo = new QueryVo();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1);//查询id为1的用户
        ids.add(2); //查询id为10的用户
        queryVo.setIds(ids);
        List<User> list = (List<User>) userMapper.findUserListByIds(queryVo);
        System.out.println(list.size());
    }
}

运行测试结果如下