Internal server error with SQL query

Question

I am new to hibernate but I have a rough idea on what usually to do, this case, I can't say that is working out. I'm trying to create a filtering system which allows you to sort out different clients in this case with users. My SQL query is working correctly:

  SELECT * FROM [de_user_site] WHERE [deactivation_time] = '9999-12-31 00:00:00.000' AND [user_id] IN (SELECT [user_id] FROM [de_users] WHERE [client_id] = 1)

And I've tried to turn that into a java query:

        queryStr = "SELECT e FROM DeSiteUser e " + queryStr;
        String queryStr = " WHERE e.deactivationTime = '9999-12-31 00:00:00.000' ";
        if (clientId != null) {
            queryStr += String.format("AND e.userId IN (SELECT id FROM DeUser u WHERE ");

        if (clientId != null) {
            queryStr += String.format("u.clientId = %d ", clientId);
        }
    }

However, I am having an issue with this:

Internal Server Error [#500]: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found 'null' near line 1, column 214 [SELECT e FROM com.velatt.dartentitlements.domain.DeSiteUser e WHERE e.deactivationTime = '9999-12-31 00:00:00.000' AND e.userId IN (SELECT id FROM com.velatt.dartentitlements.domain.DeUser u WHERE u.clientId = 6 ]

Does anyone know what I am forgetting, my SQL query is correct but I don't understand why it hasn't worked out for the java query?

Possible duplicate of [org.hibernate.hql.internal.ast.QuerySyntaxException: table is not mapped](https://stackoverflow.com/questions/23018836/org-hibernate-hql-internal-ast-querysyntaxexception-table-is-not-mapped) — Dhiral Kaniya, Jul 05 '18 at 13:37
The use of `String.format` as shown is **unsafe** as it opens you to SQL injection. Please check the hibernate documentation how to properly use parameters. — Mark Rotteveel, Jul 06 '18 at 13:21

score 0 · Accepted Answer · answered Jul 05 '18 at 13:35

You missed close braces. Error says it all.

SELECT e FROM com.velatt.dartentitlements.domain.DeSiteUser e WHERE e.deactivationTime = '9999-12-31 00:00:00.000' AND e.userId IN (SELECT id FROM com.velatt.dartentitlements.domain.DeUser u WHERE u.clientId = 6

Your code should have close braces something like this

queryStr = "SELECT e FROM DeSiteUser e " + queryStr;
String queryStr = " WHERE e.deactivationTime = '9999-12-31 00:00:00.000' ";
if (clientId != null) {
    queryStr += String.format("AND e.userId IN (SELECT id FROM DeUser u WHERE ");
    queryStr += String.format("u.clientId = %d ", clientId);
    queryStr += " ) ";
}

score 0 · Answer 2 · answered Jul 05 '18 at 13:43

Please replace your given code with below. you have missed to add ')' as the and of subquery.

queryStr = "SELECT e FROM DeSiteUser e " + queryStr;
    String queryStr = " WHERE e.deactivationTime = '9999-12-31 00:00:00.000' ";
    if (clientId != null) {
        queryStr += String.format("AND e.userId IN (SELECT id FROM DeUser u WHERE ");

    if (clientId != null) {
        queryStr += String.format("u.clientId = %d )", clientId);
    }
}

Internal server error with SQL query

2 Answers2