Use MySQL variables and assignments in hibernate

Question

I have created the following query which is now in one of my java classes being used by Hibernate.

private static final String COUNT_INTERQUARTILE_SQL
= " 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 \\:= (CONCAT_WS(' UNION ', @sql_q1, @sql_med))" +
"   PREPARE stmt1 from @sql;" +
"   EXECUTE stmt1;";`

The stack trace complains of a syntax errors for each line where I've set a mysql variable. Obviously it works in MySQL just fine.

I read that I can use double backslashes with assignments in Hibernate. This is the first time I've tried to use MySQL variables with Hibernate so am unsure if I'm missing anything out and whether 'PREPARE' and 'EXECUTE' are necessary?

Can someone with more knowledge point me where I am going wrong?

Also, where I am selecting Q1, I've placed that in single quotes, in MySQL workbench it is double quotes.

EDIT: I've added double quotes so hibernate doesn't throw a sissy fit with the assignments. I still can't for the life of me, figure out why I cannot just use '@sql' after i've prepared it.

EDIT: I receive the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PREPARE stmt1 from @sql_max; EXECUTE stmt1' at line 1

Thanks

score 0 · Answer 1 · edited May 23 '17 at 10:32

I'm not sure if this is possible, but in my opinion this doesn't make much sense.

Some people have asked similar questions (they have some code samples in the answers if you want to check):

Hibernate is an ORM (Object Relational Mapping), so it's supposed to convert data between incompatible type systems (data from database) in objects. As far as I could understand your query, you're returning a COUNT, so it would be just one single result (row) and one single column, is that right?

Due the complexity of your query, I would say there are some options I could think of:

Use Criteria or HQL to run each query using Hibernate and then in Java work with the logic to have your desired result (may become much slower than the query in MySQL)
Create a VIEW with this SELECT (if possible), map it into an object as an @Entity and query directly to it
Create a FUNCTION/PROCEDURE (this is possible) and call it using CallableStatement

I like to think that the two elements here (Hibernate x Database) should have a well define responsibility in the project. First of all, I would try to use only Criteria/HQL for the queries (to use object properties), but if needed to use SQL I would keep all queries ANSI to allow interoperability. If it's not possible, I would create an object in the database to return what I want (view/procedure/function).

Mixing specific database provider code in the query, like your example, doesn't look a good practice.

If possible, I would definitely go for option 2. If it can't be done, surely for number 3.

Hope it's somehow helpful.

Use MySQL variables and assignments in hibernate

1 Answers1