开发者

Mybatis复杂查询的实现

开发者 https://www.devze.com 2024-09-27 10:33 出处：网络 作者： 雪顶猫的鳄
目录一、环境搭建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 绑定St
相关专题：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 测试结果

      一、环境搭建

      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)！ 

      0

      上一篇:

      :下一篇

      更多 开发 相关资讯：

      精彩评论

      暂无评论...
      登录 注册
      Qedev.com
      验证码 换一张
      取 消

      关注公众号

      热门标签

      图文推荐

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

      mysql字符集引起的java.sql.SQLException:Incorrect string value:问题
      解决Feign异步调用丢失上下文问题

      解决Feign异步调用丢失上下文问题
      一文教会你使用Python来下一场雪

      一文教会你使用Python来下一场雪
      利用python调整Excel行高和列宽的操作示例

      利用python调整Excel行高和列宽的操作示例
      Python字符串检索方式

      Python字符串检索方式
      Android中常见内存泄漏的场景和解决方案详解

      Android中常见内存泄漏的场景和解决方案详解
      python解析网页上的json数据并保存到EXCEL

      python解析网页上的json数据并保存到EXCEL
      使用wxPython创建一个文件夹结构生成器

      使用wxPython创建一个文件夹结构生成器

      开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

      法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

      Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9