Use SELECT NEW JPQL to populate DTO in an @OneToMany relationship

1

Talk to people !! I'm trying to popular my DTO object, whose main object has a OneToMany relationship. Here is the code for your observations:

## Mapped Object:

public class Usuario implements Serializable, EntidadeBase {

private static final long serialVersionUID = -8398635491226829486L;

@Id @GeneratedValue(
        strategy=GenerationType.SEQUENCE, 
        generator="usuario_seq")
private Long id;

@Column(length=80, nullable=false)
private String nmUsuario;

@Column(length=80, nullable=false)
private String dsEmail;

@Column(nullable=false, columnDefinition="bit(1) default 0")
private Boolean flAtivo;

**@OneToMany(mappedBy="usuario", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
private List<Agendamento> listaAgendamentos;**

@ManyToOne
@JoinColumn(name="plano_fk", referencedColumnName="id", nullable=true)
private Plano plano;

public Usuario() {
    listaAgendamentos = new ArrayList<>();
}
...

## DTO with builders:

public class UsuarioDTO implements Serializable {

private static final long serialVersionUID = 8399655567722875347L;

private Long id;
private String nmUsuario;
private String dsEmail;
private Boolean flAtivo;
private Collection<AgendamentoDTO> listaAgendamentos;

public UsuarioDTO(Long id, String nmUsuario, String dsEmail, Boolean flAtivo, **Collection<AgendamentoDTO> listaAgendamentos**) {
    super();
    this.id = id;
    this.nmUsuario = nmUsuario;
    this.dsEmail = dsEmail;
    this.flAtivo = flAtivo;
    //this.listaAgendamentos = listaAgendamentos;
    adicionarListaAgendamento(listaAgendamentos);
}

public UsuarioDTO() {
    listaAgendamentos = new ArrayList<>();
}
... 

## Query for popular DTO.

public UsuarioDTO getByEmail(String email){

    EntityManager em = JpaUtil.getEntityManager();
    UsuarioDTO dto = null;

    StringBuffer jpql = new StringBuffer()
    .append("select new br.com.marecrescente.dto.UsuarioDTO(user.id, user.nmUsuario, user.dsEmail, user.flAtivo, **user.listaAgendamentos**) "
            + "from Usuario user where user.dsEmail= :dsEmail");
    TypedQuery<UsuarioDTO> query = em.createQuery(jpql.toString(), UsuarioDTO.class);
    query.setParameter("dsEmail", email);

    List<UsuarioDTO> usuarios = query.getResultList();
    if(!usuarios.isEmpty()){
        dto = usuarios.get(0);
    }   

    return dto;
}

## Error while executing List users = query.getResultList ();

  

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:   You have an error in your SQL syntax; check the manual that   correspond to your MariaDB server version for the right syntax to use   near 'as col_4_0_ from User usuario0_ inner join Scheduling   list on 1 user on at 1 at   sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method)     at sun.reflect.NativeConstructorAccessorImpl.newInstance (Unknown   Source) at   sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Unknown   Source) at java.lang.reflect.Constructor.newInstance (Unknown Source)

I have searched everywhere and no answer. If you can help me, I'll be very grateful. Abs.

    
asked by anonymous 24.04.2016 / 14:31

0 answers