Java Named Query

Question

I'm trying to create a Java named query which does the following postgres query:

select * from call_attempt where result is NULL and id like '0000000101%';

The 0000000101 is set to id in my code, so this is what I want to do, but it's malformed: (I'm not sure how to set the id field while using the % and it has to be inside ' ')

@NamedQuery(name = AttemptEntity.JQL.NOTENDED,
                       query = "SELECT ae FROM AttemptEntity ae WHERE ae.result IS NULL,"
                             + " ae.correlationId LIKE '=:id%'")

There's a comma in the WHERE clause and also missing an `AND`. — Andrew S, May 25 '17 at 16:15

score 2 · Accepted Answer · answered May 25 '17 at 16:15

2

First, you forgot the and, and replaced it by a comma.

Second, the % must be passed as part of the argument:

SELECT ae FROM AttemptEntity ae WHERE ae.result IS NULL
and ae.correlationId LIKE :id

And then, when executing the query:

String id = "0000000101%";
query.setParameter("id", id);

answered May 25 '17 at 16:15

JB Nizet

678,734
91
1,224
1,255

this is still saying malformed ending @NamedQuery(name = AttemptEntity.JQL.NOTENDED, query = "SELECT ae FROM AttemptEntity ae WHERE ae.result IS NULL," + " AND ae.correlationId LIKE :id") – ralyn May 25 '17 at 16:21
Again, remove that comma after IS NULL. – JB Nizet May 25 '17 at 16:23
I removed that comma after NULL, and it seems ok now. Thanks. – ralyn May 25 '17 at 16:24

score 1 · Answer 2 · answered May 25 '17 at 16:20

1

You can't have the % in the NamedQuery, but you can have it in the value you assign the parameter.

query.setParamter("id", 0000000101+ "%");

You also need to add AND and remove the comma after NULL.

Reference: Named Query with like in where clause

answered May 25 '17 at 16:20

Sadiq Ali

1,272
2
15
22

Java Named Query

2 Answers2