[ 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();}
}