使用MyBatis进行单表查询十分简单，多表连接查询相比单表查询操作稍微复杂，不过相较于使用JDBC方式简单得多：
现有八张表，每张表的结构如下图：
users:
image.png
orders:
image.png
addresses:
image.png
pay_type:
image.png
order_item:
image.png
products:
image.png
product_types:
image.png
imgaes:
image.png
1、使用SQL语句将User表作为主表查询id为1的用户的所有订单详情：
SELECT * FROM users u LEFT JOIN orders o ON o.user_id=u.id LEFT JOIN pay_type pty ON pty.id=o.pay_type LEFT JOIN addresses ad ON ad.id=o.address LEFT JOIN order_item oi ON oi.order_id=o.order_id LEFT JOIN products p ON p.id=oi.product_id LEFT JOIN product_types pt ON pt.id=p.type LEFT JOIN images img ON img.product_id=p.id WHERE u.id=1;
查询结果如下：
image.png
2、使用MyBatis以User表开始查询八张表
- 首先，根据一张表对应一个类的原则，我们要创建这八张表的对应类，需要注意的是，从不同的表出发进行多表连接查询每张表的私有成员有一些不同：
User.java
package com.qianfeng.pojo;
import java.util.List;
public class User {
private int id;
private String name;
private String password;
private int age;
private List<Order> orders;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getAge() {
return age;
}
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("User{");
sb.append("id=").append(id);
sb.append(", name='").append(name).append('\'');
sb.append(", password='").append(password).append('\'');
sb.append(", age=").append(age);
sb.append(", orders=").append(orders);
sb.append('}');
return sb.toString();
}
}
Order.java
package com.qianfeng.pojo;
import java.util.List;
public class Order {
private int id;
private double price;
private int userId;
private List<OrderItem> items;
private Address address;
private PayType pt;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public List<OrderItem> getItems() {
return items;
}
public void setItems(List<OrderItem> items) {
this.items = items;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public PayType getPt() {
return pt;
}
public void setPt(PayType pt) {
this.pt = pt;
}
@Override
public String toString() {
final StringBuilder sb = new StringBuilder("Order{");
sb.append("id=").append(id);
sb.append(", price=").append(price);
sb.append(", userId=").append(userId);
sb.append(", items=").append(items);
sb.append(", address=").append(address);
sb.append(", pt=").append(pt);
sb.append('}');
return sb.toString();
}
}
以下省略
- 创建MyBatis配置文件
mybatis.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.qianfeng.pojo"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/qianfeng/pojo/OrderMapper.xml"/>
<mapper resource="com/qianfeng/pojo/UserMapper.xml"/>
<mapper resource="com/qianfeng/pojo/AddressMapper.xml"/>
<mapper resource="com/qianfeng/pojo/ImageMapper.xml"/>
<mapper resource="com/qianfeng/pojo/OrderItemMapper.xml"/>
<mapper resource="com/qianfeng/pojo/PayTypeMapper.xml"/>
<mapper resource="com/qianfeng/pojo/ProductMapper.xml"/>
<mapper resource="com/qianfeng/pojo/ProductTypeMapper.xml"/>
</mappers>
</configuration>
- 创建mapper文件
UserMapper.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.qianfeng.pojo.User">
<select id="getUserById" resultType="User">
select * from users where id = #{id}
</select>
<select id="getUserOrdersByUid" resultMap="UserMapper">
select * from users where id = #{id}
</select>
<resultMap id="UserMapper" type="User">
<id property="id" column="id"/>
<result column="name" property="name"/>
<result column="password" property="password"/>
<result column="age" property="age"/>
<collection property="orders" column="id" select="com.qianfeng.pojo.Order.getOrderByOId2" ofType="Order"/>
</resultMap>
</mapper>
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.qianfeng.pojo.Order">
<select id="getOrderByOId" resultMap="orderMap">
select * from orders where order_id = #{id}
</select>
<resultMap id="orderMap" type="Order">
<id property="id" column="order_id"></id>
<result column="price" property="price"/>
<association property="user" column="user_id" select="com.qianfeng.pojo.User.getUserById"/>
<association property="address" column="address" select="com.qianfeng.pojo.Address.getAddressById"/>
<association property="pt" column="pay_type" select="com.qianfeng.pojo.PayType.getPayTypeById"/>
<collection property="items" column="order_id" select="com.qianfeng.pojo.OrderItem.getOrderItemByOid" ofType="OrderItem"/>
</resultMap>
<select id="getOrderByOId2" resultMap="orderMap2">
select * from orders where user_id = #{id}
</select>
<resultMap id="orderMap2" type="Order">
<id property="id" column="order_id"></id>
<result column="price" property="price"/>
<result column="user_id" property="userId"/>
<association property="address" column="address" select="com.qianfeng.pojo.Address.getAddressById"/>
<association property="pt" column="pay_type" select="com.qianfeng.pojo.PayType.getPayTypeById"/>
<collection property="items" column="order_id" select="com.qianfeng.pojo.OrderItem.getOrderItemByOid" ofType="OrderItem"/>
</resultMap>
</mapper>
以下省略。
只需要记住：多表查询对一关系使用的是association标签，对多查询使用的是collection标签。因为在MyBatis中只有这两种关系：对一和对多。当然这和传统的数据库有所不同，是由于看问题的角度导致的。传统的数据库有一对一、一对多、多对一、多对多的关系。那用户与订单的关系来看，一个用户对应多个订单，很容易理解这是一对多的关系，但是如果从订单的角度来看，一个订单只对应一个用户，这是对一的关系。
网友评论