java - 自定义 SQL 查询，将 SELECT 结果插入到 Spring Boot JPA Hibernate 中的表中

Question

我有一个使用 JPA hibernate 的 Spring Boot 项目。

我正在尝试创建一个自定义查询，将 select 的结果插入表中。我该怎么做呢？

这是我的 SQL 查询:

INSERT INTO aggregateTable

  (
    SELECT
      avgSalesA,
      avgSalesB,
      salesMade,
      tableA.employeeid
    FROM

      (
        SELECT
          avg(sales) as avgSalesA,
          count(salesMadeDaily) as SalesMadeTotal,
          employeeid
        FROM companyA
        GROUP BY employeeid
      ) tableA

    INNER JOIN

    (
      SELECT
        avg(sales) as avgSalesB,
        employeeid
      FROM companyB
      GROUP BY employeeid
    ) tableB

    ON tableA.employeeid = tableB.employeeid
  )

Answer 1

您可以简单地使用 JPA:

entityManager.executeUpdate("INSERT INTO aggregateTable (Select avgSalesA, avgSalesB, 
                             salesMade, tableA.employeeid FROM 
                             (SELECT avg(sales) as avgSalesA, count(salesMadeDaily) as 
                             SalesMadeTotal, employeeid FROM companyA GROUP BY 
                             employeeid) tableA INNER JOIN (SELECT 
                             avg(sales) as avgSalesB, employeeid FROM companyB group 
                             by employeeid) 
                             tableB ON tableA.employeeid = tableB.employeeid)");

或者如果您使用 Spring Data 存储库

@Modifying
@Query("<your insert query", nativeQuery=true)
public int insert();