一对多关联查询实践-优快云博客

在项目中经常遇到一对多关联查询
建表：
project

-- 2018-1-12：一对多外键查询
CREATE TABLE `project`(
  `projectId` INT NOT NULL PRIMARY KEY,
  `projectName` VARCHAR(20) NOT NULL
)ENGINE=innoDB DEFAULT CHARSET=utf8;

files:

-- 多个文件对应一个项目
CREATE TABLE `files`(
  `fileId` INT NOT NULL PRIMARY KEY,
  `fileName` VARCHAR(20) NOT NULL,
  `projectId` INT NOT NULL,
  FOREIGN KEY (`projectId`) REFERENCES project(projectId)
)ENGINE=innoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO project(projectId, projectName) VALUE (314,'项目报建');
INSERT INTO files(fileId, fileName, projectId)
VALUES (101,'图纸1',314),(102,'图纸2',314),(103,'图纸3',314);
INSERT INTO project(projectId, projectName) VALUES (315,'项目2'),(316,'项目3');

原型类：
Files:

package com.bim.module;

/**
 * Created by 10394 on 2018/1/12.
 */
public class Files {


    private int fileId;
    private String fileName;
    private int projectId;

    public int getProjectId() {
        return projectId;
    }

    public void setProjectId(int projectId) {
        this.projectId = projectId;
    }

    public int getFileId() {
        return fileId;
    }

    public void setFileId(int fileId) {
        this.fileId = fileId;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }
}

Project:

package com.bim.module;

import java.util.List;

/**
 * Created by 10394 on 2018/1/12.
 */
public class Project {
    private int projectId;
    private String projectName;

    private List<Files> files;

    public int getProjectId() {
        return projectId;
    }

    public void setProjectId(int projectId) {
        this.projectId = projectId;
    }

    public String getProjectName() {
        return projectName;
    }

    public void setProjectName(String projectName) {
        this.projectName = projectName;
    }

    public List<Files> getFiles() {
        return files;
    }

    public void setFiles(List<Files> files) {
        this.files = files;
    }
}

FilesMappers.java

package com.bim.dao;

import com.bim.module.Files;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by 10394 on 2018/1/12.
 *
 */
@Component
public interface FilesMapper {
    List<Files> selectAllFiles();
    int insertFiles(Files files);
    List<Files> selectFilesByProId(int projectId);
}

FilesMapper.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.bim.dao.FilesMapper">
    <resultMap id="filesMap" type="com.bim.module.Files">
        <id column="fileId" property="fileId" jdbcType="INTEGER"/>
        <result column="fileName" property="fileName" jdbcType="VARCHAR"/>
        <result column="projectId" property="projectId" jdbcType="INTEGER"/>
    </resultMap>
    <sql id="base_files_list">
        fileId,fileName
    </sql>
    <select id="selectFilesByProId" parameterType="java.lang.Integer" resultType="com.bim.module.Files">
        SELECT
        <include refid="base_files_list"/>
        FROM files WHERE projectId=#{projectId}
    </select>
    <select id="selectAllFiles" resultType="com.bim.module.Files">
        SELECT
        <include refid="base_files_list"/>
        FROM files
    </select>
    <insert id="insertFiles">
        INSERT INTO files(fileId,fileName,projectId) VALUE(
        #{fileId},
        #{fileName},
        #{projectId}
        )
    </insert>
</mapper>

ProjectMapper.java

package com.bim.dao;

import com.bim.module.Project;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * Created by 10394 on 2018/1/12.
 */
@Component
public interface ProjectMapper {
    List<Project> selectAllProject();

    int insertProject(Project project);

}

ProjectMapper.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.bim.dao.ProjectMapper">
    <resultMap id="projectMap" type="com.bim.module.Project">
        <id column="projectId" property="projectId" jdbcType="INTEGER"/>
        <result column="projectName" property="projectName" jdbcType="VARCHAR"/>
        <collection property="files" ofType="com.bim.module.Files">
            <id column="fileId" property="fileId" jdbcType="INTEGER"/>
            <result column="fileName" property="fileName" jdbcType="VARCHAR"/>
            <result column="projectId" property="projectId" jdbcType="INTEGER"/>
        </collection>
    </resultMap>
    <sql id="base_project_list">
        project.projectId,project.projectName,files.fileId,files.fileName,files.projectId
    </sql>
    <select id="selectAllProject" resultMap="projectMap">
        SELECT
        <include refid="base_project_list"/>
        FROM project,files WHERE files.projectId=project.projectId
    </select>
    <insert id="insertProject" parameterType="com.bim.module.Project">
        INSERT INTO project(projectId,projectName) VALUE (
        #{projectId},
        #{projectName}
        )
    </insert>
</mapper>

方式一：

<collection property="files" ofType="com.bim.module.Files">
            <id column="fileId" property="fileId" jdbcType="INTEGER"/>
            <result column="fileName" property="fileName" jdbcType="VARCHAR"/>
            <result column="projectId" property="projectId" jdbcType="INTEGER"/>
        </collection>

查询对应

<sql id="base_project_list">
        project.projectId,project.projectName,files.fileId,files.fileName,files.projectId
    </sql>
    <select id="selectAllProject" resultMap="projectMap">
        SELECT
        <include refid="base_project_list"/>
        FROM project,files WHERE files.projectId=project.projectId
    </select>

方式二、

<collection property="files" column="projectId" select="com.bim.dao.FilesMapper.selectFilesByProId"/>

前面这两种方式都是针对提供了projectId的时候可以查询出来含有文件的项目的，当需要查询的不管是否包含文件的项目时可以采用连接的方式

<resultMap type="com.bim.module.Project" id="resultMap">
        <id column="projectId" property="projectId" jdbcType="INTEGER"/>
        <result column="projectName" property="projectName" jdbcType="VARCHAR"/>
        <collection property="files" ofType="com.bim.module.Files" column="projectId">
            <id column="fileId" property="fileId" jdbcType="INTEGER"/>
            <result column="fileName" property="fileName" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
    <sql id="base_list">
        project.projectId,project.projectName,files.fileId,files.fileName,files.projectId
    </sql>
    <select id="selectAll" resultMap="resultMap">
        SELECT
        <include refid="base_list"/>
        FROM project LEFT JOIN files ON files.projectId=project.projectId;
    </select>