MyBatis —— 参数处理

桑鱼

发布于 2020-03-18 07:15:43

66200

代码可运行

文章被收录于专栏：学习笔记持续记录中...学习笔记持续记录中...

运行总次数：0

代码可运行

单个参数&多个参数&命名参数

单个参数

语法 #{参数名} ，直接取出参数值。

多个参数会被封装成一个map

key: param1....paramN,或者参数的索引也可以
value: 传入的参数值

通过#{key} 从map中获取指定的key的值

<?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.sangyu.mapper.EmployeeMapper">
    <select id="getEmpByIdAndLastName" resultType="com.sangyu.bean.Employee">
        select * from tbl_employee where id = #{param1} and last_name=#{param2}
    </select>
</mapper>

除了上面的方法，还可以使用命名参数

命名参数明确指定封装参数时map的key

语法： @Param("id")

指定参数名，多个参数还是封装一个map，但是此时key 使用的是@Param注解指定的值通过#{指定的key} 从map中获取指定的参数值

public interface EmployeeMapper {
    public Employee getEmpByIdAndLastName(@Param("id") Integer id,@Param("lastName") String lastName);
}

<?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.sangyu.mapper.EmployeeMapper">
    <select id="getEmpByIdAndLastName" resultType="com.sangyu.bean.Employee">
          select * from tbl_employee where id = #{id} and last_name=#{lastName}
    </select>
</mapper>

POJO&Map

POJO

如果多个参数正好是我们业务逻辑的数据模型，我就可以直接传入pojo，并通过#{属性名} 取出传入的pojo的属性值，下面的例子就是通过这种方式，直接传入JavaBean的对象。（INSERT也一样）

map

如果多个参数不是业务模型中的数据，没有对应的pojo，为了方便，我们也可以传入map，再通过 #{key} 取出map中对应的值

public interface EmployeeMapper {
    public Employee getEmpByMap(Map<String,Object> map);
}

<?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.sangyu.mapper.EmployeeMapper">
    <select id="getEmpByMap" resultType="com.sangyu.bean.Employee">
        select * from tbl_employee where  id = #{id} and last_name = #{lastName}
    </select>
</mapper>

执行测试代码

public class MyBatisTest {
    /**
     * 测试传入参数为map的情况
     * @throws IOException
     */
    @Test
    public void Test07() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据xml创建sqlSessionFactory
        SqlSession session = sqlSessionFactory.openSession(); 
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            Map<String,Object> map = new HashMap<>();
            map.put("id",1);
            map.put("lastName","aa");
            Employee employee = mapper.getEmpByMap(map);
            System.out.println(employee);
        } finally {
            session.close();
        }
    }
}

也可以传入Collection（List，Set）类型或者是数组，会把传入的List或数组封装在map中，key就是Collection，取值语法为#{list[0]}

public interface EmployeeMapper {
    public Employee getEmpByList(List<Integer> ids);
}

<select id="getEmpByList" resultType="com.sangyu.bean.Employee">
        select * from tbl_employee where  id = #{list[0]}
</select>

public class MyBatisTest {
    /**
     * 测试传入参数为List的情况
     * @throws IOException
     */
    @Test
    public void Test08() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据xml创建sqlSessionFactory
        SqlSession session = sqlSessionFactory.openSession(); // 2. 从 SqlSessionFactory 中获取 SqlSession 的实例，SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
        try {
            // 3. 获取接口的实现类对象
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            List<Integer> list = new ArrayList<>();
            list.add(0,1);
            Employee employee = mapper.getEmpByList(list);
            System.out.println(employee);
        } finally {
            session.close();
        }
    }
}

#和$取值的区别

#{} 和${} 都可以获取map中的值或者pojo对象属性的值，两者的区别是#{}会以预编译的形式，将参数设置到sql语句中（类似PreparedStatement），可以防止sql注入。${} 取出的值直接拼接在sql语句中，会有安全问题。大多数情况下，我们取参数的值都应该去使用#{}

当原生jdbc不支持占位符的地方就可以使用${} 进行取值，比如分表：按照年份分表拆分查询的时候就可以使用：

select * from ${year}_salary where xxx;
select * from tbl_employee order by ${f_name} 
select * from ${tableName} order by ${f_name}

<select id="getEmpByMap" resultType="com.sangyu.bean.Employee">
     select id,last_name,gender,email from ${tableName} where id = #{id}
</select>

@Test
public void Test09() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
    SqlSession session = sqlSessionFactory.openSession(); 
    try {
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("tableName","tbl_employee");
        map.put("id",1);
        Employee employee = mapper.getEmpByMap(map);
        System.out.println(employee);
    } finally {
        session.close();
    }
 }

Select 返回list

定义mapper接口

public interface EmployeeMapper {
    public List<Employee> getEmpsByLastNameLike(String lastName);
}

定义mapper.xml

<!-- 如果返回的是一个集合，要写集合中元素的类型   -->
<select id="getEmpsByLastNameLike" resultType="com.sangyu.bean.Employee">
    select * from tbl_employee where last_name like #{lastName}
</select>

执行测试代码

public class MyBatisTest {
    /**
     * 返回值是list
     * @throws IOException
     */
    @Test
    public void Test10() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据xml创建sqlSessionFactory
        SqlSession session = sqlSessionFactory.openSession(); // 2. 从 SqlSessionFactory 中获取 SqlSession 的实例，SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
        try {
            EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
            List<Employee> like = mapper.getEmpsByLastNameLike("%c%");
            for(Employee employee : like){
                System.out.println(employee);
            }
        } finally {
            session.close();
        }
    }
}

Select 返回map

定义mapper接口

public interface EmployeeMapper {
    // 返回一条记录的map
    // key就是列名，值就是对应的值
    public Map<String,Object> getEmpByIdReturnMap(Integer id);
}

<select id="getEmpByIdReturnMap" resultType="map">
    select * from tbl_employee where id = #{id}
</select>

执行测试代码

@Test
public void Test11() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
    SqlSession session = sqlSessionFactory.openSession(); 
    try {
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Map<String,Object> map = mapper.getEmpByIdReturnMap(1);
        System.out.println(map);
    } finally {
        session.close();
    }
}

// 执行结果
{gender=1, last_name=aa, id=1, email=aa@aa.com}

多条记录封装一个map：Map<Integer,Employee>，键是这条记录的主键，值是记录封装后的javaBean

public interface EmployeeMapper {
    // 多条记录封装一个map：Map<Integer,Employee>，键是这条记录的主键，值是记录封装后的javaBean
    // 告诉mybatis封装这个map的时候使用哪个属性作为主键
    @MapKey("id")
    public Map<Integer,Employee> getEmpByLastNameLikeReturnMap(String lastName);
}

<select id="getEmpByLastNameLikeReturnMap" resultType="map">
    select * from tbl_employee where last_name like #{lastName}
</select>

@Test
public void Test12() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据xml创建sqlSessionFactory
    SqlSession session = sqlSessionFactory.openSession(); // 2. 从 SqlSessionFactory 中获取 SqlSession 的实例，SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
    try {
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Map<Integer,Employee> map = mapper.getEmpByLastNameLikeReturnMap("%c%");
        System.out.println(map);
    } finally {
        session.close();
    }
}

// 执行结果
{3={gender=0, last_name=cc, id=3, email=cc@cc.com}, 5={gender=0, last_name=lucy, id=5, email=lucy@aa.com}, 12={gender=0, last_name=CICI, id=12, email=CICI@aa.com}}

resultMap-自定义结果映射规则

定义mapper接口

public interface EmployeeMapperPlus {
    public Employee getEmpById(Integer id);
}

定义mapper.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.sangyu.mapper.EmployeeMapperPlus">
    <resultMap id="MyEmp" type="com.sangyu.bean.Employee">
        <!--  指定主键列的封装规则
              id 定义主键会底层有优化
              column 指定哪一列
              property 指定对应的javaBean属性-->
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <!--   其他不指定的会自动封装     -->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
    </resultMap>
    <!-- resultMap 自定义结果集映射规则    -->
   <select id="getEmpById" resultMap="MyEmp">
        select * from tbl_employee where id=#{id}
   </select>
</mapper>

执行测试代码

 @Test
public void Test13() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession session = sqlSessionFactory.openSession(); 
    try {
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpById(1);
        System.out.println(employee);
    } finally {
        session.close();
    }
}

// 执行结果
Employee{id=1, lastName='aa', email='aa@aa.com', gender='1'}

resultMap-关联查询

# 创建表 tbl_dept
create table tbl_dept( id int(11) primary key auto_increment, dept_name varchar(255) );

# 修改 tbl_employee 增加字段
alter table tbl_employee add column d_id int(11);

/**
* 给Employee增加新的字段，关联Department
*/
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;
    ... // 编写对应get和set方法、有参和无参的构造器、并重写toString方法
}

public class Department {
    private Integer id;
    private String departmentName;
... // 编写对应get和set方法、有参和无参的构造器、并重写toString方法
}

编写mapper接口

public interface EmployeeMapperPlus {
    public Employee getEmpAndDept(Integer id);
}

编写mapper.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.sangyu.mapper.EmployeeMapperPlus">
    <!--  联合查询：级联属性封装结果集  -->
    <resultMap id="MyEmp" type="com.sangyu.bean.Employee">
        <!--  指定主键列的封装规则
              id 定义主键会底层有优化
              column 指定哪一列
              property 指定对应的javaBean属性-->
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <!--   其他不指定的会自动封装     -->
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>
        <result column="d_id" property="dept.id"/>
        <result column="dept_name" property="dept.departmentName"/>
    </resultMap>
    <!-- resultMap 自定义结果集映射规则    -->
   <select id="getEmpById" resultMap="MyEmp">
        select * from tbl_employee where id=#{id}
   </select>

    <!--  查询Employee的同时查询员工对应的部门
    Employee ===> Department
    一个员工有与之对应的部门信息-->
    <select id="getEmpAndDept" resultMap="MyEmp">
        select * from tbl_employee e,tbl_dept d where d.id = e.d_id and e.id = #{id};
    </select>
</mapper>

执行测试代码

@Test
public void Test14() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); // 根据xml创建sqlSessionFactory
    SqlSession session = sqlSessionFactory.openSession(); // 2. 从 SqlSessionFactory 中获取 SqlSession 的实例，SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句
    try {
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee empAndDept = mapper.getEmpAndDept(1);
        System.out.println(empAndDept);
        System.out.println(empAndDept.getDept());
    } finally {
        session.close();
    }
}

// 测试结果
Employee{id=1, lastName='aa', email='aa@aa.com', gender='1', dept=Department{id=1, departmentName='RD'}}
Department{id=1, departmentName='RD'}

第二种方式关于联合查询

<?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.sangyu.mapper.EmployeeMapperPlus">
    <resultMap id="MyEmp" type="com.sangyu.bean.Employee">
        <!--  指定主键列的封装规则
              id 定义主键会底层有优化
              column 指定哪一列
              property 指定对应的javaBean属性-->
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender"/>

        <!--  association可以指定联合的javaBean对象
             property = "dept" 指定哪个属性是联合的对象
             javaType 指定这个属性对象类型【不能省略】-->
        <association property="dept" javaType="com.sangyu.bean.Department">
            <id column="id" property="id"></id>
            <result column="dept_name" property="departmentName"/>
        </association>
    </resultMap>
    <!-- resultMap 自定义结果集映射规则    -->
   <select id="getEmpById" resultMap="MyEmp">
        select * from tbl_employee where id=#{id}
   </select>

    <!--  查询Employee的同时查询员工对应的部门
    Employee ===> Department
    一个员工有与之对应的部门信息-->
    <select id="getEmpAndDept" resultMap="MyEmp">
        select * from tbl_employee e,tbl_dept d where d.id = e.d_id and e.id = #{id};
    </select>
</mapper>

使用association进行分步查询

编写Department的mapper接口

public interface DepartmentMapper {   
    public Department getDeptById(Integer id);
}

编写Department的mapper.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">
<!--namespace：名称空间，对应mapper接口-->
<mapper namespace="com.sangyu.mapper.DepartmentMapper">

    <select id="getDeptById" resultType="com.sangyu.bean.Department">
        select id,dept_name departmentName from tbl_dept where id = #{id}
    </select>
</mapper>

编写Employee的Mapper接口

public interface EmployeeMapperPlus {
    public Employee getEmpByIdStep(Integer id);
}

编写Employee的mapper.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.sangyu.mapper.EmployeeMapperPlus">
    <resultMap id="MyEmpByStep" type="com.sangyu.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="email" property="email"/>
        <result column="gender" property="gender" />
        <!--  association 定义关联对象的封装规则
              select 表明当前属性是调用select 指定的的方法查出的结果
              column 指定将哪一列的值传给这个方法
              流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性-->
        <association property="dept"
                     select="com.sangyu.mapper.DepartmentMapper.getDeptById" column="d_id">
        </association>
    </resultMap>
    <!-- resultMap 自定义结果集映射规则    -->
   <select id="getEmpByIdStep" resultMap="MyEmpByStep">
        select * from tbl_employee where id=#{id}
   </select>
</mapper>

执行测试代码

@Test
 public void Test15() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); 
    SqlSession session = sqlSessionFactory.openSession(); 
    try {
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpByIdStep(1);
        System.out.println(employee);
        System.out.println(employee.getDept());
    } finally {
        session.close();
    }
}

延迟加载

每次查询Employee对象的时候，都会将department一起查询出来，这个时候可以使用延迟加载时候等到需要的时候再去查询，延迟加载在分布查询的基础之上加上两个配置

在主配置文件设置settings

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
 </settings>

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

xml

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

xml

sql

登录后参与评论

暂无评论