ResultType
在一个项目中,需要关联查询,则需要建立一个实体类,需要把关联查询所要查询的所有字段写入实体类中
案例:
java代码:
package com.dsf.bean;
import java.util.Date;
public class OrderUser {
//订单的属性
private int oid;
private Integer user_id;
private String number;
private Date createtime;
private String note;
//用户的属性
private int uid;
private String username;
private Date birthday;
private String sex;
private String address;
public int getOid() {
return oid;
}
public void setOid(int oid) {
this.oid = oid;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
xml配置文件:
<select id="findByUser" resultType="OrderUser">
select u.id as uid,
u.username,
u.birthday,
u.sex,
u.address,
o.id as oid,
o.user_id as userId,
o.number,
o.createtime,
o.note
from `user` u left join `order` o
on u.id=o.user_id;
</select>
ResultMap
建立查询字段与实体类成员变量的映射关系
即查询字段是user_id但是Mybatis去赋值userId
ResultMap用来实现一对多、多对多的查询,不用建立实体类
案例:
<resultMap id="resultMap1" type="com.dsf.bean.Order">
<id column="id" property="id"></id>
<!--
column:字段名
property:成员变量名
-->
<result column="user_id" property="userId"></result>
<result column="number" property="number"></result>
<result column="createtime" property="createtime"></result>
<result column="note" property="note"></result>
</resultMap>
<select id="queryAllOrders" resultMap="resultMap1">
SELECT id,user_id,number,createtime,note FROM orderx
</select>
动态标签
if标签,where标签,forEach标签(写在xml配置文件中)
- if标签
可以判断传入的参数是否为空,如果不为空则拼接sql
<if test="username != '' and username !=null ">
and username like '${username}%'
</if>
- where标签
1、不用在初始sql后边写where 1=1
2、不用在第一个拼接的sql前写and,但是你也可以手动写and
<select id="queryUserBySexAndName" parameterType="User" resultType="User">
select * from user
<where>
<if test="username != '' and username !=null ">
and username like '${username}%'
</if>
<if test="sex != '' and sex != null">
and sex = #{sex}
</if>
<if test="address != '' and address != null">
and address = #{address}
</if>
</where>
</select>
- foreach标签
向sql传递数组或List,mybatis使用foreach解析
collection:表示方法传入的集合对象的名字 collection=“xxx”
item:遍历集合时,会将集合中的元素赋值给item
open:表示你要拼接的sql以什么开始
close:表示你拼接的sql以什么结束
separator:表示拼接的分隔符
接口中的变量名不能被标签识别,必须在参数的前边加注解@Param(“xxx”)
<foreach collection="ids" item="id" open="id in(" close=")" separator=",">
#{id}
</foreach>
接口中实现
List<User> findByIds(@Param("ids") List<Integer> list);
一对一关联查询
站在订单的角度看,一个订单有一个用户跟它对应
查询结果可以有两种方式封装数据
- resultType:指定一个自定义javaBean类
- resultMap:指定映射关系(不用创建新类,使用现有的类)
resultType方式
resultType必须重新创建类
创建一个新类,把所有要查询的字段全部写入
package com.dsf.bean;
import java.util.Date;
public class OrderUser {
private int oid;
private Integer user_id;
private String number;
private Date createtime;
private String note;
private int uid;
private String username;
private Date birthday;
private String sex;
private String address;
public int getOid() {
return oid;
}
public void setOid(int oid) {
this.oid = oid;
}
public Integer getUser_id() {
return user_id;
}
public void setUser_id(Integer user_id) {
this.user_id = user_id;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public String getNote() {
return note;
}
public void setNote(String note) {
this.note = note;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
xml配置文件
<select id="findAllOrderUser" resultType="com.dsf.bean.OrderUser">
select o.id as oid,
o.user_id ,
o.number,
o.createtime,
o.note,
u.id as uid,
u.username,
u.birthday,
u.sex,
u.address
from `order` o left join `user` u
on o.user_id=u.id;
</select>
resultMap方式
不需要重新创建类,只需改造类
使用association 标签用于成员变量的类型为自定义实体类型
添加属性:autoMapping=“true”,自动映射
改造user类
public class Order {
private int id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
}
xml配置文件
<resultMap id="findAllOrder2Map" type="com.dsf.bean.Order" autoMapping="true">
<id column="id" property="id"></id>
<result column="user_id" property="userId"></result>
<association property="user" javaType="com.dsf.bean.User" autoMapping="true" >
<id column="uid" property="id"></id>
</association>
</resultMap>
<select id="findAllOrder2" resultMap="findAllOrder2Map">
select o.id as id,
o.user_id as userId,
o.number,
o.createtime,
o.note,
u.id as uid,
u.username,
u.birthday,
u.sex,
u.address
from `order` o left join `user` u
on o.user_id=u.id;
</select>
一对多关联查询
站在用户的角度看,一个用户有多个订单跟他对应
使用collection标签 映射List<元素>
public class User {
private int id;
private String username;
private Date birthday;
private String sex;
private String address;
//变量类型是集合List
private List<Order> orders;
}
xml配置文件
<resultMap id="findAllUsersMap" type="com.dsf.bean.User" autoMapping="true">
<id property="id" column="uid"></id>
<collection property="orders" ofType="com.dsf.bean.Order" autoMapping="true">
<id property="id" column="oid"></id>
</collection>
</resultMap>
<select id="findAllUsers" resultMap="findAllUsersMap">
select u.id as uid,
u.username,
u.birthday,
u.sex,
u.address,
o.id as oid,
o.user_id as userId,
o.number,
o.createtime,
o.note
from `user` u left join `order` o
on u.id=o.user_id;
</select>