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中是date
    private 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 oid
        from orders o,
             user u
        where 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 oid
        from user u,
             orders o
        where 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 u
        where ur.userId = u.id
          and 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 configuration
        PUBLIC "-//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类型转换成数据库需要的类型
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException {
        long time = date.getTime();
        preparedStatement.setLong(i, time);
    }

    // 将数据库中的类型 转换为java类型
// string 要转换的字符串
// resultSet 查询出的结果
    @Override
    public Date getNullableResult(ResultSet resultSet, String s) throws SQLException {
// 获得结果中需要的数据(long) 转换为Date类型
        long along = resultSet.getLong(s);
        Date date = new Date(along);
        return date;

    }

    // 将数据库中的类型 转换为java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int i) throws SQLException {
        long along = resultSet.getLong(i);
        Date date = new Date(along);
        return date;
    }

    // 将数据库中的类型 转换为java类型
    @Override
    public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        long aLong = callableStatement.getLong(i);
        Date date = new Date(aLong);
        return date;
    }
}