一、一对一
查询所有订单信息,关联查询下单用户信息
1.1 使用resultType
新建一个POJO对象,里面存储要查询的属性
package com.itheima.pojo;
public class OrderUser extends Order {
private String username;
private String addredss;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getAddredss() {
return addredss;
}
public void setAddredss(String addredss) {
this.addredss = addredss;
}
@Override
public String toString() {
return "OrderUser [username=" + username + ", addredss=" + addredss + "]";
}
}
package com.itheima.mapper;
import java.util.List;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;
public interface OrderMapper {
/**
* @MethodName:findOrderUserList
* @Description:
* @return
*/
public List<OrderUser> findOrderUserList();
}
<?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="com.itheima.mapper.OrderMapper">
<select id="findOrderUserList" resultType="com.itheima.pojo.OrderUser">
SELECT
o.id,
o.user_id as userId,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM
orders o
LEFT JOIN USER u ON u.id = o.user_id;
</select>
</mapper>
package com.itheima.test;
import static org.junit.Assert.*;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.itheima.mapper.OrderMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;
public class OrderMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
this.sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findOrderUserList() throws Exception {
// 获取sqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 获取OrderMapper
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<OrderUser> list = mapper.findOrderUserList();
for (OrderUser orderUser : list) {
System.err.println(orderUser);
}
}
}
1.2 使用resultMap
Order.java
package com.itheima.pojo;
import java.io.Serializable;
import java.util.Date;
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
// 订单id
private Integer id;
// 用户id
private Integer userId;
// 订单号
private String number;
// 订单创建时间
private Date createtime;
// 备注
private String note;
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.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 == null ? null : number.trim();
}
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 == null ? null : note.trim();
}
@Override
public String toString() {
return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
+ ", note=" + note + ", user=" + user + "]";
}
}
}
OrderMapper.java
package com.itheima.mapper;
import java.util.List;
import com.itheima.pojo.Order;
public interface OrderMapper {
/**
* @MethodName:findOrderUserList
* @Description:
* @return
*/
public List<Order> findOrderUserList();
}
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="com.itheima.mapper.OrderMapper">
<resultMap type="com.itheima.pojo.Order" id="order_user_map">
<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"/>
<!--
association:一对一关联映射
property:主表中附表对应的POJO属性名
javaType:POJO全限定类名
-->
<association property="user" javaType="com.itheima.pojo.User">
<!--
id:关联映射的ID
column:查询列表字段的外键名称
property:对应着POJO对象中主键的属性名
-->
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findOrderUserList" resultMap="order_user_map">
SELECT
o.id,
o.user_id,
o.number,
o.createtime,
o.note,
u.username,
u.address
FROM
orders o
LEFT JOIN USER u ON u.id = o.user_id;
</select>
</mapper>
测试类
package com.itheima.test;
import static org.junit.Assert.*;
import java.io.InputStream;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.itheima.mapper.OrderMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.OrderUser;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;
public class OrderMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
this.sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findOrderUserList() throws Exception {
// 获取sqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 获取OrderMapper
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> list = mapper.findOrderUserList();
for (Order order : list) {
System.err.println(order);
}
}
}
二、一对多
查询所有用户信息及用户关联的订单信息
package com.itheima.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;// 地址
private List<Order> orders;
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=" + orders + "]";
}
}
<?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">
<!-- namespace:命名空间,作用:1.用于隔离sql 2.使用动态代理开发DAO,namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.itheima.mapper.UserMapper">
<resultMap type="com.itheima.pojo.User" id="user_order_map">
<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="com.itheima.pojo.Order">
<id column="oid" property="id"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
</collection>
</resultMap>
<select id="findUserOrderList" resultMap="user_order_map">
SELECT
u.id,
u.username,
u.birthday,
u.sex,
u.address,
o.id AS oid,
o.number,
o.createtime,
o.note
FROM
USER u
LEFT JOIN orders o ON o.user_id = u.id
</select>
</mapper>
package com.itheima.mapper;
import java.util.List;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;
public interface UserMapper {
/**
* @MethodName:findUserOrderList
* @Description:
* @return
*/
public List<User> findUserOrderList();
}
package com.itheima.test;
import static org.junit.Assert.*;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import com.itheima.mapper.UserMapper;
import com.itheima.pojo.QueryVo;
import com.itheima.pojo.User;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
String resource = "SqlMapConfig.xml";
// 读取配置文件
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建工厂
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findUserOrderList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> list = mapper.findUserOrderList();
for (User user : list) {
System.err.println(user);
}
}
}