接着第三讲 输入输出映射
用户和订单之间是一对多的关系
需求：查询指定用户的信息及其所有的订单信息
select users.*,orders.* from t_user users,t_orders orders
where users.uid=orders.uid and users.uid=#{uid}
<resultMap type="user" id="queryUserOrdersResultMap">
<id column="uid" property="uid"/>
<result column="uname" property="uname"/>
<result column="upwd" property="upwd"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="hireDate" property="hireDate"/>
<!--collection 代表集合 ofType指定类型-->
<collection property="orderList" ofType="order">
<id column="order_id" property="order_id"/>
<result column="order_create_time" property="order_create_time"/>
<result column="order_status" property="order_status"/>
</collection>
</resultMap>
<select id="queryUserOrders" parameterType="int" resultMap="queryUserOrdersResultMap">
select users.*,orders.*
from t_user users,t_orders orders
where users.uid=orders.uid and users.uid=#{uid}
</select>
public class User {
/**
* 用户ID
*/
private int uid;
/**
* 用户名
*/
private String uname;
/**
* 密码
*/
private String upwd;
/**
* 联系方式
*/
private String phone;;
/**
* 家庭住址
*/
private String address;
/**
* 入职日期
*/
private Date hireDate;
/**
* 关联的订单信息
*/
private List<Order> orderList;
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getUpwd() {
return upwd;
}
public void setUpwd(String upwd) {
this.upwd = upwd;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
@Override
public String toString() {
return "User [uid=" + uid + ", uname=" + uname + ", upwd=" + upwd + ", phone=" + phone + ", address=" + address
+ ", hireDate=" + hireDate + ", orderList=" + orderList + "]";
}
}
订单和商品之间的关系是多对多，一个订单中可以包含多个商品，一个商品可以存在于多个订单中
需求：查询用户及订单信息关联查询商品信息
create table t_order_details(
detail_id int primary key auto_increment,
orders_id varchar(20) not null,
items_id int not null
);
insert into t_order_details(orders_id,items_id) values('X123456789',3);
insert into t_order_details(orders_id,items_id) values('X123456789',2);
insert into t_order_details(orders_id,items_id) values('X123456789',7);
insert into t_order_details(orders_id,items_id) values('X123456820',5);
insert into t_order_details(orders_id,items_id) values('X123456820',3);
insert into t_order_details(orders_id,items_id) values('X123456850',4);
select users.*,orders.*,items.* from t_user users,t_orders orders
where users.uid=orders.uid and users.uid=#{uid}
--先找到订单和订单详情之间的数据
select orders.*,detail.* from t_orders orders,t_order_details detail where orders.order_id = detail.orders_id;
--在查找订单详情和商品之间的关系
select orders.*,detail.*,items.* from t_orders orders,t_order_details detail,t_items items where orders.order_id = detail.orders_id and detail.items_id=items.item_id;
--再关联用户信息
select users.*,orders.*,items.* from t_user users,t_orders orders,t_order_details detail,t_items items where users.uid=orders.uid and orders.order_id = detail.orders_id and detail.items_id=items.item_id and users.uid=#{uid};
<resultMap type="user" id="queryUserOrdersItemsResultMap">
<id column="uid" property="uid"/>
<result column="uname" property="uname"/>
<result column="upwd" property="upwd"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="hireDate" property="hireDate"/>
<collection property="orderList" ofType="order">
<id column="order_id" property="order_id"/>
<result column="order_create_time" property="order_create_time"/>
<result column="order_status" property="order_status"/>
<collection property="itemList" ofType="item">
<id column="item_id" property="item_id"/>
<result column="item_name" property="item_name"/>
<result column="item_price" property="item_price"/>
<result column="item_count" property="item_count"/>
</collection>
</collection>
</resultMap>
<select id="queryUserOrdersItems" parameterType="int" resultMap="queryUserOrdersItemsResultMap">
select users.*,orders.*,items.*
from t_user users,t_orders orders,t_order_details detail,t_items items
where
users.uid=orders.uid
and orders.order_id = detail.orders_id
and detail.items_id=items.item_id
and users.uid=#{uid};
</select>
使用extends关键字简化代码，注意：引用的是同级关系(字段)的映射
<!-- 用户信息映射作为公共的区域 -->
<resultMap type="user" id="userBaseResult">
<id column="uid" property="uid"/>
<result column="uname" property="uname"/>
<result column="upwd" property="upwd"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="hireDate" property="hireDate"/>
</resultMap>
<!-- 映射用户中的订单信息 -->
<resultMap type="user" id="queryUserOrdersResultMap" extends="userBaseResult">
<collection property="orderList" ofType="order">
<id column="order_id" property="order_id"/>
<result column="order_create_time" property="order_create_time"/>
<result column="order_status" property="order_status"/>
</collection>
</resultMap>
<select id="queryUserOrders" parameterType="int" resultMap="queryUserOrdersResultMap">
select users.*,orders.*
from t_user users,t_orders orders
where users.uid=orders.uid and users.uid=#{uid}
</select>
<!-- 映射订单中的上品信息 -->
<resultMap type="user" id="queryUserOrdersItemsResultMap" extends="userBaseResult">
<collection property="orderList" ofType="order">
<id column="order_id" property="order_id"/>
<result column="order_create_time" property="order_create_time"/>
<result column="order_status" property="order_status"/>
<collection property="itemList" ofType="item">
<id column="item_id" property="item_id"/>
<result column="item_name" property="item_name"/>
<result column="item_price" property="item_price"/>
<result column="item_count" property="item_count"/>
</collection>
</collection>
</resultMap>
<select id="queryUserOrdersItems" parameterType="int" resultMap="queryUserOrdersItemsResultMap">
select users.*,orders.*,items.*
from t_user users,t_orders orders,t_order_details detail,t_items items
where
users.uid=orders.uid
and orders.order_id = detail.orders_id
and detail.items_id=items.item_id
and users.uid=#{uid};
</select>
SQL片段
什么是sql片段？
将在xml文件中频繁使用的字段，使用sql标签进行封装，在需要使用的抵用通过include标签引用即可
为什么要使用sql片段？
1、在同一个xml文件中会频繁的使用到相同的字段，此时需要进行封装抽离
2、需要使用另一个xml文件中的公共的字段或sql
<!--userMapper中的-->
<!-- sql片段 -->
<sql id="baseUser">
users.uid,
users.uname,
users.upwd,
users.phone,
users.address,
users.hireDate
</sql>
<!--orderMapper中的-->
<sql id="baseOrder">
orders.order_id,
orders.order_create_time,
orders.order_status,
orders.uid
</sql>
<select id="queryUsers" resultType="User">
select
<!-- refid:要引用的片段对应的id值 -->
<include refid="cn.yunhe.dao.IUserMapper.baseUser"/>
from t_user users
</select>
<select id="queryUserOrders" parameterType="int" resultMap="queryUserOrdersResultMap">
select
<include refid="baseUser"/>,
<include refid="cn.yunhe.dao.IOrderMapper.baseOrder"/>
from t_user users,t_orders orders
where users.uid=orders.uid and users.uid=#{uid}
</select>
<select id="queryUserOrdersItems" parameterType="int" resultMap="queryUserOrdersItemsResultMap">
select
<include refid="baseUser"/>,
<include refid="cn.yunhe.dao.IOrderMapper.baseOrder"/>,
items.item_id,
items.item_name,
items.item_price,
items.item_count
from t_user users,t_orders orders,t_order_details detail,t_items items
where
users.uid=orders.uid
and orders.order_id = detail.orders_id
and detail.items_id=items.item_id
and users.uid=#{uid};
</select>
动态SQL
什么是动态SQL？
可以动态的拼接sql语句 ，并且可以使用逻辑判断
需求：根据不同的查询条件，搜索数据
<!-- 模糊查询 -->
<select id="queryUserLike" parameterType="user" resultType="user">
select
<include refid="baseUser"/>
from t_user users
<!-- where会自动去掉第一个and -->
<where>
<if test="uname!=null and !uname.isEmpty()">
uname like concat('%',#{uname},'%')
</if>
<if test="phone!=null and !phone.isEmpty()">
and phone like concat('%',#{phone},'%')
</if>
</where>
</select>
测试结果
@Test
public void testQueryUsersLike() {
IUserMapper userMapper = sqlSession.getMapper(IUserMapper.class);
User user = new User();
user.setUname("j");
user.setPhone("789");
List<User> userList = userMapper.queryUserLike(user);
System.out.println(userList);
}
需求：批量删除
delete from t_user where id in(?,?,?,?);
<!-- 批量删除 -->
<delete id="delUsers">
delete from t_user
where uid in
<!--
for(int i=0;i<array.length;i++){id = array[i]}
collection:指定要遍历的对象
item:遍历中的当前对象
open：开始拼接
close：结束时的拼接
separator：遍历过程中的拼接
如果传递的是数组就用array，如果是List集合就用list -->
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
以后要学的内容：
