1.Mybatis的关联映射
1.1 一对一
一对一关系:一个订单只能属于一个人
根据订单号查询此订单属于哪个人
数据库建表:
POJO
package pojo;
import java.io.Serializable;
import java.util.Date;
public class Order implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
//一个订单属于一个人
private User user;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
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 User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", user=" + user + "]";
}
}
package pojo;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private Integer id;
private String name;
private String sex;
private Date birthday;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getname() {
return name;
}
public void setname(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", sex=" + sex + ", birthday=" + birthday + ", address=" + address
+ "]";
}
}
mapper接口
package mapper;
import java.util.List;
import pojo.Order;
import pojo.User;
public interface OrderMapper {
public Order OneToOne(Integer id);
}
mapper.xml文件
方法一:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 写Sql语句 -->
<mapper namespace="mapper.OrderMapper">
<!-- 不管字段一不一致,都要手动映射 -->
<!-- 对于相同的字段,可以通过autoMapping="true"来自动映射,此时只需要手动映射不同的字段就好了 -->
<resultMap type="pojo.Order" id="orderlist">
<!-- 全部手动映射 -->
<id column="id" property="id"/>
<result column="time" property="createtime"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="note" property="note"/>
<!-- 自动映射 -->
<association property="user" javaType="pojo.User" autoMapping="true">
<id column="user_id" property="id"/>
</association>
</resultMap>
<select id="OneToOne" parameterType="Integer" resultMap="orderlist">
select * from t_order left join t_user on t_order.user_id=t_user.id
where t_order.id=#{id}
</select>
</mapper>
方法二:
在association标签中指定select属性和column属性。
column的值为数据表中起外键作用的那个字段,select的值为usermapper.java中的方法名的全路径
表示会使用column属性的值作为参数执行usermapper.java中的指定方法
ordermapper.xml的resultMap
<resultMap type="pojo.Order" id="orderlist" autoMapping="true">
<id column="id" property="id"/>
<result column="time" property="createtime"/>
<result column="user_id" property="userId"/>
<!-- select的值为mapper接口的全路径+方法名,column指定将外键作为参数执行sql语句 -->
<association property="user" javaType="pojo.User" select="mapper.UserMapper.SelectById" column="user_id">
</association>
</resultMap>
usermapper.xml的对应配置
<select id="SelectById" parameterType="Integer" resultType="pojo.User">
select * from t_user where id = #{id}
</select>
测试
@Test
public void testSelectByIds() throws IOException {
InputStream in=Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(in);
SqlSession session=sqlSessionFactory.openSession();
//SQLSession生成一个实现类(参数是接口)
OrderMapper ordermapper=session.getMapper(OrderMapper.class);
Order order = ordermapper.OneToOne(1);
System.out.println(order);
}
查询结果
1.2 一对多
一对多关系:一个人可以拥有多个订单
POJO
只需在User类中添加一个List成员就好了
//有多个订单
private List<Order> orders;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
mapper接口
package mapper;
import pojo.Order;
import pojo.User;
public interface OrderMapper {
public Order OneToOne(Integer id);
//返回值是充当“一”的对象,返回一个User对象,其中拥有多个Order
public User OneToMany(Integer id);
public Order selectOrderByuserId(Integer id);
}
mapper.xml文件
方法一:将多张表连接为一张表,然后在这张表上查询。
连接条件为order中的userId等于user中的id,查询条件为user中的id等于传入的参数值
<!-- 多表连接,如果表之间存在想用的字段,连接后系统会自动为附表的字段改名,此时映射就会出错,因此最好自己手动起别名 -->
<!-- oid为t_order中id字段的别名 -->
<select id="OneToMany" parameterType="Integer" resultMap="onetomany">
select t_user.*,t_order.id oid,t_order.user_id,t_order.number,t_order.time,t_order.note
from t_user
left join t_order
on t_order.user_id=t_user.id
where t_user.id=#{id}
</select>
<resultMap type="pojo.User" id="onetomany" autoMapping="true">
<!-- id为主键 -->
<id column="id" property="id"/>
<collection property="orders" ofType="pojo.Order" autoMapping="true">
<id column="oid" property="id"/>
<result column="user_id" property="userId"/>
<result column="time" property="createtime"/>
</collection>
</resultMap>
resultMap和collection下的id标签均表示主键
collection的property为User类对应中的属性,查询结果会存入到该属性中;ofType为集合的泛型类型,也就是集合中的对象类型
注意:多表连接时,如果表之间存在相同的字段,连接后系统会自动将附表(也就是left join后的表)中的字段改名,此时,如果collection标签下的column属性还用原来的名字,映射就会出错(我就因为这个小坑花费了几个小时......再次感叹Bug真难改)。
因此最好手动设置别名。
方法二:将主表中的查询结果,联合附表的查询结果,映射到对象中。
①先根据用户传入的参数,从主表中查询出对应结果;
②把主表查询结果中的某些字段值作为参数传入到附表的查询语句中
③将两个表的查询结果联合封装到返回对象中
<!-- 主表查询语句 -->
<select id="OneToMany" parameterType="Integer" resultMap="onetomany">
select * from t_user where id=#{id}
</select>
<resultMap type="pojo.User" id="onetomany" autoMapping="true">
<id column="id" property="id"/>
<!-- column为充当外键的字段,以这个字段的值作为参数传递给附表 -->
<!-- select为要执行的方法,类的全路径+方法名 -->
<collection property="orders" javaType="List" ofType="pojo.Order" column="id" select="mapper.OrderMapper.selectOrderByuserId">
</collection>
</resultMap>
<!-- 附表查询语句 -->
<select id="selectOrderByuserId" parameterType="Integer" resultMap="returnorder">
select * from t_order where t_order.user_id=#{id}
</select>
<resultMap type="pojo.Order" id="returnorder" autoMapping="true">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="time" property="createtime"/>
</resultMap>
由于是分别查询表,因此不存在自动别名的坑
测试:
public class MybatisMapperJUnit {
@Test
public void testSelectByIds() throws IOException {
InputStream in=Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory =new SqlSessionFactoryBuilder().build(in);
SqlSession session=sqlSessionFactory.openSession();
//SQLSession生成一个实现类(参数是接口)
OrderMapper ordermapper=session.getMapper(OrderMapper.class);
User user=ordermapper.OneToMany(1);
System.out.println(user);
List<Order> orders=user.getOrders();
for (Order order : orders) {
System.out.println(order);
}
}
}
结果