第四章:MyBatis多表操作
数据准备
-- 1.创建User表
DROP TABLE IF EXISTS user;
CREATE TABLE user(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '用户编号',
username CHAR(20) NOT NULL COMMENT '用户姓名',
password VARCHAR(100) COMMENT '用户密码',
birthday BIGINT(20) COMMENT '生日'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user(id, username, password) VALUES
(1, 'zhangsan', '123'),
(2, 'lisi', '123'),
(3, 'wangwu', '123'),
(4, 'zhaoliu', '123'),
(5, 'tianqi', '123');
-- 2.创建Order表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '订单编号',
ordertime TIMESTAMP COMMENT '交易时间',
total DOUBLE COMMENT '总金额',
uid INT(4) COMMENT '订单所属用户编号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 建立外键约束
ALTER TABLE test.orders
ADD CONSTRAINT FK_ID1 FOREIGN KEY (uid) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE;
INSERT INTO orders(id, ordertime, total, uid) VALUES
(1, '2019-02-15 14:59:37', 3000, 1),
(2, '2019-10-10 15:00:00', 5800, 1),
(3, '2019-02-28 15:15:35', 2000, 2),
(4, '2019-02-21 15:30:45', 1000, 1),
(5, '2019-02-15 15:51:13', 1500, 2),
(6, '2019-06-07 15:58:26', 4000, 3);
-- 3.创建role表
DROP TABLE IF EXISTS role;
CREATE TABLE role(
id INT(4) PRIMARY KEY AUTO_INCREMENT COMMENT '角色编号',
roleName VARCHAR(20) COMMENT '角色名称',
roleDesc VARCHAR(40) COMMENT '角色描述'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO role(id, roleName, roleDesc) VALUES
(1, '院长', '负责全面工作'),
(2, '研究员', '课程研发工作'),
(3, '讲师', '授课工作'),
(4, '助教', '协助解决学生问题'),
(5, '班主任', '负责学生生活'),
(6, '就业指导', '负责学生就业工作');
-- 4.创建user_role表
DROP TABLE IF EXISTS user_role;
CREATE TABLE user_role(
userId INT(4) COMMENT '用户编号',
roleId INT(4) COMMENT '角色编号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 建立外键约束
ALTER TABLE test.user_role
ADD CONSTRAINT FK_ID2 FOREIGN KEY (userId) REFERENCES user(id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE test.user_role
ADD CONSTRAINT FK_ID3 FOREIGN KEY (roleId) REFERENCES role(id) ON DELETE RESTRICT ON UPDATE CASCADE;
INSERT INTO user_role(userId, roleId) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3);
一、1v1查询
用户表和订单表的关系为,一个用户有多个订单、一个订单只属于一个用户。
1v1查询案例:查询一个订单,于此同时查询出该订单所属的用户。
SELECT *
FROM orders o, user u
WHERE o.uid = u.id;
1.配置方式
1v1配置方式1:
<?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.itcast.mapper.OrderMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="orderMap" type="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--
property:当前实体(order)中的属性名称(private User usre)
javaType:当前实体(order)中的属性类型(User)
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *, o.id oid
FROM orders o, user u
WHERE o.uid = u.id
</select>
</mapper>
1v1配置方式2:
<?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.itcast.mapper.OrderMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="orderMap" type="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *, o.id oid
FROM orders o, user u
WHERE o.uid = u.id
</select>
</mapper>
2.代码演示
package com.itcast.domain;
import java.sql.Timestamp;
import java.util.Date;
public class Order {
private int id;
private Timestamp ordertime;
private double total;
private User user;//该订单属于的用户
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Timestamp ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
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 +
", user=" + user +
'}';
}
}
package com.itcast.domain;
import java.util.Date;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
public Date getBirthday() {
return birthday;
}
public Date setBirthday(Date birthday) { return this.birthday = birthday; }
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 + '\'' +
", birthday=" + birthday +
'}';
}
}
package com.itcast.mapper;
import com.itcast.domain.Order;
import java.util.List;
public interface OrderMapper {
public List<Order> findAll();
}
<?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.itcast.mapper.OrderMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="orderMap" type="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<!--
property:当前实体(order)中的属性名称(private User usre)
javaType:当前实体(order)中的属性类型(User)
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *, o.id oid
FROM orders o, user u
WHERE o.uid = u.id
</select>
</mapper>
<?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>
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.itcast.domain.User" alias="user"></typeAlias>
<typeAlias type="com.itcast.domain.Order" alias="order"></typeAlias>
</typeAliases>
<!--注册类型处理器-->
<typeHandlers>
<typeHandler handler="com.itcast.handler.DateTypeHandler"></typeHandler>
</typeHandlers>
<!--配置数据源环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="20001201"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com.itcast.mapper/UserMapper.xml"></mapper>
<mapper resource="com.itcast.mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
package com.itcast.test;
import com.itcast.domain.Order;
import com.itcast.mapper.OrderMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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();
for (Order order : orderList) {
System.out.println(order);
}
sqlSession.close();
}
}
注意:该程序还整合了上一章所学类型处理器typeHandler的使用,仔细理解反复观察!
二、1vN查询
用户表和订单表的关系为,一个用户有多个订单、一个订单只属于一个用户。
1vN查询案例:查询一个用户,于此同时查询出该用户的所有订单。
SELECT *,o.id oid
FROM user u LEFT JOIN orders o
ON u.id = o.uid;
1.配置方式
<?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.itcast.mapper.UserMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--
配置集合信息
property:集合信息
ofType:当前集合中的数据类型
-->
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *, o.id oid
FROM user u, orders o
WHERE u.id = o.uid;
</select>
</mapper>
2.代码演示
package com.itcast.domain;
import java.sql.Timestamp;
import java.util.Date;
public class Order {
private int id;
private Timestamp ordertime;
private double total;
private User user;//该定单属于的用户
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public Date getOrdertime() {
return ordertime;
}
public void setOrdertime(Timestamp ordertime) {
this.ordertime = ordertime;
}
public double getTotal() {
return total;
}
public void setTotal(double total) {
this.total = total;
}
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 +
", user=" + user +
'}';
}
}
package com.itcast.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
private List<Order> orderList;
public Date getBirthday() {
return birthday;
}
public Date setBirthday(Date birthday) { return this.birthday = birthday; }
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; }
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", orderList=" + orderList +
'}';
}
}
package com.itcast.mapper;
import com.itcast.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
}
<?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.itcast.mapper.UserMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="userMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--
配置集合信息
property:集合信息
ofType:当前集合中的数据类型
-->
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMap">
SELECT *, o.id oid
FROM user u, orders o
WHERE u.id = o.uid;
</select>
</mapper>
<?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>
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.itcast.domain.User" alias="user"></typeAlias>
<typeAlias type="com.itcast.domain.Order" alias="order"></typeAlias>
</typeAliases>
<!--注册类型处理器-->
<typeHandlers>
<typeHandler handler="com.itcast.handler.DateTypeHandler"></typeHandler>
</typeHandlers>
<!--配置分页助手插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<!--配置数据源环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="20001201"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com.itcast.mapper/UserMapper.xml"></mapper>
<mapper resource="com.itcast.mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
package com.itcast.test;
import com.itcast.domain.Order;
import com.itcast.domain.User;
import com.itcast.mapper.OrderMapper;
import com.itcast.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
}
三、NvN查询
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用。
NvN查询案例:查询用户同时查询出该用户的所有角色。
SELECT *
FROM user u, user_role ur, role r
WHERE u.id = ur.userId AND ur.roleId = r.id;
1.配置方式
<?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.itcast.mapper.UserMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="userRoleMap" type="user">
<!--封装user信息-->
<id column="userId" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--封装user内部的roleList信息-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT *
FROM user u, user_role ur, role r
WHERE u.id = ur.userId AND ur.roleId = r.id
</select>
</mapper>
2.代码演示
package com.itcast.domain;
public class Role {
private int id;
private String roleName;
private String roleDesc;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public String getRoleDesc() {
return roleDesc;
}
public void setRoleDesc(String roleDesc) {
this.roleDesc = roleDesc;
}
@Override
public String toString() {
return "Role{" +
"id=" + id +
", roleName='" + roleName + '\'' +
", roleDesc='" + roleDesc + '\'' +
'}';
}
}
package com.itcast.domain;
import java.util.Date;
import java.util.List;
public class User {
private int id;
private String username;
private String password;
private Date birthday;
private List<Order> orderList;//该用户的所有订单
private List<Role> roleList;//该用户的所哟角色
public List<Role> getRoleList() {
return roleList;
}
public void setRoleList(List<Role> roleList) {
this.roleList = roleList;
}
public Date getBirthday() {
return birthday;
}
public Date setBirthday(Date birthday) { return this.birthday = birthday; }
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; }
public List<Order> getOrderList() {
return orderList;
}
public void setOrderList(List<Order> orderList) {
this.orderList = orderList;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", birthday=" + birthday +
", roleList=" + roleList +
'}';
}
}
package com.itcast.mapper;
import com.itcast.domain.User;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
public List<User> findUserAndRoleAll();
}
<?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.itcast.mapper.UserMapper">
<!--手动指定字段与实体属性之间的映射关系-->
<resultMap id="userRoleMap" type="user">
<!--封装user信息-->
<id column="userId" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--封装user内部的roleList信息-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"></id>
<result column="roleName" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT *
FROM user u, user_role ur, role r
WHERE u.id = ur.userId AND ur.roleId = r.id
</select>
</mapper>
<?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>
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.itcast.domain.User" alias="user"></typeAlias>
<typeAlias type="com.itcast.domain.Order" alias="order"></typeAlias>
<typeAlias type="com.itcast.domain.Role" alias="role"></typeAlias>
</typeAliases>
<!--注册类型处理器-->
<typeHandlers>
<typeHandler handler="com.itcast.handler.DateTypeHandler"></typeHandler>
</typeHandlers>
<!--配置分页助手插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
<!--配置数据源环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test"/>
<property name="username" value="root"/>
<property name="password" value="20001201"/>
</dataSource>
</environment>
</environments>
<!--加载映射文件-->
<mappers>
<mapper resource="com.itcast.mapper/UserMapper.xml"></mapper>
<mapper resource="com.itcast.mapper/OrderMapper.xml"></mapper>
</mappers>
</configuration>
package com.itcast.test;
import com.itcast.domain.Order;
import com.itcast.domain.User;
import com.itcast.mapper.OrderMapper;
import com.itcast.mapper.UserMapper;
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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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.findUserAndRoleAll();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
}
多表查询 | 映射文件配置方式 |
---|---|
1v1 | 使用<resultMap>做配置 |
1vN | 使用<resultMap> + <collection>做配置 |
NvN | 使用<resultMap> + <collection>做配置(多1张表) |