Select next position

Is it possible to express this query (from Postgres) as in HQL or as a hibernate criteria query?

SELECT id, least(abs(gene_start - ?) , abs(gene_end - ?)) as div_pos
FROM arraydata.gene WHERE chromosomeref = ?
ORDER BY div_s LIMIT 1

Or i开发者_开发问答s there a other way to select a entry there is next to a given position. Where the start or the end of one entry from the table has the smallest distance to the query.

This stuff :

least(abs(gene_start - ?) , abs(gene_end - ?))

is not indexable at all. I suppose you got indexes on gene_start and gene_end, so you could do :

SELECT id, gene_start
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_start > ?
ORDER BY gene_start LIMIT 1

SELECT id, gene_end
FROM arraydata.gene WHERE chromosomeref = ?
WHERE gene_end < ?
ORDER BY gene_end DESC LIMIT 1

You can combine both with UNION. As for expressing it in Hibernate, no idea !