这里搞一个测试场景:
用户和订单,一位用户可以有多个订单,而每个订单只属于一位用户
以用户为中心,相对于订单:一对多
以订单为中心,当对于用户:一对一
两张表结构:
订单表:
用户表:
对应的POJO类:
User:
package pojo;import java.io.Serializable;
import java.util.Date;
import java.util.List;public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
private String username;
private String sex;
private Date birthday;
private String address; //附加对象List
private List<Orders> ordersList; public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
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 + ", username=" + username + ", sex=" + sex
+ ", birthday=" + birthday + ", address=" + address + "]";
}}
Orders:
package pojo;import java.io.Serializable;
import java.util.Date;public class Orders 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; @Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", 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;
}}
OrderMapper:
package mapper;import java.util.List;import pojo.Orders;
import pojo.User;public interface OrderMapper { //以订单为中心一对一关联查询
public List<Orders> selectOrders(); //以订单为中心一对多关联查询
public List<User> selectUserList();}
OrderMapper.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">
<mapper namespace="mapper.OrderMapper">
<!-- 一对一 -->
<resultMap type="pojo.Orders" id="order">
<result column="id" property="id" />
<result column="user_id" property="userId" />
<result column="number" property="number" />
<association property="user" javaType="pojo.User">
<id column="user_id" property="id" />
<result column="username" property="username" />
</association>
</resultMap>
<select id="selectOrders" resultMap="order">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
u.username
FROM orders o
left join user u
on o.user_id = u.id
</select> <!--一对多 -->
<resultMap type="pojo.User" id="user">
<id column="user_id" property="id" />
<result column="username" property="username" />
<collection property="ordersList" ofType="pojo.Orders">
<id column="id" property="id" />
<result column="number" property="number" />
</collection>
</resultMap>
<select id="selectUserList" resultMap="user">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
u.username
FROM user u
left join orders o
on o.user_id = u.id
</select></mapper>
测试两个方法:
@Test
public void testOrderList() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(); //SqlSEssion帮我生成一个实现类 (给接口)
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<Orders> selectOrdersList = orderMapper.selectOrders(); for (Orders orders : selectOrdersList) {
System.out.println(orders);
}
}
@Test
public void testUserList() throws Exception {
//加载核心配置文件
String resource = "sqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession(); //SqlSEssion帮我生成一个实现类 (给接口)
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
List<User> users = orderMapper.selectUserList();
for (User user : users) {
System.out.println(user);
} }