The name of the dt_ultimo_acesso column was not found in this ResultSet

Question

The name of the dt_ultimo_acesso column was not found in this ResultSet

Navigation

#1 by (5 votes)

9

I'm deploying a method by passing an id parameter:

@RequestMapping(method = RequestMethod.GET, value = "/entidadesUsuario/{id}", produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<Collection<Usuarios>> buscarEntidadesUsuario(@PathVariable Long id) {


        Collection<Usuarios> entidadesBuscados = uService.buscarUsuarioNasDuasTabelas(id);
        return new ResponseEntity<>(entidadesBuscados, HttpStatus.OK);

    }

What to look for in a service class:

 public Collection<Usuarios> buscarUsuarioNasDuasTabelas(Long idUsuLogado) {
        return uRepository.buscarPorUsuarioLogado(idUsuLogado);
    }

Then in the repository class:

 @Query(nativeQuery=true, value="SELECT enad.id_entidade,\r\n" + 
            "       enad.id_sistema,\r\n" + 
            "       NULL id_pagina,\r\n" + 
            "       enad.id_usuario,\r\n" + 
            "       1 flag_consultar,\r\n" + 
            "       1 flag_inserir,\r\n" + 
            "       1 flag_editar,\r\n" + 
            "       1 flag_excluir,\r\n" + 
            "       1 flag_imprimir\r\n" + 
            "  FROM glb.entidades_administradores enad\r\n" + 
            " WHERE enad.id_usuario  = :parametroId\r\n" + 
            "UNION\r\n" + 
            "SELECT pepg.id_entidade,\r\n" + 
            "       pags.id_sistema,\r\n" + 
            "       pepg.id_pagina,\r\n" + 
            "       pepg.id_usuario,\r\n" + 
            "       pepg.flag_consultar,\r\n" + 
            "       pepg.flag_inserir,\r\n" + 
            "       pepg.flag_editar,\r\n" + 
            "       pepg.flag_excluir,\r\n" + 
            "       pepg.flag_imprimir\r\n" + 
            "  FROM glb.permissoes_paginas pepg,\r\n" + 
            "       glb.paginas            pags\r\n" + 
            " WHERE pepg.id_pagina   = pags.id_pagina\r\n" + 
            "   AND pepg.id_usuario  = :parametroId")
    public Collection<Usuarios> buscarPorUsuarioLogado(@Param("parametroId") Long idUsuLogado);

And so I'm having this error:

2017-10-23 09:45:34.025  WARN 7104 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42703
2017-10-23 09:45:34.025 ERROR 7104 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : A nome da coluna dt_ultimo_acesso não foi encontrado neste ResultSet.
2017-10-23 09:45:34.038 ERROR 7104 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [SELECT enad.id_entidade,
       enad.id_sistema,
       NULL id_pagina,
       enad.id_usuario,
       1 flag_consultar,
       1 flag_inserir,
       1 flag_editar,
       1 flag_excluir,
       1 flag_imprimir
  FROM glb.entidades_administradores enad
 WHERE enad.id_usuario  = ?
UNION
SELECT pepg.id_entidade,
       pags.id_sistema,
       pepg.id_pagina,
       pepg.id_usuario,
       pepg.flag_consultar,
       pepg.flag_inserir,
       pepg.flag_editar,
       pepg.flag_excluir,
       pepg.flag_imprimir
  FROM glb.permissoes_paginas pepg,
       glb.paginas            pags
 WHERE pepg.id_pagina   = pags.id_pagina
   AND pepg.id_usuario  = ?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query] with root cause
org.postgresql.util.PSQLException: A nome da coluna dt_ultimo_acesso não foi encontrado neste ResultSet.
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2727) ~[postgresql-9.3-1100-jdbc41.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getTimestamp(AbstractJdbc2ResultSet.java:2628) ~[postgresql-9.3-1100-jdbc41.jar:na]
    at org.hibernate.type.descriptor.sql.TimestampTypeDescriptor$2.doExtract(TimestampTypeDescriptor.java:76) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:224) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:300) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2790) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1729) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1655) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.getRow(Loader.java:1544) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:727) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.processResultSet(Loader.java:972) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:930) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2617) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2600) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]

The issue is that if I run SQL directly on the database it runs without errors.

I know it has something to do with alias but I do not know how to solve it.

Class Usuarios :

@Entity
@XmlRootElement
@Table(name="usuarios" , schema="glb")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "idUsuario", scope= Usuarios.class)    
public class Usuarios implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id_usuario")
    private Long idUsuario;



    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    private String login;


    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 50)
    private String nome;


    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 15)
    private String senha;


    // @Pattern(regexp="[a-z0-9!#$%&'*+/=?^_'{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_'{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?", message="E-mail inválido")//if the field contains email address consider using this annotation to enforce field validation
    @Size(max = 50)
    private String email;

    @Column(name = "recebe_novidades")
    private Integer recebeNovidades;

    @Basic(optional = false)
    @NotNull
    @Column(name = "id_entidade_ultimo_acesso")
    private long idEntidadeUltimoAcesso;

    @Basic(optional = false)
    @NotNull
    @Column(name = "id_ultimo_sistema_acesso")
    private long idUltimoSistemaAcesso;

    @Basic(optional = false)
    @NotNull
    @Column(name = "dt_ultimo_acesso")
    @Temporal(TemporalType.TIMESTAMP)
    private Date dtUltimoAcesso;

    @Column(name = "foto_usuario")
    private BigInteger fotoUsuario;

    @Basic(optional = false)
    @NotNull
    @Column(name = "flag_administrador")
    private int flagAdministrador;



    @OneToMany(cascade = CascadeType.ALL, mappedBy = "usuarios")
    private Collection<PermissoesPaginas> permissoesPaginasCollection;


    @OneToMany(cascade = CascadeType.ALL, mappedBy = "usuarios")
    private Collection<EntidadesAdministradores> entidadesAdministradoresCollection;


    public Collection<PermissoesPaginas> getPermissoesPaginasCollection() {
        return permissoesPaginasCollection;
    }

    public void setPermissoesPaginasCollection(Collection<PermissoesPaginas> permissoesPaginasCollection) {
        this.permissoesPaginasCollection = permissoesPaginasCollection;
    }

    public Collection<EntidadesAdministradores> getEntidadesAdministradoresCollection() {
        return entidadesAdministradoresCollection;
    }

    public void setEntidadesAdministradoresCollection(
            Collection<EntidadesAdministradores> entidadesAdministradoresCollection) {
        this.entidadesAdministradoresCollection = entidadesAdministradoresCollection;
    }

    public Usuarios() {
    }

   }

sql java spring jpa resultset

asked by anonymous 20.10.2017 / 17:27

1 answer

Configure Cronjob to run every 5 minutes when it is between 5 to 20 hours What major precautions should I keep in mind for my application to work correctly on different browsers?

score 5 · Accepted Answer

Your query has the following fields in ResultSet (which is the object that represents the results of executing query): id_entidade , id_sistema , id_pagina , id_usuario , flag_consultar , flag_inserir , flag_editar , flag_excluir , flag_imprimir .

Your Usuarios v is already mapped to the following fields: id_usuario , login , nome , senha , email , recebe_novidades , id_entidade_ultimo_acesso , id_ultimo_sistema_acesso dt_ultimo_acesso , foto_usuario , flag_administrador .

Note that the set of fields listed is quite different. In fact, the only field in common between the two lists is id_usuario . However, you put this query in an annotation of a method that returns Collection<Usuarios> . That is, Hibernate will expect that when executing this query , in each line of ResultSet there will be a record whose fields can be used to construct an instance of Usuarios .

The process used to construct an instance of some entity (in this case Usuarios ) from a ResultSet line is done by matching the column names of ResultSet with the names of columns mapped to the entity. That is, a xpto of ResultSet field corresponds to the xpto field of the entity. A abc field of ResultSet corresponds to the abc field of the entity, and so on.

However, because the query fields do not match the mapped fields in the entity, Hibernate can not know how to convert rows from ResultSet to entities of type Usuario , so it gives this error. The error occurs in the dt_ultimo_acesso field only because this is the first field that Hibernate attempted to read from ResultSet , but the error could occur in any field.

You do not know exactly what your problem is, but it should be one of these:

The query is correct, but the return type of the method is wrong. The return type would be Collection of something other than Usuarios . One guess I have is that maybe it should be Collection<PermissoesPaginas> .
The query is wrong, but the return type is correct. In this case, you have to arrange the query to bring the same fields that are mapped to Usuarios .