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í)體類
1.3.1 Student 實(shí)體類
@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í)體類
@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 編寫接口方法
public interface StudentMapper {
//查詢所有的學(xué)生信息，以及對(duì)應(yīng)的老師信息
List<Student> getStudent();
}
2.1.2 編寫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 編寫接口方法
public interface StudentMapper {
//查詢所有的學(xué)生信息，以及對(duì)應(yīng)的老師信息
List<Student> getStudent2();
}
2.2.2 編寫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í)體類
@Data
public class Student1 {
private int id;
private String name;
private int tid;
}
3.1.2 教師實(shí)體類
@Data
public class Teacher1 {
private int id;
private String name;
//一個(gè)老師對(duì)應(yīng)多個(gè)學(xué)生
private List<Student1> students;
}
3.1.3 編寫接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學(xué)生及老師的信息
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"/>
<!--復(fù)雜的屬性：我們要單獨(dú)處理 對(duì)象：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 測(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 編寫接口
public interface TeacherMapper1 {
//獲取指定老師下的所有學(xué)生及老師的信息
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 測(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)文章
這篇文章主要介紹了Kafka中Producer和Consumer的作用詳解,Kafka是一個(gè)分布式的流處理平臺(tái),它的核心是消息系統(tǒng),Producer是Kafka中用來(lái)將消息發(fā)送到Broker的組件之一,它將消息發(fā)布到主題,并且負(fù)責(zé)按照指定的分區(qū)策略將消息分配到對(duì)應(yīng)的分區(qū)中,需要的朋友可以參考下2023-12-12
IntelliJ IDEA Tomcat控制臺(tái)中文亂碼問(wèn)題的四種解決方案
這篇文章主要給大家分享了4種方法完美解決IntelliJ IDEA Tomcat控制臺(tái)中文亂碼問(wèn)題,文中有詳細(xì)的圖文介紹,對(duì)我們的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2023-08-08
詳細(xì)理解JAVA面向?qū)ο蟮姆庋b,繼承,多態(tài),抽象
這篇文章主要介紹了Java基礎(chǔ)之面向?qū)ο髾C(jī)制（多態(tài)、繼承）底層實(shí)現(xiàn),文中有非常詳細(xì)的代碼示例,對(duì)正在學(xué)習(xí)java的小伙伴們有非常好的幫助,需要的朋友可以參考下2021-07-07
Java web網(wǎng)站訪問(wèn)量的統(tǒng)計(jì)
這篇文章主要為大家詳細(xì)介紹了Java web網(wǎng)站訪問(wèn)量的統(tǒng)計(jì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
實(shí)戰(zhàn)干貨之基于SpringBoot的RabbitMQ多種模式隊(duì)列
RabbitMQ 是一個(gè)由Erlang語(yǔ)言開(kāi)發(fā)的AMQP的開(kāi)源實(shí)現(xiàn)，支持多種客戶端。用于在分布式系統(tǒng)中存儲(chǔ)轉(zhuǎn)發(fā)消息，在易用性、擴(kuò)展性、高可用性等方面表現(xiàn)不俗，下文將帶你深入了解 RabbitMQ 多種模式隊(duì)列2021-09-09
這篇文章主要介紹了解析idea內(nèi)嵌瀏覽器翻譯的相關(guān)知識(shí)，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-10-10
淺談java中異常拋出后代碼是否會(huì)繼續(xù)執(zhí)行
這篇文章主要給大家介紹了java中異常拋出后代碼是否會(huì)繼續(xù)執(zhí)行，文章通過(guò)幾種情況的代碼示例給大家詳細(xì)分析了這個(gè)情況，有需要的朋友們可以參考借鑒，下面來(lái)一起看看吧。2016-10-10
使用IDEA創(chuàng)建servlet?JavaWeb?應(yīng)用及使用Tomcat本地部署的實(shí)現(xiàn)
本文主要介紹了使用IDEA創(chuàng)建servlet?JavaWeb?應(yīng)用及使用Tomcat本地部署2022-01-01
MyBatis-Plus如何最優(yōu)雅最簡(jiǎn)潔地完成數(shù)據(jù)庫(kù)操作
Mybatis-Plus是一個(gè)?Mybatis?的增強(qiáng)工具,在?Mybatis?的基礎(chǔ)上只做增強(qiáng)不做改變,為簡(jiǎn)化開(kāi)發(fā)、提高效率而生,下面這篇文章主要給大家介紹了關(guān)于MyBatis-Plus如何最優(yōu)雅最簡(jiǎn)潔地完成數(shù)據(jù)庫(kù)操作的相關(guān)資料,需要的朋友可以參考下2022-03-03
java動(dòng)態(tài)方法調(diào)度實(shí)例分析
這篇文章主要介紹了java動(dòng)態(tài)方法調(diào)度,結(jié)合實(shí)例形式對(duì)比分析了java的動(dòng)態(tài)方法調(diào)度原理、使用方法與注意事項(xiàng),需要的朋友可以參考下2016-06-06