← 返回学习路线

SYSTEM

多表查询

Java基础多表查询system
src/content/notes/system/多表查询.md

多表查询

一对一

一对一实现:

  • 数据准备

    CREATE TABLE person(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(20),
    	age INT
    );
    INSERT INTO person VALUES (NULL,'张三',23),(NULL,'李四',24),(NULL,'王五',25);
    
    CREATE TABLE card(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	number VARCHAR(30),
    	pid INT,
    	CONSTRAINT cp_fk FOREIGN KEY (pid) REFERENCES person(id)
    );
    INSERT INTO card VALUES (NULL,'12345',1),(NULL,'23456',2),(NULL,'34567',3);
  • bean 类

    public class Card {
        private Integer id;     //主键id
        private String number;  //身份证号
        private Person p;       //所属人的对象
        ......
    }
    
    public class Person {
        private Integer id;     //主键id
        private String name;    //人的姓名
        private Integer age;    //人的年龄
    }
  • 配置文件 OneToOneMapper.xml,MyBatisConfig.xml 需要引入(可以把 bean 包下起别名)

    <?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="OneToOneMapper">
    
        <!--配置字段和实体对象属性的映射关系-->
        <resultMap id="oneToOne" type="card">
           	<!--column 表中字段名称,property 实体对象变量名称-->
            <id column="cid" property="id" />
            <result column="number" property="number" />
            <!--
                association:配置被包含对象的映射关系
                property:被包含对象的变量名
                javaType:被包含对象的数据类型
            -->
            <association property="p" javaType="bean.Person">
                <id column="pid" property="id" />
                <result column="name" property="name" />
                <result column="age" property="age" />
            </association>
        </resultMap>
    
        <select id="selectAll" resultMap="oneToOne"> <!--SQL-->
            SELECT c.id cid,number,pid,NAME,age FROM card c,person p WHERE c.pid=p.id
        </select>
    </mapper>
  • 核心配置文件 MyBatisConfig.xml

    <!-- mappers引入映射配置文件 -->
    <mappers>
        <mapper resource="one_to_one/OneToOneMapper.xml"/>
        <mapper resource="one_to_many/OneToManyMapper.xml"/>
        <mapper resource="many_to_many/ManyToManyMapper.xml"/>
    </mappers>
  • 测试类

    public class Test01 {
        @Test
        public void selectAll() throws Exception{
            //1.加载核心配置文件
            InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");
    
            //2.获取SqlSession工厂对象
            SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(is);
    
            //3.通过工厂对象获取SqlSession对象
            SqlSession sqlSession = ssf.openSession(true);
    
            //4.获取OneToOneMapper接口的实现类对象
            OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);
    
            //5.调用实现类的方法,接收结果
            List<Card> list = mapper.selectAll();
    
            //6.处理结果
            for (Card c : list) {
                System.out.println(c);
            }
    
            //7.释放资源
            sqlSession.close();
            is.close();
        }
    }

一对多

一对多实现:

  • 数据准备

    CREATE TABLE classes(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(20)
    );
    INSERT INTO classes VALUES (NULL,'程序一班'),(NULL,'程序二班')
    
    CREATE TABLE student(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(30),
    	age INT,
    	cid INT,
    	CONSTRAINT cs_fk FOREIGN KEY (cid) REFERENCES classes(id)
    );
    INSERT INTO student VALUES (NULL,'张三',23,1),(NULL,'李四',24,1),(NULL,'王五',25,2);
  • bean 类

    public class Classes {
        private Integer id;     //主键id
        private String name;    //班级名称
        private List<Student> students; //班级中所有学生对象
        ........
    }
    public class Student {
        private Integer id;     //主键id
        private String name;    //学生姓名
        private Integer age;    //学生年龄
    }
  • 映射配置文件

    <mapper namespace="OneToManyMapper">
        <resultMap id="oneToMany" type="bean.Classes">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
    
            <!--collection:配置被包含的集合对象映射关系-->
            <collection property="students" ofType="bean.Student">
                <id column="sid" property="id"/>
                <result column="sname" property="name"/>
                <result column="sage" property="age"/>
            </collection>
        </resultMap>
        <select id="selectAll" resultMap="oneToMany"> <!--SQL-->
            SELECT c.id cid,c.name cname,s.id sid,s.name sname,s.age sage FROM classes c,student s WHERE c.id=s.cid
        </select>
    </mapper>
  • 代码实现片段

    //4.获取OneToManyMapper接口的实现类对象
    OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);
    
    //5.调用实现类的方法,接收结果
    List<Classes> classes = mapper.selectAll();
    
    //6.处理结果
    for (Classes cls : classes) {
        System.out.println(cls.getId() + "," + cls.getName());
        List<Student> students = cls.getStudents();
        for (Student student : students) {
            System.out.println("\t" + student);
        }
    }

多对多

学生课程例子,中间表不需要 bean 实体类

  • 数据准备

    CREATE TABLE course(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(20)
    );
    INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');
    
    CREATE TABLE stu_cr(
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	sid INT,
    	cid INT,
    	CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),
    	CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)
    );
    INSERT INTO stu_cr VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
  • bean类

    public class Student {
        private Integer id;     //主键id
        private String name;    //学生姓名
        private Integer age;    //学生年龄
        private List<Course> courses;   // 学生所选择的课程集合
    }
    public class Course {
        private Integer id;     //主键id
        private String name;    //课程名称
    }
  • 配置文件

    <mapper namespace="ManyToManyMapper">
        <resultMap id="manyToMany" type="Bean.Student">
            <id column="sid" property="id"/>
            <result column="sname" property="name"/>
            <result column="sage" property="age"/>
    
            <collection property="courses" ofType="Bean.Course">
                <id column="cid" property="id"/>
                <result column="cname" property="name"/>
            </collection>
        </resultMap>
        <select id="selectAll" resultMap="manyToMany"> <!--SQL-->
            SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHERE sc.sid=s.id AND sc.cid=c.id
        </select>
    </mapper>

鉴别器

需求:如果查询结果是女性,则把部门信息查询出来,否则不查询 ;如果是男性,把 last_name 这一列的值赋值

<!-- 
    column:指定要判断的列名 
    javaType:列值对应的java类型
   -->
<discriminator javaType="string" column="gender">
    <!-- 女生 -->
    <!-- resultType不可缺少,也可以使用resutlMap -->
    <case value="0" resultType="com.bean.Employee">
        <association property="dept"
                     select="com.dao.DepartmentMapper.getDeptById"
                     column="d_id">
        </association>
    </case>
    <!-- 男生 -->
    <case value="1" resultType="com.bean.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
    </case>
</discriminator>