Spring Boot Jpa JPQL 选择特定列以外的列

Question

例如，我有两个实体 OneToOne协会。

@Entity
class Entity1(
    @Column val columnToSelect1: String,
    @Column val columnToSelect2: String,
    @Column val columnToSelect3: String,
    @Column val columnToSelect4: String,
    @Column val columnToSelect5: String,
    @Column val columnToSelect6: String,
    @Column val columnToSelect7: String,
    @Column val columnToSelect8: String,
    @Column val columnToSelect9: String,
    @Column val columnToSelect10: String,


    @OneToOne
    @JoinColumn
    val columnNotToSelect: Entity2
)

而且，有很多时候我想选择除 val columnNotToSelect: Entity2 之类的特定列之外的其他列。 .
那是因为选择 Entity2将导致触发另一个并不总是需要的查询。

现在，我正在实现这样的要求。

interface Entity1Getter {
    fun getColumnToSelect1(): String
    fun getColumnToSelect2(): String
    fun getColumnToSelect3(): String
    ...
}

interface Entity1CrudRepository : CrudRepository<Entity1, UUID> {
    // select all columns
    fun findAll(): List<Entity1>

    // select all columns except columnNotToSelect
    @Query(
        "SELECT " +
        "e.columnToSelect1 as columnToSelect1" +
        "e.columnToSelect2 as columnToSelect2" +
        "e.columnToSelect3 as columnToSelect3" +
        "e.columnToSelect4 as columnToSelect4" +
        "e.columnToSelect5 as columnToSelect5" +
        "e.columnToSelect6 as columnToSelect6" +
        "e.columnToSelect7 as columnToSelect7" +
        "e.columnToSelect8 as columnToSelect8" +
        "e.columnToSelect9 as columnToSelect9" +
        "e.columnToSelect10 as columnToSelect10" +
        "FROM Entity1 e"
    )
    fun findAllExceptOneColumn(): List<Entity1Getter>
}

我必须排列我要查询的所有列，效率非常低。

题

有什么不同的方法吗？选择特定列除外 ?

或者，是否有任何不同的方式来不选择关联列 ? (不触发另一个查询)

谢谢 :D

Answer 1

JPA 世界中所需的行为称为 EntityGraph .您可以创建一个特定的实体图(或一组图，如果需要多个)。然后标记一个特定的查询方法来使用这个实体图。作为返回结果，您将获取原始实体，但不在图中的所有其他属性将处于 LAZY 获取模式。

例如:

@NamedEntityGraph(
    name = "Entity1.exceptColumn", 
    attributeNodes = {
        @NamedAttributeNode("columnToSelect1"), 
        @NamedAttributeNode("columnToSelect2"), 
        @NamedAttributeNode("columnToSelect3"), 
        @NamedAttributeNode("columnToSelect4"), 
        @NamedAttributeNode("columnToSelect5"), 
        @NamedAttributeNode("columnToSelect6"), 
        @NamedAttributeNode("columnToSelect7"), 
        @NamedAttributeNode("columnToSelect8"), 
        @NamedAttributeNode("columnToSelect9"), 
        @NamedAttributeNode("columnToSelect10")
    }
)
@Entity
class Entity1(
    @Column val columnToSelect1: String,
    @Column val columnToSelect2: String,
    @Column val columnToSelect3: String,
    @Column val columnToSelect4: String,
    @Column val columnToSelect5: String,
    @Column val columnToSelect6: String,
    @Column val columnToSelect7: String,
    @Column val columnToSelect8: String,
    @Column val columnToSelect9: String,
    @Column val columnToSelect10: String,

    @OneToOne
    @JoinColumn
    val columnNotToSelect: Entity2
)

然后在您的数据存储库中:

interface Entity1CrudRepository : CrudRepository<Entity1, UUID> {
    // select all columns
    fun findAll(): List<Entity1>

    // select all columns except columnNotToSelect
    @EntityGraph(value = "Entity1.exceptColumn")
    @Query("SELECT e FROM Entity1 e ")
    fun findAllExceptOneColumn(): List<Entity1>

}

注意:您使用的是相同的 Entity1类，但实体图外的属性以 LAZY fetch 模式标记。

实现此目的的另一种方法是使用投影查询，在这种情况下，您正在创建一个 DTO，并为此编写一个查询:

data class Entity1Projection (
    val columnToSelect1: String,
    val columnToSelect2: String,
    val columnToSelect3: String,
    val columnToSelect4: String,
    val columnToSelect5: String,
    val columnToSelect6: String,
    val columnToSelect7: String,
    val columnToSelect8: String,
    val columnToSelect9: String,
    val columnToSelect10: String
)

然后在您的存储库中，您可以定义以下内容:

interface Entity1CrudRepository : CrudRepository<Entity1, UUID> {
    // select all columns
    fun findAll(): List<Entity1>

    // select all columns except columnNotToSelect
    @Query("""
    SELECT new fully.qualified.package_name.Entity1Projection(
        e.columnToSelect1, 
        e.columnToSelect2, 
        e.columnToSelect3, 
        e.columnToSelect4, 
        e.columnToSelect5, 
        e.columnToSelect6, 
        e.columnToSelect7, 
        e.columnToSelect8, 
        e.columnToSelect9, 
        e.columnToSelect10
    ) 
    FROM Entity1 e """)
    fun findAllExceptOneColumn(): List<Entity1Projection>

}

每种方法都有其优点和缺点，您可以决定使用哪种方法。