The name of the dt_ultimo_access column was not found in this Resultset

Asked

Viewed 1,020 times

9

I’m deploying a method 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);

    }

Searching in a service class:

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

And 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 then I’m having this mistake:

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 point is that if I run SQL directly in the database it runs error-free.

I know it has something to do with alias but I don’t know how to solve....

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() {
    }

   }
java sql jpa spring resultset

  • glb is a schema? Also add the DDL of entidades_administradores. It may not have the field in it, but Usuarios should wait for you.

  • Yes.. glb is a schema

  • Please add the DDL of entidades_administradores and the code of Usuarios, would be possible?

  • No offense taken, but I don’t see any asterisks in the columns, and the date column is not explicit in the query

  • Without having the class code Usuarios it is difficult to answer this question.

  • @Victorstafusa edited the question and put the user class

  • I already gave an answer, but just to be sure, what’s the class code like PermissoesPaginas and EntidadesAdministradores?

  • @Victorstafusa simmm

Show 3 more comments

1 answer

5


Your query has the following fields in ResultSet (which is the object representing the results obtained from the implementation of query): id_entidade, id_sistema, id_pagina, id_usuario, flag_consultar, flag_inserir, flag_editar, flag_excluir, flag_imprimir.

Already its entity Usuarios is mapped with 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 the id_usuario. However, you put this query in an annotation of a method that returns Collection<Usuarios>. I mean, Hibernate will expect that by executing this query, on 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 your case Usuarios) from a line of ResultSet is done by matching the names of the columns of the ResultSet with the names of columns mapped in the entity. That is, a field xpto of ResultSet corresponds to the field xpto of the entity. A field abc of ResultSet corresponds to the field abc of the entity, and so on.

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

You can’t know exactly what the solution to your problem is, but it should be one of those:

  • To query is right, but the return type of the method is wrong. The return type would be a Collection of something other than Usuarios. A hunch I have is that maybe it should be Collection<PermissoesPaginas>.

  • To query is wrong, but the return type is correct. In this case, you have to arrange the query for it to bring the same fields that are mapped to Usuarios.

  • Both the query how much the type of return is wrong.

Browser other questions tagged java sql jpa spring resultset

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