HQL Hibernate many tables

Question

I'm new to hibernate, and i'm trying to make a list out of 4 tables, but it's not working.

public List<DocumentoAssinanteTO> listAssinanteSemImagemByDocument(DocumentoTO documento, UsuarioDepartamentoTO ud) {
    StringBuilder hql = new StringBuilder();
    hql.append(" SELECT DA.id, ");
    hql.append(" DOC.id,");
    hql.append(" UD.id, ");
    hql.append(" D.id, ");
    hql.append(" U.id, ");
    hql.append(" U.nome,");
    hql.append(" FROM ").append(DocumentoAssinanteTO.class.getName()).append(" DA ");
    hql.append(" INNER JOIN DA.documento DOC ");
    hql.append(" INNER JOIN DA.usuarioDepartamento UD ");
    hql.append(" INNER JOIN UD.usuario              U");
    hql.append(" INNER JOIN UD.departamento         D");
    hql.append(" WHERE DOC = :idDocumento AND UD = :idUserDep ");
    hql.append(" AND U.assinatura IS NULL ");


    Query query = queryTransform(hql.toString());
    query.setLong("idDocumento", documento.getId());
    query.setLong("idUserDep", ud.getId());

    return query.list();
}

maybe it's the JOIN part, don't know if i should use INNER, LEFT or just JOIN

I want to create a hql like this sql

 SELECT docass.id_documento_assinante,doc.id,
        docass.id_user_depto,u.id,u.nome
FROM DCF_DOCUMENTO_ASSINANTE as docass
JOIN DCF_CONTENT as doc ON doc.id = docass.id_documento
JOIN DCF_USUARIO_DEPARTAMENTO as userDep ON userDep.id = docass.id_user_depto
JOIN DCF_USUARIOS as u  ON u.id = userDep.id_usuario
WHERE u.id_anexo_assinatura is null

joins explianed : http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join — dinesh707, Sep 30 '14 at 11:45
as it is not a native query have you tried it first in an sql server in a native form to see if its running?? cause joins + hql i ve had troubles before — AntJavaDev, Sep 30 '14 at 11:46
Your `DOC` is probably an object and you comparing it with long. Post error that you receive. — Aleksandr M, Sep 30 '14 at 11:54

score 0 · Answer 1 · answered Sep 30 '14 at 12:22

First of all your problem is not fully cleared, define your database models here first, and from your query i have come to know that

 hql.append(" DOC.id, ");

the last Coma will not be added here hql.append(" DOC.id ");

Here

hql.append(" FROM ").append(DocumentoAssinanteTO.class.getName()).append(" DA ");

you don't need to get name of class by calling class.getname() method you just call here the bean or name of Model.

and there is lot of problem in your inner joins.

Further if you provide me structure of your schema and models name if you are using JPA or Hibernate i will found good to help you — Irfan Nasim, Sep 30 '14 at 12:23

score 0 · Accepted Answer · answered Sep 30 '14 at 13:10

Ok, i got it working, made some adjusts

public List listAssinanteSemImagemByDocument(DocumentoTO documento) { StringBuilder hql = new StringBuilder(); hql.append(" SELECT DA.id, "); hql.append(" DOC.id,"); hql.append(" UD.id, "); hql.append(" U.id, "); hql.append(" U.nome "); hql.append(" FROM ").append(DocumentoAssinanteTO.class.getName()).append(" DA "); hql.append(" JOIN DA.documento DOC"); hql.append(" JOIN DA.usuarioDepartamento UD"); hql.append(" JOIN UD.usuario U"); hql.append(" WHERE DOC = :idDocumento "); hql.append(" AND U.assinatura IS NULL ");

    Query query = queryTransform(hql.toString());
    query.setLong("idDocumento", documento.getId());

    return query.list();

}

the real problem was here the last comma. hehe

hql.append(" U.nome,");

HQL Hibernate many tables

2 Answers2