JAVAEE框架技术之9-myBatis高级查询技术文档

快来打我* 2023-09-29 17:20 28阅读 0赞

## 高级查询 ##

Mybatis作为一个ORM框架，也对sql的高级查询作了支持，下面我来学习mybatis中的一对一，一对多， 多对多

### 案例说明 ###

此案例中的业务关系是用户，订单，订单详情，商品之间的关系，其中
        一个订单属性于一个人
        一个订单可以有多个订单详情
        一个订单详情中包含一个商品信息
    它们的关系是：
        订单和人是一对一关系
        订单和订单详情是一对多的关系
        订单和商品是多对多的关系

### 表分析 ###

导入课程资料中的数据库及实体类

![在这里插入图片描述][00912ecbd5094ccca659a93a2edd1768.png_pic_center]

### 业务需求 ###

一对一查询： 查询订单，并且查询出下单人信息。

一对多查询：查询订单，查询出下单人信息并且查询出订单详情。

多对多查询：查询订单，查询出下单人信息并且查询出订单详情中的商品数据。

## 一对一查询 ##

> 需求：查询订单，并且查询出下单人信息

### sql分析 ###

-- 查询订单，并且查询出下单人的信息
    SELECT
    	* 
    FROM
    	tb_user as u
    	left join tb_order AS o ON u.id = o.user_id 
    WHERE
    	o.order_number = 20200921001

### 代码实现 ###

@Data
    public class Order {
        
        private Integer id;
        private Long userId;
        private String orderNumber;
        //添加User对象
        private User user;
    }

public interface UserMapper {
        
        /**
         *  一对一：查询订单，并且查询出下单人信息
         * @param orderNumber
         * @return
         */
        public Order oneToOne(@Param("orderNumber") String orderNumber);
    }

<resultMap id="oneToOneResultMap" type="Order" autoMapping="true">
            
            <id property="id" column="id"/>
            
            <association property="user" javaType="User" autoMapping="true">
                
                <id property="id" column="id"/>
                <result property="userName" column="user_name"/>
            </association>
        </resultMap>
        
        <select id="oneToOne" resultMap="oneToOneResultMap">
            SELECT   *  FROM
                 tb_user as u left join tb_order as o on u.id = o.user_id
            WHERE
                 o.order_number = #{orderNumber}
        </select>

public class UserDaoTest {
        
        private UserMapper userMapper;
        private SqlSession sqlSession;
        SqlSessionFactory sqlSessionFactory;
        
        @Before
        public void setUp() throws Exception {
        
            //指定核心配置文件的位置
            String resource = "mybatis-config.xml";
            //加载核心配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            //构建sqlSessionFactory对象
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            //获取SqlSession对象,SqlSession可以操作crud
            sqlSession = sqlSessionFactory.openSession();
            //动态代理
            userMapper = sqlSession.getMapper(UserMapper.class);
        }
    
        //一对一查询
        @Test
        public void oneToOne(){
        
            Order order = this.userMapper.oneToOne("20200921001");
            System.out.println(order);
        }
    }

## 一对多查询 ##

> \*\*一对多查询：\*\*查询订单，查询出下单人信息并且查询出订单详情

### sql分析 ###

![在这里插入图片描述][d6e957aecdb349ec9ded267f6c7551bd.png_pic_center]

-- 查询订单，查询出下单人信息并且查询出订单详情。
     SELECT
    			o.id as o_id,
    			u.id as u_id,
    			d.id as d_id,
    			u.user_name,
    			o.order_number,
    			d.total_price
    	FROM
    			tb_order AS o
    			left join tb_user AS u ON o.user_id = u.id
    			left join tb_orderdetail as d on d.order_id = o.id
    	WHERE
    			o.order_number = 20200921001;

### 代码实现 ###

/**
     * 订单表
     */
    @Data
    public class Order {
        
        private Integer id;
        private Long userId;
        private String orderNumber;
        //一对一：添加User对象
        private User user;
        //一对多：添加Orderdetail
        private List<Orderdetail> orderdetails;
    }

/**
         * 一对多：查询订单，查询出下单人信息并且查询出订单详情
         * @param orderNumber
         * @return
         */
        public Order oneToMany(@Param("orderNumber") String orderNumber);

<resultMap id="oneToManyResultMap" type="Order" autoMapping="true">
            
            <id property="id" column="o_id"/>
            
            <association property="user" javaType="User" autoMapping="true">
                <id property="id" column="u_id"/>
            </association>
            
            <collection property="orderdetails" javaType="List" ofType="Orderdetail" autoMapping="true">
                <id property="id" column="d_id"/>
            </collection>
        </resultMap>
        
        <select id="oneToMany" resultMap="oneToManyResultMap">
            SELECT
                   o.id as o_id,
                   u.id as u_id,
                   d.id as d_id,
                   u.user_name,
                   o.order_number,
                   d.total_price
            FROM
                 tb_order AS o
                     left join tb_user AS u ON o.user_id = u.id
                     left join tb_orderdetail as d on d.order_id = o.id
            WHERE
                    o.order_number = #{orderNumber};
        </select>

//一对多
        @Test
        public void oneToMany(){
        
            Order order = this.userMapper.oneToMany("20200921001");
            System.out.println(order);
        }

## 多对多查询 ##

> \*\*多对多查询：\*\*查询订单，查询出下单人信息并且查询出订单详情中的商品数据
> 
> 定单和商品表 是多对多的对应关系

### sql分析 ###

-- 查询订单，查询出下单人信息并且查询出订单详情中的商品数据。
    SELECT
    	o.id as o_id,
    	u.id as u_id,
    	d.id as d_id,
    	i.id as i_id,
    	u.user_name,
    	o.order_number,
    	d.total_price,
    	i.item_name
    FROM
    	tb_order AS o
    	left join tb_user AS u ON o.user_id = u.id 
    	left join tb_orderdetail as d on d.order_id = o.id
    	left join tb_item as i on d.item_id = i.id
    WHERE
    	o.order_number = 20200921001

### 代码实现 ###

@Data
    public class Orderdetail {
        
        private Integer id;
        private Double totalPrice;
        private Integer status;    
        /*商品信息*/
        private Item item;
    }

/**
         * 多对多查询：查询订单，查询出下单人信息并且查询出订单详情中的商品数据。
         * @param orderNumber
         * @return
         */
        public Order manyToMany(@Param("orderNumber") String orderNumber);

<resultMap id="manyToManyResultMap" type="Order" autoMapping="true">
            
            <id property="id" column="o_id"/>
    
            
            <association property="user" javaType="User" autoMapping="true">
                <id property="id" column="u_id"/>
            </association>
    
            
            <collection property="orderdetails" javaType="List" ofType="Orderdetail" autoMapping="true">
                <id property="id" column="d_id"/>
    
                
                <association property="item" javaType="Item" autoMapping="true">
                    <id property="id" column="i_id"/>
                </association>
            </collection>
    
        </resultMap>
        
        <select id="manyToMany" resultMap="manyToManyResultMap">
            SELECT
                   o.id as o_id,
                   u.id as u_id,
                   d.id as d_id,
                   i.id as i_id,
                   u.user_name,
                   o.order_number,
                   d.total_price,
                   i.item_name
            FROM
                 tb_order AS o
                     left join tb_user AS u ON o.user_id = u.id
                     left join tb_orderdetail as d on d.order_id = o.id
                     left join tb_item as i on d.item_id = i.id
            WHERE
                    o.order_number = #{orderNumber}
        </select>

//多对多
        @Test
        public void manyToMany(){
        
            Order order = this.userMapper.manyToMany("20200921001");
            System.out.println(order);
        }

## ResultMap的继承 ##

回顾以上多表映射中resultMap映射中其实有 一对一，一对多，多对多中都有一对一对映射很重复的，每一次都需要写，不好，其实我们可以把相同的一对一映射进行抽取，然后再继承过来。

### 代码实现 ###

![在这里插入图片描述][d0366d46a777486c8f98c8b2bba0294d.png_pic_center]

## 分页插件 ##

### PageHelper分页插件 ###

Mybatis的plugin实现原理

### 添加依赖 ###

<dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>3.7.5</version>
    </dependency>
    <dependency>
      <groupId>com.github.jsqlparser</groupId>
      <artifactId>jsqlparser</artifactId>
      <version>0.9.1</version>
    </dependency>

### 配置插件 ###

<plugins>
        
        <plugin interceptor="com.github.pagehelper.PageHelper">
            
            <property name="dialect" value="mysql"/>
            
            <property name="rowBoundsWithCount" value="true"/>
        </plugin>
    </plugins>
    5.x后续方案
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
     </plugin>

### 实现分页 ###

/**
     * 分页查询
     * @return
     */
    public List<User> queryAll();

<select id="queryAll" resultType="User">
        select * from tb_user
    </select>

//分页查询
        @Test
        public void queryAll(){
        
            //实现分页   参数1:当前页(从1开始)  参数2:显示多少条
            PageHelper.startPage(2, 2);
            //你只管查询所有,如何分页交给 PageHelper.startPage(2, 2);完成
            List<User> users = this.userMapper.queryAll();
            for (User user : users) {
        
                System.out.println(user);
            }
            //获取更多的分页信息
            PageInfo<User> pageInfo = new PageInfo<User>(users);
            
            System.out.println("当前页：" + pageInfo.getPageNum());
            System.out.println("当前页显示的条数：" + pageInfo.getPageSize());
            System.out.println("总页码：" + pageInfo.getPages());
            System.out.println("最后一页：" + pageInfo.getLastPage());
            System.out.println("分页相关的信息：" + pageInfo.getList());
            System.out.println("分页总条数：" + pageInfo.getTotal());
        }

## 懒加载(延迟加载） ##

**思考问题？**

一对多：我们查询用户时，要不要把关联的订单查询出来

多对一：我们查询订单时，要不要把关联的用户查询出来

**什么是懒加载？**

就是在需要它的时候才加载，不需要的话就不加载

**使用场景**

一对多，多对多 通常采用延迟加载

一对一，多对一 通常采用立即加载

### 配置懒加载 ###

### 一对多 collection延迟加载 ###

> 一对多：我们查询用户时，要不要把关联的订单查询出来

@Data
    public class User {
        
        private Long id;
        private String userName;
        private String password;
        private String name;
        private Integer age;
        private Integer sex;
        private Date birthday;
        private Date created;
        private Date updated;
        /*在一方添加多方对象*/
        private List<Order> orders;
    }

public List<User> queryAllUser(Integer id);
    public Order      queryOrderById(Integer id);

<resultMap id="userByIdMap" type="User" autoMapping="true">
    
    <collection property="orders" column="id" 			      select="cn.yanqi.mapper.UserMapper.queryOrderById" ofType="Order" autoMapping="true"/>
    </resultMap>
    
    <select id="queryAllUser" resultMap="userByIdMap">
    	select * from tb_user where id = #{id}
    </select>
    
    <select id="queryOrderById" resultType="Order">
    	select * from tb_order where id = #{id}
    </select>

@Test
        public void queryUserById(){
        
            List<User> list = this.userMapper.queryAllUser(2);
            // list.forEach(System.out::println); 需要时才会加载order
        }

### 多对一 **association** 延迟加载 ###

> 多对一：我们查询订单时，要不要把关联的用户查询出来

@Data
    public class Order {
        
        private Integer id;
        private Long userId;
        private String orderNumber;
        
        //一对一：添加User对象
        private User user;
    
        //一对多：添加Orderdetail
        private List<Orderdetail> orderdetails;
    }

public Order queryOrderById2(Integer id);

<resultMap id="OrderResultMap" type="Order" autoMapping="true">
    <id column="id" property="id"/>
    
    <association property="user" javaType="User" column="id" select="queryUserById" fetchType="lazy" autoMapping="true"/>
    </resultMap>
    
    
    <select id="queryOrderById2" resultMap="OrderResultMap">
    	select * from tb_order where id = #{id}
    </select>
    
    
    <select id="queryUserById" resultType="User">
    	select * from tb_user where id = #{id}
    </select>

@Test
        public void queryOrderById2(){
        
            Order order = this.userMapper.queryOrderById2(2);
            User user = order.getUser();//需要时才加载user
            System.out.println(user);
        }

## 逆向工程 ##

我们之前都是根据数据库中表的字段来完成实体类的书写，再mapper接口和对应的sql映射文件，这此常规的操作是不是很重复，其实这些东西可以自动生成。以下就是告拆大家如何快速实现CRUD操作：

### mybatis-generator ###

#### 添加插件 ####

*  **pom.xml**

<build>
        <plugins>
            
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.2</version>
                <configuration>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
    
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>5.1.16</version>
                    </dependency>
                </dependencies>
            </plugin>
        </plugins>
    </build>

#### 配置文件 ####

![在这里插入图片描述][db915d058ead46b194c1b88dbe3d9198.png_pic_center]

*  **datasource.properties**

db.driverClassName=com.mysql.jdbc.Driver
    db.url=jdbc:mysql:///mybatis?characterEncoding=utf-8
    db.username=root
    db.password=root

*  **generatorConfig.xml**

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE generatorConfiguration
          PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
          "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
    
    <generatorConfiguration>
       
       <properties resource="datasource.properties"></properties>
    
       
       
    
       
       
       
       <context id="default" targetRuntime="MyBatis3">
    
          
          <commentGenerator>
             <property name="suppressDate" value="true" />
             
             <property name="suppressAllComments" value="true" />
          </commentGenerator>
    
          
          <jdbcConnection driverClass="${db.driverClassName}"
                      connectionURL="${db.url}"
                      userId="${db.username}"
                      password="${db.password}">
          </jdbcConnection>
    
          
          <javaTypeResolver>
             
             
             
             <property name="forceBigDecimals" value="false" />
          </javaTypeResolver>
    
          
          
          <javaModelGenerator targetPackage="cn.yanqi.pojo"
                         targetProject=".\src\main\java">
             
             <property name="enableSubPackages" value="false" />
             
             <property name="constructorBased" value="true" />
             
             <property name="trimStrings" value="true" />
             
             <property name="immutable" value="false" />
          </javaModelGenerator>
    
          
          <sqlMapGenerator targetPackage="mappers"
                       targetProject=".\src\main\resources">
             
             <property name="enableSubPackages" value="false" />
          </sqlMapGenerator>
    
          
          <javaClientGenerator type="XMLMAPPER"
                          targetPackage="cn.yanqi.dao" targetProject=".\src\main\java">
             
             <property name="enableSubPackages" value="false" />
          </javaClientGenerator>
    
          <table tableName="tb_user" domainObjectName="User"
                enableCountByExample="false" enableUpdateByExample="false"
                enableDeleteByExample="false" enableSelectByExample="false"
                selectByExampleQueryId="false">
          </table>
          <table tableName="tb_order" domainObjectName="Order"
                enableCountByExample="false" enableUpdateByExample="false"
                enableDeleteByExample="false" enableSelectByExample="false"
                selectByExampleQueryId="false">
          </table>
          <table tableName="tb_orderdetail" domainObjectName="Orderdetail"
                enableCountByExample="false" enableUpdateByExample="false"
                enableDeleteByExample="false" enableSelectByExample="false"
                selectByExampleQueryId="false">
          </table>
          <table tableName="tb_item" domainObjectName="Item"
                enableCountByExample="false" enableUpdateByExample="false"
                enableDeleteByExample="false" enableSelectByExample="false"
                selectByExampleQueryId="false">
          </table>
       </context>
    </generatorConfiguration>

### IDEA插件代码生成器 ###

Easy Code的代码生成器，从实体类到controller service dao sql都给生成好，很方便

#### IDEA连接数据库 ####

![在这里插入图片描述][57095406122b4a448dfa23aaf8da57ab.png_pic_center]

#### 下载插件 ####

![在这里插入图片描述][92f1cece1de641419e6ed2fcf0e26b5d.png_pic_center]

#### 生成代码 ####

![在这里插入图片描述][d813f64fef4c41829fabaad8244be3c4.png_pic_center]

false">

[00912ecbd5094ccca659a93a2edd1768.png_pic_center]: https://img-blog.csdnimg.cn/00912ecbd5094ccca659a93a2edd1768.png#pic_center
[d6e957aecdb349ec9ded267f6c7551bd.png_pic_center]: https://img-blog.csdnimg.cn/d6e957aecdb349ec9ded267f6c7551bd.png#pic_center
[d0366d46a777486c8f98c8b2bba0294d.png_pic_center]: https://img-blog.csdnimg.cn/d0366d46a777486c8f98c8b2bba0294d.png#pic_center
[db915d058ead46b194c1b88dbe3d9198.png_pic_center]: https://img-blog.csdnimg.cn/db915d058ead46b194c1b88dbe3d9198.png#pic_center
[57095406122b4a448dfa23aaf8da57ab.png_pic_center]: https://img-blog.csdnimg.cn/57095406122b4a448dfa23aaf8da57ab.png#pic_center
[92f1cece1de641419e6ed2fcf0e26b5d.png_pic_center]: https://img-blog.csdnimg.cn/92f1cece1de641419e6ed2fcf0e26b5d.png#pic_center
[d813f64fef4c41829fabaad8244be3c4.png_pic_center]: https://img-blog.csdnimg.cn/d813f64fef4c41829fabaad8244be3c4.png#pic_center