I'm trying to combine sql with criteria. I'm using code for a store locater that is sql coded, but i wanted to add criteria based expressions.
Code:
Criteria criteria = getSession().createCriteria(UserEnt.class);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
String query = "";
String formula = "(3959 * acos(cos(radians("+lat+")) * cos(radians(lat)) * cos(radians(lng) - radians("+lng+")) + sin(radians("+lat+")) * sin(radians(lat)))) distance";
query += "SELECT id, " + formula + " FROM UserEnt ";
query += "GROUP BY distance HAVING distance < "+maxdistance;
criteria.add(Expression.sql(query));
criteria.setFirstResult(first.intValue()).setMaxResults(max.intValue());
return criteria.list();
When i run this, i get the following error.
16:19:31,495 WARN JDBCExceptionReporter:77 - SQL Error: 1064, SQLState: 42000
16:19:31,510 ERROR JDBCExceptionReporter:78 - 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 'SELECT id, (3959 * acos(cos(radians(40.755367)) * cos(radians(lat)) * cos(radian' at line 1
org.hibernate.exception.SQLGrammarException: could not execute query
[Ljava.lang.StackTraceElement;@1f13b08
I have ran the criteria.toString output in the mysql console and it ran correctly. Anyone have any ideas why Expression.sql doesn't seem to like this?
Login
FAQ
Search
