CODEWITHSUNDEEP
spring-bootoracle11gspring-data-jpa
Nacho Escursell
Nacho Escursell

Reputation: 161

Spring Boot pageable count query error with Oracle

I'm having a trouble with Spring JPA with oracle.

First of all, I need to make a list (pageable) of elements, but I onlyhave a view to do all things.

That's the reason I need to use "distinct" clause on my SQL statement. Client don't give me other way to get the data.

The class:

@Entity
@IdClass(TRN04MunicipalityIdEntity.class)
@Table(name = "VISTA_EVENTOS_OGP")
@NamedQuery(name = "TRN04MunicipalityEntity.findId",
            query = "select distinct e from TRN04MunicipalityEntity e where e.idMunicipio = ?1 and e.idProvincia = ?2"
            + " order by COD_PROV_NORA")
@NamedQuery(name = "TRN04MunicipalityEntity.findAllOf",
            query = "select distinct e from TRN04MunicipalityEntity e where COD_MUN_NORA is not null order by COD_PROV_NORA")

public class TRN04MunicipalityEntity {

    @Id
    @Column(name = "COD_MUN_NORA", precision = 3, scale = 0)
    private BigDecimal idMunicipio;
    
    @Id
    @Column(name = "COD_PROV_NORA", precision = 3, scale = 0)
    private BigDecimal idProvincia;

    @Column(name = "MUNICIPIO_ES", length = 50, insertable = false, updatable = false)
    @Basic()
    private String municipioEs;

    @Column(name = "MUNICIPIO_EU", length = 50, insertable = false, updatable = false)
    @Basic()
    private String municipioEu;
}

When I use pagination on "findAllOf" have some weird stuff.

Pagination query it's ok (case with page 1)

select
        * 
    from
        ( select
            distinct trn04munic0_.COD_MUN_NORA as cod_mun_nora4_0_,
            trn04munic0_.COD_PROV_NORA as cod_prov_nora5_0_,
            trn04munic0_.MUNICIPIO_ES as municipio_es6_0_,
            trn04munic0_.MUNICIPIO_EU as municipio_eu7_0_ 
        from
            VISTA_EVENTOS_OGP trn04munic0_ 
        where
            COD_MUN_NORA is not null 
        order by
            COD_PROV_NORA ) 
    where
        rownum <= ?

But... Count query is meaningless

select
        count(distinct trn04munic0_.COD_MUN_NORA,
        trn04munic0_.COD_PROV_NORA) as col_0_0_ 
    from
        VISTA_EVENTOS_OGP trn04munic0_ 
    where
        COD_MUN_NORA is not null

Having two elements on count function, it's nonsense

Any ideas?

Tanks a lot

Upvotes: 0

Views: 1039

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81882

Simply specify the appropriate count query explicitly using @Query( ... countQuery="...").

Upvotes: 1

Related Questions