mybatis

Mybatis-2

@(知识)

定义resultMap

如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系 ，resultMap实质上还需要将查询结果映射到pojo对象中。
resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。

• resultMap的配置

<resultMap type="com.yyt.mybatis.po.User" id="userListResultMap">
	<id column="_id" property="id"/>
	<result column="_name" property="username"/>
	<result column="_birthday" property="birthday"/>
	<result column="_sex" property="sex"/>
	<result column="_address" property="address"/>
</resultMap>

---

<select id="findUserListForResultMap" parameterType="queryvo" resultMap="userListResultMap">
		SELECT id _id,username _name,birthday _birthday,sex _sex,address _address FROM `user`
</select>

动态sql

• if语句

<!-- 传递pojo综合查询用户信息 -->
<select id="findUserList" parameterType="user" resultType="user">
	select * from user 
	where 1=1 
	<if test="id!=null">
	and id=#{id}
	</if>
	<if test="username!=null and username!=''">
	and username like '%${username}%'
	</if>
</select>

• where语句

<select id="findUserList" parameterType="user" resultType="user">
	select * from user 
	<where>
	<if test="id!=null and id!=''">
	and id=#{id}
	</if>
	<if test="username!=null and username!=''">
	and username like '%${username}%'
	</if>
	</where>
</select>

• foreach语句
  • 向sql传递数组或List，mybatis使用foreach解析
  • mapper配置queryvo类中包括一个Integer集合ids

<select id="findUserByForeach" parameterType="queryvo" resultMap="userListResultMap">
	SELECT * FROM USER WHERE username LIKE '%张%'
	<!-- 判断queryvo对象里面的ids集合-->
	<if test="ids !=null and ids.size>0">
		<!-- 遍历ids,并非在上面sql语句上加入 and id in(......) 括号中间写遍历的参数以逗号隔开-->
		<foreach collection="ids" open="and id in(" close=")" item="id" separator=",">
			#{id}
		</foreach>
	</if>
</select>

* 运行结果
    * `Preparing: SELECT * FROM USER WHERE username LIKE '%张%' and id in( ? , ? , ? )`
    * `Parameters: 10(Integer), 89(Integer), 16(Integer)`

• sql片段
  • Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的
  • 代码如下

<select id="findUserListForResultMap" parameterType="queryvo" resultMap="userListResultMap">
	SELECT id _id,username _name,birthday _birthday,sex _sex,address _address FROM `user`
	<where>
		<include refid="id_username_user"/>
	</where>
</select>
<sql id="id_username_user">
	<if test="user.id != null and user.id != ''">
		and id=${user.id}
	</if>
	<if test="user.username != null and user.username != ''">
		and username like '%${user.username}%'
	</if>
</sql>

关联查询

• 一对一的查询一张订单对应只有一个用户
  • mapper代码

<!-- 查询订单关联用户信息使用resultmap -->
<resultMap type="Orders" id="orderUserResultMap">
	<id column="id" property="id"/>
	<result column="user_id" property="userId"/>
	<result column="number" property="number"/>
	<result column="createtime" property="createtime"/>
	<result column="note" property="note"/>
	<!-- 一对一关联映射 -->
	<!-- 
	property:Orders对象的user属性
	javaType：user属性对应 的类型
	 -->
	<association property="user" javaType="cn.itcast.po.User">
		<!-- column:user表的主键对应的列  property：user对象中id属性-->
		<id column="user_id" property="id"/>
		<result column="username" property="username"/>
		<result column="address" property="address"/>
	</association>
</resultMap>
<select id="findOrdersWithUserResultMap" resultMap="orderUserResultMap">
	SELECT
		o.id,
		o.user_id,
		o.number,
		o.createtime,
		o.note,
		u.username,
		u.address
	FROM
		orders o
	JOIN `user` u ON u.id = o.user_id
</select>

* 在订单实体类中加入用户的属性,并提供get,set方法

• 一对多查询一个用户可以有多个订单
  • mapper代码

<!-- 配置一对多的关系 -->
<resultMap type="user" id="userAndordersMapper">
	<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" ofType="orders">
		<id property="id" column="oid"/>
		<result property="number" column="number"/>
		<result property="createtime" column="createtime"/>
		<result property="note" column="note"/>
	</collection>
</resultMap>
<!-- 一对多的sql语句的写法配置 -->
<select id="findUserOrdersByUser" resultMap="userAndordersMapper">
	SELECT
	u.*, o.id oid,
	o.number,
	o.createtime,
	o.note
	FROM
	`user` u
	LEFT JOIN orders o ON u.id = o.user_id
</select>

• 在用户实体类中加入订单对象的list集合属性,并提供get和set方法