MSSQL Invalid Syntax with Hibernate when using DISTINCT?

Question

So I have this piece of Java code:

final Query query = session.createSQLQuery("SELECT DISTINCT(expense_document.id) FROM expense_document JOIN generic_object ON expense_document.id = generic_object.id JOIN expense_document_item ON expense_document_item.document_id = expense_document.id JOIN generic_object ON expense_document_item.id = generic_object.id WHERE expense_document.client_id = :client_id").setParameter("client_id", client.getId()).setMaxResults(1000);

and when this code is executed I get:

org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'from'.

I can not find what MS SQL does not like about this query. When I am connected to MySQL, this line will not cause any problems.

Query looks fine to me. Are you sure this the query. – Pரதீப் Jan 28 '16 at 08:21 — Pரதீப், Jan 28 '16 at 08:21

Tobb · Accepted Answer · 2016-01-28T08:33:41.447

1

Try removing the parenthesis around the parameter to the distinct keyword.

Use it like this:

select distinct something from somewhere

edited Jan 28 '16 at 08:33

answered Jan 28 '16 at 08:21

Tobb

11,850
6
52
77

When testing this using Squirrel against SQLServer, it actually worked with the paranthesis. But if you say that removing them fixed your problem, I'll keep the answer. – Tobb Jan 28 '16 at 08:32
2

The thing about drivers said in the other answer might be onto something, whenever I get a weird error from sqlserver it's always due to the sqljdbc driver. Use jtds instead.. – Tobb Jan 28 '16 at 09:12

score 1 · Answer 2 · edited May 23 '17 at 11:54

Your query looks fine.. are you using an up to date driver for MSSQL?

See how to configure hibernate config file for sql server for supported SQL server drivers.

You should be able to do:

final Query query = session.createSQLQuery("SELECT DISTINCT(expense_document.id) FROM expense_document JOIN generic_object ON expense_document.id = generic_object.id JOIN expense_document_item ON expense_document_item.document_id = expense_document.id JOIN generic_object ON expense_document_item.id = generic_object.id WHERE expense_document.client_id = :client_id").setParameter("client_id", client.getId()).setMaxResults(1000);

or

final Query query = session.createSQLQuery("SELECT DISTINCT expense_document.id FROM expense_document JOIN generic_object ON expense_document.id = generic_object.id JOIN expense_document_item ON expense_document_item.document_id = expense_document.id JOIN generic_object ON expense_document_item.id = generic_object.id WHERE expense_document.client_id = :client_id").setParameter("client_id", client.getId()).setMaxResults(1000);

However, if you're using hibernate, why aren't you using HQL, JPA Query language or Criteria? That should ensure that you don't have to change SQL syntax depending upon the vendor as they differ slightly in different flavours. I feel using JPA with entity manager might be the way forward if you're switching between different databases as createSqlQuery will send the string as native SQL to the vendor.

Hibernate EntityManager implements the programming interfaces and lifecycle rules as defined by the JPA 2.0 specification https://docs.jboss.org/hibernate/entitymanager/3.6/reference/en/html_single/

If you're using something like hibernate, then createSqlQuery is really good for sending vendor specific queries when you want to set hints for example.

MSSQL Invalid Syntax with Hibernate when using DISTINCT?

2 Answers2

Linked