Sql Server query runs fine within SQLServer,but the same query does not work with JDBI

Question

How come that I can execute the following query just fine in Sql Server

SELECT notificationMessage FROM NotificationMessages WHERE timesUsed < 2

but when the query gets called in

@SqlQuery("SELECT notificationMessage FROM NotificationMessages WHERE timesUsed < 2")
List<String> getNotificationMessages();

I get the following error

There is only one expression of non-boolean type in a context where there was expected a condition near 'timeUsed'

Just google translated it for fast track. Another funny thing is that my exception messages are printed in danish -> only the sql exceptions. I have tried to run SET LANGUAGE English helps in Sql Server but not in exception messages from jdbi in Intellij.

EDIT 1 In case someone has the same problem, then I went with the solution to SELECT all messages and then do all the logic in code. I did something like the following:

public interface NotificationDAO {
@SqlQuery("SELECT id, notificationMessage, timesUsed, messageType, messageDays FROM NotificationMessages WHERE messageType = 'REMINDER'")
    List<NotificationMessage> getReminderNotificationMessages();
}

public class NotificationResource {
    ...
    private NotificationMessage pickNotificationMessage(){
        List<NotificationMessage> notificationMessages = notificationDAO.getReminderNotificationMessages();
        // Extract the number of times a message has been sent out to users, store in Integer list.
        List<Integer> timesUsedList = notificationMessages.stream().map(nm -> nm.getTimesUsed()).collect(Collectors.toList());
        // Remove all those messages which have been used once more than others. //TODO maybe another type of removal of messages
        List<NotificationMessage> notificationMessagesTruncated = notificationMessages.stream().filter(nm -> nm.getTimesUsed() <= Collections.max(timesUsedList)-1).collect(Collectors.toList());
        ...
    }
    ...
}

EDIT 2 @zloster I would not say that it is an exact duplicate of the post you are linking. Because their problem was to bind a list to a query, That I had got working fine with my solution. My problem was that I could not figure out why I could not make a simple lower than operation on my query such as for example WHERE 1 < 2. I did not realize that < was a reserved character by StringTemplate. But yes the link you gave has the solution for my problem "... brackets < like this \< ...", but I would not agree to say that the questions it self is a duplicate.

So their question is "How do you bind a list to a sql query IN statement?"

Where as my question is "Why does comparison operators not work in JDBI sql"

And is was of course because I was using the annotation @UseStringTemplate3StatementLocator to make my lists map to IN statements. Which I had working, which they did not. But I could not make my lower than or greater than operations, of any kind, work.

Answer 1

I have found the solution for my query problem, searching for another topic on JDBI i stumbled upon this post And I noted this phrase especially

Also note that with this annotation, you cannot use '<' character in your SQL queries without escaping (beacause it is a special symbol used by StringTemplate).

And I am using the annotation @UseStringTemplate3StatementLocator in my interface, I have tested it and it works with the gt, lt, gte, lte operators when they are escaped with \\. So if anyone is using the same annotation and has the same issue as I did, this solves the problem if you use these query operators <, >, <=, >=.