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.

Possible duplicate of [Jdbi - how to bind a list parameter in Java?](http://stackoverflow.com/questions/32526233/jdbi-how-to-bind-a-list-parameter-in-java) — zloster, Apr 19 '17 at 12:45
@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. — Yantes, Apr 20 '17 at 07:04

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

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 <, >, <=, >=.

edited May 23 '17 at 12:09

Community

1
1

answered Mar 10 '17 at 10:04

Yantes

251
3
18

Why are you using StringTemplate and JDBI? My exploration of the topic suggested that they are very similar and with JDBI you don't need StringTemplate because they are essentially use the same approach to modeling the SQL query execution (from application code perspective). – zloster Apr 19 '17 at 08:32
@zloster Because I have some queries where I use the `IN(...)` statement and to be able to bind my group in the `IN` statement I need the annotation to make `@BindIn` work. See above post that I am linking to. I chose to do it on the Class level, perhaps I could just have done it on the method level, but I have not tried it yet. Because the `<` then gets a reserved character by StringTemplate it cannot be used as a `lower than` operator in the queries. If you know a better way to map a group in a `IN (...)` statement I am all ears :). – Yantes Apr 19 '17 at 10:01
Thanks for the explanation. I've missed that the solution for this in JDBI requeires `StringTemplate`. Which reminds me to check how this is handled in the upcoming release of JDBI 3. I've found very similar question here: http://stackoverflow.com/questions/32526233/jdbi-how-to-bind-a-list-parameter-in-java Checkout the linked article there. It is from the original author of the JDBI. There two approaches are described: database specific and the other one using `StringTemplate`. – zloster Apr 19 '17 at 12:21

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

1 Answers1