Spring Data JPA--多表关联查询--实例

忘是亡心i 2022-09-14 11:07 198阅读 0赞

原文网址：[Spring Data JPA--多表关联查询--实例\_IT利刃出鞘的博客-CSDN博客][Spring Data JPA--_--_IT_-CSDN]

# 简介 #

多表联查有以下方案

1.  原生SQL
2.  JHQL
3.  Specification
4.  实体关联（不推荐）
    
    1.  见：[JPA--注解--实体关联\_IT利刃出鞘的博客-CSDN博客][JPA--_--_IT_-CSDN]
    2.  不推荐的原因：增删改查时强关联，操作不便捷
5.  手动查一个表之后，在查另一个。

对于以上四种方案，第三四种不再介绍，本文只介绍原生SQL、JHQL和Specification这三种方案。

# 公共代码 #

## 配置文件及依赖 ##

**application.yml**

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/jpa?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        username: root
        password: 222333
    
      jpa:
        # 打印sql
        show-sql: true
        # 关闭属性的延时加载
        open-in-view: false
    #    hibernate:
    #      # 是否开启自动更新数据库表结构。生产中不要开启。
    #      #   有以下几个值供选择。常用：update、none
    #      #   create：每次加载hibernate时都删除上次生成的表，再根据你的model类生成新表。即使两次没有改变也这样执行，可能导致表数据丢失。
    #      #   create-drop：每次加载hibernate时，先删除已存在的表结构再重新生成，sessionFactory一关闭，表就自动删除。
    #      #   update：第一次加载hibernate时根据model类自动建立表结构，以后加载hibernate时根据model类自动更新表结构。
    #      #           即使表结构变了但表中的行仍然存在不会删除以前的行。
    #      #           部署到服务器后，表结构是不会被马上建立起来的，是要等应用第一次运行起来后才会。
    #      #   validate：每次加载hibernate时，验证创建数据库表结构，只会和数据库中的表进行比较，不会创建新表，但是会插入新值。
    #      #   none：关闭自动更新
    #      ddl-auto: none
    #
    #      # hibernate5及之后的命名策略配置。
    #      naming:
    #        # 负责模型对象层次的处理，将对象模型处理为逻辑名称
    #        #   有以下5个值供选择：
    #        #     ImplicitNamingStrategyJpaCompliantImpl（默认值）后四者均继承自它。
    #        #     ImplicitNamingStrategyComponentPathImpl
    #        #     ImplicitNamingStrategyLegacyHbmImpl
    #        #     ImplicitNamingStrategyLegacyJpaImpl
    #        #     SpringImplicitNamingStrategy
    #        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
    #
    #        # 映射成真实的数据名称的处理，将逻辑名称处理为物理名称。
    #        #   有以下2个值供选择：
    #        #     PhysicalNamingStrategyStandardImpl：直接映射，若有@Column则以其为准。等同于之前的DefaultNamingStrategy
    #        #     SpringPhysicalNamingStrategy（默认值）：字段为小写，当有大写字母的时候会转换为分隔符号“_”。等同于之前的ImprovedNamingStrategy
    #        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    #
    #        # hibernate5之前的命名策略配置。
    #        #   有以下2个值供选择：
    #        #     DefaultNamingStrategy（默认值）：直接映射，若有@Column则以其为准。
    #        #     ImprovedNamingStrategy：字段为小写，当有大写字母的时候会转换为分隔符号“_”。
    #        # naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy

**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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.5.3</version>
            <relativePath/> 
        </parent>
        <groupId>com.example</groupId>
        <artifactId>demo_jpa</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>demo_jpa</name>
        <description>demo_jpa</description>
        <properties>
            <java.version>1.8</java.version>
        </properties>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>
            <dependency>
                <groupId>org.hibernate.javax.persistence</groupId>
                <artifactId>hibernate-jpa-2.1-api</artifactId>
                <version>1.0.2.Final</version>
            </dependency>
    
            <dependency>
                <groupId>com.github.xiaoymin</groupId>
                <artifactId>knife4j-spring-boot-starter</artifactId>
                <version>3.0.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <optional>true</optional>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                    <configuration>
                        <excludes>
                            <exclude>
                                <groupId>org.projectlombok</groupId>
                                <artifactId>lombok</artifactId>
                            </exclude>
                        </excludes>
                    </configuration>
                </plugin>
            </plugins>
        </build>
    
    </project>

## 表结构及数据 ##

DROP TABLE IF EXISTS t_user;
    DROP TABLE IF EXISTS t_order;
    
    CREATE TABLE `t_user`
    (
        `id`   bigint AUTO_INCREMENT,
        `user_name` VARCHAR(32),
        `age`  int,
        PRIMARY KEY (id)
    ) ENGINE = InnoDB;
    
    CREATE TABLE `t_order`
    (
        `id`       bigint AUTO_INCREMENT,
        `user_id`  bigint,
        `remark`  VARCHAR(128),
        PRIMARY KEY (id)
    ) ENGINE = InnoDB;
    
    INSERT INTO `t_user` values (1, 'Tony1', 21);
    INSERT INTO `t_user` values (2, 'Tony2', 22);
    INSERT INTO `t_user` values (3, 'Tony3', 23);
    INSERT INTO `t_user` values (4, 'Tony3', 24);
    
    INSERT INTO `t_order` VALUES (1, 1, "这是备注1");
    INSERT INTO `t_order` VALUES (2, 2, "这是备注2");
    INSERT INTO `t_order` VALUES (3, 2, "这是备注3");

## 代码 ##

**Entity**

package com.example.demo.business.order.entity;
    
    import lombok.Data;
    
    import javax.persistence.*;
    
    
    @Data
    @Entity
    @Table(name = "t_order")
    public class Order {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private Long userId;
    
        private String remark;
    
    }

package com.example.demo.business.user.entity;
    
    import lombok.Data;
    
    import javax.persistence.*;
    
    @Data
    @Entity
    @Table(name = "t_user")
    public class User {
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Long id;
    
        private String userName;
    
        private Integer age;
    }

# 实例：原生SQL与JHQL #

**其他网址**

[spring data jpa query inner join写法\_Guoye的专栏-CSDN博客][spring data jpa query inner join_Guoye_-CSDN]

**需求**

现在有两张表，订单表（t\_order），用户表（t\_user），两者的关联关系如下：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_17_color_FFFFFF_t_70_g_se_x_16][]

想通过用户名（t\_user.user\_name）查找其订单。

**Repository**

package com.example.demo.business.order.repository;
    
    import com.example.demo.business.order.entity.Order;
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.data.repository.query.Param;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    @Repository
    public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {
        @Query(value = "SELECT * FROM t_order, t_user " +
                " WHERE t_order.user_id = t_user.id AND " +
                " t_user.user_name = :userName", nativeQuery = true)
        List<Order> findOrderByUserNameNative(@Param("userName") String userName);
    
        @Query("SELECT o FROM Order o, User u WHERE o.userId = u.id AND u.userName = :userName")
        List<Order> findOrderByUserNameJhql(@Param("userName") String userName);
    }

**Controller**

package com.example.demo.business.order.controller;
    
    import com.example.demo.business.order.entity.Order;
    import com.example.demo.business.order.repository.OrderRepository;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.ArrayList;
    import java.util.List;
    
    @Api(tags = "订单")
    @RestController
    @RequestMapping("/order")
    public class OrderController {
        @Autowired
        private OrderRepository orderRepository;
    
        @ApiOperation("使用原生SQL")
        @PostMapping("origin")
        public void findByUserNameOrigin() {
            List<Order> orderList = orderRepository.findOrderByUserNameNative("Tony2");
            System.out.println(orderList);
        }
    
        @ApiOperation("使用JHQL")
        @PostMapping("jhql")
        public void findByUserNameJhql() {
            List<Order> orderList = orderRepository.findOrderByUserNameJhql("Tony2");
            System.out.println(orderList);
        }
    }

**测试**

**测试原生SQL**

访问：[http://localhost:8080/order/origin][http_localhost_8080_order_origin]

后端结果

Hibernate: SELECT * FROM t_order, t_user  WHERE t_order.user_id = t_user.id AND  t_user.user_name = ?
    [Order(id=2, userId=2, remark=这是备注2), Order(id=3, userId=2, remark=这是备注3)]

**测试JHQL**

访问：[http://localhost:8080/order/jhql][http_localhost_8080_order_jhql]

后端结果

Hibernate: select order0_.id as id1_0_, order0_.remark as remark2_0_, order0_.user_id as user_id3_0_ from t_order order0_ cross join t_user user1_ where order0_.user_id=user1_.id and user1_.user_name=?
    [Order(id=2, userId=2, remark=这是备注2), Order(id=3, userId=2, remark=这是备注3)]

# 实例：Specification #

**需求**

现在有两张表，订单表（t\_order），用户表（t\_user），两者的关联关系如下：

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_17_color_FFFFFF_t_70_g_se_x_16][]

想通过用户名（t\_user.user\_name）、年龄（t\_user.age）、订单备注（t\_order.remark）来查找数据。

**Controller**

package com.example.demo.business.order.controller;
    
    import com.example.demo.business.order.entity.Order;
    import com.example.demo.business.order.repository.OrderRepository;
    import com.example.demo.business.user.entity.User;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import io.swagger.models.auth.In;
    import org.apache.commons.lang3.StringUtils;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.jpa.domain.Specification;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.persistence.criteria.*;
    import java.util.ArrayList;
    import java.util.List;
    
    @Api(tags = "订单")
    @RestController
    @RequestMapping("/order")
    public class OrderController {
        @Autowired
        private OrderRepository orderRepository;
    
        @ApiOperation("Specification")
        @PostMapping("specification")
        public void findByUserNameSpecification() {
            Specification<Order> specification = buildCondition("Tony", 22, "这是备注");
            List<Order> orderList = orderRepository.findAll(specification);
            System.out.println(orderList);
        }
    
        private Specification<Order> buildCondition(String userName, Integer age, String remark) {
            //重写toPredicate方法
            return (Root<Order> root, CriteriaQuery<?> cq, CriteriaBuilder cb) -> {
    
                // t_order表中的字段
                List<Predicate> orderPredicateList = new ArrayList<>();
                if (StringUtils.isNotBlank(remark)) {
                    Predicate remarkPredicate = cb.like(root.get("remark").as(String.class), "%" + remark + "%");
                    orderPredicateList.add(remarkPredicate);
                }
    
                // t_user表中的字段
                Root<User> userRoot = cq.from(User.class);
                List<Predicate> userPredicateList = new ArrayList<>();
                if (StringUtils.isNotBlank(userName)) {
                    // 本处我都转为小写，进行模糊匹配
                    Predicate userNamePredicate = cb.like(cb.lower(userRoot.get("userName").as(String.class)),
                            "%" + userName.toLowerCase() + "%");
                    userPredicateList.add(userNamePredicate);
                }
                if (age != null) {
                    Predicate agePredicate = cb.equal(userRoot.get("age").as(Integer.class), age);
                    userPredicateList.add(agePredicate);
                }
    
                Predicate orderPredicate  = cb.and(orderPredicateList.toArray(new Predicate[0]));
                Predicate userPredicate   = cb.and(userPredicateList.toArray(new Predicate[0]));
    
                // 通过t_order.user_id和t_user.id进行联结
                Predicate middlePredicate = cb.equal(root.get("userId"), userRoot.get(("id")));
    
                return cb.and(orderPredicate, userPredicate, middlePredicate);
            };
        }
    
        // private Specification<Order> buildCondition2(String userName, Integer age, String remark) {
        //     //重写toPredicate方法
        //     return (Root<Order> root, CriteriaQuery<?> cq, CriteriaBuilder cb) -> {
        //
        //         List<Predicate> orderPredicateList = new ArrayList<>();
        //         if (StringUtils.isNotBlank(remark)) {
        //             Predicate remarkPredicate = cb.like(root.get("remark").as(String.class), remark);
        //             orderPredicateList.add(remarkPredicate);
        //         }
        //
        //         // 若用joine，则Order实体类中要有User字段，且要有@ManyToOne之类的注解
        //         Join<Order, User> userJoin = root.join("t_user", JoinType.INNER);
        //
        //         // t_user表中的字段
        //         List<Predicate> userPredicateList = new ArrayList<>();
        //         if (StringUtils.isNotBlank(userName)) {
        //             // 本处我都转为小写，进行模糊匹配
        //             Predicate userNamePredicate = cb.like(cb.lower(userJoin.get("userName").as(String.class)),
        //                     "%" + userName.toLowerCase() + "%");
        //             userPredicateList.add(userNamePredicate);
        //         }
        //         if (age != null) {
        //             Predicate agePredicate = cb.equal(userJoin.get("age").as(Integer.class), age);
        //             userPredicateList.add(agePredicate);
        //         }
        //
        //         Predicate orderPredicate = cb.and(orderPredicateList.toArray(new Predicate[0]));
        //         Predicate userPredicate  = cb.and(userPredicateList.toArray(new Predicate[0]));
        //
        //         return cb.and(orderPredicate, userPredicate);
        //     };
        // }
    }

**测试**

访问：[http://localhost:8080/order/specification][http_localhost_8080_order_specification]

后端结果

Hibernate: select order0_.id as id1_0_, order0_.remark as remark2_0_, order0_.user_id as user_id3_0_ from t_order order0_ cross join t_user user1_ where (order0_.remark like ?) and (lower(user1_.user_name) like ?) and user1_.age=22 and order0_.user_id=user1_.id
    [Order(id=2, userId=2, remark=这是备注2), Order(id=3, userId=2, remark=这是备注3)]

# 其他网址 #

[jpa Join Specification方式多对多关联查询 多表关联\_dadao2003的博客-CSDN博客][jpa Join Specification_ _dadao2003_-CSDN]  
[使用JPA Specification 多条件 子查询 组合 and or 分组 分页 排序\_BurukeYou-CSDN博客][JPA Specification _ _ _ and or _ _ _BurukeYou-CSDN]  
[SpringBoot JPA使用Specification多表查询LEFT JOIN\_小石潭记丶的博客-CSDN博客][SpringBoot JPA_Specification_LEFT JOIN_-CSDN]

[Spring Data JPA--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/120638986
[JPA--_--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/119975003
[spring data jpa query inner join_Guoye_-CSDN]: https://blog.csdn.net/aa390481978/article/details/108007130
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBASVTliKnliIPlh7rpnpg_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/50e3bcb050bb4e13aeb5eddedb1a2e87.png
[http_localhost_8080_order_origin]: http://localhost:8080/order/origin
[http_localhost_8080_order_jhql]: http://localhost:8080/order/jhql
[http_localhost_8080_order_specification]: http://localhost:8080/order/specification
[jpa Join Specification_ _dadao2003_-CSDN]: https://blog.csdn.net/dadao2003/article/details/106406476
[JPA Specification _ _ _ and or _ _ _BurukeYou-CSDN]: https://blog.csdn.net/weixin_41347419/article/details/105098113
[SpringBoot JPA_Specification_LEFT JOIN_-CSDN]: https://blog.csdn.net/qq_33371766/article/details/111083968