通过XML、注解配置MyBatis SQL映射器

最新推荐文章于 2022-03-27 15:32:06 发布

T_白日梦想家

最新推荐文章于 2022-03-27 15:32:06 发布

阅读量956

点赞数

分类专栏：后端开发文章标签： mybatis mybatis xml mybatis注解

本文链接：https://blog.csdn.net/A632189007/article/details/79392254

版权

后端开发专栏收录该内容

41 篇文章 0 订阅

订阅专栏

SpringBoot配置MyBatis在 SpringBoot整合MyBatis开发这篇文章中已经描述了，这篇文章主要讲解一些SQL映射器中的写法及常用标签。

定义xml映射文件

<mapper namespace="com.test.springboot.mybatis.dao.UserMapper">

	<resultMap id="AddressResultMap" type="com.test.springboot.mybatis.bean.AddressPo">
		<id column="address_id" jdbcType="INTEGER" property="id" />
		<result column="address" jdbcType="VARCHAR" property="address" />
		<result column="user_id" jdbcType="VARCHAR" property="user_id" />
	</resultMap>

	<resultMap id="UserResultMap" type="com.test.springboot.mybatis.bean.UserPo">
	  	<id column="id" jdbcType="VARCHAR" property="id" />
	    <result column="username" jdbcType="VARCHAR" property="username" />
	    <result column="password" jdbcType="VARCHAR" property="password" />
	    <result column="realname" jdbcType="VARCHAR" property="realname" />
	    <result column="company" jdbcType="VARCHAR" property="company" />
	    <result column="job" jdbcType="VARCHAR" property="job" />
	    <result column="salt" jdbcType="VARCHAR" property="salt" />
	    <result column="status" jdbcType="TINYINT" property="status" />
	    <result column="time" jdbcType="BIGINT" property="time" />
	    <!-- 一对多映射 -->
	    <collection property="addressList" resultMap="AddressResultMap"/>
	</resultMap>
  
	<sql id="Base_Column_List" >
		id, username, password, realname, company, job, salt, status, time
	</sql>

	<select id="queryList" resultMap="UserResultMap">
	    SELECT 
	    	<include refid="Base_Column_List"/>
	    FROM auth_user
	    <where>
	        <if test="username != null">
	            username = #{username}
	        </if>
	        <if test="job != null">
	            AND job = #{job}
	        </if>
	    </where>
	</select>
  	
  	<select id="queryByUsername" resultMap="UserResultMap">
  		SELECT
  			u.id, u.username as aaa, u.password, u.realname, u.company, u.job, u.salt, u.status, u.time, a.address, a.id as address_id, a.user_id 
  		FROM 
  			auth_user 
  		AS u LEFT JOIN auth_address AS a ON u.id = a.user_id WHERE u.username = #{username}
  	</select>
  	
</mapper>

namespace：该标签用于指定映射器对应的接口文件，需要写接口文件全路径。

resultMap：用户设置SQL返回数据保存字段信息。

resultMap.type：用于指定返回信息对应的实体类。

sql：可编写一个SQL片段，用于复用到具体的SQL操作中。

select：用作查询操作的SQL标签。

MyBatis 不同映射语句实例

INSERT

通过自增长设置主键

<insert id="insertStudent" parameterType="Student" useGeneratedKeys="true" keyProperty="studId">
    INSERT INTO STUDENTS(NAME, EMAIL, PHONE) VALUES(#{name},#{email},#{phone})
</insert>

针对支持序列生成主键值

<insert id="insertStudent" parameterType="Student">
    <selectKey keyProperty="studId" resultType="int" order="BEFORE">
        SELECT ELEARNING.STUD_ID_SEQ.NEXTVAL FROM DUAL
    </selectKey>
    INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL, PHONE) VALUES(#{studId},#{name},#{email},#{phone})
</insert>

//使用触发器  
<insert id="insertStudent" parameterType="Student">
    INSERT INTO STUDENTS(NAME,EMAIL, PHONE) VALUES(#{name},#{email},#{phone})
    <selectKey keyProperty="studId" resultType="int" order="AFTER">
        SELECT ELEARNING.STUD_ID_SEQ.CURRVAL FROM DUAL
    </selectKey>
</insert>

SELECT

<select id="queryList" resultMap="UserResultMap">
    SELECT 
    	<include refid="Base_Column_List"/>
    FROM auth_user
    <where>
        <if test="username != null">
            username = #{username}
        </if>
        <if test="job != null">
            AND job = #{job}
        </if>
    </where>
</select>

条件不定查询，通过where标签及if、choose等标签可对字段进行判断，看是否需要添加到where条件中进行查询。

  	<select id="queryByUsername" resultMap="UserResultMap">
  		SELECT
  			u.id, u.username as aaa, u.password, u.realname, u.company, u.job, u.salt, u.status, u.time, a.address, a.id as address_id, a.user_id 
  		FROM 
  			auth_user 
  		AS u LEFT JOIN auth_address AS a ON u.id = a.user_id WHERE u.username = #{username}
  	</select>

多表联合查询，需要在resultMap配置好一对多、一对一等关系。SQL返回结果会直接注入到resultMap配置的实体类中。

MyBatis结果集映射规则

对于 List, Collection, Iterable类型，返回 java.util.ArrayList
对于Map 类型，返回 java.util.HashMap
对于Set 类型：返回 java.util.HashSet
对于SortedSet类型：返回java.util.TreeSet

MyBatis 映射关系

一对一映射

方式1：使用点语法进行映射

public class Student {
   private Integer studId;
   private String name;
   private String email;
   private Address address;        //一个地址对象， 每个学生对应一个地址  
}

<resultMap type="Student" id="StudentWithAddressResult">
  <id property="studId" column="stud_id" />
  <result property="name" column="name" />
  <result property="email" column="email" />
  <result property="phone" column="phone" />
  <result property="address.addrId" column="addr_id" />
  <result property="address.street" column="street" />
  <result property="address.city" column="city" />
  <result property="address.state" column="state" />
  <result property="address.zip" column="zip" />
  <result property="address.country" column="country" />
</resultMap>

<select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
    SELECT 
	    STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
	FROM 
		STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
    WHERE 
	    STUD_ID=#{studId}
</select>

方式2：使用嵌套结果 ResultMap进行映射

<resultMap type="Address" id="AddressResult">
      <id property="addrId" column="addr_id" />
      <result property="street" column="street" />
      <result property="city" column="city" />
</resultMap>

<resultMap type="Student" id="StudentWithAddressResult">
      <id property="studId" column="stud_id" />
      <result property="name" column="name" />
      <result property="email" column="email" />
      <association property="address" resultMap="AddressResult" />
</resultMap>

<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
    SELECT 
	    STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
    FROM 
	    STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
    WHERE 
	    STUD_ID=#{studId}
</select>

方式3：本身内部嵌套

<resultMap type="Student" id="StudentWithAddressResult">
      <id property="studId" column="stud_id" />
      <result property="name" column="name" />
      <result property="email" column="email" />
      <association property="address" javaType="Address">
	        <id property="addrId" column="addr_id" />
	        <result property="street" column="street" />
	        <result property="city" column="city" />
	        <result property="state" column="state" />
	        <result property="zip" column="zip" />
	        <result property="country" column="country" />
      </association>
</resultMap>

<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
    SELECT 
	    STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
    FROM 
	    STUDENTS S LEFT OUTER JOIN ADDRESSES A ON S.ADDR_ID=A.ADDR_ID
    WHERE 
	    STUD_ID=#{studId}
</select>

一对多映射

使用<collection>元素将一对多类型的结果映射到一个对象集合上

使用嵌套对象形式显示

public class Tutor {
    private Integer tutorId;
    private String name;
    private String email;
    private Address address;
    private List<Course> courses;
    / setters & getters
}

<resultMap type="Tutor" id="TutorResult">
	<id column="tutor_id" property="tutorId" />
	<result column="tutor_name" property="name" />
	<result column="email" property="email" />
	<collection property="courses" resultMap="CourseResult" />
</resultMap>

<select id="findTutorById" parameterType="int" resultMap="TutorResult">
	SELECT 
		T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID, C.NAME, DESCRIPTION, START_DATE, END_DATE
	FROM 
		TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID
	LEFT OUTER JOIN 
		COURSES C ON T.TUTOR_ID=C.TUTOR_ID
	WHERE 
		T.TUTOR_ID=#{tutorId}
</select>

使用嵌套语句实现

<resultMap type="Tutor" id="TutorResult">
	<id column="tutor_id" property="tutorId" />
	<result column="tutor_name" property="name" />
	<result column="email" property="email" />
	<association property="address" resultMap="AddressResult" />
	<collection property="courses" column="tutor_id" select="findCoursesByTutor" />
</resultMap>

<select id="findTutorById" parameterType="int" resultMap="TutorResult">
    SELECT 
	    T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL
    FROM TUTORS T WHERE T.TUTOR_ID=#{tutorId}
</select>

<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
  SELECT * FROM COURSES WHERE TUTOR_ID=#{tutorId}
</select>

注意：嵌套 Select 语句查询会导致 N+1 选择问题。首先，主查询将会执行(1 次),对于主查询返回的每一行，另外一个查询将会被执行(主查询 N 行,则此查询 N 次)。对于大型数据库而言,这会导致很差的性能问题。

动态SQL

mybatis 提供: <if>,<choose>,<where>,<foreach>,<trim> 进行构造动态SQL

if

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult"></select>
    SELECT * FROM COURSES WHERE TUTOR_ID= #{tutorId}
    <if test="courseName != null">
	    AND NAME LIKE #{courseName}
    </if>
</select>

当if中test条件成立时，才会添加if中的内容到SQL语句中。

choose, when, otherwise

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
    SELECT * FROM COURSES
    <choose>
        <when test="searchBy == 'Tutor'">
            WHERE TUTOR_ID= #{tutorId}
        </when>
        <when test="searchBy == 'CourseName'">
            WHERE name like #{courseName}
        </when>
        <otherwise>
            WHERE TUTOR start_date >= now()
        </otherwise>
    </choose>
</select>

mybatis计算<choose> 测试条件的值，且使用第一个值为true的子句，如果没有条件为true，则使用<otherwise>内的子句。

where

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
    SELECT * FROM COURSES
    <where>
        <if test=" tutorId != null ">
            TUTOR_ID= #{tutorId}
        </if>
        <if test="courseName != null">
            AND name like #{courseName}
        </if>
        <if test="startDate != null">
            AND start_date >= #{startDate}
        </if>
        <if test="endDate != null">
            AND end_date <= #{endDate}
        </if>
    </where>
</select>

trim

<select id="searchCourses" parameterType="hashmap" resultMap="CourseResult">
     SELECT * FROM COURSES
	 <trim prefix="WHERE" prefixOverrides="AND | OR"> 
	     <if test=" tutorId != null ">
	         TUTOR_ID= #{tutorId}
	     </if>  
	     <if test="courseName != null">
	        AND name like #{courseName}
	     </if>
     </trim>
</select>

trim标记是一个格式化的标记，可以完成set或者是where标记的功能。

prefix：前缀。

prefixoverride：去掉第一个and或者是or。

<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
	<if test="name != null and name.length()>0"> name=#{name} , </if>
	<if test="gender != null and gender.length()>0"> gender=#{gender} ,  </if>
</trim>

假如说name和gender的值都不为null的话打印的SQL为：update user set name='xx' , gender='xx' where id='x'。gender后面跟的逗号不会显示。

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

suffix：后缀

foreach

<select id="searchCoursesByTutors" parameterType="map" resultMap="CourseResult">
	SELECT * FROM COURSES
	<if test="tutorIds != null">
		<where>
			<foreach item="tutorId" collection="tutorIds">
				OR tutor_id=#{tutorId}
			</foreach>
		</where>
	</if>
</select>

多个参数操作

使用map形式引入

<select id="findAllStudentsByNameEmail" resultMap="StudentResult" parameterType="map">
	select 
		stud_id, name,email, phone from Students
    where 
	    name=#{name} and email=#{email}
</select>

使用参数替代

<select id="findAllStudentsByNameEmail" resultMap="StudentResult">
	select 
		stud_id, name,email, phone from Students
    where 
	    name=#{param1} and email=#{param2}
</select>

注解配置SQL映射器

基于注解的一些内容

INSERT
UPDATE
SELECT
DELETE

基本使用

数据插入操作

@Insert("INSERT INTO STUDENTS(NAME,EMAIL,ADDR_ID, PHONE) 
VALUES(#{name},#{email},#{address.addrId},#{phone})")

通过以下注解实现主键

@Options(useGeneratedKeys = true, keyProperty = "studId")

通过此注解为任意SQL语句指定主键值(使用此注解生成主键)

@SelectKey(statement="SELECT STUD_ID_SEQ.NEXTVAL FROM DUAL",
keyProperty="studId", resultType=int.class, before=true)

使用触发器生成主键

@SelectKey(statement="SELECT STUD_ID_SEQ.CURRVAL FROM DUAL",
keyProperty="studId", resultType=int.class, before=false)

结果集映射

@Results(
{
    @Result(id = true, column = "stud_id", property = "studId"),
    @Result(column = "name", property = "name"),
    @Result(column = "email", property = "email"),
    @Result(column = "addr_id", property = "address.addrId")
})

一对一映射

嵌套sql语句形式

@Results(
{
	@Result(id = true, column = "stud_id", property = "studId"),
	@Result(column = "name", property = "name"),
	@Result(column = "email", property = "email"),
	@Result(property = "address", column = "addr_id", one = @One(select = "com.mybatis3.mappers.StudentMapper.findAddressById"))
})

嵌套对象

此种方式并没有注解形式的实现，可以通过在XML中定义映射resultMap集，然后通过@ResultMap进行映射

@ResultMap("com.mybatis3.mappers.StudentMapper.StudentWithAddressResult")

一对多映射

同样的只有嵌套SQL的形式

 @Result(property = "courses", column = "tutor_id", many = @Many(select = "com.mybatis3.mappers.TutorMapper.findCoursesByTutorId"))

通过XML、注解配置MyBatis SQL映射器

定义xml映射文件

MyBatis 不同映射语句实例

INSERT

通过自增长设置主键

针对支持序列生成主键值

SELECT

MyBatis结果集映射规则

MyBatis 映射关系

一对一映射

方式1： 使用点语法进行映射

方式2： 使用嵌套结果 ResultMap进行映射

方式3：本身内部嵌套

一对多映射

使用嵌套对象形式显示

使用嵌套语句实现

动态SQL

if

choose, when, otherwise

where

trim

foreach

多个参数操作

使用map形式引入

使用参数替代

注解配置SQL映射器

基于注解的一些内容

基本使用

一对一映射

嵌套sql语句形式

嵌套对象

一对多映射

方式1：使用点语法进行映射

方式2：使用嵌套结果 ResultMap进行映射