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

Question

我有一个使用JPA hibernate的spring boot项目。

我试图创建一个自定义查询，将选择的结果插入表中。我该怎么做？

这是我的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数据存储库

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