在项目中经常遇到一对多关联查询
建表:
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>
这样即使没有提供projectId也能查出所有的project