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

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标签ifchoose等标签可对字段进行判断,看是否需要添加到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>

iftest条件成立时, 才会添加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>

假如说namegender的值都不为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"))
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值