How to play query with Mysql queries in Hibernate?

Asked

Viewed 238 times

3

I am using JPA 2.0 and trying to play this query in Hibernate , but I am not able to visualize how to do.

I did it in Mysql language

SELECT totalassociado, 
       totalcriticado, 
       items.item_codigo_item, 
       items.item_data_item, 
       items.item_descricao, 
       items.item_status, 
       items.empresa, 
       items.usuario_nome, 
       Associado.usuario_codigo, 
       items.usuario_codigo 
FROM   (SELECT Count(item.`codigo`)          AS TotalAssociado, 
               item.`codigo_item`            AS item_codigo_item, 
               item.`data_item`              AS item_data_item, 
               item.`descricao`              AS item_descricao, 
               item.`status`                 AS item_status, 
               item.codigo_empresa, 
               usuario.`codigo`              AS usuario_codigo, 
               usuario.`nome`                AS usuario_nome, 
               usuario.`permissao`           AS usuario_permissao, 
               tecnico_item.`codigo_tecnico` AS tecnico_item_codigo_tecnico, 
               tecnico_item.`codigo_item`    AS tecnico_item_codigo_item 
        FROM   `item` item 
               INNER JOIN `tecnico_item` tecnico_item 
                       ON item.`codigo` = tecnico_item.`codigo_item` 
               INNER JOIN `usuario` usuario 
                       ON tecnico_item.`codigo_tecnico` = usuario.`codigo` 
        GROUP  BY usuario.codigo, 
                  item.codigo_empresa) AS Associado, 
       (SELECT Count(item.`codigo`)          AS TotalCriticado, 
               item.`codigo_item`            AS item_codigo_item, 
               item.`data_item`              AS item_data_item, 
               item.`descricao`              AS item_descricao, 
               item.`status`                 AS item_status, 
               usuario.`codigo`              AS usuario_codigo, 
               usuario.`nome`                AS usuario_nome, 
               usuario.`permissao`           AS usuario_permissao, 
               tecnico_item.`codigo_tecnico` AS tecnico_item_codigo_tecnico, 
               tecnico_item.`codigo_item`    AS tecnico_item_codigo_item 
        FROM   `item` item 
               JOIN `tecnico_item` tecnico_item 
                 ON item.`codigo` = tecnico_item.`codigo_item` 
               JOIN `usuario` usuario 
                 ON tecnico_item.`codigo_tecnico` = usuario.`codigo` 
        WHERE  item.status = 'CRITICADO') AS Criticado, 
       (SELECT item.`codigo`                 AS item_codigo, 
               item.`codigo_item`            AS item_codigo_item, 
               item.`data_item`              AS item_data_item, 
               item.`descricao`              AS item_descricao, 
               item.`status`                 AS item_status, 
               item.codigo_empresa           AS Empresa, 
               usuario.`codigo`              AS usuario_codigo, 
               usuario.`nome`                AS usuario_nome, 
               usuario.`permissao`           AS usuario_permissao, 
               tecnico_item.`codigo_tecnico` AS tecnico_item_codigo_tecnico, 
               tecnico_item.`codigo_item`    AS tecnico_item_codigo_item 
        FROM   `item` item 
               JOIN `tecnico_item` tecnico_item 
                 ON item.`codigo` = tecnico_item.`codigo_item` 
               JOIN `usuario` usuario 
                 ON tecnico_item.`codigo_tecnico` = usuario.`codigo`) AS items 
WHERE  Associado.usuario_codigo = items.usuario_codigo

Someone could give an idea / help?

ainda não consegui fazer a complicação está justamente em passar para jpql ou hql na hora de utilizar a sintaxe comecei assim:Query query = manager
            .createQuery(
                    "select m,d from (select m from Item m left join  m.tecnicos t "
                            + "where (m.status ='CRITICADO' or m.status ='JUSTIFICADO') and t.codigo=:codigo ) as m,"
                            + "(select d from Item d join d.tecnicos t where t.codigo=:codigo) as d",
                    Object[].class).setParameter("codigo", 3L);

    List<Object[]> items = query.getResultList();
    System.out.println("---Impressão de Items----");
    int i = 0;
    for (Object[] item : items)
        System.out.println(++i + " ITem  : "
                + ((Item) item[0]).getDescricao() + "Quantidade ===>  "
                + ((Item) item[1]).getDescricao());
}e recebi a mensagem:2014-03-21 11:46:25,161 ERROR [org.hibernate.hql.internal.ast.ErrorCounter] line 1:17: unexpected token: m

2014-03-21 11:46:25,162 ERROR [org.hibernate.hql.internal.Ast.Errorcounter] line 1:17: Unexpected token: m line 1:17: Unexpected token: m or ( de-mapping m

mysql hibernate jpa-2.0

  • Could you share with us what you have tried in Hibernate? Did you do it through an HQL query? If yes, you could post the query and the error you found?

  • I haven’t been able to make the complication yet is just in passing to jpql or hql when using the syntax

  • I put up what I tried.

1 answer

1


You can use HQL (Hibernate Query Language) for this purpose - HQL has a syntax very close to SQL and also supports sub-query’s:

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries

Still, in the HQL you will have to make some adaptations, such as:

  • Join syntax;
  • Change the table name to the full name of the Pojo classes (remove the delimiters / crase);
  • Of the columns related in the clause WHERE and that are mapped as foreign key should receive the respective POJO classes as parameters;

Browser other questions tagged mysql hibernate jpa-2.0

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