欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Mybatis框架使用:映射文件

程序员文章站 2022-03-22 10:22:21
1. 基本的增删改操作目前的版本已经可以无需指定参数类型(parameterType="Employee"可以省略)insert into tbl_employee (last_name, email, gender)values (#{lastName}, #{email}, #{gender})update tbl_e...

1. 基本的增删改操作

目前的版本已经可以无需指定参数类型(parameterType="Employee"可以省略)

<insert id="insertEmployee">
	insert into tbl_employee (last_name, email, gender)
	values (#{lastName}, #{email}, #{gender})
</insert>

<update id="updateEmployee">
	update tbl_employee
	set last_name = #{lastName}, email = #{email}, gender = #{gender}
	where id = #{id}
</update>

<delete id="deleteEmployeeById">
	delete from tbl_employee
	where id = #{id}
</delete>

在定义接口方法时,可以直接将返回值定义为Integer,Long或者boolean类型,Mybatis可以直接将结果封装成对应的类型

public interface EmployeeMapper {
	public Employee selectEmployeeById(Integer id);
	public Long insertEmployee(Employee employee);
	public boolean updateEmployee(Employee employee);
	public Integer deleteEmployeeById(Integer id);
}

在测试时需要注意要手动提交数据

public void test2() throws Exception {
	InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
	SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
	SqlSession session = factory.openSession();//此时的session默认关闭自动提交
	/* 业务代码 */
	session.commit();//手动提交事务
    session.close();
}

也可以传入true来实现自动提交

SqlSession session = factory.openSession(true);

  • 获取自增主键的值:在配置文件中添加useGeneratedKeys属性值为true,添加keyProperty属性,用来指定获取到的主键值封装给JavaBean的哪个属性
<insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
	insert into tbl_employee (last_name, email, gender)
	values (#{lastName}, #{email}, #{gender})
</insert>

此时再获取参数的id属性值就可以得到自增主键的值

Employee employee = new Employee(null, "周翔", "0", "zhouxiang@qq.com");
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
employeeMapper.insertEmployee(employee);
System.out.println(employee.getId());

2. 参数处理


2.1 单个参数:

使用#{参数名}就可以取出参数值

<select id="selectEmployeeById" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{idtest}
</select>
public Employee selectEmployeeById(Integer id);

大括号中的参数名可以与接口中定义的形参名不一致

2.2 多个参数:

默认情况

多个参数会被封装成一个Map,#{}就是从Map中获取指定的key的值,在封装时,key的值是param1, ... ,paramN,或者可以使用参数的索引,从0开始

<select id="selectEmployeeByNameAndId" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{param1} and last_name = #{param2}
</select>
public Employee selectEmployeeByNameAndId(Integer id, String lastName);

命名参数

使用@Param注解明确的指定封装参数时Map的key

public Employee selectEmployeeByNameAndId(@Param("id") Integer id, @Param("lastName") String lastName);

此时使用#{指定的key}即可取出对应的参数值

<select id="selectEmployeeByNameAndId" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{id} and last_name = #{lastName}
</select>

传入POJO

如果多个参数正好是业务逻辑的数据模型,就可以直接传入pojo,此时使用#{属性名}就可以取出对应的属性值

传入Map

如果多个参数不是业务逻辑中的数据模型,没有对应的pojo,在不常用的情况下可以传入Map,此时#{key}就可以取出map中对应的值

HashMap<String, Object> map = new HashMap<>();
map.put("id",2);
map.put("lastName","简隋英");
Employee employee = employeeMapper.selectEmployeeByMap(map);
<select id="selectEmployeeByMap" resultType="com.dudu.domain.Employee">
	select id, last_name, email, gender
	from tbl_employee
	where id = #{id} and last_name = #{lastName}
</select>

传入TO

如果多个参数不是业务逻辑中的数据模型,没有对应的pojo,在常用的情况下,推荐编写一个TO(Transfer Object)数据传输对象

特殊情况

如果参数是Collection类型或者是数组的话,在封装时使用的key是collection(List可以使用list,数组可以使用array)

public Employee selectEmployeeById(List<Integer> ids);
<select id="selectEmployeeById" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{list[0]}
</select>
List<Integer> list = new ArrayList<>();
list.add(2);
Employee employee = employeeMapper.selectEmployeeById(list);

2.3 # 与 $ 取值的区别

#是以预编译的形式,将参数设置到SQL语句中,相当于PreparedStatement,可以防止sql注入
$取出的值直接拼装在SQL语句中,会存在安全问题

select id, last_name, email, gender from tbl_employee where id = 2 and last_name = ?

大多数情况下取参数的值都应该使用#,但当原生JDBC不支持占位符的位置时,可以使用$

2.4 # 取值时指定参数相关规则

  • javaType
  • jdbcType:在数据为null时,有些数据库(比如Oracle)不能识别mybatis对null的特殊处理,此时必须设置
    mybatis对所有的null值都映射的是原生JDBC的OTHER类型,Oracle不识别这个类型,因此在取参数值时,需要写成#{id, jdbcType=NULL},也可以在全局配置文件中设置
<settings>
	<setting name="jdbcTypeForNull" value="NULL"/>
</settings>
  • mode(存储过程)
  • numericScale(小数点后位数)
  • resultType
  • typeHandler
  • jdbcTypeName

3. select查询操作

  • id:唯一标识符,需要和接口的方法名一致
  • resultType:返回值类型,值为别名或者全类名,如果返回的是集合,定义集合中元素的类型。不能与resultMap同时使用

3.1 返回List

public List<Employee> selectEmployeeByName(String lastName);
<select id="selectEmployeeByName" resultType="com.dudu.domain.Employee">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where last_name like #{lastName}
</select>

3.2 返回Map

封装一条记录(key为属性名,value为对应的属性值)

public Map<String, Object> selectEmployeeByIdReturnMap(Integer id);
<select id="selectEmployeeByIdReturnMap" resultType="map">
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id = #{id}
</select>

{gender=0, last_name=简隋英, id=2, email=jiansuiying@qq.com}

封装多条记录(key为一条数据的主键,value为数据对应的JavaBean)

@MapKey("id")//指定封装Map的时候使用哪个属性作为key
public Map<Integer, Object> selectEmployeeByIdReturnMapWithBean(Integer id);
<select id="selectEmployeeByIdReturnMapWithBean" resultType="com.dudu.domain.Employee" >
	select id, last_name as lastName, email, gender 
	from tbl_employee 
	where id < #{id}
</select>

{1=Employee{id=1, lastName=‘费渡’, gender=‘0’, email=‘feidu@qq.com’},
2=Employee{id=2, lastName=‘简隋英’, gender=‘0’, email=‘jiansuiying@qq.com’}}

3.3 自定义结果映射规则

使用resultMap标签来自定义结果映射规则

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<!-- 列 = 属性 对应 -->
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
    </resultMap>
	<!-- 通过id来引用 -->
    <select id="selectEmployeeById" resultMap="MyEmp">
    	select id, last_name, email, gender
        from tbl_employee
        where id = #{id}
    </select>
</mapper>

如果数据表列名和属性名是相同的话是可以不在resultMap中再次定义,但习惯性都会重新定义一次

3.4 关联查询


3.4.1 使用级联属性来封装JavaBean中的对象

public Employee selectEmpWithDeptByID(Integer id);
<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 使用级联属性进行封装 -->
        <result column="dept_id" property="dept.id"/>
        <result column="dept_name" property="dept.deptName"/>
    </resultMap>
    
    <select id="selectEmpWithDeptByID" resultMap="MyEmp">
    	select te.id, last_name, email, gender, dept_id, dept_name
    	from tbl_employee as te left join tbl_dept td on te.dept_id = td.id
    	where te.id = #{id}
    </select>
</mapper>

3.4.2 使用association标签来定义单个对象的封装规则

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 
        	property="dept"指定哪个属性是联合的对象 
        	javaType="Department"指定这个属性对象的类型【不能省略】 
        -->
        <association property="dept" javaType="com.dudu.domain.Department">
        	<id column="id" property="id"/>
        	<result column="dept_name" property="deptName"/>
        </association>
    </resultMap>
    
    <select id="selectEmpWithDeptByID" resultMap="MyEmp">
    	select te.id, last_name, email, gender, dept_id, dept_name
    	from tbl_employee as te left join tbl_dept td on te.dept_id = td.id
    	where te.id = #{id}
    </select>
</mapper>

使用association进行分步查询

<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        <!-- 
			property属性指定哪个属性是联合的对象
			select属性指定用于查询该对象的SQL语句
			column属性指定将哪一列作为参数传给select中的查询
			该配置的含义为:使用dept_id列作为参数,将selectDeptById方法的结果作为dept对象的值
		 -->
        <association property="dept"
        			 select="com.dudu.dao.DepartmentMapper.selectDeptById"
        			 column="dept_id"/>
    </resultMap>
    
    <select id="selectEmployeeById" resultMap="MyEmp">
    	select * from tbl_employee
    	where id = #{id}
    </select>
</mapper>
<mapper namespace="com.dudu.dao.DepartmentMapper">
	<select id="selectDeptById" resultType="com.dudu.domain.Department">
		select id, dept_name from tbl_dept where id = #{id}
	</select>
</mapper>

分步查询可以实现延迟加载(按需加载):在全局配置文件中加入如下配置

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

此时,如果不需要部门的相关信息,则部门mapper对应的查询方法不会执行

3.4.3 使用collection来定义集合类型的封装规则

部门类中有员工List属性,通过部门id来查询部门的信息

<mapper namespace="com.dudu.dao.DepartmentMapper">
	<resultMap id="MyDept" type="com.dudu.domain.Department">
		<id column="dept_id" property="id"/>
		<result column="dept_name" property="deptName"/>
		<!-- 指定集合中元素的类型 -->
		<collection property="employees" ofType="com.dudu.domain.Employee">
			<!-- 定义集合中元素的封装规则 -->
			<id column="emp_id" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="gender" property="gender"/>
			<result column="email" property="email"/>
		</collection>
	</resultMap>
	
	<select id="selectDeptById" resultMap="MyDept">
		select
			td.id as dept_id,
			td.dept_name as dept_name,
			te.id as emp_id,
			te.last_name as last_name,
			te.email as email,
            te.gender as gender
        from
            tbl_dept as td
            left join tbl_employee as te
        on td.id = te.dept_id
        where td.id = #{id}
    </select>
</mapper>

分步查询

<mapper namespace="com.dudu.dao.DepartmentMapper">
	<resultMap id="MyDept" type="com.dudu.domain.Department">
		<id column="id" property="id"/>
		<result column="dept_name" property="deptName"/>
		<collection property="employees"
					select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
					column="id"/>
    </resultMap>
	<select id="selectDeptById" resultMap="MyDept">
		select id, dept_name from tbl_dept
		where id = #{id}
    </select>
</mapper>
<mapper namespace="com.dudu.dao.EmployeeMapper">
	<resultMap id="MyEmp" type="com.dudu.domain.Employee">
		<id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
    </resultMap>
	<select id="selectEmployeeByDeptId" resultMap="MyEmp">
		select id, last_name, gender, email from tbl_employee
		where dept_id = #{deptId}
    </select>
</mapper>

关于延迟加载的设置此时同样生效

扩展:

  1. 当分步查询需要传递多个参数时,可以将参数封装成一个Map:{ 参数名=相应的列名, … }
<collection property="employees"
			select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
			column="{deptId = id, ......}"/>
  1. fetchType属性
    在主配置文件中配置过懒加载(lazy)后,也可以使用该属性来改变为立即加载(eager)
<collection property="employees"
			select="com.dudu.dao.EmployeeMapper.selectEmployeeByDeptId"
			column="{deptId = id, ......}"
			fetchType="eager"/>/>

3.4.4 使用discriminator鉴别器:

判断某列的值,然后根据某列的值改变封装行为

<resultMap id="MyEmp" type="com.dudu.domain.Employee">
	<id column="id" property="id"/>
	<result column="last_name" property="lastName"/>
	<result column="gender" property="gender"/>
	<result column="email" property="email"/>
	<!-- 需要判断的列名,和该列的java类型 -->
	<discriminator javaType="string" column="gender">
		<case value="1" resultType="com.dudu.domain.Employee">
			<association property="dept"
						 select="com.dudu.dao.DepartmentMapper.selectDeptById"
						 column="dept_id"/>
		</case>
		<case value="0" resultType="com.dudu.domain.Employee">
			<id column="id" property="id"/>
			<result column="last_name" property="lastName"/>
			<result column="gender" property="gender"/>
			<result column="email" property="email"/>
		</case>
    </discriminator>
</resultMap>

本文地址:https://blog.csdn.net/Alice_Lee_Lee/article/details/109461585

相关标签: Mybatis笔记