精华内容
下载资源
问答
  • MyBatis高级查询

    2018-01-29 00:15:00
    -------------------------...MyBatis 高级查询 1、MyBatis 作为一个 ORM 框架,也对 SQL 的高级查询做了支持, MyBatis 高级查询分为如下三种: (1)一对一 (2)一对多 (3)多对多 ...

    -------------------------siwuxie095

       

       

       

       

       

       

    MyBatis 高级查询

       

       

    1、MyBatis 作为一个 ORM 框架,也对 SQL 的高级查询做了支持,

    MyBatis 高级查询分为如下三种:

       

    1一对一

       

    2一对多

       

    3多对多

       

       

    「MyBatis 高级查询 也被称为 MyBatis 高级结果映射

       

       

       

    2、案例:用户订单订单详情商品的关系

       

    1)一对一

       

    1)场景:订单和用户的关系

    2)描述:相对于订单来说,一个订单只能属于一个用户

       

    PS:实际上,订单和用户的关系是一对多,这里采用了

    相对的概念,从单向看,就是一对一

       

       

    2)一对多

       

    1)场景:订单和订单详情的关系

    2)描述:一个订单可以有多个订单详情,一个订单详情只能属于一个订单

       

       

    3)多对多

       

    1)场景:订单和商品的关系

    2)描述:一个订单可以有多个商品,一个商品可以属于多个订单

       

    PS:这里需要订单详情在中间,以维护关系

       

       

       

    3、建表

       

    1

       

    1)通过外键建立关系:假设的任意一方为,在的那一方创建

    字段作为外键,指向的那一方的主键,并将外键设置为唯一

       

    2)通过主键建立关系:方的主键对应另方的主键

       

       

    2:通过外键建立关系

       

    的那一方创建字段作为外键,指向的那一方的主键

       

       

    3:创建第三张表维护关系

       

    第三张表至少要有两个字段作为外键,分别指向双方的主键

       

       

       

       

       

       

       

       

       

    【made by siwuxie095】

    转载于:https://www.cnblogs.com/siwuxie095/p/8372990.html

    展开全文
  • Mybatis高级查询

    2019-11-08 10:59:06
    一、一对一查询 0. 工程目录结构 1. 数据库设计 CREATE DATABASE mybatis_mapper; USE mybatis_mapper; DROP TABLE teacher; CREATE TABLE teacher( teacherNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '教师...
    一、一对一查询
    1. 工程目录结构

    在这里插入图片描述

    2. 工程pom.xml文件
    <?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.itcast</groupId>
        <artifactId>mybatis_one2one</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <packaging>jar</packaging>
    
        <dependencies>
    
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.4.5</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.26</version>
            </dependency>
    
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.12</version>
            </dependency>
    
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.10</version>
            </dependency>
    
        </dependencies>
    
    </project>
    
    3. 数据库设计
    CREATE DATABASE mybatis_mapper;
    
    USE mybatis_mapper;
    
    DROP TABLE teacher;
    
    CREATE TABLE teacher(
    	teacherNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '教师编号',
    	teacherName VARCHAR(32) NOT NULL COMMENT '教师姓名',
    	PRIMARY KEY (teacherNo)
    )ENGINE = INNODB DEFAULT CHARSET = UTF8;
    
    INSERT INTO teacher(teacherNo,teacherName) VALUES(10,'张三教师');
    INSERT INTO teacher(teacherNo,teacherName) VALUES(11,'李四教师');
    
    --------------------------------------------------------------------------
    
    DROP TABLE student;
    
    CREATE TABLE student(
    	studentNo INT(8) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
    	studentName VARCHAR(32) NOT NULL COMMENT '学生姓名',
    	teacherNo INT(8) NOT NULL COMMENT '教师编号',
    	PRIMARY KEY (studentNo),
    	FOREIGN KEY (teacherNo) REFERENCES teacher(teacherNo)
    )ENGINE = INNODB DEFAULT CHARSET = UTF8;
    
    INSERT INTO student(studentNo,studentName,teacherNo) VALUES(100,'王五学生',10);
    INSERT INTO student(studentNo,studentName,teacherNo) VALUES(101,'赵六学生',11);
    
    4. 实体类:Student
    public class Student implements Serializable {
        private Integer studentNo;
        private String studentName;
        private Teacher teacher;
    }
    
    5. 实体类:Teacher
    public class Teacher implements Serializable {
        private Integer teacherNo;
        private String teacherName;
    }
    
    6. DAO接口:StudentDao
    public interface StudentDao {
        /**
         * 根据条件查询
         * @param student
         * @return
         */
        List<Student> findByCondition(Student student);
    }
    
    7. 接口对应的映射文件:StudentDao.xml
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace="dao.StudentDao">
        <resultMap id="studentMap" type="domain.Student">
            <id property="studentNo" column="studentNo"></id>
            <result property="studentName" column="studentName"></result>
            <association property="teacher" column="teacher" javaType="domain.Teacher">
                <id property="teacherNo" column="teacherNo"></id>
                <result property="teacherName" column="teacherName"></result>
            </association>
        </resultMap>
    
        <select id="findByCondition" parameterType="domain.Student" resultMap="studentMap">
            SELECT *
            FROM student s, teacher t
            WHERE s.teacherNo = t.teacherNo
                <if test="studentNo != null">
                    AND studentNo = #{studentNo}
                </if>
                <if test="studentName != null">
                    AND studentName = #{studentName}
                </if>
        </select>
    </mapper>
    
    8. 数据源配置文件(db.properties)
    jdbc.driver = com.mysql.jdbc.Driver
    jdbc.url = jdbc:mysql://localhost:3306/mybatis_mapper
    jdbc.username = root
    jdbc.password = root
    
    9. 全局配置文件(mybatis-config.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"></properties>
    
        <environments default="development">
            <environment id="development">
                <transactionManager type="jdbc"></transactionManager>
                <dataSource type="pooled">
                    <property name="driver" value="${jdbc.driver}"/>
                    <property name="url" value="${jdbc.url}"/>
                    <property name="username" value="${jdbc.username}"/>
                    <property name="password" value="${jdbc.password}"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            <mapper resource="dao/StudentDao.xml"></mapper>
        </mappers>
    
    </configuration>
    
    10. Junit测试类
    public class TestMybatis {
        private InputStream inputStream;
        private SqlSessionFactory sqlSessionFactory;
        private SqlSession sqlSession;
    
        @Before
        public void init() {
            try {
                inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            } catch (IOException e) {
                e.printStackTrace();
            }
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            sqlSession = sqlSessionFactory.openSession();
        }
    
        @After
        public void destroy() {
            sqlSession.commit();
            if (sqlSession != null) {
                sqlSession.close();
            }
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    
        @Test
        public void test_findCondition() {
            Student student = new Student();
            student.setStudentNo(101);
            StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
            List<Student> students = studentDao.findByCondition(student);
            for (Student stu : students) {
                System.out.println(stu);
            }
        }
    }
    
    展开全文
  • mybatis高级查询

    2019-12-16 21:38:50
    -- 订单查询关联用户的resultMap --> <resultMap id="OrdersUserResultMap" type="nuc.edu.rwenjie.pojo.Orders"> <!-- 配置要映射的订单信息 --> <!-- id:配置查询列中的唯一标识, 订单信息...

    1.一对一

    <!-- 订单查询关联用户的resultMap -->
        <resultMap id="OrdersUserResultMap" type="nuc.edu.rwenjie.pojo.Orders">
            <!-- 配置要映射的订单信息 -->
    
            <!-- id:配置查询列中的唯一标识, 订单信息中的唯一标识,如果有多个列组成唯一标识,配置多个id
                column:订单信息的唯一标识的列
                property:订单信息的唯一标识的列 所映射到Orders中的哪个属性
            -->
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!-- 配置映射的关联的用户信息-->
            <!-- association:用于映射关联查询单个对象的信息
                property:要将关联查询对象的用户信息映射到Orders中的哪个属性
            -->
             <association property="user" javaType="nuc.edu.rwenjie.pojo.Users">
                <!-- id:关联查询的用户的唯一标识 -->
                <id column="user_id" property="id"/>
                <result column="username" property="username"/>
                <result column="sex" property="sex"/>
                <result column="address" property="address"/>
            </association>
        </resultMap>
    
    <!-- 一对一 查询订单及订单对应的用户信息 -->
        <select id="findOrdersUser" resultMap="OrdersUserResultMap">
            SELECT
    	        orders.*,
    	        Users.username,
    	        users.sex,
    	        users.address
            FROM
    	        orders, users
            WHERE orders.user_id = users.id
        </select>

     

    2.一对多

    <!-- 查询订单及订单明细的resultMap
        使用extend继承,不在配置订单信息和用户信息的映射
        <resultMap id="findOrdersAndOrderDetailResultMap" type="nuc.edu.rwenjie.pojo.Orders" extends="OrdersUserResultMap">
        -->
        <resultMap id="findOrdersAndOrderDetailResultMap" type="nuc.edu.rwenjie.pojo.Orders" >
            <!-- 订单信息 -->
            <id column="id" property="id"/>
            <result column="user_id" property="userId"/>
            <result column="createtime" property="createtime"/>
            <result column="note" property="note"/>
            <!-- 订单用户信息 -->
            <association property="user" javaType="nuc.edu.rwenjie.pojo.Users">
                <!-- id:关联查询的用户的唯一标识 -->
                <id column="user_id" property="id"/>
                <result column="username" property="username"/>
                <result column="sex" property="sex"/>
                <result column="address" property="address"/>
            </association>
            <!-- 订单明细信息
            一个订单查询出来多条明细,要使用collection进行映射
            collection:对关联对象查询到的多条记录映射到集合对象中
            property:将关联对象查询到的多条记录映射到nuc.edu.rwenjie.pojo.Orders
            ofType:指定映射到list集合属性中pojo的类型
            -->
            <!--         上面的这两部分可以用extends实现             -->
    
            <collection property="orderDetailList" ofType="nuc.edu.rwenjie.pojo.OrderDetail">
    
    
                <!-- id:订单明细的唯一标识
                    property:要将订单明细的唯一标识映射到nuc.eud.rwenjie.pojo.OrderDetail的那个属性
                -->
    
                <!--column:SQL语句中的别名  property:OrderDetail中的唯一标识-->
                <id column="orderdetail_id" property="i_d" />
                <result column="items_id" property="itemsId"/>
                <result column="items_num" property="itemsNum"/>
                <result column="orders_id" property="ordersId"/>
            </collection>
    
        </resultMap>
    
    
        <!-- 一对多
           查询顶和订单明细
         -->
        <select id="findOrdersAndOrderDetail" resultMap="findOrdersAndOrderDetailResultMap">
            SELECT
    	        orders.*,
    	        Users.username,
    	        users.sex,
    	        users.address,
    	        orderdetail.id orderdetail_id,
    	        orderdetail.items_id,
    	        orderdetail.items_num,
    	        orderdetail.orders_id
            FROM
    	        orders, users, orderdetail
            WHERE orders.user_id = users.id AND orderdetail.orders_id = orders.id
        </select>

    3.多对多

        <!-- 查询用户及购买的商品 -->
        <resultMap id="findUserAndItemsResultMap" type="nuc.edu.rwenjie.pojo.Users">
            <!-- 用户信息 -->
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="sex" property="sex"/>
            <result column="address" property="address"/>
            <!-- 用户所购买的订单信息
                一个用户对应多个订单, 使用collection
             -->
            <collection property="ordersList" ofType="nuc.edu.rwenjie.pojo.Orders">
                <id column="id" property="id"/>
                <result column="user_id" property="userId"/>
                <result column="createtime" property="createtime"/>
                <result column="note" property="note"/>
    
                <!-- 订单明细 一个订单包含多个订单明细-->
                <!-- collection相当于一个循环 订单明细应该在内循环中 -->
                <collection property="orderDetailList" ofType="nuc.edu.rwenjie.pojo.OrderDetail">
                    <id column="orderdetail_id" property="i_d"/>
                    <result column="items_id" property="itemsId"/>
                    <result column="items_num" property="itemsNum"/>
                    <result column="orders_id" property="ordersId"/>
    
                    <!-- 商品信息  一个订单明细对应一个商品信息 -->
                    <association property="items" javaType="nuc.edu.rwenjie.pojo.Items">
                        <id column="items_id" property="id"/>
                        <result column="items_name" property="name"/>
                        <result column="items_price" property="price"/>
                        <result column="items_datetime" property="datetime"/>
                        <result column="items_datail" property="detail"/>
                    </association><!--Items-->
                </collection><!--OrderDrtail-->
            </collection><!-- Orders-->
        </resultMap><!-- Users-->
    
        <!-- 查询用户及用户所购买的商品信息 -->
        <select id="findUserAndItems" resultMap="findUserAndItemsResultMap">
            SELECT
    	        orders.*,
    	        Users.username,
    	        users.sex,
    	        users.address,
    	        orderdetail.id orderdetail_id,
    	        orderdetail.items_id,
    	        orderdetail.items_num,
    	        orderdetail.orders_id,
    		items.name items_name,
    		items.price items_price,
    		items.datetime items_datetime,
    		items.detail items_datail
            FROM orders, users, orderdetail, items
            WHERE orders.user_id = users.id AND orderdetail.orders_id = orders.id AND orderdetail.items_id = items.id
        </select>

    源代码见:https://download.csdn.net/download/weixin_42542092/12035352

    展开全文
  • mybatis 高级查询

    2018-01-23 11:00:15
    ...-- 返回方式,子查询 --> ...-- 返回方式,子查询 --> ...这里的column是说明传哪个值给子...MyBatis中使用collection标签来解决一对多的关联查询, ofType 属性指定集合中元素的对象类型。

    1.oneToOne

    <select id= "getClass" parameterType ="int" resultMap="ClassResultMap" >
              select *
              from
              class c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
         </select >
         <resultMap type="common.pojo.Classes" id="ClassResultMap" >
               <id property= "id" column ="c_id" />
               <result property= "name" column ="c_name" />
               <association property= "teacher" javaType="common.pojo.Teacher" >
                   <id property= "id" column ="t_id" />
                   <result property= "name" column ="t_name" />
               </association>
         </resultMap >

         <!-- 返回方式,子查询 -->
         <select id="getClass2" parameterType="int" resultMap="ClassResultMap2" >
              select *
              from
              class where c_id=#{id}
         </select >

         <resultMap type="common.pojo.Classes" id="ClassResultMap2" >
               <id property= "id" column ="c_id" />
               <result property= "name" column ="c_name" />
               <association property= "teacher" column="teacher_id"
                   select= "getTeacher">
               </association>
         </resultMap >

         <select id="getTeacher" parameterType="int" resultMap="teacherResultMap" >
              select *
              from teacher where t_id=#{id}
         </select >
         <resultMap type="common.pojo.Teacher" id="teacherResultMap" >
               <id property= "id" column ="t_id" />
               <result property= "name" column ="t_name" />
         </resultMap >

    2.oneToMany
    <mapper namespace="mapping.classesMapper" >
         <!-- 返回方式,resultMap -->
         <select id="getClass" parameterType="int" resultMap="ClassResultMap" >
              select *
              from
              class c,teacher t,student s where c.teacher_id=t.t_id and
              s.class_id=c.c_id and c.c_id=#{id}
         </select >
         <resultMap type="common.pojo.Classes" id="ClassResultMap" >
               <id property= "id" column ="c_id" />
               <result property= "name" column ="c_name" />
               <association property= "teacher" javaType="common.pojo.Teacher" >
                   <id property= "id" column ="t_id" />
                   <result property= "name" column ="t_name" />
               </association>
              <collection property= "students" ofType="common.pojo.Student" >
                   <id property= "id" column ="s_id" />
                   <result property= "name" column ="s_name" />
               </collection>
         </resultMap >
    ----------------------------------------------------------------------------------
         <!-- 返回方式,子查询 -->
         <select id="getClass2" parameterType="int" resultMap="ClassResultMap2" >
              select *
              from
              class where c_id=#{id}
         </select >

         <resultMap type="common.pojo.Classes" id="ClassResultMap2" >
               <id property= "id" column ="c_id" />
               <result property= "name" column ="c_name" />
               <association property= "teacher" column="teacher_id"
                   select= "getTeacher">
               </association>
               <collection property= "students" ofType="common.pojo.Student"
                   column= "c_id" select="getStudent" ></collection>
                 这里的column是说明传哪个值给子查询
         </resultMap >

         <select id="getTeacher" parameterType="int" resultType="common.pojo.Teacher" >
              select
              t_id id,t_name
              name
              from teacher where t_id=#{id}
         </select >

         <select id="getStudent" parameterType="int" resultType="common.pojo.Student" >
              select
              s_id id,s_name
              name
              from student where class_id=#{id}
         </select >

    </mapper>

    MyBatis中使用collection标签来解决一对多的关联查询,ofType属性指定集合中元素的对象类型。

    展开全文
  • 一、mybatis高级查询和分页查询  mybatis中的#表示占位符,$表示直接拼接SQL 二、高级查询和分页查询的综合 QueryObject: public class QueryObject { private Long id; private String keyword; private ...
  • mybatis高级查询1

    2020-02-10 14:19:05
    总结下mybatis高级查询使用 案列:采用 用户,订单,订单详情,商品。 一对一:用户和订单 一对多:订单和订单详情 多对多:订单详情和商品 准备工作: 创建四张表和数据 TB_USER,TB_ORDER,TB_ORDERDETAIL,TB_...
  • 【笔记】Mybatis高级查询(准备) 【笔记】Mybatis高级查询(一)–使用自动映射处理一对一关系 【笔记】Mybatis高级查询(二)–使用resultMap配置一对一映射 【笔记】Mybatis高级查询(三)–使用标签实现嵌套查询...
  • 5. MyBatis高级查询 在关系型数据库中,我们经常要处理一对一、一对多的关系。例如:一个学生只能在一个班级,一个班级可以存在很多学生。 数据准备 -- ---------------------------- -- Table structure for `t_...
  • 文章目录mybatis映射器映射器配置文件mybatis高级查询mybatis 关系映射多对一 嵌套结果多对一 嵌套查询一对多 嵌套结果一对多 嵌套查询 mybatis映射器 MyBatis基于动态代理机制,无需再编写Dao的实现,名称统一以...
  • MyBatis详解5--MyBatis 高级查询需求缘起1.一对一查询1.1 创建一个订单表1.2 创建订单实体类1.3 编写接口 OrdersMapper.java1.3 编写 OrdersMapper.xml1.4 在配置文件 mybatis-configuration.xml 中注册 OrderMapper...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,392
精华内容 1,356
关键字:

mybatis高级查询