當前位置：首頁 > 编程语言 > asp.net >内容正文

asp.net

MyBatis多表查询之XML和注解实现(resultMap结果集映射配置数据库字段与实体类属性一一映射)

發(fā)布時間：2024/4/15 asp.net 35 豆豆

生活随笔收集整理的這篇文章主要介紹了 MyBatis多表查询之XML和注解实现(resultMap结果集映射配置数据库字段与实体类属性一一映射) 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

MyBatis多表查詢

多表模型分類

一對一：在任意一方建立外鍵，關(guān)聯(lián)對方的主鍵。
一對多：在多的一方建立外鍵，關(guān)聯(lián)一的一方的主鍵。
多對多：借助中間表，中間表至少兩個字段，分別關(guān)聯(lián)兩張表的主鍵。

數(shù)據(jù)準備

sql

CREATE DATABASE db2;USE db2;-- 創(chuàng)建person表 CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT ); INSERT INTO person VALUES (NULL,'張三',23); INSERT INTO person VALUES (NULL,'李四',24); INSERT INTO person VALUES (NULL,'王五',25);-- 創(chuàng)建card表 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); INSERT INTO card VALUES (NULL,'23456',2); INSERT INTO card VALUES (NULL,'34567',3);-- 創(chuàng)建班級表 CREATE TABLE classes(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) ); INSERT INTO classes VALUES (NULL,'一班'); INSERT INTO classes VALUES (NULL,'二班');-- 創(chuàng)建學生表 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); INSERT INTO student VALUES (NULL,'李四',24,1); INSERT INTO student VALUES (NULL,'王五',25,2); INSERT INTO student VALUES (NULL,'趙六',26,2);-- 創(chuàng)建課程表 CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20) ); INSERT INTO course VALUES (NULL,'語文'); INSERT INTO course VALUES (NULL,'數(shù)學');-- 學生表與課程表的中間表 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); INSERT INTO stu_cr VALUES (NULL,1,2); INSERT INTO stu_cr VALUES (NULL,2,1); INSERT INTO stu_cr VALUES (NULL,2,2);

表之間的關(guān)系

多表操作之XML

目錄結(jié)構(gòu)

MyBatis核心配置文件以及jdbc,log4j配置文件放在src目錄下

MyBatis核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>  <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration><properties resource="jdbc.properties"/><settings><setting name="logImpl" value="log4j"/></settings><typeAliases><package name="com.fs.bean"/></typeAliases><environments default="mysql"><environment id="mysql"><transactionManager type="JDBC"></transactionManager><dataSource type="POOLED"><property name="driver" value="${driver}" /><property name="url" value="${url}" /><property name="username" value="${username}" /><property name="password" value="${password}" /></dataSource></environment></environments><mappers><mapper resource="com/fs/one_to_one/OneToOneMapper.xml"/><mapper resource="com/fs/one_to_many/OneToManyMapper.xml"/><mapper resource="com/fs/many_to_many/ManyToManyMapper.xml"/><mappers><package name="com.fs"/></mappers></mappers> </configuration>

jdbc配置文件

driver=com.mysql.jdbc.Driver url=jdbc:mysql://192.168.93.132:3306/db2 username=root password=root

log4j配置文件

# Global logging configuration # ERROR WARN INFO DEBUG log4j.rootLogger=DEBUG, stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

bean實體類

一對一的多表查詢XML

OneToOneMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.fs.table01.OneToOneMapper"><resultMap id="oneToOne" type="card"> <id column="cid" property="id" /> <result column="number" property="number" /><association property="p" javaType="person"><id column="pid" property="id" /><result column="name" property="name" /><result column="age" property="age" /></association></resultMap> <select id="selectAll" resultMap="oneToOne">SELECT c.id cid,number,pid,NAME,age FROM card c,person p WHERE c.pid=p.id</select> </mapper>

映射接口
OneToOneMapper

package com.fs.table01;import com.fs.bean.Card;import java.util.List;public interface OneToOneMapper {//查詢?nèi)?/span>public abstract List<Card> selectAll(); }

測試類

package com.fs.table01;import com.fs.bean.Card; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test;import java.io.InputStream; import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加載核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.獲取SqlSession工廠對象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通過工廠對象獲取SqlSession對象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.獲取OneToOneMapper接口的實現(xiàn)類對象OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class);//5.調(diào)用實現(xiàn)類的方法，接收結(jié)果List<Card> list = mapper.selectAll();//6.處理結(jié)果for (Card c : list) {System.out.println(c);}//7.釋放資源sqlSession.close();is.close();} }

一對一XML圖解

一對多的表查詢XML

OneToManyMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.fs.table02.OneToManyMapper"><resultMap id="oneToMany" type="classes"><id column="cid" property="id"/><result column="cname" property="name"/><collection property="students" ofType="student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/></collection></resultMap><select id="selectAll" resultMap="oneToMany">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>

OneToManyMapper

package com.fs.table02;import com.fs.bean.Classes;import java.util.List;public interface OneToManyMapper {//查詢?nèi)?/span>public abstract List<Classes> selectAll(); }

測試類

package com.fs.table02;import com.fs.bean.Classes; import com.fs.bean.Student; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test;import java.io.InputStream; import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加載核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.獲取SqlSession工廠對象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通過工廠對象獲取SqlSession對象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.獲取OneToManyMapper接口的實現(xiàn)類對象OneToManyMapper mapper = sqlSession.getMapper(OneToManyMapper.class);//5.調(diào)用實現(xiàn)類的方法，接收結(jié)果List<Classes> classes = mapper.selectAll();//6.處理結(jié)果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);}}//7.釋放資源sqlSession.close();is.close();} }

一對多XML圖解

多對多的多表查詢XML

ManyToManyMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.fs.table03.ManyToManyMapper"><resultMap id="manyToMany" type="student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/><collection property="courses" ofType="course"><id column="cid" property="id"/><result column="cname" property="name"/></collection></resultMap><resultMap id="selectCourse" type="course"><id column="cid" property="id"/><result column="cname" property="name"/><collection property="students" ofType="student"><id column="sid" property="id"/><result column="sname" property="name"/><result column="sage" property="age"/></collection></resultMap><select id="selectAll" resultMap="manyToMany">SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHEREsc.sid=s.id AND sc.cid=c.id</select><select id="selectAllCourse" resultMap="selectCourse">SELECT sc.sid,s.name sname,s.age sage,sc.cid,c.name cname FROM student s,course c,stu_cr sc WHEREsc.sid=s.id AND sc.cid=c.id</select> </mapper>

ManyToManyMapper

package com.fs.table03;import com.fs.bean.Course; import com.fs.bean.Student;import java.util.List;public interface ManyToManyMapper {//查詢?nèi)?/span>public abstract List<Student> selectAll();List<Course> selectAllCourse(); }

** 測試類 **

package com.fs.table03;import com.fs.bean.Course; import com.fs.bean.Student; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Test;import java.io.InputStream; import java.util.List;public class Test01 {@Testpublic void selectAll() throws Exception{//1.加載核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.獲取SqlSession工廠對象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通過工廠對象獲取SqlSession對象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.獲取ManyToManyMapper接口的實現(xiàn)類對象ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);//5.調(diào)用實現(xiàn)類的方法，接收結(jié)果List<Student> students = mapper.selectAll();//6.處理結(jié)果for (Student student : students) {System.out.println(student.getId() + "," + student.getName() + "," + student.getAge());List<Course> courses = student.getCourses();for (Course cours : courses) {System.out.println("\t" + cours);}}//7.釋放資源sqlSession.close();is.close();}@Testpublic void selectAllCourse() throws Exception{//1.加載核心配置文件InputStream is = Resources.getResourceAsStream("MyBatisConfig.xml");//2.獲取SqlSession工廠對象SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);//3.通過工廠對象獲取SqlSession對象SqlSession sqlSession = sqlSessionFactory.openSession(true);//4.獲取ManyToManyMapper接口的實現(xiàn)類對象ManyToManyMapper mapper = sqlSession.getMapper(ManyToManyMapper.class);List<Course> courses = mapper.selectAllCourse();//5.調(diào)用實現(xiàn)類的方法，接收結(jié)果for (Course cours : courses) {System.out.println("課程編號:"+cours.getId()+","+"課程名字:"+cours.getName());List<Student> students = cours.getStudents();for (Student student : students) {System.out.println(student);}}//7.釋放資源sqlSession.close();is.close();} }

多對多的圖解

多表操作之注解

項目結(jié)構(gòu)

由于項目結(jié)構(gòu)差不多,我這里就直接圖解,相當于配置文件刪除了,多了接口,讓MyBatis動態(tài)代理生成實現(xiàn)類,來完成操作

一對一的多表查詢注解圖解

一對多的多表查詢注解圖解

多對多的表查詢注解圖解

多表查詢小結(jié):

多表操作-XML

多表模型分類：一對一、一對多、多對多。
<resultMap>：配置字段和對象屬性的映射關(guān)系標簽。
id 屬性：唯一標識
type 屬性：實體對象類型

<id>：配置主鍵映射關(guān)系標簽。
<result>：配置非主鍵映射關(guān)系標簽。
column 屬性：表中字段名稱
property 屬性：實體對象變量名稱

<association>：配置被包含對象的映射關(guān)系標簽。
property 屬性：被包含對象的變量名
javaType 屬性：被包含對象的數(shù)據(jù)類型

<collection>：配置被包含集合對象的映射關(guān)系標簽。
property 屬性：被包含集合對象的變量名
ofType 屬性：集合中保存的對象數(shù)據(jù)類型

注解多表操作

@Results：封裝映射關(guān)系的父注解。
Result[] value()：定義了 Result 數(shù)組

@Result：封裝映射關(guān)系的子注解。
column 屬性：查詢出的表中字段名稱
property 屬性：實體對象中的屬性名稱
javaType 屬性：被包含對象的數(shù)據(jù)類型
one 屬性：一對一查詢固定屬性
many 屬性：一對多查詢固定屬性

@One：一對一查詢的注解。
select 屬性：指定調(diào)用某個接口中的方法

@Many：一對多查詢的注解。
select 屬性：指定調(diào)用某個接口中的方法

總結(jié)

以上是生活随笔為你收集整理的MyBatis多表查询之XML和注解实现(resultMap结果集映射配置数据库字段与实体类属性一一映射)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： Mybatis的CRUD之XML方式以及
下一篇： javascript基本语法(输入输出语