Mybatis復(fù)雜查詢的實(shí)現(xiàn)
更新時(shí)間：2024年09月27日 09:43:17 作者：雪頂貓的鱷
本文主要介紹了Mybatis復(fù)雜查詢的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
一、環(huán)境搭建
1.1 建表
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, '喬丹');
INSERT INTO teacher(`id`, `name`) VALUES (2, '科比');
INSERT INTO teacher(`id`, `name`) VALUES (1, '墊腳哥');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '杜蘭特', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '德羅贊', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '詹姆斯', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '卡子哥', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '喬治', '1');
1.2 導(dǎo)入依賴
<dependencies>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.16</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
</dependencies>
1.3 創(chuàng)建實(shí)體類(lèi)
1.3.1 Student 實(shí)體類(lèi)
@Data
@NoArgsConstructor
public class Student {
private int id;
private String name;
//學(xué)生要關(guān)聯(lián)一個(gè)老師
private Teacher teacher;
}
1.3.2 Teacher 實(shí)體類(lèi)
@Data
public class Teacher {
private int id;
private String name;
}
1.4 創(chuàng)建Mapper接口
1.4.1 StudentMapper 接口
public interface StudentMapper {
}
1.4.2 TeacherMapper 接口
public interface TeacherMapper {
@Select("select *from teacher where id=#{tid}")
Teacher getTeacher(@Param("tid") int id);
}
1.5 創(chuàng)建Mapper.xml文件
1.5.1 namespace 綁定StudentMapper接口
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
</mapper>
1.5.2 namespace 綁定TeacherMapper接口
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.kuang.dao.TeacherMapper"> </mapper>
1.6 核心配置文件中注冊(cè)Mapper.xml文件
<?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="db.properties"/>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<typeAliases>
<package name="com.kuang.pojo"/>
<!-- <typeAlias type="com.kuang.pojo.User" alias="User"/>-->
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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 class="com.kuang.dao.TeacherMapper"/>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
</configuration>
1.7 測(cè)試
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
測(cè)試結(jié)果
二、多對(duì)一復(fù)雜查詢
2.1 按照查詢嵌套處理
2.1.1 編寫(xiě)接口方法
public interface StudentMapper {
//查詢所有的學(xué)生信息，以及對(duì)應(yīng)的老師信息
List<Student> getStudent();
}
2.1.2 編寫(xiě)sql標(biāo)簽
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
<select id="getStudent" resultMap="StudentTeacher">
select *from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--復(fù)雜的屬性：我們要單獨(dú)處理 對(duì)象：association 集合：collection -->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher" >
select *from teacher where id=#{id}
</select>
</mapper>
2.1.3 測(cè)試
@Test
public void testStudent(){
//第一步獲取sqlsession對(duì)象
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
2.1.4 測(cè)試結(jié)果
2.2 按照結(jié)果嵌套處理
2.2.1 編寫(xiě)接口方法
public interface StudentMapper {
//查詢所有的學(xué)生信息，以及對(duì)應(yīng)的老師信息
List<Student> getStudent2();
}
2.2.2 編寫(xiě)sql標(biāo)簽
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.StudentMapper">
<!--按照結(jié)果嵌套查詢-->
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid =t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
2.2.3 測(cè)試
@Test
public void testStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent2();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
2.2.4 測(cè)試結(jié)果
三、一對(duì)多復(fù)雜查詢
3.1 按照結(jié)果嵌套查詢
3.1.1 學(xué)生實(shí)體類(lèi)
@Data
public class Student1 {
private int id;
private String name;
private int tid;
}
3.1.2 教師實(shí)體類(lèi)
@Data
public class Teacher1 {
private int id;
private String name;
//一個(gè)老師對(duì)應(yīng)多個(gè)學(xué)生
private List<Student1> students;
}
3.1.3 編寫(xiě)接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學(xué)生及老師的信息
Teacher1 getTeacher2(@Param("tid") int id);
}
3.1.4 編寫(xiě)TeacherMapper1.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper1">
<select id="getTeacher1" resultType="Teacher1">
select *from mybatis.teacher;
</select>
<!--按照結(jié)果嵌套查詢-->
<select id="getTeacher2" resultMap="TeacherStudent1">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id and t.id = #{tid};
</select>
<resultMap id="TeacherStudent1" type="Teacher1">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--復(fù)雜的屬性：我們要單獨(dú)處理 對(duì)象：association 集合：collection -->
<!--javaType="" 指定屬性的類(lèi)型 集合中的泛型信息我們使用ofType獲取-->
<collection property="student1s" ofType="Student1" javaType="java.util.List" >
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
3.1.5 測(cè)試
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class);
Teacher1 teacher = mapper.getTeacher2(1);
System.out.println(teacher);
}
3.1.6 測(cè)試結(jié)果
3.2 根據(jù)查詢嵌套處理
3.2.1 編寫(xiě)接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學(xué)生及老師的信息
Teacher1 getTeacher3(@Param("tid") int id);
}
3.2.2 編寫(xiě)TeacherMapper1.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kuang.dao.TeacherMapper1">
<select id="getTeacher3" resultType="com.kuang.pojo.Teacher1" resultMap="TeacherStudent2">
select *from mybatis.teacher where id = #{tid};
</select>
<resultMap id="TeacherStudent2" type="Teacher1">
<collection property="student1s" javaType="ArrayList" ofType="Student1" select="getStudentByTeacherId" column="id"/>
</resultMap>
<select id="getStudentByTeacherId" resultType="Student1">
select *from mybatis.student where id = #{tid}
</select>
</mapper>
3.2.3 測(cè)試
@Test
public void test2() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper1 mapper = sqlSession.getMapper(TeacherMapper1.class);
Teacher1 teacher = mapper.getTeacher3(1);
System.out.println(teacher);
}
3.2.4 測(cè)試結(jié)果
到此這篇關(guān)于Mybatis復(fù)雜查詢的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)Mybatis復(fù)雜查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Spring使用ResourceLoader統(tǒng)一管理本地資源詳解
在項(xiàng)目開(kāi)發(fā)中,我們經(jīng)常需要讀取各種本地資源文件,Spring 框架提供了一個(gè)強(qiáng)大而優(yōu)雅的解決方案,即ResourceLoader 接口,下面我們就來(lái)看看具體實(shí)現(xiàn)方法吧2026-01-01
在SpringBoot中使用UniHttp簡(jiǎn)化天地圖路徑規(guī)劃調(diào)用實(shí)踐記錄(場(chǎng)景分析)
本文介紹了如何在SpringBoot項(xiàng)目中使用UniHttp簡(jiǎn)化天地圖路徑規(guī)劃接口的調(diào)用,通過(guò)一個(gè)具體的例子展示了如何根據(jù)中文地址獲取經(jīng)緯度坐標(biāo),并使用UniHttp調(diào)用天地圖路徑規(guī)劃服務(wù),感興趣的朋友一起看看吧2025-02-02
SpringBoot項(xiàng)目導(dǎo)入aliyun oss starter依賴后啟動(dòng)報(bào)錯(cuò)問(wèn)題
這篇文章主要介紹了SpringBoot項(xiàng)目導(dǎo)入aliyun oss starter依賴后啟動(dòng)報(bào)錯(cuò)問(wèn)題及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01
SpringBoot配置RocketMQ的詳細(xì)過(guò)程
這篇文章主要介紹了SpringBoot配置RocketMQ的詳細(xì)過(guò)程,本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),感興趣的朋友跟隨小編一起看看吧2024-03-03
MyBatis 擴(kuò)展BaseTypeHandler 轉(zhuǎn)換泛型 JSON 列表的解
本文詳細(xì)介紹了MyBatis中處理JSON轉(zhuǎn)換的bug及其解決方法,文章還介紹了如何處理抽象類(lèi)和自定義對(duì)象的JSON轉(zhuǎn)換,并提供了一個(gè)基類(lèi)AbstractListTypeHandler作為示例,最后,文章附上了MyBatis內(nèi)建的javaTypeAlias鏈接,感興趣的朋友跟隨小編一起看看吧2025-12-12
maven目前在web上面的使用方式很普遍，而打包的方式也存在很多方式，本文就詳細(xì)的介紹了三種方式，具有一定的參考價(jià)值，感興趣的可以了解下2021-06-06
詳解springboot shiro jwt實(shí)現(xiàn)權(quán)限管理
為什么使用jwt呢，因?yàn)榭梢酝ㄟ^(guò)URL，POST參數(shù)或者在HTTP header發(fā)送，因?yàn)閿?shù)據(jù)量小，傳輸速度也很快。本篇通過(guò)具體代碼來(lái)進(jìn)行詳情解析，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值2021-09-09
java中catalina.home與catalina.base區(qū)別點(diǎn)整理
在本篇文章里小編給大家整理的是關(guān)于java項(xiàng)目中catalina.home與catalina.base區(qū)別點(diǎn)，需要的朋友們可以學(xué)習(xí)下。2020-02-02
一篇文章帶了解如何用SpringBoot在RequestBody中優(yōu)雅的使用枚舉參數(shù)
這篇文章主要介紹了SpringBoot中RequestBodyAdvice使用枚舉參數(shù)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-08-08
這篇文章主要介紹了Java Synchronized的使用詳解，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07