Mybatis復雜查詢的實現(xiàn)
更新時間：2024年09月27日 09:43:17 作者：雪頂貓的鱷
本文主要介紹了Mybatis復雜查詢的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
一、環(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 導入依賴
<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)建實體類
1.3.1 Student 實體類
@Data
@NoArgsConstructor
public class Student {
private int id;
private String name;
//學生要關(guān)聯(lián)一個老師
private Teacher teacher;
}
1.3.2 Teacher 實體類
@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 核心配置文件中注冊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 測試
@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();
}
測試結(jié)果
二、多對一復雜查詢
2.1 按照查詢嵌套處理
2.1.1 編寫接口方法
public interface StudentMapper {
//查詢所有的學生信息，以及對應(yīng)的老師信息
List<Student> getStudent();
}
2.1.2 編寫sql標簽
<?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"/>
<!--復雜的屬性：我們要單獨處理 對象：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 測試
@Test
public void testStudent(){
//第一步獲取sqlsession對象
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 測試結(jié)果
2.2 按照結(jié)果嵌套處理
2.2.1 編寫接口方法
public interface StudentMapper {
//查詢所有的學生信息，以及對應(yīng)的老師信息
List<Student> getStudent2();
}
2.2.2 編寫sql標簽
<?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 測試
@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 測試結(jié)果
三、一對多復雜查詢
3.1 按照結(jié)果嵌套查詢
3.1.1 學生實體類
@Data
public class Student1 {
private int id;
private String name;
private int tid;
}
3.1.2 教師實體類
@Data
public class Teacher1 {
private int id;
private String name;
//一個老師對應(yīng)多個學生
private List<Student1> students;
}
3.1.3 編寫接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學生及老師的信息
Teacher1 getTeacher2(@Param("tid") int id);
}
3.1.4 編寫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"/>
<!--復雜的屬性：我們要單獨處理 對象：association 集合：collection -->
<!--javaType="" 指定屬性的類型 集合中的泛型信息我們使用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 測試
@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 測試結(jié)果
3.2 根據(jù)查詢嵌套處理
3.2.1 編寫接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學生及老師的信息
Teacher1 getTeacher3(@Param("tid") int id);
}
3.2.2 編寫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 測試
@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 測試結(jié)果
到此這篇關(guān)于Mybatis復雜查詢的實現(xiàn)的文章就介紹到這了,更多相關(guān)Mybatis復雜查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Spring使用ResourceLoader統(tǒng)一管理本地資源詳解
在項目開發(fā)中,我們經(jīng)常需要讀取各種本地資源文件,Spring 框架提供了一個強大而優(yōu)雅的解決方案,即ResourceLoader 接口,下面我們就來看看具體實現(xiàn)方法吧2026-01-01
在SpringBoot中使用UniHttp簡化天地圖路徑規(guī)劃調(diào)用實踐記錄(場景分析)
本文介紹了如何在SpringBoot項目中使用UniHttp簡化天地圖路徑規(guī)劃接口的調(diào)用,通過一個具體的例子展示了如何根據(jù)中文地址獲取經(jīng)緯度坐標,并使用UniHttp調(diào)用天地圖路徑規(guī)劃服務(wù),感興趣的朋友一起看看吧2025-02-02
SpringBoot項目導入aliyun oss starter依賴后啟動報錯問題
這篇文章主要介紹了SpringBoot項目導入aliyun oss starter依賴后啟動報錯問題及解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
這篇文章主要介紹了SpringBoot配置RocketMQ的詳細過程,本文通過實例代碼給大家介紹的非常詳細,感興趣的朋友跟隨小編一起看看吧2024-03-03
MyBatis 擴展BaseTypeHandler 轉(zhuǎn)換泛型 JSON 列表的解
本文詳細介紹了MyBatis中處理JSON轉(zhuǎn)換的bug及其解決方法,文章還介紹了如何處理抽象類和自定義對象的JSON轉(zhuǎn)換,并提供了一個基類AbstractListTypeHandler作為示例,最后,文章附上了MyBatis內(nèi)建的javaTypeAlias鏈接,感興趣的朋友跟隨小編一起看看吧2025-12-12
maven目前在web上面的使用方式很普遍，而打包的方式也存在很多方式，本文就詳細的介紹了三種方式，具有一定的參考價值，感興趣的可以了解下2021-06-06
詳解springboot shiro jwt實現(xiàn)權(quán)限管理
為什么使用jwt呢，因為可以通過URL，POST參數(shù)或者在HTTP header發(fā)送，因為數(shù)據(jù)量小，傳輸速度也很快。本篇通過具體代碼來進行詳情解析，對大家的學習或工作具有一定的參考借鑒價值2021-09-09
java中catalina.home與catalina.base區(qū)別點整理
在本篇文章里小編給大家整理的是關(guān)于java項目中catalina.home與catalina.base區(qū)別點，需要的朋友們可以學習下。2020-02-02
一篇文章帶了解如何用SpringBoot在RequestBody中優(yōu)雅的使用枚舉參數(shù)
這篇文章主要介紹了SpringBoot中RequestBodyAdvice使用枚舉參數(shù)，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-08-08
這篇文章主要介紹了Java Synchronized的使用詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-07-07