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>