query executes in bank but in project points error

Question

query executes in bank but in project points error

Navigation

#1 by (1 votes)

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 using a JPA / Hibernate project using the annotation:

 ´nativeQuery=true´

And it did not work on the project, this error appeared in the log:

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

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

The 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 the API I have the

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

asked by anonymous 05.10.2017 / 13:52

1 answer

Losing image quality when resizing in pygame How to return 2 objects at the same time in a method in Java?

score 1 · Accepted Answer

Your query is valid, as you yourself observed when you run it directly on a SQL client.

The problem is that you are using aliases in the query, exactly in nome calls, naming them as Distrito , Entidade , Municipio and UF . When provider JPA attempts to make the binding from ResultSet to the entity it does not find any columns named nome , due to 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 cause ResultSet to have the column named nome , see that we only remove the alias from one of the columns that originally was called nome .