Spring Boot Jpa JPQL Selecting columns except specific columns

Question

For example, I have two entities with a OneToOne association.

@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
)

And, there are many times when I want to select except specific columns like val columnNotToSelect: Entity2.
That's because selecting Entity2 will cause trigger another query that is NOT always required.

For now, I'm implementing that requirement like this.

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>
}

It's quite inefficient that I have to arrange all columns I want to query.

Question

1. Are there any different ways to select except specific columns?
2. Or, are there any different ways to not to select associated columns? (not to trigger another query)

Thx :D

Answer 1

The required behaviour in JPA world is called EntityGraph. You can create a specific entity graph (or set of graphs if multiple are required). And then mark a specific query method to use this entity graph. As a return result you will fetch the original entity, but all other properties, which are not in graph will be in LAZY fetch mode.

For example:

@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
)

And then in your data repository:

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>

}

Note: you are using the same Entity1 class, but properties out of entity graph are marked in LAZY fetch mode.

Other way of achieving this is to use a projection query, in this case you are creating a DTO, and write a query for this:

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
)

And then in your repository you can define following:

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>

}

Each of the approaches has its pros and cons, you decide which to use.