MyBatis —— 第三十一章 MyBatis 的多表操作
1. 一对一查询
1.1. 一对一查询的模型
用户表和订单表的关系为:一个用户有多个订单,一个订单只从属于一个用户
一对一查询的需求: 查询一个订单,与此同时查询出该订单的所属用户
1.2. 示例代码
order
数据表
名 | 类型 | 允许空值 | 是否主键 | 是否外键 |
---|---|---|---|---|
id |
int |
false |
true |
false |
order_time |
varchar(255) |
false |
false |
false |
total |
double |
false |
false |
false |
uid |
int |
false |
false |
true |
create table orders
(
id int auto_increment
primary key,
order_time varchar(255) not null,
total double not null,
uid int not null,
constraint orders_id_uindex
unique (id),
constraint orders_user_id_fk
foreign key (uid) references user (id)
);
sqlMapConfig.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>
<!-- Load the external properties file through the properties tag -->
<properties resource="jdbc.properties"></properties>
<!-- Define alias -->
<typeAliases>
<typeAlias type="com.gregperlinli.domain.User" alias="user" />
<typeAlias type="com.gregperlinli.domain.Order" alias="order" />
</typeAliases>
<!-- Data source environment -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- Load mapping profile -->
<mappers>
<mapper resource="com/gregperlinli/mapper/UserMapper.xml"></mapper>
<mapper resource="com/gregperlinli/mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
com/yourname/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.gregperlinli.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!-- Manually specify how fields are mapped to entity attributes
column: Field name of the data table
property: Property name of the entity
-->
<id column="oid" property="id" />
<result column="order_time" property="orderTime" />
<result column="total" property="total" />
<result column="uid" property="user.id" />
<!--
property: The property name (private User user) of the current entity (order)
javaType: The type of property in the current entity
-->
<association property="user" javaType="user">
<id column="uid" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<result column="birthday" property="birthday" />
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
select *, o.id oid from orders o,user u where o.uid = u.id
</select>
</mapper>
com.yourname.domain.User
/**
* @author gregPerlinLi
* @since 2021-10-08
*/
public class User {
private int id;
private String username;
private String password;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
com.yourname.domain.Order
/**
* @author gregPerlinLi
* @since 2021-10-13
*/
public class Order {
private int id;
private Date orderTime;
private double total;
private int uid;
/**
* Which user does the current order belong to
*/
private User user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrderTime() {
return orderTime;
}
public void setOrderTime(Date orderTime) {
this.orderTime = orderTime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderTime=" + orderTime +
", total=" + total +
", uid=" + uid +
", user=" + user +
'}';
}
}
com.yourname.mapper.OrderMapper
/**
* @author gregPerlinLi
* @since 2021-10-13
*/
public interface OrderMapper {
/**
* Find all order
* @return order list
*/
List<Order> findAll();
}
com.yourname.test.MybatisTest
/**
* @author gregPerlinLi
* @since 2021-10-12
*/
public class MybatisTest {
@Test
public void test1() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orderList = mapper.findAll();
orderList.forEach(System.out::println);
sqlSession.close();
}
}
2. 一对多查询
2.1. 一对多查询的模型
用户表和订单的关系为:一个用户有多个订单,一个订单只从属于一个用户
一对多查询的需求: 查询一个用户,与此同时查询出该用户具有的订单
2.2. 示例代码
com/yourname/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.gregperlinli.mapper.UserMapper">
<resultMap id="userMap" type="user">
<id column="uid" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<result column="birthday" property="birthday" />
<!-- Configure list information
property: List name
ofType: The data type of the current list
-->
<collection property="orderList" ofType="order">
<!-- Encapsulate the data of order -->
<id column="oid" property="id" />
<result column="order_time" property="orderTime" />
<result column="total" property="total" />
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
select *, o.id oid from user u, orders o where u.id = o.uid
</select>
</mapper>
com.yourname.mapper.UserDao
/**
* @author gregPerlinLi
* @since 2021-10-08
*/
public interface UserMapper {
/**
* FInd all
*
* @return User list
*/
List<User> findAll();
}
com.yourname.test.MybatisTest
/**
* @author gregPerlinLi
* @since 2021-10-12
*/
public class MybatisTest {
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findAll();
userList.forEach(System.out::println);
sqlSession.close();
}
}
3. 多对多查询
3.1. 多对多查询的模型
用户表和角色表的关系为:一个用户有多个角色,一个角色被多个用户使用
多对多查询的需求: 查询用户同时查询出该用户的所有角色
3.2. 示例代码
role
数据表
名 | 类型 | 允许空值 | 是否主键 | 是否外键 |
---|---|---|---|---|
id |
int |
false |
true |
false |
role_name |
varchar(50) |
false |
false |
false |
role_desc |
varchar(50) |
true |
false |
false |
create table role
(
id int auto_increment
primary key,
role_name varchar(255) not null,
role_desc varchar(50) null,
constraint role_id_uindex
unique (id),
constraint role_role_name_uindex
unique (role_name)
);
user_role
列表
名 | 类型 | 允许空值 | 是否主键 | 是否外键 |
---|---|---|---|---|
user_id |
int |
false |
false |
true |
role_id |
int |
false |
false |
true |
create table user_role
(
user_id int not null,
role_id int not null,
constraint user_role_role_id_fk
foreign key (role_id) references role (id),
constraint user_role_user_id_fk
foreign key (user_id) references user (id)
);
sqlMapConfig.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>
<!-- Load the external properties file through the properties tag -->
<properties resource="jdbc.properties"></properties>
<!-- Define alias -->
<typeAliases>
<typeAlias type="com.gregperlinli.domain.User" alias="user" />
<typeAlias type="com.gregperlinli.domain.Order" alias="order" />
<typeAlias type="com.gregperlinli.domain.Role" alias="role" />
</typeAliases>
<!-- Data source environment -->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- Load mapping profile -->
<mappers>
<mapper resource="com/gregperlinli/mapper/UserMapper.xml"></mapper>
<mapper resource="com/gregperlinli/mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
com/yourname/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.gregperlinli.mapper.UserMapper">
<resultMap id="userRoleMap" type="user">
<!-- user information -->
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="password" property="password" />
<result column="birthday" property="birthday" />
<!-- roleList information inside user -->
<collection property="roleList" ofType="role">
<id column="role_id" property="id" />
<result column="role_name" property="roleName" />
<result column="role_desc" property="roleDesc" />
</collection>
</resultMap>
<select id="findUserAndRole" resultMap="userRoleMap">
select * from user u, user_role ur, role r where u.id = ur.user_id and ur.role_id = r.id
</select>
</mapper>
com.yourname.mapper.UserMapper
/**
* @author gregPerlinLi
* @since 2021-10-08
*/
public interface UserMapper {
/**
* Find user ans role
* @return user list with role
*/
List<User> findUserAndRole();
}
com.yourname.test.MybatisTest
/**
* @author gregPerlinLi
* @since 2021-10-12
*/
public class MybatisTest {
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList = mapper.findUserAndRole();
userList.forEach(System.out::println);
sqlSession.close();
}
}
4. 知识要点
MyBatis 多表配置方式:
- 一对一配置: 使用
<resultMap>
做配置 - 一对多配置: 使用
<resultMap
+<collection>
做配置 - 多对多配置: 使用
<resultMap>
+<collection>
做配置