Day10--mybatis多表连接查询学习（一对一、一对多、多对多）

MyBatis是一个优秀的持久层框架，支持将SQL语句、存储过程以及高级映射转换成Java对象。下面是MyBatis处理一对一、一对多、多对多关系的方式及相应的代码示例。

数据库表

假设有四个表：user 、orders、role、user_role---->创建代码（占位较长）放在文章末尾···
首先先了解对应关系:
一对一：1订单对应1用户
一对多：1用户对应多订单
多对多：一个用户对应多种角色，一种角色对应多个用户

Java实体类(一表一数据库标准)

public class Orders {private Integer id;private Date ordertime;private Double total;//将user表注入order表(一对一)private User user;// getters and setters}public class Role {private Integer id;private String roleName;private String roleDesc;// getters and setters}public class User {private Integer id;private String username;private String password;// 关于时间的转换处理，这里采用一个工具类在mybatis_config中配置好后隐式调用实现，代码文章末尾给出// 数据库中时间的储存是bigint在Java中是dateprivate Date birthday;//将order表中数据注入user,类型为ArrayList<User>（一对多）private ArrayList<User> ordersArrayList;//将role表中数据注入user,类型为ArrayList<Role>（多对多）private ArrayList<Role> rolesArrayList;// getters and setters}
public class User_role {private Integer userId;private Integer roleId;// getters and setters

本次都采用XML独立映射文件来执行查询语句具体如下：

Mapper接口

import java.util.ArrayList;
public interface Mapper {//    一对一ArrayList<Orders> selectOrderUser();//    一对多ArrayList<User> selectUserOrders();//    多对多ArrayList<User> selectInfoRole();
}

Mapper.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接口-->
<mapper namespace="Day10.Mapper"><!--数据库的一对一查询++++注意：association+++javaType--><select id="selectOrderUser" resultMap="oTo">select *, o.id oidfrom orders o,user uwhere o.uuid = u.id</select><resultMap id="oTo" type="Day10.Model.Orders"><!--手动去指定字段与实体属性的映射关系--><!--            column: 数据表的字段名称--><!--            property: 实体属性的名称--><id column="oid" property="id"></id><result column="ordertime" property="ordertime"></result><result column="total" property="total"></result><!--            property: 当前实体(order)中的属性名称(private User user)--><!--            javaType: 当前实体(order)中的属性类型(User)--><association property="user" javaType="Day10.Model.User"><id column="uuid" property="id"></id><result column="username" property="username"></result><result column="password" property="password"></result><result column="birthday" property="birthday"></result></association></resultMap><!--数据库的一对多查询++++注意：collection+++ofTyp--><select id="selectUserOrders" resultMap="oTm">select *, o.id oidfrom user u,orders owhere o.uuid = u.id</select><resultMap id="oTm" type="Day10.Model.User"><id column="uuid" property="id"></id><result column="username" property="username"></result><result column="password" property="password"></result><result column="birthday" property="birthday"></result><collection property="ordersArrayList" ofType="Day10.Model.Orders"><id column="oid" property="id"></id><result column="ordertime" property="ordertime"></result><result column="total" property="total"></result></collection></resultMap><!--数据库的多对多查询++++注意：collection+++ofType--><select id="selectInfoRole" resultMap="mTm">select *from role r,user_role ur,user uwhere ur.userId = u.idand ur.roleId = r.id</select><resultMap id="mTm" type="Day10.Model.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="rolesArrayList" ofType="Day10.Model.Role"><id column="roleId" property="id"></id><result column="roelName" property="roleName"></result><result column="roleDesc" property="roleDesc"></result></collection></resultMap>
</mapper>

mybatis_config.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//mybatis.org//DTD Config 3.0//EN""http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration><settings><setting name="logImpl" value="LOG4J"/></settings><!-- 处理日期转换隐式调用--><typeHandlers><typeHandler handler="Day10.DateTypeHandler"></typeHandler></typeHandlers><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="com.mysql.cj.jdbc.Driver"/><property name="url" value="jdbc:mysql://localhost:3306/mts?serverTimezone=UTC"/><property name="username" value="root"/><property name="password" value="123456"/></dataSource></environment></environments><mappers>
<!--        这里的写的是mapper映射文件--><mapper resource="mybatis/mappers/mts/Mapper.xml"/></mappers>
</configuration>

调用测试

import Day10.Model.Orders;
import Day10.Model.User;
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.apache.log4j.PropertyConfigurator;import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Properties;public class Dao {public static SqlSession session;public static Mapper mapper;static {try {session = getSession();mapper = session.getMapper(Mapper.class);} catch (IOException e) {throw new RuntimeException(e);}}public static SqlSession getSession() throws IOException {InputStream in = Resources.getResourceAsStream("mybatis/config/mybatis_config_mts.xml");SqlSessionFactory fac = new SqlSessionFactoryBuilder().build(in);SqlSession sqlSession = fac.openSession();return sqlSession;}public static void logOut() throws IOException {Properties properties = new Properties();FileInputStream in = new FileInputStream("src/resources/log4j.properties");properties.load(in);PropertyConfigurator.configure(properties);}public static void main(String[] args) {ArrayList<Orders> orders = mapper.selectOrderUser();for (Orders o : orders) {System.out.println(o);}ArrayList<User> users = mapper.selectUserOrders();for (User user : users) {System.out.println(user);}ArrayList<User> users1 = mapper.selectInfoRole();for (User user : users1) {System.out.println(user);}}
}

通过上述例子，我们可以看到MyBatis通过resultMap、association、collection等标签来实现复杂的数据库关系映射，简化了SQL查询和Java对象之间的转换。
结尾线

数据库表创建代码

CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',`username` varchar(50) NOT NULL COMMENT '用户名',`password` varchar(50) NOT NULL COMMENT '密码',`birthday` bigint NOT NULL COMMENT '生日',PRIMARY KEY (`id`)
);
INSERT INTO `user` VALUES ('1', 'aa', '1233', '20221213');
INSERT INTO `user` VALUES ('2', 'bb', '2133', '20211212');
INSERT INTO `user` VALUES ('3', 'cc', '2133', '10211212');
INSERT INTO `user` VALUES ('4', 'xx', '12343', '20111213');
INSERT INTO `user` VALUES ('5', 'yy', '12343', '20111213');CREATE TABLE `orders` (`id` int NOT NULL AUTO_INCREMENT COMMENT '订单ID',`ordertime` bigint NOT NULL,`total` double NOT NULL COMMENT '总共订单数量',`uuid` int NOT NULL COMMENT '用户的ID',PRIMARY KEY (`id`),KEY `user_orders` (`uuid`),CONSTRAINT `user_orders` FOREIGN KEY (`uuid`) REFERENCES `user` (`id`)
);
INSERT INTO `orders` VALUES ('1', '20211112', '12.5', '1');
INSERT INTO `orders` VALUES ('2', '20211115', '12.5', '3');
INSERT INTO `orders` VALUES ('3', '20311115', '12.5', '2');
INSERT INTO `orders` VALUES ('4', '20221213', '15', '2');
INSERT INTO `orders` VALUES ('5', '20221212', '15', '1');CREATE TABLE `role` (`id` int NOT NULL AUTO_INCREMENT COMMENT '角色ID',`roleName` varchar(50) NOT NULL COMMENT '角色名称',`roleDesc` varchar(100) NOT NULL COMMENT '角色的描述',PRIMARY KEY (`id`)
);
INSERT INTO `role` VALUES ('1', '商家', '负责卖商品的商家');
INSERT INTO `role` VALUES ('2', '买家', '负责买商品的顾客');
INSERT INTO `role` VALUES ('3', '管理员', '负责商品的管理问题');CREATE TABLE `user_role` (`userId` int NOT NULL COMMENT '用户id',`roleId` int NOT NULL COMMENT '角色id',KEY `user_userId` (`userId`),KEY `role_roleId` (`roleId`),CONSTRAINT `role_roleId` FOREIGN KEY (`roleId`) REFERENCES `role` (`id`),CONSTRAINT `user_userId` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
INSERT INTO `user_role` VALUES ('1', '2');
INSERT INTO `user_role` VALUES ('2', '1');
INSERT INTO `user_role` VALUES ('3', '1');

时间转换工具类（直接复制粘贴使用就好）

package Day10;import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;public class DateTypeHandler extends BaseTypeHandler<Date> {// 将java类型转换成数据库需要的类型@Overridepublic void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {long time = date.getTime();preparedStatement.setLong(i, time);}// 将数据库中的类型 转换为java类型
// string 要转换的字符串
// resultSet 查询出的结果@Overridepublic Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
// 获得结果中需要的数据(long) 转换为Date类型long along = resultSet.getLong(s);Date date = new Date(along);return date;}// 将数据库中的类型 转换为java类型@Overridepublic Date getNullableResult(ResultSet resultSet, int i) throws SQLException {long along = resultSet.getLong(i);Date date = new Date(along);return date;}// 将数据库中的类型 转换为java类型@Overridepublic Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {long aLong = callableStatement.getLong(i);Date date = new Date(aLong);return date;}
}