springJPA如果利用注解的方式进行多表关联操作

前言:上一篇我写了个用JPA的Specification这个接口怎么做条件查询并且进行分页的,想学的自己去找一下

地址:springJPA动态分页

今天我们来写个利用jpa的@Query注解实现多表联合查询的demo
注意: 不建议在实际项目中用这玩意.
因为:
1. 用@Query写的sql 可读性极差,给后期维护这段代码的同学造成极大的困扰,
2. 费半天劲写出来的代码人家用mybatis在mapper.xml里面直接几行sql就搞定了,
3. 如果真要必须得用@Query 去写sql 一定加好注释,(可能也许你们这项目前期设计的时候没想好数据结构导致设计成这样)
直接上代码我懒得解释了
controller

   @PostMapping("/get-product-inst-info")
    public ProductInstDTO getProductInstDTO(@RequestBody ProParms proParms) {
        return productInstanceService.getProductInstDTO(proParms);
    }

service

   public ProductInstDTO getProductInstDTO(ProParms proParms){
      return productInstRepository.queryDefNameByIdRaw(proParms.getCategoryCode(), proParms.getProductDefId(),proParms.getId());
    }

ProductInstDTO

package com.king.alice.manage.instance.entity;

import lombok.Data;

import java.io.Serializable;

/**
 * @author 大魔王
 * @description:
 * @date 2024/4/1 14:14
 */
@Data
public class ProductInstDTO implements Serializable {
    private static final long serialVersionUID = 7177992261178275987L;
    private String instId;
    private String defName;
    private String instName;
}

repository

package com.king.alice.manage.instance.repository;

import com.king.alice.manage.instance.entity.ProParms;
import com.king.alice.manage.instance.entity.ProductInst;

import com.king.alice.manage.instance.entity.ProductInstDTO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author 大魔王
 * @description: TODO
 * @date 2024/3/21 16:51
 */
@Repository
public interface ProductInstRepository extends JpaRepository<ProductInst, String>, JpaSpecificationExecutor<ProductInst> {

    @Query(nativeQuery = true,
            value = "select a.id as instId,b.name as defName,a.name as  instName from product_inst a " +
                    "left join product_def b on a.product_def_id = b.id  where " +
                    "(:categoryCode IS NULL OR a.category_code = :categoryCode)" +
                    "AND (:productDefId IS NULL OR a.product_def_id = :productDefId)" +
                    "AND (:id IS NULL OR a.id = :id)"
    )
    List<Map<String, Object>> queryDefNameById(String categoryCode, String productDefId, String id);


    default ProductInstDTO queryDefNameByIdRaw(String categoryCode, String productDefId, String id) {
        List<Map<String, Object>> rawResults = queryDefNameById(categoryCode, productDefId, id);
        if (!rawResults.isEmpty()) {
            Map<String, Object> firstResult = rawResults.get(0);
            ProductInstDTO productInstDTO = new ProductInstDTO();
            productInstDTO.setInstId((String)firstResult.get("instid"));
            productInstDTO.setDefName((String)firstResult.get("defname"));
            productInstDTO.setInstName((String)firstResult.get("instname"));
            return productInstDTO;
        } else {
            // 返回空对象、null，或者抛出异常，根据您的业务需求决定
            return null;
        }
    }
}