SET @number_of_rows := (SELECT COUNT(*) FROM carecube.visit);
SET @quartile := (ROUND(@number_of_rows*0.25));
SET @medianquartile := (ROUND(@number_of_rows*0.50));
SET @sql_q1 := (CONCAT('(SELECT "Q1" AS quartile, visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET ', @quartile, ')'));
SET @sql_med := (CONCAT('(SELECT "Median" AS quartile, visit.id FROM carecube.visit order by visit.id LIMIT 1 OFFSET ', @medianquartile, ')'));
SET @sql_max := (CONCAT('SELECT "MAX" AS quartile, max(visit.id) as id FROM carecube.visit order by visit.id '));
SET @sql_q3 := (CONCAT('(SELECT "Q3" AS quartile, visit.id FROM carecube.visit order by visit.id DESC LIMIT 1 OFFSET ', @quartile, ');'));
SET @sql_min := (CONCAT('SELECT "MIN" AS quartile, min(visit.id) as id FROM carecube.visit'));

SET @sql := (CONCAT_WS(' UNION ', @sql_min, @sql_q1, @sql_med, @sql_q3));

PREPARE stmt1 from @sql;
EXECUTE stmt1;

This executes perfectly in MySQL workbench, but complains of syntax errors near prepare/execute in hibernate in java.

Can someone with more knowledge of using sql variables shed some light on this please? thank you.

Thanks.

You need to extract this script into a stored procedure and then you need to call it from Hibernate.

Check out this article for more on calling stored procedures and functions from Hibernate.