第四章：MyBatis多表操作

OVERVIEW

• 第四章：MyBatis多表操作
• • • • 数据准备
    • 一、1v1查询
    • • 1.配置方式
      • 2.代码演示
    • 二、1vN查询
    • • 1.配置方式
      • 2.代码演示
    • 三、NvN查询
    • • 1.配置方式
      • 2.代码演示

数据准备

-- 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：

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><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 oidFROM orders o, user uWHERE o.uid = u.idselect>
mapper>

1v1配置方式2：

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 oidFROM orders o, user uWHERE o.uid = u.idselect>
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;}@Overridepublic 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;}@Overridepublic 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();
}

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><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 oidFROM orders o, user uWHERE o.uid = u.idselect>
mapper>

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 {@Testpublic 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.配置方式

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><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 oidFROM user u, orders oWHERE 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;}@Overridepublic 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;}@Overridepublic 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();
}

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><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 oidFROM user u, orders oWHERE u.id = o.uid;select>
mapper>

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 {@Testpublic 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.配置方式

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"><id column="userId" property="id">id><result column="username" property="username">result><result column="password" property="password">result><result column="birthday" property="birthday">result><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 rWHERE u.id = ur.userId AND ur.roleId = r.idselect>
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;}@Overridepublic 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;}@Overridepublic 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();
}

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"><id column="userId" property="id">id><result column="username" property="username">result><result column="password" property="password">result><result column="birthday" property="birthday">result><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 rWHERE u.id = ur.userId AND ur.roleId = r.idselect>
mapper>

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 {@Testpublic 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();}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！