query executes in the database but in the project points error

Asked

Viewed 48 times

4

I have a query native:

SELECT dist.nome Distrito,
    enti.nome Entidade,
    dist.id_distrito,
    dist.codigo_dne,
    dist.id_entidade,
    dist.id_municipio,
    dist.id_uf,
    dist.flag_ativo,
    muni.nome Municipio,
    unfe.nome UF
FROM glb.distritos  dist,
    glb.entidades  enti,
    glb.municipios muni,
    glb.ufs        unfe
WHERE dist.id_entidade  = enti.id_entidade
AND dist.id_municipio = muni.id_municipio
AND muni.id_uf        = unfe.id_uf

I tried to use in a JPA/Hibernate project using the annotation: 

 ´nativeQuery=true´

And it didn’t work in the project, this error appeared in the log:

A nome da coluna nome não foi encontrado neste ResultSet.

But by running the query in the Postgres database it brings the information.

Error appears when I make a screen script request 

$http({
            method : 'GET',
            url : 'http://localhost:8080/user/distritos'
        }).then(function(response) {
            $scope.distritos = response.data;

        }, function(response) {
            console.log(response.data);
            console.log(response.status);
        });

for the API In API I have the method 

@RequestMapping(method = RequestMethod.GET, value = "/distritos", produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<Collection<Distritos>> buscarTodosDistritos() {
        Collection<Distritos> distritosBuscados = distritosService.buscarFiltro();
            return new ResponseEntity<>(distritosBuscados, HttpStatus.OK);
    }

in the method distritosService.buscarFiltro(); 

@Query( nativeQuery=true,  value="SELECT dist.nome Distrito, dist.id_distrito, dist.id_entidade, dist.id_municipio, dist.id_uf, dist.codigo_dne, dist.flag_ativo,  enti.nome Entidade, muni.nome Municipio, unfe.nome UF FROM glb.distritos  dist, glb.entidades  enti, glb.municipios muni, glb.ufs unfe WHERE dist.id_entidade  = enti.id_entidade AND dist.id_municipio = muni.id_municipio AND muni.id_uf = unfe.id_uf ")
    public  Collection<Distritos>  buscarFiltro();
sql postgresql hibernate jpa jpql

  • Could put the sector of the code it returns this error?

  • @Felipeavelar I edited the question..

  • There really is no column nome in the ResultSet, since you’re using aliases for them: dist.nome Distrito, enti.nome Entidade, etc. Just to see this use only dist.nome

  • 1

    @Brunocésar you were right. It worked. Posta como resposta.

1 answer

1


Your query is yes valid, as you yourself noted when running it directly on an SQL client.

The problem is you’re using aliases in the consultation, exactly in the calls nome, naming them as Distrito, Entidade, Municipio and UF. When the preview JPA tries to do the Binding of ResultSet for the entity it finds no column called nome, on behalf of output name.

In your case, just change the query to something like this:

SELECT dist.nome,
    enti.nome Entidade,
    dist.id_distrito,
    dist.codigo_dne,
    dist.id_entidade,
    dist.id_municipio,
    dist.id_uf,
    dist.flag_ativo,
    muni.nome Municipio,
    unfe.nome UF
FROM glb.distritos  dist,
    glb.entidades  enti,
    glb.municipios muni,
    glb.ufs        unfe
WHERE dist.id_entidade  = enti.id_entidade
AND dist.id_municipio = muni.id_municipio
AND muni.id_uf        = unfe.id_uf

This will make it no ResultSet have the column called nome, note that we have only removed the alias of one of the columns that was originally called nome.

Browser other questions tagged sql postgresql hibernate jpa jpql

You are not signed in. Login or sign up in order to post.