Error trying to query via HQL

Question

Error trying to query via HQL

Asked 6 years, 3 months ago

Viewed 59 times

0

I have the following entities:

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;

import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@Entity
@Table(name="objeto")
@Inheritance(strategy=InheritanceType.JOINED)
public class Objeto implements Serializable{

    private static final long serialVersionUID = 1L;

    @Getter
    @Setter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Getter
    @Setter
    @XmlElement
    @Column(nullable = false, length = 13, unique = true)
    private String numero;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String sigla;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String nome;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String categoria;

    @Getter
    @Setter
    @XmlElement(name = "evento")
    @OneToMany(mappedBy = "objeto", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    private List<Evento> eventos;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String erro;

}

Event:

import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalTime;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.persistence.Transient;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlTransient;

import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@Entity
@Table(name = "evento")
public class Evento implements Serializable {

    private static final long serialVersionUID = 1L;

    @Getter
    @Setter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String tipo;

    @Getter
    @Setter
    @XmlElement
    @Column
    private Integer status;

    @Getter
    @Setter
    @XmlTransient
    @Column
    private LocalDate data;

    @Getter
    @Setter
    @XmlElement(name = "data")
    @Transient
    private String dataAux;

    @Getter
    @Setter
    @XmlTransient
    @Column
    private LocalTime hora;

    @Getter
    @Setter
    @XmlElement(name = "hora")
    @Transient
    private String horaAux;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String descricao;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String recebedor;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String documento;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String comentario;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String local;

    @Getter
    @Setter
    @XmlElement
    @Column
    private Integer codigo;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String cidade;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String uf;

    @Getter
    @Setter
    @XmlElement
    @OneToOne(mappedBy = "evento", cascade = CascadeType.ALL)
    private Destino destino;

    @Getter
    @Setter
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "objeto_id", referencedColumnName = "id")
    private Objeto objeto;

}

Fate:

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;

import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@AllArgsConstructor
@EqualsAndHashCode
@NoArgsConstructor
@XmlRootElement
@XmlAccessorType(XmlAccessType.FIELD)
@Entity
@Table(name = "destino")
public class Destino implements Serializable {

    private static final long serialVersionUID = 1L;

    @Getter
    @Setter
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String local;

    @Getter
    @Setter
    @XmlElement
    @Column
    private Integer codigo;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String cidade;

    @Getter
    @Setter
    @XmlElement
    @Column
    private String uf;

    @Getter
    @Setter
    @OneToOne
    @JoinColumn(name = "evento_id")
    private Evento evento;

}

I’m trying to make an appointment where I want to return a List of Objeto provided that its events do not contain an event whose description is Object delivered to the consignee. My appointment was like this:

public List<Objeto> obterObjetosPendentes() {
        List<Objeto> retorno = new ArrayList<Objeto>();
        final StringBuilder hql = new StringBuilder();
        hql.append("select objeto from Objeto objeto ");
        hql.append("inner join Evento evento where evento.descricao != :descricao");
        final Query query = this.em.createQuery(hql.toString());
        query.setParameter("descricao", "Objeto entregue ao destinatário");
        retorno = query.getResultList();
        return retorno;
    }

But I have the error in the execution:

2018-03-15 13:39:34,256 WARNING [com.roknauta.tracker.controller.ObjetoController] (default task-54) Objeto LB624873525SE já cadastrado
2018-03-15 13:39:43,047 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-54) SQL Error: 1064, SQLState: 42000
2018-03-15 13:39:43,051 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-54) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where evento1_.descricao<>'Objeto entregue ao destinatário'' at line 1
2018-03-15 13:39:43,099 ERROR [org.jboss.as.ejb3.invocation] (default task-54) WFLYEJB0034: EJB Invocation failed on component ObjetoService for method public java.util.List com.roknauta.tracker.service.ObjetoService.obterObjetosPendentes(): javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleExceptionInOurTx(CMTTxInterceptor.java:188)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:277)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:332)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:240)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:509)
    at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(

Where is the error? I thank you in advance.

1 answer

Browser other questions tagged java mysql hibernate hql

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

by Roknauta • **2,811** points · Answer 1 · 2018-03-15T17:13:44+00:00

I managed to solve, I forgot to make the relationship between Object and Event in the query. I realized this when I turned on the sql logs and saw the sql that went to the database. So, my query is now:

public List<Objeto> obterObjetosPendentes() {
        List<Objeto> retorno = new ArrayList<Objeto>();
        final StringBuilder hql = new StringBuilder();
        hql.append("select objeto from Objeto objeto ");
        hql.append("inner join objeto.eventos evento where evento.descricao != :descricao");
        final Query query = this.em.createQuery(hql.toString());
        query.setParameter("descricao", "Objeto entregue ao destinatário");
        retorno = query.getResultList();
        return retorno;
    }