MyBatis(五)：mybatis关联映射

Mybatis中表与表之间的关系分为一下4类：

1）一对一

2）一对多

3）多对一

4）多对多

创建数据Demo表

数据库表：

用户表user：记录了购买商品的用户信息。

订单表orders：记录了用户所创建的订单（购买商品的订单）。

订单明细表orderdetail：记录了订单的详细信息即购买商品的信息。

商品表items：记录商品信息。

数据模型：

数据表之间的关系：

先分析数据级别之间有关系的表之间的业务关系：

usre和orders：

user —-> orders：一个用户可以创建多个订单，一对多
orders —-> user：一个订单只由一个用户创建，一对一

orders和orderdetail：

orders —-> orderdetail：一个订单可以包括多个订单明细，因为一个订单可以购买多个商品，每个商品的购买信息在orderdetail记录，一对多关系。
orderdetail —-> orders：一个订单明细只能包括在一个订单中，一对一

orderdetail和itesm：

orderdetail —-> itesms：一个订单明细只对应一个商品信息，一对一
items —-> orderdetail:一个商品可以包括在多个订单明细，一对多

创建Demo表

/*
Navicat MySQL Data Transfer

Source Server         : mysql1
Source Server Version : 50712
Source Host           : localhost:3306
Source Database       : relation

Target Server Type    : MYSQL
Target Server Version : 50712
File Encoding         : 65001

Date: 2019-03-24 15:57:05
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for items
-- ----------------------------
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` (
  `items_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `price` double(10,2) DEFAULT NULL,
  `detail` varchar(255) DEFAULT NULL,
  `pic` varchar(255) DEFAULT NULL,
  `createtime` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of items
-- ----------------------------
INSERT INTO `items` VALUES ('1', '巧克力', '28.00', '黑巧克力', 'pic url', '2019-01-01');
INSERT INTO `items` VALUES ('2', '运动袜', '35.00', '运动袜', 'pic url', '2019-01-01');
INSERT INTO `items` VALUES ('3', '高跟鞋', '890.00', '高跟鞋', 'pic url', '2019-01-01');

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `createtime` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '1', '2', '2019-03-24');
INSERT INTO `orders` VALUES ('2', '2', '1', '2019-03-22');
INSERT INTO `orders` VALUES ('3', '2', '2', '2019-03-24');

-- ----------------------------
-- Table structure for ordersdetail
-- ----------------------------
DROP TABLE IF EXISTS `ordersdetail`;
CREATE TABLE `ordersdetail` (
  `ordersdetail_id` int(11) NOT NULL AUTO_INCREMENT,
  `items_id` int(11) DEFAULT NULL,
  `items_num` int(11) DEFAULT NULL,
  `orders_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of ordersdetail
-- ----------------------------
INSERT INTO `ordersdetail` VALUES ('1', '1', '1', '1');
INSERT INTO `ordersdetail` VALUES ('2', '2', '1', '1');
INSERT INTO `ordersdetail` VALUES ('3', '1', '1', '2');
INSERT INTO `ordersdetail` VALUES ('4', '2', '1', '3');
INSERT INTO `ordersdetail` VALUES ('5', '3', '1', '3');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `gender` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'zhangsan', '1999-10-01', '北京市海淀区海淀黄庄', '男');
INSERT INTO `user` VALUES ('2', 'lili', '1989-02-02', '北京市朝阳区大运村', '女');

配置mybatis-generator插件，生成mapper、mapper.xml、model代码:

第一步：创建demo maven项目：

填写Group Id、Artifact Id、Packaging选择为pom：

此时项目值包含mybatie-study

然后，创建maven module-------选中mybatie-study maven项目，然后右键-》弹出菜单，菜单中选择New->选择Other...

在弹出New窗口中选择Maven->Maven Module

填写Maven Module名称

点击finish完成Maven Module创建。

第二步：在Maven Module项目mybatis-relation下/src/main/resources目录下，创建generatorConfig.xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd" >
<generatorConfiguration>
    <!-- jdbc连接库jar包路径 -->
    <classPathEntry location="D:\IDE\maven\repository\mysql\mysql-connector-java\5.1.38\mysql-connector-java-5.1.38.jar" />
    <!-- eclipse tool配置 Location = E:\Java\apache-maven-3.2.3\bin\mvn.bat Working 
        Direction = ${workspace_loc:/linetoy-common} Arguments = mybatis-generator:generate -->
    <context id="DB2Tables" targetRuntime="MyBatis3">
        <!-- 去掉注释，一般都会去掉，那个注释真的不敢让人恭维 -->
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!-- jdbc连接配置 -->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
            connectionURL="jdbc:mysql://localhost:3306/relation?characterEncoding=UTF-8" userId="root"
            password="root">
        </jdbcConnection>
        <!-- 数字字段是否强制使用BigDecimal类 -->
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>
        <!-- entity创建后放在那个项目的哪个包路径上 -->
        
        <javaModelGenerator targetPackage="com.dx.mybatis01.model"
            targetProject="D:\git\springboot_learn01\mybatie-study\mybatis-relation\src\main\java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator> 
        
        <!-- dao创建后放在那个项目的哪个包路径上 -->
        <sqlMapGenerator targetPackage="com.dx.mybatis01.dao"
            targetProject="D:\git\springboot_learn01\mybatie-study\mybatis-relation\src\main\java">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>
        <!-- dao的.xml描述sql文件创建后放在那个项目的哪个包路径上 -->
        <javaClientGenerator type="XMLMAPPER"
            targetPackage="com.dx.mybatis01.dao"
            targetProject="D:\git\springboot_learn01\mybatie-study\mybatis-relation\src\main\java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>
        <!-- 要生成的表配置，可以多个 tableName：表名 domainObjectName：指定类名 -->
        <table tableName="orders" domainObjectName="Orders"
            enableCountByExample="false" enableUpdateByExample="false"
            enableDeleteByExample="false" enableSelectByExample="false"
            selectByExampleQueryId="false">
              <generatedKey column="id" sqlStatement="Mysql" identity="true" />
        </table>
    </context>
</generatorConfiguration>

该配置文件用来配置生成mybatis代码规则，以及代码生成目录，将要生成代码的table配置。

第三步：在Maven Module项目mybatis-relation中pom.xml中引入依赖包：

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>com.dx.mybatis01</groupId>
        <artifactId>mybatie-study</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <groupId>com.dx.mybatis01.relation</groupId>
    <artifactId>mybatis-relation</artifactId>

    <dependencies>
        <!-- 配置mybatis的开发包 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!-- 配置mysql支持包 -->
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>          
        <!-- 日志 slf4j是规范/接口 日志实现:log4j,logback,common-logging 这里使用:slf4j + log4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.2</version>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.mybatis.generator/mybatis-generator-core -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>
                
    </dependencies>
    <build>
        <defaultGoal>compile</defaultGoal>
        <finalName>mybatis-plugin</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.1</version>
                <configuration>
                    <skip>true</skip>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <configuration>   
                    <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

第四步：选中Maven Module项目右键运行生成mybatis代码：

在Maven build.....弹出窗口中，Global参数中填写“mybatis-generator:generate”，并运行

执行日志：

Apache Maven 3.3.9 (bb52d8502b132ec0a5a3f4c09453c07478323dc5; 2015-11-11T00:41:47+08:00)
Maven home: D:\Java_Study\apache-maven-3.3.9
Java version: 1.8.0_161, vendor: Oracle Corporation
Java home: C:\Program Files\Java\jdk1.8.0_161\jre
Default locale: zh_CN, platform encoding: GBK
OS name: "windows 7", version: "6.1", arch: "amd64", family: "dos"
[DEBUG] Created new class realm maven.api
[DEBUG] Importing foreign packages into class realm maven.api
。。。。。。
[DEBUG] Populating class realm maven.api
[INFO] Error stacktraces are turned on.
[DEBUG] Reading global settings from D:\Java_Study\apache-maven-3.3.9\conf\settings.xml
[DEBUG] Reading user settings from D:\Java_Study\apache-maven-3.3.9\conf\settings.xml
[DEBUG] Reading global toolchains from D:\Java_Study\apache-maven-3.3.9\conf\toolchains.xml
[DEBUG] Reading user toolchains from C:\Users\Administrator\.m2\toolchains.xml
[DEBUG] Using local repository at D:\IDE\maven\repository
[DEBUG] Using manager EnhancedLocalRepositoryManager with priority 10.0 for D:\IDE\maven\repository
[INFO] Scanning for projects...
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for central (https://repo.maven.apache.org/maven2).
[DEBUG] Extension realms for project com.dx.mybatis01.relation:mybatis-relation:jar:0.0.1-SNAPSHOT: (none)
[DEBUG] Looking up lifecyle mappings for packaging jar from ClassRealm[plexus.core, parent: null]
[DEBUG] Extension realms for project com.dx.mybatis01:mybatie-study:pom:0.0.1-SNAPSHOT: (none)
[DEBUG] Looking up lifecyle mappings for packaging pom from ClassRealm[plexus.core, parent: null]
[DEBUG] Resolving plugin prefix mybatis-generator from [org.apache.maven.plugins, org.codehaus.mojo]
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for snapshots (http://snapshots.maven.codehaus.org/maven2).
[DEBUG] Resolved plugin prefix mybatis-generator to org.mybatis.generator:mybatis-generator-maven-plugin from POM com.dx.mybatis01.relation:mybatis-relation:jar:0.0.1-SNAPSHOT
[DEBUG] === REACTOR BUILD PLAN ================================================
[DEBUG] Project: com.dx.mybatis01.relation:mybatis-relation:jar:0.0.1-SNAPSHOT
[DEBUG] Tasks:   [mybatis-generator:generate]
[DEBUG] Style:   Regular
[DEBUG] =======================================================================
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building mybatis-relation 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[DEBUG] Resolving plugin prefix mybatis-generator from [org.apache.maven.plugins, org.codehaus.mojo]
[DEBUG] Resolved plugin prefix mybatis-generator to org.mybatis.generator:mybatis-generator-maven-plugin from POM com.dx.mybatis01.relation:mybatis-relation:jar:0.0.1-SNAPSHOT
[DEBUG] Lifecycle default -> [validate, initialize, generate-sources, process-sources, generate-resources, process-resources, compile, process-classes, generate-test-sources, process-test-sources, generate-test-resources, process-test-resources, test-compile, process-test-classes, test, prepare-package, package, pre-integration-test, integration-test, post-integration-test, verify, install, deploy]
[DEBUG] Lifecycle clean -> [pre-clean, clean, post-clean]
[DEBUG] Lifecycle site -> [pre-site, site, post-site, site-deploy]
[DEBUG] === PROJECT BUILD PLAN ================================================
[DEBUG] Project:       com.dx.mybatis01.relation:mybatis-relation:0.0.1-SNAPSHOT
[DEBUG] Dependencies (collect): []
[DEBUG] Dependencies (resolve): []
[DEBUG] Repositories (dependencies): [nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public, default, releases)]
[DEBUG] Repositories (plugins)     : [nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public, default, releases)]
[DEBUG] -----------------------------------------------------------------------
[DEBUG] Goal:          org.mybatis.generator:mybatis-generator-maven-plugin:1.3.5:generate (default-cli)
[DEBUG] Style:         Regular
[DEBUG] Configuration: <?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <configurationFile default-value="${project.basedir}/src/main/resources/generatorConfig.xml">D:\git\springboot_learn01\mybatie-study\mybatis-relation/src/main/resources/generatorConfig.xml</configurationFile>
  <contexts>${mybatis.generator.contexts}</contexts>
  <jdbcDriver>${mybatis.generator.jdbcDriver}</jdbcDriver>
  <jdbcPassword>${mybatis.generator.jdbcPassword}</jdbcPassword>
  <jdbcURL>${mybatis.generator.jdbcURL}</jdbcURL>
  <jdbcUserId>${mybatis.generator.jdbcUserId}</jdbcUserId>
  <outputDirectory default-value="${project.build.directory}/generated-sources/mybatis-generator">${mybatis.generator.outputDirectory}</outputDirectory>
  <overwrite default-value="false">true</overwrite>
  <project>${project}</project>
  <skip default-value="false">${mybatis.generator.skip}</skip>
  <sqlScript>${mybatis.generator.sqlScript}</sqlScript>
  <tableNames>${mybatis.generator.tableNames}</tableNames>
  <verbose default-value="false">true</verbose>
</configuration>
[DEBUG] =======================================================================
[INFO] 
[INFO] --- mybatis-generator-maven-plugin:1.3.5:generate (default-cli) @ mybatis-relation ---
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for apache.snapshots (http://repository.apache.org/snapshots).
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for sonatype-nexus-snapshots (https://oss.sonatype.org/content/repositories/snapshots).
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for repository.jboss.org (http://repository.jboss.org/maven2).
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for snapshots.jboss.org (http://snapshots.jboss.org/maven2).
[DEBUG] Using mirror nexus-aliyun (http://maven.aliyun.com/nexus/content/groups/public) for oss.sonatype.org/jboss-snapshots (http://oss.sonatype.org/content/repositories/jboss-snapshots).
[DEBUG] Dependency collection stats: {ConflictMarker.analyzeTime=1, ConflictMarker.markTime=0, ConflictMarker.nodeCount=17, ConflictIdSorter.graphTime=0, ConflictIdSorter.topsortTime=1, ConflictIdSorter.conflictIdCount=14, ConflictIdSorter.conflictIdCycleCount=0, ConflictResolver.totalTime=2, ConflictResolver.conflictItemCount=17, DefaultDependencyCollector.collectTime=739, DefaultDependencyCollector.transformTime=5}
[DEBUG] org.mybatis.generator:mybatis-generator-maven-plugin:jar:1.3.5:
。。。。。。
[DEBUG] Configuring mojo org.mybatis.generator:mybatis-generator-maven-plugin:1.3.5:generate from plugin realm ClassRealm[plugin>org.mybatis.generator:mybatis-generator-maven-plugin:1.3.5, parent: sun.misc.Launcher$AppClassLoader@33909752]
[DEBUG] Configuring mojo 'org.mybatis.generator:mybatis-generator-maven-plugin:1.3.5:generate' with basic configurator -->
[DEBUG]   (f) configurationFile = D:\git\springboot_learn01\mybatie-study\mybatis-relation\src\main\resources\generatorConfig.xml
[DEBUG]   (f) outputDirectory = D:\git\springboot_learn01\mybatie-study\mybatis-relation\target\generated-sources\mybatis-generator
[DEBUG]   (f) overwrite = true
[DEBUG]   (f) project = MavenProject: com.dx.mybatis01.relation:mybatis-relation:0.0.1-SNAPSHOT @ D:\git\springboot_learn01\mybatie-study\mybatis-relation\pom.xml
[DEBUG]   (f) skip = false
[DEBUG]   (f) verbose = true
[DEBUG] -- end configuration --
[INFO] Connecting to the Database
Sun Mar 24 14:42:43 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
[INFO] Introspecting table orders
[DEBUG] Retrieving column information for table "orders"
[DEBUG] Found column "id", data type 4, in table "relation..orders"
[DEBUG] Found column "user_id", data type 4, in table "relation..orders"
[DEBUG] Found column "number", data type 4, in table "relation..orders"
[DEBUG] Found column "createtime", data type 91, in table "relation..orders"
[INFO] Generating Record class for table orders
[INFO] Generating Mapper Interface for table orders
[INFO] Generating SQL Map for table orders
[INFO] Saving file OrdersMapper.xml
[INFO] Saving file Orders.java
[INFO] Saving file OrdersMapper.java
[WARNING] Existing file D:\git\springboot_learn01\mybatie-study\mybatis-relation\src\main\java\com\dx\mybatis01\model\Orders.java was overwritten
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.499 s
[INFO] Finished at: 2019-03-24T14:42:44+08:00
[INFO] Final Memory: 19M/491M
[INFO] ------------------------------------------------------------------------

刷新项目，此时可以发现项目中已经包含了新的代码生成：

一对一查询

实例：查询订单信息，关联查询创建订单的用户信息。

SELECT 
orders.*,user.username,user.gender,user.address 
FROM orders,USER 
WHERE orders.user_id = user.id

主查询表：orders
关联查询表：user
联结查询：内联结

利用ResultType

1、SQL语句的书写要领：先确定查询的主表，在确定查询的关联表，关联查询是使用内联结还是外联结。
2、POVO扩展类创建。(这是由于关联查询出来的信息是多张表的综合字段，所以我们可以根据POJO创建我们的扩展类)
3、Mapper.xml和Mapper.java的代理编写
4、进行测试

在Maven Module项目mybatis-relation下src/main/resources下创建mybatis.cfg.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="cacheEnabled" value="true"/>
    </settings>
    <typeAliases>
        <!-- <typeAlias type="com.demo.module.News" alias="News"/> -->
    </typeAliases>
    <environments default="development"> 
        <!-- 配置数据源的相关信息 -->
        <environment id="development">
            <transactionManager type="jdbc" /> <!-- 使用JDBC方式管理 -->
            <dataSource type="POOLED"> <!-- 设置数据源类型，此时为POOLED -->
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://localhost:3306/relation" />
                <!-- 设置数据库连接的用户名和密码 -->
                <property name="username" value="root" />
                <property name="password" value="root" /> 
            </dataSource> 
        </environment>
    </environments>
    <mappers>
          <mapper resource="com/dx/mybatis01/dao/OrdersExtendsMapper.xml"></mapper>
    </mappers>
</configuration>

在com.dx.mybatis01.module下创建module：

Order.java

package com.dx.mybatis01.model;

import java.util.Date;
import java.util.List;

public class Orders {
    private Integer id;

    private Integer userId;

    private Integer number;

    private Date createtime;  
    // 用户信息
    private User user;       
    //商品信息存放集合
    private List<OrdersDetail> orderdetails;   

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }

    public List<OrdersDetail> getOrderdetails() {
        return orderdetails;
    }

    public void setOrderdetails(List<OrdersDetail> orderdetails) {
        this.orderdetails = orderdetails;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public Integer getNumber() {
        return number;
    }

    public void setNumber(Integer number) {
        this.number = number;
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
}

View Code

OrdersExtends.java

package com.dx.mybatis01.model;

public class OrdersExtends extends Orders{
    private String username;
    private String gender;
    private String address;
    ......
}

在com.dx.mybatis01.dao下创建mapper和mapper.xml：

OrdersExtendsMapper.java

package com.dx.mybatis01.dao;

import java.util.List;

import com.dx.mybatis01.model.OrdersExtends;

public interface OrdersExtendsMapper {
     List<OrdersExtends>  findOrderAndUser();
}

OrdersExtendsMapper.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.dx.mybatis01.dao.OrdersExtendsMapper">
  <select id="findOrderAndUser" resultType="com.dx.mybatis01.model.OrdersExtends">
      SELECT 
          orders.*,user.username,user.gender,user.address 
      FROM orders,USER 
      WHERE orders.user_id = user.id
  </select>
</mapper>

测试类：

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.After;
import org.junit.Before;
import org.junit.Test;

import com.dx.mybatis01.dao.OrdersExtendsMapper;
import com.dx.mybatis01.model.OrdersExtends;

public class OrdersExtendsTest {
    private SqlSessionFactory sqlSessionFactory = null;
    private SqlSession session = null;

    @Before
    public void Init() throws IOException {
        // 加载总配置文件，转化为流
        String resource = "mybatis.cfg.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // 创建sqlSessionFactory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
    }

    @After
    public void destory() {
        this.session.close();
    }

    @Test
    public void test() {
        OrdersExtendsMapper ordersExtendsMapper = session.getMapper(OrdersExtendsMapper.class);
        List<OrdersExtends> list = ordersExtendsMapper.findOrderAndUser();
        for (OrdersExtends o : list) {
            // getUserid没有和数据库对应，所以无法获取默认为null
            System.out.println(o.getId() + ":" + o.getUserId() + ":" + o.getUsername() + ":" + o.getAddress());
        }
    }
}

输出：

1:null:zhangsan:北京市海淀区海淀黄庄
2:null:lili:北京市朝阳区大运村
3:null:lili:北京市朝阳区大运村

注意：上述测试代码中，由于userid没有和数据库中的字段进行对应(user_id)，所以会造成数据映射不成功而默认为null。

项目代码结构如下：

利用ResultMap

思路：利用 ResultMap 有点类似于Hibernate中POJO类中的设置。将我们对应的属性关联到类中。
1、使用resultMap将查询结果中的订单信息映射到Orders对象中，
2、在orders类中添加User属性，将关联查询出来的用户信息映射到orders对象中的user属性中。
Order.java

public class Orders {
    private Integer id;

    private Integer userId;

    private Integer number;

    private Date createtime;  
    // 用户信息
    private User user;       
    ......
}

OrderMapper和Mapper xml如下：

OrderMapper.java

package com.dx.mybatis01.dao;

import java.util.List;

import com.dx.mybatis01.model.Orders;

public interface OrdersMapper {    
    List<Orders> findOrderAndUserResultMap();
}

OrdersMapper.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.dx.mybatis01.dao.OrdersMapper">
  <!-- 订单查询关联用户的查询 -->
  <resultMap type="com.dx.mybatis01.model.Orders" id="OrderUserResultMap">
    <!-- 配置映射的订单信息 -->
    <id column="id" property="id"/>
     <result column="user_id" property="userId"/>      
    <result column="number" property="number"/>
    <result column="createtime" property="createtime"/>
    <result column="note" property="note"/>

    <!-- 配置关联的用户信息
        association:用来映射关联查询单个对象的信息
        property ：将关联信息映射到Order的哪个属性中去
        javaType 映射到那个java类中
      -->
    <association property="user" javaType="com.dx.mybatis01.model.User">
    <!-- id  关联查询用户的唯一标识 ，外键 column 指定唯一标识用户信息的字段，property表示类中属性 -->
        <id column="id" property="id"/>             
        <result column="username" property="username"/>
        <result column="gender" property="gender"/>
        <result column="address" property="address"/>
    </association>
  </resultMap>

  <select id="findOrderAndUserResultMap" resultMap="OrderUserResultMap">
      SELECT orders.*,user.username,user.gender,user.address 
      FROM orders,USER 
      WHERE orders.user_id = user.id
  </select>
</mapper>

测试类：

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.After;
import org.junit.Before;
import org.junit.Test;

import com.dx.mybatis01.dao.OrdersMapper;
import com.dx.mybatis01.model.Orders;

public class OrdersMapperTest {
    private SqlSessionFactory sqlSessionFactory = null;
    private SqlSession session = null;

    @Before
    public void Init() throws IOException {
        // 加载总配置文件，转化为流
        String resource = "mybatis.cfg.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // 创建sqlSessionFactory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
    }

    @After
    public void destory() {
        this.session.close();
    }

    @Test
    public void test() {
        OrdersMapper ordersExtendsMapper = session.getMapper(OrdersMapper.class);
        List<Orders> list = ordersExtendsMapper.findOrderAndUserResultMap();
        for (Orders o : list) {
            System.out.println(o.getId() + ":" + o.getUserId() + ":" + o.getUser().getUsername() + ":" + o.getUser().getAddress());
        }
    }
}

测试打印信息：

1:1:zhangsan:北京市海淀区海淀黄庄
2:2:lili:北京市朝阳区大运村
3:2:lili:北京市朝阳区大运村

说明：
利用 ResultMap，我们可以利用其中的association 属性将外键关联的相关类进行映射。这也是使用ResultMap 的便利之一。

ResultType和ResultMap实现一对一查询比较
1、resultType：使用resultType实现较为简单，如果pojo中没有包括查询出来的列名，需要增加列名对应的属性，即可完成映射。如果没有查询结果的特殊要求建议使用resultType。
2、resultMap：需要单独定义resultMap，实现有点麻烦，如果对查询结果有特殊的要求，使用resultMap可以完成将关联查询映射pojo的属性中。
3、resultMap可以实现延迟加载，resultType无法实现延迟加载。

一对多查询

实例：查询订单及订单明细的信息。

        SELECT
            t10.id,t10.user_id,t10.number,t10.createtime,
            t11.username,t11.gender,t11.address,
            t12.ordersdetail_id,t12.items_id,t12.items_num,t12.orders_id
        FROM orders t10
        inner join USER t11 on t10.user_id=t11.id
        left outer join ordersdetail t12 on t10.id=t12.orders_id

主查询表：orders
确定关联查询表：orderdetail
联结查询：内联结

利用resultType将上边的查询结果映射到pojo中，订单信息的就是重复。而我们对于对orders映射不能出现重复记录。所以我们这里只能利用ResultMap。

利用ResultMap

POJO类

// POJO类
    public class User {
        private int id;
        private String username;
        private Date birthday;
        private String address;
        private String gender;
    }



    public class Orders {

        private User user;

        private int id;
        private int userId;
        private String number;
        private Date createtime;
        private String note;

        private List<OrdersDetail> orderdetails;  //  一对多关系设置

    }

    public class OrdersDetail {
        private int id;
        private int ordersId;
        private int itemsId;
        private int itemsNum;

    }

OrdersMapper.java

package com.dx.mybatis01.dao;

import java.util.List;

import com.dx.mybatis01.model.Orders;

public interface OrdersMapper {
    // 查询订单(关联用户)订单明细
    public List<Orders> findOrderAndDetailResultMap();
}

OrdersMapper.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.dx.mybatis01.dao.OrdersMapper">
    <resultMap type="com.dx.mybatis01.model.Orders" id="OrderAndDetailResultMap">
        <!-- 配置映射的订单信息 -->
        <id column="id" property="id" />
        <result column="user_id" property="userId" />
        <result column="number" property="number" />
        <result column="createtime" property="createtime" />

        <!-- 配置关联的用户信息 association:用来映射关联查询单个对象的信息 一对多的逆向使用 property ：将关联信息映射到Order的哪个属性中去 
            javaType 映射到那个java类中 -->
        <association property="user" javaType="com.dx.mybatis01.model.User">
            <!--  id 关联查询用户的唯一标识 ，外键 column 指定唯一标识用户信息的列  -->
            <id column="id" property="id" />
            <result column="username" property="username" />
            <result column="gender" property="gender" />
            <result column="address" property="address" />
        </association>

        <!-- 订单明细信息 一个订单包含多条明细，要使用collection进行映射 一对多使用 配置在一的一方添加属性 collection：对关联查询的多条记录映射到集合对象中去 
            property: 将关联查询出来的多条记录映射到类中的那个属性中 list ofType : 指定映射到集合属性中的pojo类，list的泛型 -->
        <collection property="orderdetails" ofType="com.dx.mybatis01.model.OrdersDetail">
            <!-- id 订单明细唯一标识 -->
            <id column="ordersdetail_id" property="id" />
            <result column="items_id" property="itemsId" />
            <result column="items_num" property="itemsNum" />
            <result column="orders_id" property="ordersId" />
        </collection>
    </resultMap>

    <select id="findOrderAndDetailResultMap" resultMap="OrderAndDetailResultMap">
        SELECT
            t10.id,t10.user_id,t10.number,t10.createtime,
            t11.username,t11.gender,t11.address,
            t12.ordersdetail_id,t12.items_id,t12.items_num,t12.orders_id
        FROM orders  t10
        inner join USER t11 on t10.user_id=t11.id
        left outer join ordersdetail t12 on t10.id=t12.orders_id
    </select>
</mapper>

或者

<?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.dx.mybatis01.dao.OrdersMapper">
    <resultMap id="BaseResultMap" type="com.dx.mybatis01.model.Orders">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_id" jdbcType="INTEGER" property="userId" />
        <result column="number" jdbcType="INTEGER" property="number" />
        <result column="createtime" jdbcType="DATE" property="createtime" />
    </resultMap>
    <sql id="Base_Column_List">
        id, user_id, number, createtime
    </sql>
    <!-- 订单查询关联用户的查询 -->
    <resultMap type="com.dx.mybatis01.model.Orders" id="OrderUserResultMap"
        extends="BaseResultMap">
        <!-- 配置关联的用户信息 association:用来映射关联查询单个对象的信息 property ：将关联信息映射到Order的哪个属性中去 
            javaType 映射到那个java类中 -->
        <association property="user" javaType="com.dx.mybatis01.model.User">
            <!-- id 关联查询用户的唯一标识 ，外键 column 指定唯一标识用户信息的字段，property表示类中属性 -->
            <id column="id" property="id" />
            <result column="username" property="username" />
            <result column="gender" property="gender" />
            <result column="address" property="address" />
        </association>
    </resultMap>

    <resultMap type="com.dx.mybatis01.model.Orders" id="OrderAndDetailResultMap"
        extends="OrderUserResultMap">
        <!-- 订单明细信息 一个订单包含多条明细，要使用collection进行映射 一对多使用 配置在一的一方添加属性 collection：对关联查询的多条记录映射到集合对象中去 
            property: 将关联查询出来的多条记录映射到类中的那个属性中 list ofType : 指定映射到集合属性中的pojo类，list的泛型 -->
        <collection property="orderdetails" ofType="com.dx.mybatis01.model.OrdersDetail">
            <!-- id 订单明细唯一标识 -->
            <id column="ordersdetail_id" property="id" />
            <result column="items_id" property="itemsId" />
            <result column="items_num" property="itemsNum" />
            <result column="orders_id" property="ordersId" />
        </collection>
    </resultMap>

    <select id="findOrderAndDetailResultMap" resultMap="OrderAndDetailResultMap">
        SELECT
            t10.id,t10.user_id,t10.number,t10.createtime,
            t11.username,t11.gender,t11.address,
            t12.ordersdetail_id,t12.items_id,t12.items_num,t12.orders_id
        FROM orders t10
        inner join USER t11 on t10.user_id=t11.id
        left outer join ordersdetail t12 on t10.id=t12.orders_id
    </select>
</mapper>

测试代码：

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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.After;
import org.junit.Before;
import org.junit.Test;

import com.dx.mybatis01.dao.OrdersMapper;
import com.dx.mybatis01.model.Orders;
import com.dx.mybatis01.model.OrdersDetail;

public class OrdersMapperTest {
    private SqlSessionFactory sqlSessionFactory = null;
    private SqlSession session = null;

    @Before
    public void Init() throws IOException {
        // 加载总配置文件，转化为流
        String resource = "mybatis.cfg.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);

        // 创建sqlSessionFactory
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        session = sqlSessionFactory.openSession();
    }

    @After
    public void destory() {
        this.session.close();
    }

    @Test
    public void test() {
        OrdersMapper ordersExtendsMapper = session.getMapper(OrdersMapper.class);
        List<Orders> list = ordersExtendsMapper.findOrderAndDetailResultMap();
        for (Orders o : list) {
            System.out.println(o.getId() + ":" + o.getUserId() + ":" + o.getUser().getUsername() + ":"
                    + o.getUser().getAddress() + ":o.getOrderdetails().size()=" + o.getOrderdetails().size());
            for (OrdersDetail od : o.getOrderdetails()) {
                System.out.println("------" + od.getId() + ":" + od.getItemsId() + ":" + od.getItemsNum()
                        + ":" + od.getOrdersId());
            }
        }
    }
}

添加log4j.properties配置，设置打印执行sql，在Maven Module项目mybatis-relation中src/main/resources下添加log4j.properties

log4j.rootLogger=debug,stdout,logfile
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout

log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.Java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

打印执行结果：

log4j:ERROR Could not find value for key log4j.appender.logfile
log4j:ERROR Could not instantiate appender named "logfile".
DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - PooledDataSource forcefully closed/removed all connections.
DEBUG - Opening JDBC Connection
Sun Mar 24 17:10:10 CST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
DEBUG - Created connection 1295083508.
DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4d3167f4]
DEBUG - ==>  Preparing: 

SELECT 
　　t10.id,t10.user_id,t10.number,t10.createtime, t11.username,t11.gender,t11.address, t12.ordersdetail_id,t12.items_id,t12.items_num,t12.orders_id 
FROM orders t10 inner 
join USER t11 on t10.user_id=t11.id 
left outer join ordersdetail t12 on t10.id=t12.orders_id 

DEBUG - ==> Parameters: 
DEBUG - <==      Total: 5
1:1:zhangsan:北京市海淀区海淀黄庄:o.getOrderdetails().size()=2
------1:1:1:1
------2:2:1:1
2:2:lili:北京市朝阳区大运村:o.getOrderdetails().size()=1
------3:1:1:2
3:2:lili:北京市朝阳区大运村:o.getOrderdetails().size()=2
------4:2:1:3
------5:3:1:3
DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4d3167f4]
DEBUG - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4d3167f4]
DEBUG - Returned connection 1295083508 to pool.

注意：

对于一对多或者多对一的查询，建议使用ResultMap映射进行代码编写，
mybatis使用resultMap的collection对关联查询的多条记录映射到一个list集合属性中。

使用resultType实现：
将订单明细映射到orders中的orderdetails中，需要自己处理，使用双重循环遍历，去掉重复记录，将订单明细放在orderdetails中。
特别注意：

如果上边ordersdetail的唯一键数据库列名字为id，则会导致order关联出来ordersdetail记录只为一条记录。

多对多查询

实例：查询用户及用户购买商品信息。
查询主表是：user
关联表：由于用户和商品没有直接关联，通过订单和订单明细进行关联，所以关联表：
orders、orderdetail、items
联结查询：内联结

利用ResultMap

POJO类

    public class User {
        private int id;
        private String username;
        private Date birthday;
        private String address;
        private String sex;
        private List<Orders> orderslist;    //一对多
    }

    public class Orders {
        private User user;      // 用户信息
        private int id;
        private int userId;
        private String number;
        private Date createtime;
        private String note;
        private List<OrdersDetail> orderdetails;    //商品信息存放集合
    }

    public class OrdersDetail {
        private int id;
        private int ordersId;
        private int itemsId;
        private int itemsNum;
        private Items items;    //商品信息
    }

    public class Items {
        private int id;
        private String name;
        private double price;
        private String detail;
        private String pic;
        private Date createtime;
    }

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.dx.mybatis01.dao.UserMapper">   
    <!-- 用户及用户购买的商品明细 -->
    <resultMap type="com.dx.mybatis01.model.User" id="UserAndItemResultMap">
        <!-- 映射用户信息 -->
        <id column="user_id" property="id" />
        <result column="username" property="username" />
        <result column="gender" property="gender" />
        <result column="address" property="address" />

        <!-- 订单信息 一个用户创建多个订单 使用collection -->
        <collection property="orderslist" ofType="com.dx.mybatis01.model.Orders">
            <id column="id" property="id" />
            <result column="user_id" property="userId" />
            <result column="number" property="number" />
            <result column="createtime" property="createtime" />

            <!-- 订单明细 一个订单包含多个订单明细 所以写在订单信息里面 -->

            <collection property="orderdetails" ofType="com.wf.model.OrdersDetail">
                <!-- id 订单明细唯一标识 -->
                <id column="orderdetail_id" property="id" />
                <result column="items_id" property="itemsId" />
                <result column="items_num" property="itemsNum" />
                <result column="orders_id" property="ordersId" />

                <!-- 商品信息 一个订单明细对应一个商品 -->
                <association property="items" javaType="com.wf.model.Items">
                    <id column="items_id" property="id" />
                    <result column="items_name" property="name" />
                    <result column="item_price" property="price" />
                    <result column="item_datail" property="detail" />
                </association>
            </collection>
        </collection>
    </resultMap>
    <select id="findUserAndItemResultMap" resultMap="UserAndItemResultMap">
        select
         t10.*,t11.*,t12.*,t13.*
        from user t10
        left outer join orders t11 on t10.user_id=t11.id
        left outer join ordersdetail t12 on t11.id=t12.order_id
        left outer join items t13 on t12.items_id=t13.items_id
        where id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

注意：

这里是把user的id修改为了user_id，否则也会造成上边提到的错误，导致关联出来订单不是多条只能是一条。

参考：

https://blog.csdn.net/sinat_28978689/article/details/74999738

posted @ 2019-03-22 17:55 cctext 阅读(14232) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

yy

基础才是编程人员应该深入研究的问题，警告自己问题解决不了时，多从运行原理底层研究后再考虑方案。

MyBatis(五)：mybatis关联映射

创建数据Demo表

数据库表：

数据模型：

数据表之间的关系：

创建Demo表

配置mybatis-generator插件，生成mapper、mapper.xml、model代码:

一对一查询

利用ResultType

利用ResultMap

一对多查询

利用ResultMap

多对多查询

利用ResultMap

公告