mybatis 关联sql查询

来源：互联网发布：新理念外语网络教学app 编辑：程序博客网时间：2024/06/05 17:56

mybatis关联sql语句
一，有时候我们定义的实体类和数据库里面的字段名不一致的时候，我们就获取不到数据库里面的值
   解决这种问题，我们有两种解决方式，在xml配置文件中
   1.运用别名
       <select id="getCourseById" parameterType="int" resultType="Course">
       select c.*,c.courseCode Code,c.courseName Name from course c where id=#{id}
       </select>
   2.mybatis提供了一个resultMap来对结果集进行映射
       
   <resultMap type="Course" id="CourseMap">
       
       <id column="id" property="id"/>
       
       <result column="courseCode" property="Code"/>
       <result column="courseName" property="Name"/>

   </resultMap>

   
   <select id="getCourseById" parameterType="int" resultMap="CourseMap">
       select * from course where id=#{id}
   </select>

二，如果我们有以下需求，通过学生表查老师表
   Teacher实体类
   private int id;
   private String name;
   private String gender;
   private String researchArea;
   private String title;

   Student实体类
   private int id;
   private String name;
   private String gender;
   private String major;
   private String grade;
   //private int supervisorId;//指导老师的编号
   private Teacher teacher;//表示一条teacher记录，在实体类中对应一个代表实体对象

数据库
CREATE TABLE student (
id number(11) primary key,
name varchar2(10) NOT NULL,
gender char(2) NOT NULL,
major varchar2(20) NOT NULL,
grade char(4) NOT NULL,
supervisorId number(11) DEFAULT NULL
);
CREATE TABLE teacher (
id number(11) primary key,
name varchar2(10) NOT NULL,
gender char(2) NOT NULL,
researchArea varchar2(20) NOT NULL,
title varchar2(6) NOT NULL
);
   第一种方式：嵌套结果方式
   1. resultType查询出来的值，不能直接注给实体类对象，在查询的时候只能查询到学生的信息，但是不能查询到老师的信息
   <select id="getStudentById" parameterType="int" resultType="Student">
       select * from student s join teacher t on s.id=#{id} and s.supervisorId = t.id
   </select>

   2.所以我们就要使用第二种方式，也是resultMap，这种方式查出来的结果，老师的id，name，gender和学生的id ，name，gender的结果相同
      所以还得进一步改善
   <resultMap type="Student" id="StudentMap">
       <id column="id" property="id"/>
       <result column="name" property="name"/>
       <result column="gender" property="gender"/>
       <result column="major" property="major"/>
       <result column="grade" property="grade"/>
       
       
       <association property="teacher" column="supervisorId" javaType="Teacher">
           <id column="id" property="id"/>
           <result column="name" property="name"/>
           <result column="gender" property="gender"/>
           <result column="researchArea" property="researchArea"/>
           <result column="title" property="title"/>
       </association>

   </resultMap>
   
   <select id="getStudentById" parameterType="int" resultMap="StudentMap">
       select * from student s join teacher t on s.id=#{id} and s.supervisorId =
   </select>

   3.出现上面这种问题，是因为数据库中的两张表的字段是一样的，下面这样定义就可以解决这个问题
   <resultMap type="Student" id="StudentMap">
       <id column="id" property="id"/>
       <result column="name" property="name"/>
       <result column="gender" property="gender"/>
       <result column="major" property="major"/>
       <result column="grade" property="grade"/>
       
       
       <association property="teacher" column="supervisorId" javaType="Teacher">
           <id column="tid" property="id"/>
           <result column="tname" property="name"/>
           <result column="tgender" property="gender"/>
           <result column="researchArea" property="researchArea"/>
           <result column="title" property="title"/>
       </association>

   </resultMap>
   
   <select id="getStudentById" parameterType="int" resultMap="StudentMap">
       select s.*,t.id tid,t.tname tname,t.gender tgender,t.* from student s join teacher t on s.id=#{id} and s.supervisorId =
   </select>

   那么可以把查询语句调换位置么
   <select id="getStudentById" parameterType="int" resultMap="StudentMap">
       select t.*,t.id tid,t.tname tname,t.gender tgender,s.* from student s join teacher t on s.id=#{id} and s.supervisorId =
   </select>

   答案是不可以的   因为这是根据sql语句顺序执行的


   第二种方式：嵌套查询方式
   <resultMap type="Student" id="StudentMap">
   <association property="teacher" column="supervisorId" select="getTeacherById" />
   </resultMap>
   <select id="getTeacherById" parameterType="int" resultType="Teacher">
       select * from teacher where id =#{supervisorId}
   </select>
   <select id="getStudentById" parameterType="int" resultMap="StudentMap">
       select * from student where id =#{id}
   </select>

1 0