Subquery in Java HQL

Question

Subquery in Java HQL

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Hello, I'm in a project that uses geolocation and I'm doing a feature to list the stores closest to the location chosen by the user. I'm using the PostgreSQL database and did this query to perform the search:

SELECT  ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, 
TELEFONE
FROM (
SELECT ID, CIDADE, CNPJ,DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE,
(6371 * acos(
 cos( radians(latitude_local_usuario) )
 * cos( radians( loja.lat ) )
 * cos( radians( loja.lon ) - radians(longitude_local_usuario) )
 + sin( radians(latitude_local_usuario) )
 * sin( radians( loja.lat ) ) 
 )
) AS distancia
FROM loja

) x
GROUP BY ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE, DISTANCIA
HAVING DISTANCIA < distacia_raio_usuario
ORDER BY DISTANCIA ASC;

With this query I can perform the search perfectly executing it directly in the database. By java I'm doing it like this:

TypedQuery<Estudio> query = this.manager
            .createQuery("select ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE" 
                    + "FROM ("
                    + "select ID, CIDADE, CNPJ,DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE,"
                    + "(6371 * acos(" + "cos( radians(:lat) )" + "* cos( radians( loja.lat ) )"
                    + "* cos( radians( loja.lon ) - radians(:lon) )" + "+ sin( radians(:lat) )"
                    + "* sin( radians( loja.lat ) ) " + ")" + ") AS distancia" 
                    + "FROM loja) x"
                    + "GROUP BY ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE, DISTANCIA"
                    + "HAVING DISTANCIA < 5" + "ORDER BY DISTANCIA ASC;", Loja.class);

query.setParameter("lat", lat);
    query.setParameter("lon", lon);
    return query.getResultList();

However, I'm getting the following error:

  ERROR [org.hibernate.hql.internal.ast.ErrorCounter] (default task-18) line 1:84: unexpected token: select: line 1:84: unexpected token: select

I do not know why the error, but running the query below works normally:

  TypedQuery<Estudio> query = this.manager.createQuery("select l from Loja l",
            Loja.class);

java postgresql hibernate hql

asked by anonymous 07.09.2017 / 22:31

2 answers

Returning callback values in the main function - JS / NodeJS Display none in chrome for iphone

score 1 · Answer 1

TypedQuery<Estudio> query = this.manager
            .createQuery("select ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE" 
                    + "FROM (" ...

You are concatenating strings to build a SQL command and this can lead to some unexpected results. In the case above, note that the PHONE field and the keyword FROM will be concatenated into a single PHONEFROM keyword, generating syntax errors in the generated final SQL: / p>

select ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONEFROM (select ID, CIDADE, CN ...

Set white space at the end of the String on each line to avoid the problem. I also recommend switching to StringBuilder when doing multiple string concatenation operations:

StringBuilder sql = new StringBuilder();
sql.append("select ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE "); 
sql.append("FROM ( "); 
... // Observe o espaço em branco no final de cada linha para evitar misturar tokens
TypedQuery<Estudio> query = this.manager
            .createQuery( sql.toString());

score 0 · Answer 2

As hql does not accept subquery after a FROM, just as a select element or in a WHERE, I had the idea of putting the query inside a stored function and calling it through hql. The stored funtion is as follows:

 CREATE OR REPLACE FUNCTION public."retorna_locais_roximos"(IN latitude double precision, IN longitude double precision, IN dist integer)
    RETURNS SETOF bigint
    LANGUAGE 'sql'

AS $function$
SELECT  ID
FROM (
    SELECT ID, CIDADE, CNPJ,DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE,
    (6371 * acos(
     cos( radians(LATITUDE) )
     * cos( radians( loja.lat ) )
     * cos( radians( loja.lon ) - radians(LONGITUDE) )
     + sin( radians(LATITUDE) )
     * sin( radians( loja.lat ) ) 
     )
    ) AS distancia
    FROM loja

  ) x
  GROUP BY ID, CIDADE, CNPJ, DESCRICAO, EMAIL, ENDERECO, LAT, LON, NOME, TELEFONE, DISTANCIA
  HAVING DISTANCIA < DIST
ORDER BY DISTANCIA ASC;

$function$;

ALTER FUNCTION public."retorna_locais_proximos"(double precision, double precision, integer)
    OWNER TO postgres;

And I call it with hql like this:

   StoredProcedureQuery query1 = this.manager.createStoredProcedureQuery("retorna_locais_proximos")
    .registerStoredProcedureParameter(1, Double.class, ParameterMode.IN)
    .registerStoredProcedureParameter(2, Double.class, ParameterMode.IN)
    .registerStoredProcedureParameter(3, Integer.class, ParameterMode.IN)
    .setParameter(1, lat)
    .setParameter(2, lon)
    .setParameter(3, distancia);

It worked well, I just do not know if it's the right thing to do.

This Stored Function returns only the ids of the places, after that I can make a simpler query to search the stores for id.