Mybatis复杂查询的实现

2024-09-27 10:16 开发作者：雪顶猫的鳄

一、环境搭建

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 创建实体类

1.3.1 Student 实体类

@Data
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    //学生要关联一个老师
    private Teacher teacher;
    }

1.3.2 Teacher 实体类

@Data
public class Teacher {
    private int id;
    private String name;
    }

1.4 创建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 创建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 ver编程客栈sion="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();
    }

测试结果

Mybatis复杂查询的实现

二、多对一复杂查询

2.1 按照查询嵌套处理

2.1.1 编写接口方法

public interface StudentMapper {
 //查询所有的学生信息，以及对应的老师信息 
 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 测试结果

Mybatis复杂查询的实现

2.2 按照结果嵌套处理

2.2.1 编写接口方法

public interface StudentMapper {
 //查询所有的学生信息，以及对应的老师信息
 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">        
        <!--按照结果嵌套查询-->
        <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>
android

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 测试结果

Mybatis复杂查询的实现

三、一对多复杂查询

3.1 按照结果嵌套查询

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;
    //一个老师对应多个学生
    private List<Student1> studentqwxdYifoPis;
    }

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>
    <!--按照结果嵌套查询-->
    <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 测试结果

Mybatis复杂查询的实现

3.2 根据查询嵌套处理

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 测试结果

Mybatis复杂查询的实现

到此这篇关于Mybatis复杂查询的实现的文章就介绍到这了,更多相关Mybatis复杂查询内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

继续阅读：Mybatis复杂查询

目录

一、环境搭建

1.1 建表

1.2 导入依赖

1.3 创建实体类

1.3.1 Student 实体类

1.3.2 Teacher 实体类

1.4 创建Mapper接口

1.4.1 StudentMapper 接口

1.4.2 TeacherMapper 接口

1.5 创建Mapper.xml文件

1.5.1 namespace 绑定StudentMapper接口

1.5.2 namespace 绑定TeacherMapper接口

1.6 核心配置文件中注册Mapper.xml文件

1.7 测试

二、多对一复杂查询

2.1 按照查询嵌套处理

2.1.1 编写接口方法

2.1.2 编写sql标签

2.1.3 测试

2.1.4 测试结果

2.2 按照结果嵌套处理

2.2.1 编写接口方法

2.2.2 编写sql标签

2.2.3 测试

2.2.4 测试结果

三、一对多复杂查询

3.1 按照结果嵌套查询

3.1.1 学生实体类

3.1.2 教师实体类

3.1.3 编写接口

3.1.4 编写TeacherMapper1.xml文件

3.1.5 测试

3.1.6 测试结果

3.2 根据查询嵌套处理

3.2.1 编写接口

3.2.2 编写TeacherMapper1.xml文件

3.2.3 测试

3.2.4 测试结果

更多精彩内容

精彩评论

最新开发

如何实现Python编写的图形界面可以自由拖动

mysql字符集引起的java.sql.SQLException:Incorrect string value:问题

解决Feign异步调用丢失上下文问题

python模拟重载初始化函数的方法详解

SQL注入攻击及其在SpringBoot中使用MyBatisPlus的防范策略的方法

开发排行榜

springboot后端存储富文本内容的思路与步骤(含图片内容)

PyCharm运行python测试,报错“没有发现测试”/“空套件”的解决

return base64.b64encode(b).decode(

基于C语言实现钻石棋游戏的示例代码

Sublime Text 3解决中文乱码问题（实测可用）