MyBatis入门介绍(二)

<!-- 使用包装类型查询用户 
使用ognl从对象中取属性值，如果是包装对象可以使用.操作符来取内容部的属性
-->
<select id="findUserByQueryVo" parameterType="queryvo" resultType="user">
  SELECT * FROM user where username like '%${user.username}%'
</select>

<select id="findUserCount" resultType="int">
  select count(1) from user
</select>

<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>

<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>

SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16)
SELECT * FROM USERS WHERE username LIKE '%张%'  id IN (10,89,16)

<if test="ids!=null and ids.size>0">
    <foreach collection="ids" open=" and id in(" close=")" item="id" separator="," >
      #{id}
    </foreach>
</if>

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

public class OrdersCustom extends Orders {
  private String username;// 用户名称
  private String address;// 用户地址
get/set。。。。
}

<!-- 查询所有订单信息 -->
<select id="findOrdersList" resultType="cn.itcast.mybatis.po.OrdersCustom">
SELECT
orders.*,
user.username,
user.address
FROM
orders, user
WHERE orders.user_id = user.id 
</select>

<!-- 查询订单关联用户信息使用resultmap -->
<resultMap type="cn.itheima.po.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>

<resultMap type="cn.itheima.po.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" ofType="cn.itheima.po.Orders">
  <id property="id" column="oid"/>  
       <!--用户id已经在user对象中存在，此处可以不设置-->
  <!-- <result property="userId" column="id"/> -->
  <result property="number" column="number"/>
  <result property="createtime" column="createtime"/>
  <result property="note" column="note"/>
  </collection>
</resultMap>
<select id="getUserOrderList" resultMap="userOrderResultMap">
  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>