[ Mybatis 多表关联查询 ] resultMap

一. resultMap

1. 使用场景:

2. 查询映射:

(1)单表查询映射:

(2)多表查询映射:

a. 在学生表里查专业

b. 在专业表里查学生

二. 其他注意事项

1. 插件下载

2. #{ } 和 ${ }的区别

一. resultMap

1. 使用场景:

(1)当数据库列名和java类中的属性名不同时,可⽤ resultMap 配置映射 (下列代码中有举例)；

(2)在单表查询和多表查询中可以使⽤, resultMap 映射并查询数据

2. 查询映射:

(1)单表查询映射:

<?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">
<!-- 专门写SQL语句的文件--><mapper namespace="mybatispro.dao.AdminDao"><!--接口的地址--><!--resultMap标签:对数据库中的列名和java类中的属性名进行映射 常用在多表--><resultMap id="adminMap" type="Admin"><!--数据库列名(column)   java类中的属性名(property)--><id column="id" property="id"/><!--映射主键使用id标签--><result column="account" property="account"/><result column="password" property="password"/><result column="gender" property="gender"/></resultMap><select id="findAdmins" parameterType="string" resultMap="adminMap">select id,account,age as adminAge from admin order by ${col}  desc</select></mapper>

注意: 只有数据库列名和java类中的属性名相同时才会自动映射

而使用resultMap相当于手动匹配,即使名称不一样也会映射

使用as起别名让二者名称相同,也可以自动映射

(2)多表查询映射

a. 在学生表里查专业

注意看图 , 图很重要 !

在查询时,会遇到多表关联,例如在查询学生信息表时,会关联专业表(id,major,info) , 课程表(id,class,info)...当关联的数据很多时,在创建类的时候,需要定义的属性(专业id,专业major,专业info,课程id,课程class,课程info)就会很多,为了减少冗余,我们可以把每个数据表看成一个对象,这个对象里面包含了各自的属性

package mybatispro.model;public class Student {private  int id;private int num;private String name ;private String gender;private String phone;private Major major;//学生关联专业/*关联表中的数据,在设计类时,不建议把关联类中的属性,在本类中再定义一遍,减少冗余private Major major;private String mname;*/public int getId() {return id;}public void setId(int id) {this.id = id;}public int getNum() {return num;}public void setNum(int num) {this.num = num;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getGender() {return gender;}public void setGender(String gender) {this.gender = gender;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public Major getMajor() {return major;}public void setMajor(Major major) {this.major = major;}@Overridepublic String toString() {return "Student{" +"id=" + id +", num=" + num +", name='" + name + '\'' +", gender='" + gender + '\'' +", phone='" + phone + '\'' +", major=" + major +'}';}
}

package mybatispro.dao;
import mybatispro.model.Student;
import java.util.ArrayList;public interface StudentDao {Student findStudentById(int id);ArrayList<Student> findStudents();
}

<?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">
<!-- 专门写SQL语句的文件-->
<mapper namespace="mybatispro.dao.StudentDao"><!-- 多表关联查询时,需要自定义映射关系 -->
<resultMap id="studentMap" type="Student"><id property="id" column="id"/><result property="num" column="num"/><result property="name" column="name"/><result property="gender" column="gender"/><result property="phone" column="phone"/><!-- 关联表数据映射--><association property="major" javaType="Major"><!--association相当于创建一个major对象,这个对象里面封装了major的所有属性--><!-- property(java类中的属性名)  javaType(属性名对应的类型名) column(数据库列名)--><id column="mid" property="id"/><result column="mname" property="name"/></association>
</resultMap><!--查所有学生信息--><select id="findStudents" resultMap="studentMap">selects.id,s.num,s.name,s.gender,s.phone,m.id as mid ,m.name as mnamefrom student s inner join major m on s.majorid= m.id</select><!--查单个学生信息 通过id--><select id="findStudentById"  parameterType="int" resultMap="studentMap">selects.id,s.num,s.name,s.gender,s.phone,m.id as mid,m.name as mnamefrom student s inner join major m on s.majorid= m.idwhere s.id =#{id}</select>
</mapper>

package mybatispro.test;import mybatispro.dao.StudentDao;
import mybatispro.model.Student;
import mybatispro.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;import java.io.IOException;
import java.util.ArrayList;public class TestStudent {public static void main(String[] args) throws IOException {SqlSession sqlSession = MybatisUtil.getSqlSession();StudentDao studentDao =sqlSession.getMapper(StudentDao.class);//查单个学生信息Student  student = studentDao.findStudentById(3);System.out.println(student);//获取学生信息System.out.println(student.getId());System.out.println(student.getName());//获取专业信息,需要先获得专业对象System.out.println(student.getMajor().getName());System.out.println(student.getMajor().getId());/*查所有学生信息ArrayList<Student> students =  studentDao.findStudents();System.out.println(students);*/sqlSession.close();}
}

b. 在专业表里查学生

注意看图 , 图很重要 !

<?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">
<!-- 专门写SQL语句的文件-->
<mapper namespace="mybatispro.dao.MajorDao"><resultMap id="majorMap" type="Major"><id column="id" property="id"/><result column="name" property="name"/><!--用来接收多个对象,将多个对象封装到集合中--><collection property="students" javaType="arrayList" ofType="Student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="num" property="num"/></collection></resultMap><select id="findMajorById" parameterType="int"  resultMap="majorMap">selectm.id,m.name,s.id as sid,s.name as sname,s.numfrommajor minner join student son m.id = s.majoridwhere m.id = #{id}</select>
</mapper>

package mybatispro.dao;import mybatispro.model.Major;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;public interface MajorDao {Major findMajorById(int id);/*当SQL语句很简单时,就不需要写在对应的xml文件当中,直接在接口中使用@Insert等注解标签就行复杂的额SQL建议鞋子xml文件中*/@Insert("insert into major(name) value(#{name})")int insertMajor(Major major);@Delete("delete from major where id=#{id}")int deleteMajor(int id);}

package mybatispro.model;import java.util.ArrayList;public class Major {private int id;private String name;private ArrayList<Student> students; //一个专业对应(关联)多个学生public ArrayList<Student> getStudents() {return students;}public void setStudents(ArrayList<Student> students) {this.students = students;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Major{" +"id=" + id +", name='" + name + '\'' +", students=" + students +'}';}
}

package mybatispro.test;import mybatispro.dao.MajorDao;
import mybatispro.model.Major;
import mybatispro.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;import java.io.IOException;public class TestMajor {public static void main(String[] args) throws IOException {SqlSession  sqlSession = MybatisUtil.getSqlSession();MajorDao majorDao = sqlSession.getMapper(MajorDao.class);/*  Major major = majorDao.findMajorById(1);System.out.println(major);*//*简单语句---添加Major major = new Major();major.setName("数学");majorDao.insertMajor(major);*///简单语句---删除majorDao.deleteMajor(6);sqlSession.commit();sqlSession.close();}
}