I have a query that it is bringing me 2 results. It works properly when I use it on MySQL Workbench, but when I use it on my hibernate mapping as a formula, I'm getting this error:
SEVERE: Servlet.service() for servlet [FacesServlet] in context with path [/glic_web_admin] threw exception [javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'shipmentre0_.SEPARATOR ', ') FROM glic_shipment s LEFT OUTER JOIN glic_shipment' at line 1
Here is my SQL query in hibernate mapping:
<property name="supplier" type="java.lang.String"
formula="(SELECT GROUP_CONCAT(pnSon.supplier SEPARATOR ', ')
FROM glic_shipment s
LEFT OUTER JOIN glic_shipment sSon ON sSon.id_shipment_parent = s.id
LEFT OUTER JOIN glic_rel_purchase_shipment_common_data pscdSon ON pscdSon.id = sSon.id_common_data
LEFT OUTER JOIN glic_purchase_notice pnSon ON pnSon.id = pscdSon.id_purchase_notice)" />
The expected output of this query is a String which have the results concatenated. I know it's a syntax problem but I didn't find how to solve this.
EDIT: This is the query that hibernate is executing:
(SELECT GROUP_CONCAT(pnSon.supplier shipmentre0_.SEPARATOR ', ') FROM glic_shipment s LEFT OUTER JOIN glic_shipment sSon ON sSon.id_shipment_parent = s.id LEFT OUTER JOIN glic_rel_purchase_shipment_common_data pscdSon ON pscdSon.id = sSon.id_common_data LEFT OUTER JOIN glic_purchase_notice pnSon ON pnSon.id = pscdSon.id_purchase_notice) as formula27_0_
How can I solve this?
You have not to write SEPARATOR ', '
try as follows:
<property name="supplier" type="java.lang.String"
formula="(SELECT GROUP_CONCAT(pnSon.supplier, ' ')
FROM glic_shipment s
LEFT OUTER JOIN glic_shipment sSon ON sSon.id_shipment_parent = s.id
LEFT OUTER JOIN glic_rel_purchase_shipment_common_data pscdSon ON pscdSon.id = sSon.id_common_data
LEFT OUTER JOIN glic_purchase_notice pnSon ON pnSon.id = pscdSon.id_purchase_notice)" />