mybatis-03-一对多关系映射(附源码)

• sb_mybatis

 

/*
Navicat MySQL Data Transfer

Source Server         : 阿里云
Source Server Version : 50724
Source Host           : youxiu326.xin:3306
Source Database       : mybatis

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

Date: 2019-01-26 19:25:25
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for group_
-- ----------------------------
DROP TABLE IF EXISTS `group_`;
CREATE TABLE `group_` (
  `group_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(254) NOT NULL DEFAULT '',
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of group_
-- ----------------------------
INSERT INTO `group_` VALUES ('1', 'Group-1');
INSERT INTO `group_` VALUES ('2', 'Group-2');

-- ----------------------------
-- Table structure for post
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(254) NOT NULL DEFAULT '',
  `content` varchar(256) DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO `post` VALUES ('1', '1', 'MyBatis关联数据查询', '在实际项目中，经常使用关联表的查询，比如：多对一，一对多等。这些查询是如何处理的呢，这一讲就讲这个问题。我们首先创建一个 post 表，并初始化数据.', '2015-09-23 21:40:17');
INSERT INTO `post` VALUES ('2', '1', 'MyBatis开发环境搭建', '为了方便学习，这里直接建立java 工程，但一般都是开发 Web 项目。', '2015-09-23 21:42:14');
INSERT INTO `post` VALUES ('3', '2', '这个是别人发的', 'content,内容...', '1998-05-05 00:00:00');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'Jone', '18', 'test1@baomidou.com');
INSERT INTO `student` VALUES ('2', 'Jack', '20', 'test2@baomidou.com');
INSERT INTO `student` VALUES ('3', 'Tom', '28', 'test3@baomidou.com');
INSERT INTO `student` VALUES ('4', 'Sandy', '21', 'test4@baomidou.com');
INSERT INTO `student` VALUES ('5', 'Billie', '24', 'test5@baomidou.com');
INSERT INTO `student` VALUES ('6', null, '24', 'test6@qq.com');
INSERT INTO `student` VALUES ('7', 'TomT', '15', 'test7@baomidou.com');

-- ----------------------------
-- Table structure for student_group
-- ----------------------------
DROP TABLE IF EXISTS `student_group`;
CREATE TABLE `student_group` (
  `student_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student_group
-- ----------------------------
INSERT INTO `student_group` VALUES ('1', '1');
INSERT INTO `student_group` VALUES ('2', '1');
INSERT INTO `student_group` VALUES ('1', '2');

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL DEFAULT '',
  `mobile` int(10) unsigned NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'yiibai', '100', '2015-09-23 20:11:23');

 

<?xml version="1.0" encoding="UTF-8"?>
<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>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.20.BUILD-SNAPSHOT</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.huarui</groupId>
    <artifactId>sb_mybatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sb_mybatis</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.3</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginRepository>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
    </pluginRepositories>

</project>

 

server.port=8080

#mysql
spring.datasource.url=jdbc:mysql://39.108.85.204:3306/mybatis?useUnicode=true&characterEncoding=utf8
spring.datasource.username=youxiu326
spring.datasource.password=zz123456.ZZ
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

#mybatis xml文件扫描
mybatis.mapper-locations=classpath:/mapper/*.xml

 

使用插件生成entity 与 mapper xml 文件后，插件使用参考: https://www.cnblogs.com/youxiu326/p/mybatis-02.html

 步入正题

 

• collection 一对多(在collection 定义返回列)

// 帖子类
public class Post {
    private Integer postId;

    private Integer userId;

    private String title;

    private String content;

    private Date created;
}

// 用户类 (一用户多封帖子)
public class User {
    private Integer id;

    private String username;

    private Integer mobile;

    private Date created;

    private List posts;
}

 

 <resultMap type="com.huarui.entity.User" id="resultUserMap">
    <result property="id" column="id" />
    <result property="username" column="username" />
    <result property="mobile" column="mobile" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />
    <collection property="posts" ofType="com.huarui.entity.Post" column="user_id">
      <id property="postId" column="post_id" javaType="int" jdbcType="INTEGER"/>
      <result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
      <result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
      <result column="created" property="created" jdbcType="TIMESTAMP" />
    </collection>
  </resultMap>

  <select id="getUser" resultMap="resultUserMap" parameterType="java.lang.Integer">
  SELECT u.*,p.*
  FROM user u, post p
  WHERE u.id=p.user_id AND id=#{user_id}
  </select>

public interface UserMapper {
    User getUser(Integer id);
}

 

• collection一对多(在collection 关联查询 select)

<select id="getPostsByUserId" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select
    <include refid="Base_Column_List" />
    from post
    where user_id = #{userId,jdbcType=INTEGER}
  </select>

<resultMap type="com.huarui.entity.User" id="resultUserMap2">
    <result property="id" column="id" />
    <result property="username" column="username" />
    <result property="mobile" column="mobile" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />
    <!-- column 为字表中关联外键列 post表中关联user中的id列-->
    <collection property="posts" ofType="com.huarui.entity.Post" column="id"
                select="com.huarui.dao.PostMapper.getPostsByUserId">
    </collection>
  </resultMap>


  <select id="getUser2"  resultMap="resultUserMap2" parameterType="java.lang.Integer">
    select
    <include refid="Base_Column_List" />
    from user
    where id=#{id}
  </select>

public interface UserMapper {
    User getUser(Integer id);
    User getUser2(Integer id);
}

 

• 　　至此　　getUser()　　getUser2()  都可以查询user时关联查询出posts 

 

　　github:　https://github.com/youxiu326/sb_mybatis.git