MyBatis学习笔记04

目录

三十八、MyBatis_动态sql_简介&环境搭建

三十九、MyBatis_动态sql_if_判断&OGNL

四十、MyBatis_动态sql_where_查询条件

四十一、MyBatis_动态sql_trim_自定义字符串截取

四十二、MyBatis_动态sql_choose_分支选择

四十三、MyBatis_动态sql_set_与if结合的动态更新

四十四、MyBatis_动态sql_foreach_遍历集合

四十五、MyBatis_动态sql_foreach_mysql下foreach批量插入的两种方式

四十八、MyBatis_动态sql_内置参数_parameter&_databaseId

四十九、MyBatis_动态sql_bind_绑定

五十、MyBatis_动态sql_sql抽取可重用的sql片段

---

三十八、MyBatis_动态sql_简介&环境搭建

第一步：EmployeeMapperDynamicSQL.java文件

package com.nanjing.mybatis.dao;

public interface EmployeeMapperDynamicSQL {
}

第二步：EmployeeMapperDynamicSQL.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.nanjing.mybatis.dao.EmployeeMapperDynamicSQL">

    <!--
    if
    choose (when, otherwise)
    trim (where, set)
    foreach
    -->
</mapper>

三十九、MyBatis_动态sql_if_判断&OGNL

第一步：EmployeeMapperDynamicSQL.java文件

public interface EmployeeMapperDynamicSQL {

    //携带了哪个字段查询条件就带上这个字段的值
    public List<Employee> getEmpsByConditionIf(Employee employee);
}

第二步：EmployeeMapperDynamicSQL.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.nanjing.mybatis.dao.EmployeeMapperDynamicSQL">

    <!--
    if
    choose (when, otherwise)
    trim (where, set)
    foreach
    -->
    <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值-->
    <!--public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <select id="getEmpsByConditionIf" resultType="com.nanjing.mybatis.bean.Employee">
        select * from tbl_employee
        where
        <!-- test：判断表达式（OGNL）
        OGNL参照PPT或者官方文档
        c:if  test
        从参数中取值进行判断

        遇见特殊符号应该去写转义字符：
        &&;
        -->
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="lastName!=null and lastName!=''">
            and last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=''">
            and email=#{email}
        </if>
        <!--ognl会进行字符串与数字的转换判断 "0"==0 -->
        <if test="gender==0 or gender==1">
            and gender=#{gender}
        </if>
    </select>

</mapper>    

第三步：MyBatisTest.java文件

public class MyBatisTest {


    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }


    @Test
    public void testDynamicSql() throws IOException {
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try {
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            //select * from tbl_employee where id=? and last_name like ? and email=?
            Employee employee=new Employee(1,"%T%","jerry@atguigu.com",null);
            List<Employee> emps = mapper.getEmpsByConditionIf(employee);
            for(Employee emp:emps){
                System.out.println(emp);
            }
        } finally {
            openSession.close();
        }

    }
}

四十、MyBatis_动态sql_where_查询条件

第一步：EmployeeMapperDynamicSQL.xml文件

<!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值-->
    <!--public List<Employee> getEmpsByConditionIf(Employee employee);-->
    <select id="getEmpsByConditionIf" resultType="com.nanjing.mybatis.bean.Employee">
        select * from tbl_employee
        <!--where-->
        <where>
        <!-- test：判断表达式（OGNL）
        OGNL参照PPT或者官方文档
        c:if  test
        从参数中取值进行判断

        遇见特殊符号应该去写转义字符：
        &&;
        -->
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="lastName!=null and lastName!=''">
            and last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=''">
            and email=#{email}
        </if>
        <!--ognl会进行字符串与数字的转换判断 "0"==0 -->
        <if test="gender==0 or gender==1">
            and gender=#{gender}
        </if>
        </where>
    </select>

第二步：MyBatisTest.java文件

@Test
    public void testDynam