Subquery in Java HQL

Question

Hello, I am in a project that uses geolocation and I am doing a feature to list the stores closer to the location chosen by the user. I am using the Postgresql database and I made 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 by running it directly in the database. By java I am doing as follows:

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 don’t know why the error, but running the query below works normally:

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

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 an SQL command and this can lead to some unexpected results. In the above case note that the field TELEPHONE and the keyword FROM will be concatenated into a single word PHONEFROM, generating syntax errors in the generated final SQL:

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

Set white spaces at the end of the string on each line to avoid the problem. I also advise switching to the use of Stringbuilder when performing 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());

Answer 2

Since hql does not accept subquery after a FROM, just as a select element or in a WHERE, I had the idea of placing the query inside a stored Function and calling it through hql. The stored funtion was 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 her with the hql this way:

   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 don’t know if it’s the right thing to do.

This Stored Function returns only the site ids, after which I can make a simple query to search the stores by id.