在Spring Data JPA中获取第一行

huangapple
go评论104阅读模式
英文:

Getting first row in Spring Data JPA

问题

我正在尝试实现一个与以下逻辑等效的简单查询：

select * from (
    select C.*, Row_number() OVER ( partition BY B.b_id ORDER BY C.c_id DESC ) as rn
    from C
    join B on B.b_id = C.b_id
    join A on A.a_id = B.a_id
    where A.other_field = 1234
) as subquery where rn = 1

简言之，有1-N关系A -> B和B -> C；对于给定的实体A，对于每个B，获取最后一个C。应该可以实现一个本地查询，就像这个答案中所述，但我希望有更好的方法。这是唯一的方法吗，就像这个旧答案所说的那样？

英文:

I'm trying to implement a simple query that is logically equivalent to

select * from (
	select C.*, Row_number() OVER ( partition BY B.b_id ORDER BY C.c_id DESC ) as rn
	from C
	join B on B.b_id = C.b_id
	join A on A.a_id = B.a_id
	where A.other_field = 1234
) as subquery where rn = 1

In words: there are 1-N relations A -> B and B -> C; given an entity A, for each B get the last C. It should be possible to implement a native query, as in this answer, but I was hoping for a better approach. Is this the only way, as this old answer said?

答案1

得分: 1

好的，这是我为你问题找到的解决方案，但没有使用窗口函数。

如果我正确理解你的问题，你想要检索每个不同B组的最大C，连接A是为了过滤目的。

这是我写的JPQL：

SELECT c FROM C c WHERE c.c_id IN (
  SELECT MAX(aux.c_id)
  FROM A a JOIN a.classesB b JOIN b.classesC aux
  WHERE a.other_field = 1234
  GROUP BY b.b_id)

这是我写的代码来验证解决方案：

@Entity(name = "A")
public class ClassA {

    @Id
    private long a_id;
    private long other_field;
    @OneToMany(mappedBy = "classA")
    private Set<ClassB> classesB;

    /* getters and setters omitted */
}

@Entity(name = "B")
public class ClassB {

    @Id
    private long b_id;
    @ManyToOne @JoinColumn(name = "a_id")
    private ClassA classA;
    @OneToMany(mappedBy = "classB")
    private Set<ClassC> classesC;

    /* getters and setters omitted */
}

@Entity(name = "C")
public class ClassC {

    @Id
    private long c_id;
    @ManyToOne @JoinColumn(name = "b_id")
    private ClassB classB;

    /* getters and setters omitted */
}

@Stateless
public class Service {

    @PersistenceContext
    EntityManager em;

    public void execute() {
        em.createQuery("""
            SELECT c FROM C c WHERE c.c_id IN (
                SELECT MAX(aux.c_id)
                FROM A a JOIN a.classesB b JOIN b.classesC aux
                WHERE a.other_field = 1234
                GROUP BY b.b_id
            )
            """, ClassC.class)
            .getResultStream()
            .map(c -> "A: %05d B: %05d C: %05d".formatted(
                    c.getClassB().getClassA().getA_id(),
                    c.getClassB().getB_id(),
                    c.getC_id()))
            .forEach(System.out::println);
    }
}

Service.execute() 方法按照 1 中描述的理解打印结果。

现在，关于窗口函数。

首先，根据规范，你不能将子查询写入 FROM 子句。因此，即使窗口函数被接受，也不可能在 FROM 子句中的子查询中使用它们，就像你在你的例子中所做的那样。

然而，为了理解，让我们尝试执行仅具有内部查询的代码 - 没有外部查询的 WHERE 子句 - 以及其窗口函数：

/* 为了以类型化方式检索结果而创建的虚拟对象 */
public record QueryRecord(long c_id, long rn) {
}

@Stateless
public class Service {

    @PersistenceContext
    EntityManager em;

    public void execute() {
        em.createQuery("""
            SELECT NEW package.QueryRecord(c.c_id, ROW_NUMBER() OVER(PARTITION BY b.b_id ORDER BY c.c_id DESC) AS rn)
            FROM C c JOIN c.classB b JOIN b.classA a
            WHERE a.other_field = 1234
            """, QueryRecord.class)
            .getResultStream()
            .forEach(System.out::println);
    }
}

但是，执行上述代码会引发以下异常：

org.hibernate.query.sqm.StrictJpaComplianceViolation: Encountered non-compliant non-standard function call [ROW_NUMBER], but strict JPA compliance was requested; use JPA's FUNCTION(functionName[,...]) syntax name instead

在这一点上，我陷入了死胡同。我可以在 persistence.xml 中插入 <property name="hibernate.jpa.compliance.query" value="false"/> 或使用本地查询。两者都可以正常工作，但我不知道它们是否合格作为规范答案。

英文:

Alright, I guess I found a solution for your problem, but without using window functions.

If I understand your question correctly, you want to retrieve the bigest C for each group of distinct B (1) - joining with A was done for filtering purposes.

This is the JPQL I wrote:

SELECT c FROM C c WHERE c.c_id IN (
  SELECT MAX(aux.c_id)
  FROM A a JOIN a.classesB b JOIN b.classesC aux
  WHERE a.other_field = 1234
  GROUP BY b.b_id)

And this is the code I wrote to verify the solution:

@Entity(name = &quot;A&quot;)
public class ClassA {

    @Id
    private long a_id;
    private long other_field;
    @OneToMany(mappedBy = &quot;classA&quot;)
    private Set&lt;ClassB&gt; classesB;

    /* getters and setters omitted */
}

@Entity(name = &quot;B&quot;)
public class ClassB {

    @Id
    private long b_id;
    @ManyToOne @JoinColumn(name = &quot;a_id&quot;)
    private ClassA classA;
    @OneToMany(mappedBy = &quot;classB&quot;)
    private Set&lt;ClassC&gt; classesC;

    /* getters and setters omitted */
}

@Entity(name = &quot;C&quot;)
public class ClassC {

    @Id
    private long c_id;
    @ManyToOne @JoinColumn(name = &quot;b_id&quot;)
    private ClassB classB;

    /* getters and setters omitted */
}

@Stateless
public class Service {

    @PersistenceContext
    EntityManager em;

    public void execute() {
        em.createQuery(&quot;&quot;&quot;
            SELECT c FROM C c WHERE c.c_id IN (
                SELECT MAX(aux.c_id)
                FROM A a JOIN a.classesB b JOIN b.classesC aux
                WHERE a.other_field = 1234
                GROUP BY b.b_id
            )
            &quot;&quot;&quot;, ClassC.class)
            .getResultStream()
            .map(c -&gt; &quot;A: %05d B: %05d C: %05d&quot;.formatted(
                    c.getClassB().getClassA().getA_id(),
                    c.getClassB().getB_id(),
                    c.getC_id()))
            .forEach(System.out::println);
    }
}

The Service.execute() method prints the result according to the understand described in 1.

Now, about the window functions.

First of all, as per the specification, you can't write subqueries into the FROM clause. So, even if window functions were accepted, it wouldn't be possible to use them in a subquery into the FROM clause - as you did in your example.

However, for the sake of understanding, let's try to execute just the inner query - without the WHERE clause of the outer query - and its window function:

/* dummy object created to retrieve the result in a typed way */
public record QueryRecord(long c_id, long rn) {
}

@Stateless
public class Service {

    @PersistenceContext
    EntityManager em;

    public void execute() {
        em.createQuery(&quot;&quot;&quot;
            SELECT NEW package.QueryRecord(c.c_id, ROW_NUMBER() OVER(PARTITION BY b.b_id ORDER BY c.c_id DESC) AS rn)
            FROM C c JOIN c.classB b JOIN b.classA a
            WHERE a.other_field = 1234
            &quot;&quot;&quot;, QueryRecord.class)
            .getResultStream()
            .forEach(System.out::println);
    }
}

But, executing the previous code, gives the following exception:

org.hibernate.query.sqm.StrictJpaComplianceViolation: Encountered non-compliant non-standard function call [ROW_NUMBER], but strict JPA compliance was requested; use JPA&#39;s FUNCTION(functionName[,...]) syntax name instead

At this point, I was finding myself in a dead end. I could either insert &lt;property name=&quot;hibernate.jpa.compliance.query&quot; value=&quot;false&quot;/&gt; into the persistence.xml or use native queries. Both works fine, but I don't know if they would qualify as canonical answer.

答案2

得分: 0

以下是翻译好的部分：

你可以像以下这样使用选项：

@Entity
@Immutable
@Subselect(
    "SELECT " +
    "    functions.*" +
    "FROM (" +
    "    SELECT " +
    "        C.*, " +
    "        Row_number() OVER ( partition BY B.b_id ORDER BY C.c_id DESC ) as rn " +
    "    FROM " +
    "        C " +
    "     JOIN " +
    "         B " +
    "    ON " +
    "        B.b_id = C.b_id " +
    "     JOIN " +
    "         A " +
    "    ON " +
    "        A.a_id = B.a_id " +
    "    WHERE " +
    "        A.a_id = 1234 " +
    ") AS functions "
)
public class YourData {

    @Id
    private Integer propertieOfC1;

    private String propertieOfC2;

    // and more other for C table...

    private String propertieOfC3;

    private int rn;

    //getters and setters bellow
    //hashcode and equals
}

@Repository
public interface YourDataRepository extends JpaRepository<YourData, Integer> {

    YourData findByRn(int rn);
}

或者你可以创建一个数据库视图，像这样：

CREATE OR REPLACE VIEW your_functions_viewer AS
    SELECT
        functions.*
    FROM (
        SELECT
             C.*,
            Row_number() OVER ( PARTITION BY B.b_id ORDER BY C.c_id DESC ) as rn
        FROM
            C
        JOIN
            B
        ON
             B.b_id = C.b_id
        JOIN
            A
        ON
              A.a_id = B.a_id
        WHERE
             A.a_id = 1234
    ) AS functions;

然后你可以在Java中像这样使用：

@Entity
@Immutable
@Table(name = "your_functions_viewer")
public class YourData {

    @Id
    private Integer propertieOfC1;

    private String propertieOfC2;

    // and more other for C table...

    private String propertieOfC3;

    private int rn;

    //getters and setters bellow
    //hashcode and equals
}

@Repository
public interface YourDataRepository extends JpaRepository<YourData, Integer> {

    YourData findByRn(int rn);
}

更多信息可以查看 vladmihalcea 的网站。


<details>
<summary>英文:</summary>
You can do something like this options

@Entity
@Immutable
@Subselect(
"SELECT " +
" functions."
"FROM (" +
" SELECT " +
" C., " +
" Row_number() OVER ( partition BY B.b_id ORDER BY C.c_id DESC ) as rn " +
" FROM " +
" C " +
" JOIN " +
" B " +
" ON " +
" B.b_id = C.b_id " +
" JOIN " +
" A " +
" ON " +
" A.a_id = B.a_id " +
" WHERE " +
" A.a_id = 1234 " +
") AS functions "
)
public class YourData {

@Id
private Integer propertieOfC1;
private String propertieOfC2;
// and more other for C table...
private String propertieOfC3;
private int rn;
//getters and setters bellow

//hashcode and equals

}

@Repository
public interface YourDataRepository extends JpaRepository<YourData,Integer>{

YourData findByRn(int rn);

}


or you can creat a viwer on database like this

CREATE OR REPLACE VIEW your_functions_viwer AS
SELECT
functions.*
FROM (
SELECT
C.*,
Row_number() OVER ( partition BY B.b_id ORDER BY C.c_id DESC ) as rn
FROM
C
JOIN
B
ON
B.b_id = C.b_id
JOIN
A
ON
A.a_id = B.a_id
WHERE
A.a_id = 1234
) AS functions;


And then you can use on Java like this

@Entity
@Immutable
@Table(name = "your_functions_viwer")
public class YourData {

@Id
private Integer propertieOfC1;
private String propertieOfC2;
// and more other for C table...
private String propertieOfC3;
private int rn;
//getters and setters bellow

//hashcode and equals

}

@Repository
public interface YourDataRepository extends JpaRepository<YourData,Integer>{

YourData findByRn(int rn);

}


for more you can see the [vladmihalcea][1]
[1]: https://vladmihalcea.com/map-jpa-entity-to-view-or-sql-query-with-hibernate/
</details>

huangapple
  • 本文由 发表于 2023年3月3日 22:25:20
  • 转载请务必保留本文链接：https://go.coder-hub.com/75628289.html
  • java
  • jpa
  • spring-data-jpa
  • sql-server
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定