一、懒加载

比联表查询更高效
用到角色表查角色名的时候，系统才会通过懒加载去执行查询role表的SQL语句
UserMapper.xml：

<!-- 懒加载 513 -->
        <select id="getLazyUserById" resultMap="lazyUserList" parameterType="int">
            select * from smbms_user where id=#{id}
        </select>
        <resultMap type="User" id="lazyUserList">
            <result property="id" column="id"/>
            <!-- 此处实现懒加载，用到了才会执行，提高查询效率 -->
            <association property="role" column="userRole" select="getLazyUserRole">
                <result property="roleCode" column="roleCode"/>
                <result property="roleName" column="roleName"/>
            </association>
        </resultMap>
        <select id="getLazyUserRole" resultType="Role">
            select roleCode,roleName from smbms_role where id=#{userRole}
        </select>

接口：
//懒加载接口 public List getLazyUserById(Integer id);
UserTest：

//懒加载测试
    @Test
    public void getLazyUserById(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        List<User> list = sqlSession.getMapper(UserMapper.class).getLazyUserById(1);
        for (User user : list) {
            logger.info("用户表用户名"+user.getUserName());
            //用到角色表查角色名的时候，系统才会通过懒加载去执行查询role表的SQL语句
            logger.info("角色表角色名"+user.getRole().getRoleName());
        }
        MybatisUtil.closeSqlSession(sqlSession);
    }

二、实现多条件查询

实现动态SQL的元素
if choose where set trim foreach
1、通过用户名和角色id获得用户表所有信息；测试时输入名或者输入角色id分别查询
UserMapper：

<!-- 动态SQL OGNL表达式 513-->
        <select id="getUserAllByNameAndRoleIdOGNL" resultMap="userRoleOneToOne" parameterType="User">
            select u.*,r.roleName,r.roleCode,r.createdBy from smbms_user u,smbms_role r 
            where u.userRole = r.id
            <if test="userName != null and userName !=''">
                and u.userName like CONCAT('%',#{userName},'%')
            </if>
            <if test="userRole != null">
                and u.userRole = #{userRole}
            </if>
        </select>

接口：
//动态SQL OGNL表达式 public List getUserAllByNameAndRoleIdOGNL(User user);
Test：

//OGNL测试
    @Test
    public void getUserAllByNameAndRoleIdOGNL(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        User puser = new User();
        puser.setUserName("");
        puser.setUserRole(1);
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserAllByNameAndRoleIdOGNL(puser);
        for (User user : list) {
            logger.info("OGNL测试--"+user.getUserName());
            //logger.info("角色表角色名"+user.getRole().getRoleName());
        }
        MybatisUtil.closeSqlSession(sqlSession);
    }

2、根据供应商名和联系人查询供应商表全部信息，测试时分别查询；如果测试时不传入查询参数，则显示全部内容
有三种替代where的方式
① ②where 1=1 ③if—trim
ProviderMapper.xml：

<!-- OGNL表达式 -->
        <select id="getProviderAllByNameAndContactOGNLXX"  resultType="Provider" parameterType="Provider">
            <!-- 方案三  -->
            select * from smbms_provider
            <!-- 把第一个and/or字符替换成where -->
            <trim prefix="where" prefixOverrides="and|or">
                <if test="proName != null and proName!=''">
                    and proName like CONCAT('%',#{proName},'%') 
                </if>
                <if test="proContact != null ">
                    and proContact = #{proContact}
                </if>
            </trim>
            <!-- 方案二  -->
            <!-- 方案一
            select * from smbms_provider where 1=1
            <if test="proName != null and proName!=''">
                and proName like CONCAT('%',#{proName},'%') 
            </if>
            <if test="proContact != null ">
                and proContact = #{proContact}
            </if>
            -->
            <!-- 方案一  -->
            <!-- 方案一  
            <where> 
                <if test="proName != null and proName!=''">
                    and proName like CONCAT('%',#{proName},'%') 
                </if>
                <if test="proContact != null ">
                    and proContact = #{proContact}
                </if>
            </where>
            -->
        </select>

接口：
//OGNL表达式 public List getProviderAllByNameAndContactOGNLXX(Provider provider);
ProviderTest：

//ORGL 表达式
    @Test
    public void getProviderAllByNameAndContactOGNLXX(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Provider provider1 = new Provider();
        //provider1.setProName("北京");
        provider1.setProContact("张国强");
        List<Provider> list = sqlSession.getMapper(ProviderMapper.class).getProviderAllByNameAndContactOGNLXX(provider1);
        for (Provider provider : list) {
            logger.info("根据供应商名字和联系人查询信息---"+provider.getId()+"--"+provider.getProName());
        }
        MybatisUtil.closeSqlSession(sqlSession);
    }

三、实现更新操作

1、动态更新用户表

修改表中1个或者多个字段信息，其他字段的信息完成自动填充；不加的话，修改1个或者多个字段信息，其他字段会被自动填充成null;
标签可以替换原来SQL语句中的set,还可以不用管{}后面的，逗号
UserMapper.xml：

<!-- 动态 修改全部信息  -->
        <update id="updateUserAll" parameterType="User">
            update  smbms_user 
            <set>
                <if test="userCode != null">
                    userCode = #{userCode},
                </if>
                <if test="userName != null">
                    userName = #{userName},
                </if>
                <if test="userPassword != null">
                    userPassword = #{userPassword},
                </if>
                <if test="gender != null">
                    gender = #{gender},
                </if>
                <if test="birthday != null">
                    birthday = #{birthday},
                </if>
                <if test="phone != null">
                    phone = #{phone},
                </if>
                <if test="address != null">
                    address = #{address},
                </if>
                <if test="userRole != null">
                    userRole = #{userRole},
                </if>
                <if test="createdBy != null">
                    createdBy = #{createdBy},
                </if>
                <if test="creationDate != null">
                    creationDate = #{creationDate},
                </if>
                <if test="modifyBy != null">
                    modifyBy = #{modifyBy},
                </if>
                <if test="modifyDate != null">
                    modifyDate = #{modifyDate},
                </if>
            </set>
            where id=#{id}
        </update>

接口：
//动态更新 public int updateUserAll(User user);
UserTest：

//动态修改/更新测试
    @Test
    public void updateUserAll(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        User puser = new User();
        puser.setId(17);
        puser.setUserName("小白");
        puser.setUserPassword("abcabc");
        int i = sqlSession.getMapper(UserMapper.class).updateUserAll(puser);
        logger.info(i);
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }

2、动态foreach查找角色号是1和2的人——-两种方法

第一种：传数组型参数
第二种：传List型参数
第三种：传map型
传map型进行多条件查询
UserMapper：

<!--foreach标签  collection传参的类型 item 数组的名称-->
        <!-- 第一种传数组 -->
        <select id="getUserByUserRoleIds_foreach_array"  resultType="User"> 
            select * from smbms_user where userRole in
            <foreach collection="array" item="roleIds" open="(" separator="," close=")">
                #{roleIds}
            </foreach>
        </select>
        <!-- 第二种 传List -->
        <select id="getUserByUserRoleIds_foreach_list"  resultType="User"> 
            select * from smbms_user where userRole in
            <foreach collection="list" item="roleIds" open="(" separator="," close=")">
                #{roleIds}
            </foreach>
        </select>
        <!-- 第三种 传map-->
        <select id="getUserByUserRoleIds_foreach_map"  resultType="User"> 
            select * from smbms_user where userRole in
            <foreach collection="rkey" item="roleMap" open="(" separator="," close=")">
                #{roleMap}
            </foreach>
        </select>
        <!-- 传map，多条件查询 -->
        <select id="getUserByUserRoleIds_foreach_map2"  resultType="User"> 
            select * from smbms_user where 1=1
            <if test="rKey !=null">
             and userRole in
            <foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
                #{roleMap}
            </foreach>
            </if>
            <if test="userName != null">
            and userName like CONCAT('%',#{userName},'%')
            </if>
        </select>

接口：
//动态foreach查询第一种 public List getUserByUserRoleIds_foreach_array(Integer[] roleIds);
//动态foreach查询第二种
public List getUserByUserRoleIds_foreach_list(List roleIds);
//动态foreach查询第三种 public List getUserByUserRoleIds_foreach_map(Map
roleMap);
//传map的多条件查询 public List getUserByUserRoleIds_foreach_map2(Map
roleMap);
UserTest：

//动态foreach查找、第一种
    @Test
    public void getUserByUserRoleIds_foreach_array(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Integer[] arr = {1,2};
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserByUserRoleIds_foreach_array(arr);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());
        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }
    //动态foreach查找、第二种
    @Test
    public void getUserByUserRoleIds_foreach_list(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        List<Integer> list1 = new ArrayList<Integer>();
        list1.add(1);
        list1.add(2);
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserByUserRoleIds_foreach_list(list1);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());
        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }
//动态foreach查找、第三种
    @Test
    public void getUserByUserRoleIds_foreach_map(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        List<Integer> list1 = new ArrayList<Integer>();
        list1.add(1);
        list1.add(2);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("rkey", list1);
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserByUserRoleIds_foreach_map(map);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());
        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }
//map多条件查询
    @Test
    public void getUserByUserRoleIds_foreach_map2(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        List<Integer> list1 = new ArrayList<Integer>();
        list1.add(1);
        list1.add(2);
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("rKey", list1);
        map.put("userName", "赵");
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserByUserRoleIds_foreach_map2(map);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());
        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }

3、练习通过供应商不同的id查询这些供应商下的订单列表信息

BillMapper：

<!-- 动态查询传数组  根据不同供应商id查询订单列表 -->
        <select id="getBillByProvidersIds_foreach_array"  resultType="Bill"> 
            select * from smbms_bill where providerId in
            <foreach collection="array" item="providerIds" open="(" separator="," close=")">
                #{providerIds}
            </foreach>
        </select>
        <!-- List是第二种，实现方法和userMapper中实现一样 -->

接口：
//根据不同供应商id查询订单列表 public List getBillByProvidersIds_foreach_array(Integer[] providerIds);
BillTest：

    //动态foreach  根据不同供应商id查询订单列表
    @Test
    public void getBillByProvidersIds_foreach_array(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Integer[] arr = {1,2};
        List<Bill> list = sqlSession.getMapper(BillMapper.class).getBillByProvidersIds_foreach_array(arr);
        for (Bill bill : list) {
            logger.info(bill.getId()+"--"+bill.getBillCode()+"--"+bill.getProductName()+"--"+bill.getProductDesc()+"--"+bill.getProviderId());
        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }

四、choose多条件查询

choose类似于switch；满足其中一个条件之后，就不在执行下面的条件；如果之前添加都不满足，那就执行最后otherwise；
UserMapper：

<!-- choose(when,otherwise) 多条件查询 -->
        <select id="getUserChooseWhen" parameterType="User" resultType="User">
            select * from smbms_user where 1=1
            <choose>
                <when test="userName != null">
                    and userName like CONCAT('%',#{userName},'%')
                </when>
                <when test="userCode != null">
                    and userCode =#{userCode}
                </when>
                <otherwise>
                <!-- 如果前面两条没查到，就按照创建时间降序排序 -->
                    order by creationDate desc
                </otherwise>
            </choose>
        </select>

接口：

//choose多条件查询514
    public List<User> getUserChooseWhen(User user);

UserTest：

//choose多条件查询514，类似于switch，满足一个条件就不执行其他条件。
    @Test
    public void getUserChooseWhen(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        User pu = new User();
        pu.setUserName("孙");
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserChooseWhen(pu);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());

        }
        sqlSession.commit();
        MybatisUtil.closeSqlSession(sqlSession);
    }

五、简单的分页查询

UserMapper：

<!-- 分页查询基本写法514 -->
        <select id="getUserByPage" parameterType="map" resultType="User">
            select * from smbms_user order by id desc limit #{from},#{size}
        </select>

接口：
//分页查询基本写法 514 public List getUserByPage(Map map);
UserTest：

//分页查询基本方法，514
    @Test
    public void getUserByPage(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        //当前是第几页
        int pageIndex = 2;
        //每页显示多少条
        int pageSize = 3;
        Map<String, Object> map = new HashMap<String,Object>();
        map.put("from", (pageIndex-1)*pageSize);//从第几条开始，
        map.put("size", pageSize);//显示多少条
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserByPage(map);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());

        }
        MybatisUtil.closeSqlSession(sqlSession);
    }

六、面试：对比$和

UserMapper：

<!-- 面试题，$和#号对比 -->
        <!-- 用户名密码登录 -->
        <select id="Login" parameterType="User" resultType="User">
            select * from smbms_user 
            where userCode = #{userCode}
            and userPassword = #{userPassword}
        </select>

接口：

//对比$和#号的区别 514
    public List<User> Login(User user);

UserTest：

//对比$和#。登录举例如果xml文件中，userCode前面是$符号，那么Set里要写成“‘admin’”
    @Test
    public void Login(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        User pu = new User();
        pu.setUserCode("admin");
        //sql注入，#{}可以防止sql注入，？站位 动态填充参数（‘参数值’）
        //${}前面写$符号，随便字符串拼接一个密码加上or 1=1,都会登录成功
        pu.setUserPassword("1234567");
        List<User> list = sqlSession.getMapper(UserMapper.class).Login(pu);
        if(list.size()>0){
            logger.info("登录成功");
        }else{
            logger.info("登录失败");
        }
//        for (User user : list) {
//            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());
//
//        }
        MybatisUtil.closeSqlSession(sqlSession);
    }

七、不建议使用的万能sql
UserMapper:

<!-- 万能sql -->
        <select id="getUserAnayWhere" parameterType="string" resultType="User">
            ${sql}
        </select>

接口：

//万能sql 514
    public List<User> getUserAnayWhere(@Param("sql")String sql);

UserTest：

//万能sql,很危险；只供内部使用
    @Test
    public void getUserAnayWhere(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        String sql = "select * from smbms_user where id = 1";
        List<User> list = sqlSession.getMapper(UserMapper.class).getUserAnayWhere(sql);
        for (User user : list) {
            logger.info(user.getId()+"--"+user.getUserName()+"--"+user.getUserRole());

        }
        MybatisUtil.closeSqlSession(sqlSession);
    }