"上篇实现利用 mybatis 实现单表增删改查，今天利用 mybatis 实现多表联合查询。 1. 创建数据库 mybatis2，建立 student、class、student_class 三张表 [链接] DROP TABLE IF EXISTS `class`; CREATE TABLE `c ...."

someone1
AI专业者开发社区 1 号成员
AI开发者社区 • 0 回帖 • 1 浏览 • 5 个月前

Mybatis实现多表联合查询

上篇实现利用 mybatis 实现单表增删改查，今天利用 mybatis 实现多表联合查询。

1. 创建数据库 mybatis2，建立 student、class、student_class 三张表

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO class VALUES (1, '一班');
INSERT INTO class VALUES (2, '二班');
INSERT INTO class VALUES (3, '三班');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student  (
id int(5) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
sex varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES (1, '张三', '男');
INSERT INTO student VALUES (2, '李四', '女');
INSERT INTO student VALUES (3, '王五', '男');
INSERT INTO student VALUES (4, '王麻子', '女');
INSERT INTO student VALUES (5, ' 赵六', '男');
-- ----------------------------
-- Table structure for student_class
-- ----------------------------
DROP TABLE IF EXISTS student_class;
CREATE TABLE student_class  (
sid int(11) NOT NULL,
cid int(11) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of student_class
-- ----------------------------
INSERT INTO student_class VALUES (1, 2);
INSERT INTO student_class VALUES (2, 2);
INSERT INTO student_class VALUES (4, 4);
INSERT INTO student_class VALUES (3, 3);
INSERT INTO student_class VALUES (5, 3);
SET FOREIGN_KEY_CHECKS = 1;

2. 在 com.domain 包下创建实体类

package com.domain;

public class Student {
    private int id;
    private String name;
    private String sex;
</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">学生所在的班级</span>
<span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Class sClass;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">getter/setter/toString【已省略】</span>


}

package com.domain;
import java.util.List;
public class Class {
private int class_id;
private String class_name;
</span><span style="color: rgba(0, 0, 255, 1)">private</span> List&lt;Student&gt;<span style="color: rgba(0, 0, 0, 1)"> students;

</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">getter/setter/toString</span>

 
}

package com.domain;
/**


@author admin


@version 1.0.0


@ClassName Student_Class.java


@Description TODO


@createTime 2020 年 01 月 15 日 16:13:00
*/
public class Student_Class {
private int sid;
private int cid;
//getter/setter/toString



}

package com.domain;
/**


@author admin


@version 1.0.0


@ClassName StudentClass.java


@Description TODO


@createTime 2020 年 01 月 15 日 18:10:00
*/
public class StudentClass {
private int id;
private String name;
private String sex;
private int class_id;
private String class_name;
//getter/setter/toString



}

3. 在 com.dao 下创建接口 StudentDao

package com.dao;
import com.domain.Class;
import com.domain.Student;
import com.domain.StudentClass;
import java.util.List;
/**


@author admin


@version 1.0.0


@ClassName StudentDao.java


@Description TODO


@createTime 2020 年 01 月 15 日 16:20:00
*/
public interface StudentDao {
//获取某学生的学号、姓名以及某学生所在的班级名  一对一关联查询  一个学生对应一个班级
List<Student> findByName(String studentName);
List<StudentClass> findByName2(String studentName);
//获取指定班级下的所有学生 【班级编号、班级名、学生姓名、性别】 一对多关联查询 一个班级对应多个学生
List<Class> findAllStudent(int cid);
}

4. 创建主约束文件 SqlMapConfig.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">
<!-- mybatis 的主配置文件 -->
<configuration>
    <!-- 配置环境 -->
    <environments default="mysql">
        <!-- 配置 mysql 的环境-->
        <environment id="mysql">
            <!-- 配置事务的类型-->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源（连接池） -->
            <dataSource type="POOLED">
                <!-- 配置连接数据库的 4 个基本信息 -->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis2?useUnicode=true&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 配置映射文件的位置 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">mappers</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">package </span><span style="color: rgba(255, 0, 0, 1)">name</span><span style="color: rgba(0, 0, 255, 1)">="com.dao"</span><span style="color: rgba(0, 0, 255, 1)">&gt;&lt;/</span><span style="color: rgba(128, 0, 0, 1)">package</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">mappers</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

</configuration>

5. 在 com.dao 下创建子约束文件 studentDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--mybaits 头约束 -->
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--配置约束-->
<mapper namespace="com.dao.StudentDao">
<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">1.获取学生编号、姓名、对应的班级名称</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> 可以显示指出列名，如果用"*"代替，则mybatis会自动匹配resultMap中提供的列名 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="findByName"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="String"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="studentClassMap"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
    select a.id,a.name,b.class_name from student a,class b,
    student_class c where a.id=c.sid and b.class_id = c.cid and a.name=#{name}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">resultMap中的type表示返回什么类型的对象</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="studentClassMap"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="com.domain.Student"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">主键字段</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">property表示com.domain.Student的字段,coloum为表中的字段，进行配置映射</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">非主键字段</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">association字面意思关联，这里只专门做一对一关联； property表示是com.domain.Student的属性名称
    javaType表示该属性是什么类型对象</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">association </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="sClass"</span><span style="color: rgba(255, 0, 0, 1)"> javaType</span><span style="color: rgba(0, 0, 255, 1)">="com.domain.Class"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
        <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> property 表示com.domain.Class中的属性； column 表示表中的列名 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="class_name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="class_name"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">association</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)">第二种方式，新建一个类，类似于视图</span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="findByName2"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="String"</span><span style="color: rgba(255, 0, 0, 1)"> resultType</span><span style="color: rgba(0, 0, 255, 1)">="com.domain.StudentClass"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
     select * from student a,class b,
    student_class c where a.id=c.sid and b.class_id = c.cid and a.name=#{name}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">select </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="findAllStudent"</span><span style="color: rgba(255, 0, 0, 1)"> parameterType</span><span style="color: rgba(0, 0, 255, 1)">="int"</span><span style="color: rgba(255, 0, 0, 1)"> resultMap</span><span style="color: rgba(0, 0, 255, 1)">="getClassStudent"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span><span style="color: rgba(0, 0, 0, 1)">
  select * from class a left join student_class b on a.class_id=b.cid
  left join student c on b.sid = c.id where a.class_id = #{class_id}
</span><span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">select</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

<span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">resultMap </span><span style="color: rgba(255, 0, 0, 1)">id</span><span style="color: rgba(0, 0, 255, 1)">="getClassStudent"</span><span style="color: rgba(255, 0, 0, 1)"> type</span><span style="color: rgba(0, 0, 255, 1)">="com.domain.Class"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="class_id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="class_id"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="class_name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="class_name"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 128, 0, 1)">&lt;!--</span><span style="color: rgba(0, 128, 0, 1)"> property表示集合类型属性名称，ofType表示集合中的对象是什么类型 </span><span style="color: rgba(0, 128, 0, 1)">--&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">collection </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="students"</span><span style="color: rgba(255, 0, 0, 1)"> ofType</span><span style="color: rgba(0, 0, 255, 1)">="com.domain.Student"</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">id </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="id"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="name"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
        <span style="color: rgba(0, 0, 255, 1)">&lt;</span><span style="color: rgba(128, 0, 0, 1)">result </span><span style="color: rgba(255, 0, 0, 1)">property</span><span style="color: rgba(0, 0, 255, 1)">="sex"</span><span style="color: rgba(255, 0, 0, 1)"> column</span><span style="color: rgba(0, 0, 255, 1)">="sex"</span><span style="color: rgba(0, 0, 255, 1)">/&gt;</span>
    <span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">collection</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>
<span style="color: rgba(0, 0, 255, 1)">&lt;/</span><span style="color: rgba(128, 0, 0, 1)">resultMap</span><span style="color: rgba(0, 0, 255, 1)">&gt;</span>

</mapper>

6. 编写测试类 test

package com.dao;
import com.domain.Class;
import com.domain.Student;
import com.domain.StudentClass;
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 java.io.InputStream;
import java.util.List;
/**


@author admin


@version 1.0.0


@ClassName test2.java


@Description TODO


@createTime 2020 年 01 月 11 日 23:22:00
*/
public class test2 {
private InputStream in;
private SqlSession sqlSession;
private StudentDao studentDao;
@Before//用于在测试方法执行之前执行
public void init() throws Exception {
//1. 读取配置文件，生成字节输入流
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. 获取 SqlSessionFactory 工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//3. 获取 SqlSession 对象
sqlSession = factory.openSession();
//4. 获取 dao 的代理对象
studentDao = sqlSession.getMapper(StudentDao.class);
}
@After//用于在测试方法执行之后执行
public void destroy() throws Exception {
//提交事务
        sqlSession.commit();
//6. 释放资源
        sqlSession.close();
in.close();
}
@Test
public void findByName(){
List<Student> lists = studentDao.findByName("张三");
for(Student s:lists){
System.out.println(s.getId()+""+s.getName()+"  "+s.getsClass().getClass_name());
}
}
@Test
public void findByName2(){
List<StudentClass> lists = studentDao.findByName2("张三");
for(StudentClass s:lists){
System.out.println(s.toString());
}
}
@Test
public void findAllStudent(){
List<Class> lists = studentDao.findAllStudent(2);
for(Class c:lists){
System.out.println(c.toString());
}
}
}

7. 小结

以上实现了一对一查询与一对多查询, 有配置 resultMap 的方式也可以通过新建一个实体类的方式

mybatis 讲究一一对应映射进去，得按照人家规则来，熟练程度不如自己封装一个操作 JDBC 工具类来的快。

参考自：https://www.cnblogs.com/wucj/p/5148813.html

Mybatis实现多表联合查询

相关帖子

随便看看

Mybatis实现多表联合查询