动态sql、关联/级联查询(多表查询)

1. 动态sql

OGNL表达式

OGNL 表达式,是一种开源的表达式语言,Mybatis的动态SQL就是基于它

关系	OGNL
e1&e2 与关系	e1 and e2
e1丨e2 或关系	e1 or e2
e1==e2 相等关系	e1==e2 或者 e1 eq e2
e1!=e2 不等关系	e1!=e2 或者 e1 neq e2
比较关系	大于 e1>e2(e1 gt e2) ; 小于 e1<e2(e1 lt e2) ; 大于等于e1>=e2(e1 gte e2); 小于等于e1<=e2(e1 lte e2)
取反	!e1 或者 not e1

1.1 If 标签

where 后添加 1=1 防止不满足所有条件，查询出错

<!-- 根据条件查询用户 -->
<select id="findUserByWhere" parameterType="user" resultType="user">
	SELECT id, username, birthday, sex, address FROM `user`
	WHERE 1=1
	<if test="sex != null and sex != ''">
		AND sex = #{sex}
	</if>
	<if test="username != null and username != ''">
		AND username LIKE
		'%${username}%'
	</if>
</select>

1.2 5.2. Where标签

where标签可以自动添加where，同时处理sql语句中第一个and关键字（会自动去除Where后第一个满足if的sql语句的AND）

<!-- 根据条件查询用户 -->
<select id="findUserByWhere" parameterType="user" resultType="user">
	SELECT id, username, birthday, sex, address FROM `user`
<!-- where标签可以自动添加where，同时处理sql语句中第一个and关键字 -->
	<where>
		<if test="sex != null">
			AND sex = #{sex}
		</if>
		<if test="username != null and username != ''">
			AND username LIKE
			'%${username}%'
		</if>
	</where>
</select>

1.3 Sql片段

Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的。
例如上述案例中查询的数据一致，可以提取出来 – id, username, birthday, sex, address
– 使用include标签加载sql片段；refid是sql片段id

<!-- 声明sql片段 -->
<sql id="userFields">
	id, username, birthday, sex, address
</sql>

<!-- 根据条件查询用户 -->
<select id="findUserByWhere" parameterType="user" resultType="user">
	<!-- SELECT id, username, birthday, sex, address FROM `user` -->
	<!-- 使用include标签加载sql片段；refid是sql片段id -->
	SELECT <include refid="userFields" /> FROM `user`
	<!-- where标签可以自动添加where关键字，同时处理sql语句中第一个and关键字 -->
	<where>
		<if test="sex != null">
			AND sex = #{sex}
		</if>
		<if test="username != null and username != ''">
			AND username LIKE
			'%${username}%'
		</if>
	</where>
</select>

1.4 foreach标签

向sql传递数组或List，mybatis使用foreach解析

• foreach标签，进行遍历
  • collection：遍历的集合
  • item：遍历的项目，自定义命名，但是和后面的#{}里面要一致
  • open：在前面添加的sql片段
  • close：在结尾处添加的sql片段
  • separator：指定遍历的元素之间使用的分隔符

<!-- 根据ids查询用户 -->
<!--  SELECT * FROM user WHERE id IN (1,10,24)  -->
<select id="findUserByIds" parameterType="queryVo" resultType="user">
	SELECT * FROM `user`
	<where>
		<!-- foreach标签，进行遍历 -->
		<!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
		<!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
		<!-- open：在前面添加的sql片段 -->
		<!-- close：在结尾处添加的sql片段 -->
		<!-- separator：指定遍历的元素之间使用的分隔符 -->
		<foreach collection="ids" item="item" open="id IN (" close=")"
			separator=",">
			#{item}
		</foreach>
	</where>
</select>

1.5 set 标签

set标签可以自动添加set，同时处理sql语句中最后一个 , (逗号)

<!--
    更新用户
-->
<update id="updateUser" parameterType="user">
    update user_info
    <set>
        <if test="username!=null and username!=''">
            name = #{username} ,
        </if>
        <if test="address!=null and address!=''">
            addresss = #{address} ,
        </if>
        <if test="sex!=null and sex!=''">
            gender = #{sex} ,
        </if>
    </set>
    where id = #{id}
</update>

唯一需要注意的就是,每一个条件判断的sql对于 SET 来说是要加 , ,Mybatis会自动去掉多余
的逗号 , ,但是不会自动加上缺少的逗号

1.6 choose(when,otherwise)

相当于Java基础中的 if…else if…else if…else…

    <select id="queryByDynamic2" parameterType="com.softeem.entity.Student"
            resultType="com.softeem.entity.Student">
        SELECT
        <include refid="BASE_COLUMN"/>
        FROM student
        <where>
            <choose>
                <when test="name!=null and name!=''">
                    name LIKE concat('%',#{name},'%')
                </when>
                <when test="address!=null and address!=''">
                    address LIKE concat('%',#{address},'%')
                </when>
                <otherwise>
                    id=1
                </otherwise>
            </choose>
        </where>
    </select>

2. 关联查询

2.1 一对一查询

查询所有订单信息，关联查询下单用户信息
sql语句：

SELECT
	o.id,
	o.user_id userId,
	o.number,
	o.createtime,
	o.note,
	u.username,
	u.address
FROM
	`order` o
LEFT JOIN `user` u ON o.user_id = u.id

2.1.2 方法一：使用resultType

创建包含订单信息和用户信息的entity类，和查询返回的数据映射
正常的Order订单类（和数据库对应）

public class Order {
	// 订单id
	private int id;
	// 用户id
	private Integer userId;
	// 订单号
	private String number;
	// 订单创建时间
	private Date createtime;
	// 备注
	private String note;
}

包含用户信息的订单entity类，OrderUser类继承Order类

public class OrderUser extends Order {
	private String username;
	private String address;
}

UserMapper.xml
resultType=“com.sxy.entity.OrderUser” 与返回的数据对应

<!-- 查询订单，同时包含用户数据 -->
<select id="queryOrderUser" resultType="com.sxy.entity.OrderUser">
	SELECT
	o.id,
	o.user_id  userId,
	o.number,
	o.createtime,
	o.note,
	u.username,
	u.address
	FROM
	`order` o
	LEFT JOIN `user` u ON o.user_id = u.id
</select>

2.1.2 方法二：使用resultMap

在Order类中加入User属性， User对象存储关联查询的用户信息
Order订单类

public class Order {
	// 订单id
	private int id;
	// 用户id
	private Integer userId;
	// 订单号
	private String number;
	// 订单创建时间
	private Date createtime;
	// 备注
	private String note;
	
	//用户信息
	private User user;
	
}

UserMapper.xml

• association ：配置一对一属性
• property:order里面的User属性名
• javaType:属性类型

<resultMap type="order" id="orderUserResultMap">
	<id property="id" column="id" />
	<result property="userId" column="user_id" />
	<result property="number" column="number" />
	<result property="createtime" column="createtime" />
	<result property="note" column="note" />

	<!-- association ：配置一对一属性 -->
	<!-- property:order里面的User属性名 -->
	<!-- javaType:属性类型 -->
	<association property="user" javaType="user">
		<!-- id:声明主键，表示user_id是关联查询对象的唯一标识-->
		<id property="id" column="user_id" />
		<result property="username" column="username" />
		<result property="address" column="address" />
	</association>

</resultMap>

<!-- 一对一关联，查询订单，订单内部包含用户属性 -->
<select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
	SELECT
	o.id,
	o.user_id,
	o.number,
	o.createtime,
	o.note,
	u.username,
	u.address
	FROM
	`order` o
	LEFT JOIN `user` u ON o.user_id = u.id
</select>

2.2 一对多查询

查询所有用户信息及用户关联的订单信息。
用户信息和订单信息为一对多关系。

sql语句：

SELECT
	u.id,
	u.username,
	u.birthday,
	u.sex,
	u.address,
	o.id oid,
	o.number,
	o.createtime,
	o.note
FROM
	`user` u
LEFT JOIN `order` o ON u.id = o.user_id

在User类中加入List orders属性,代表一个用户有多个订单

public class User {
 private int id; 
 private String username;//用户姓名
 private string sex;//性别 
 private Date birthday;//生日 
 private String address;//地址 
 private List<Order> orders;
 }

UserMapper.xml

<resultMap type="user" id="userOrderResultMap">
	<id property="id" column="id" />
	<result property="username" column="username" />
	<result property="birthday" column="birthday" />
	<result property="sex" column="sex" />
	<result property="address" column="address" />

	<!-- 配置一对多的关系 -->
	<collection property="orders" javaType="list" ofType="order">
		<!-- 配置主键，是关联Order的唯一标识 -->
		<id property="id" column="oid" />
		<result property="number" column="number" />
		<result property="createtime" column="createtime" />
		<result property="note" column="note" />
	</collection>
</resultMap>

<!-- 一对多关联，查询订单同时查询该用户下的订单 -->
<select id="findUserOrder" resultMap="userOrderResultMap">
	SELECT
	u.id,
	u.username,
	u.birthday,
	u.sex,
	u.address,
	o.id oid,
	o.number,
	o.createtime,
	o.note
	FROM
	`user` u
	LEFT JOIN `order` o ON u.id = o.user_id
</select>

3. 级联查询

3.1 一对一查询

    <resultMap id="manytoone2" type="com.softeem.dto.ManyToOne">
        <!-- id标签和result都是对字段做映射的,唯一不同的就是id代表对主键的映射 -->
        <id property="id" column="id" javaType="INTEGER" jdbcType="INTEGER"/>
        <result property="courseId" column="course_id"/>
        <!-- 一对一的关联的对象,用association标签建立关系 -->
        <association property="course" javaType="com.softeem.entity.Course"
                     column="course_id" select="com.softeem.dao.CourseDao.queryById"/>
    </resultMap>
    
    <select id="selectManyToOne2" resultMap="manytoone2">
        SELECT * FROM student WHERE id=#{id}
    </select>

    <mapper namespace="com.softeem.dao.CourseDao">
        <!--查询单个-->
        <select id="queryById" resultMap="CourseMap">
        select course_id,
        course_name,
        course_content
        from course
        where course_id = #{courseId}
        </select>

3.2 一对多查询

一般一对多查询都会指定 中元属性 fetchType=lazy 代表懒加载,默认为eager ,因为开发不知道关联的列表(一对多中的多)到底有多少,所以设置为lazy代表需要用到的的时候,才会做第二步级联查询的sql

    <resultMap type="com.softeem.dto.OneToMany" id="onetomany">
        <id property="courseId" column="course_id" jdbcType="INTEGER"/>
        <!-- 一对多的关联关系用collection标签 -->
        <collection property="studentList" javaType="java.util.List"
                    ofType="com.softeem.entity.Student"
                    column="course_id"
                    select="com.softeem.dao.StudentDao.selectBycourseId"
                    fetchType="lazy"/>
    </resultMap>
    
    <select id="selectOneToMany" resultMap="onetomany">
    select course_id,
    course_name,
    course_content
    from course
    where course_id = #{courseId}
    </select>

<select id="selectBycourseId" resultType="com.softeem.entity.Student">
SELECT * FROM student WHERE course_id=#{courseId}
</select>