ejb3unit-1.2 SessionBean Test

[tom chansky]

I just tried EJB3Unit-1.2 for my session bean. It works fine if the entity beans inside the session bean has declared NamedQueries.

But, when I have an operation (i.e. findByName(String firstName, String lastName)) inside the same session bean that uses dynamic query such as the following:

SELECT c FROM Client c JOIN c.names n WHERE lower(n.firstName) like :firstName AND lower(n.lastName) like :lastName

EJB3Unit for some strange reasons cannot return any results from the query!!

Does anyone ever have problems with dynamic queries with JOIN tables?

[Lasse Koskela]

Originally posted by tom chansky:
Does anyone ever have problems with dynamic queries with JOIN tables?

Just to clarify, have you located the problem into the query being a JOIN or does the problem remain for non-joined queries as well?

[tom chansky]

The problem occurs in JOIN objects/tables. If I've a Select query that involves only one object/table, then EJB3Unit has no problem with it, and it will return me results. With JOIN tables/objects it returns null.

[tom chansky]

Actually, I should correct the last statement I said. "With JOIN objects/tables it returns an empty List".

[Jeanne Boyarsky]

Tom,
I can see two possible points of failure in that query.

The first is the where clause. (For example, what is being passed as first name and last name.) Does "SELECT c FROM Client c JOIN c.names n" return anything? If so, the problem is likely to be in the where clause.

The second is the join. Can you run "select * from client c, name n where c.names = n.names" (or whatever the mapped SQL is for you database) at the command line to see if it returns anything?

[tom chansky]

i tried the following 2 queries, and they worked, EJB3Unit was able to return results:

@NamedQuery(name = "findTest", query = "select c from Client c JOIN c.names n")

@NamedQuery(name = "findTest", query = "select c from Client c JOIN c.names n where lower(n.firstName) like '%r%' and lower(n.lastName) like '%p%'")

But when I tried the following, EJB3Unit returned me an empty list:

@NamedQuery(name = "findTest", query = "select c from Client c JOIN c.names n where lower(n.firstName) like :firstName and lower(n.lastName) like :lastName")

:firstName was passed via "query.setParameter("firtName", "%" + firstName.toLowerCase() + "%")", and so was :lastName.

so, somehow the parameters cause some strange problems.

[Lasse Koskela]

Have you tried checking what kind of SQL does ejb3unit execute against the database for that parameter-based query? Maybe that would reveal something useful.

[Jeanne Boyarsky]

Tom,
Is it safe to assume that "firtName" doesn't contain a typo in your real code?

query.setParameter("firtName", "%" + firstName.toLowerCase() + "%")"

It's good that you know the join and concept of like are working correctly. One more thing to narrow it down is to literally do the same thing:

query.setParameter("firstName", "%r%");
query.setParameter("lastName", "%p%");

This should return exactly the same thing as your example where you didn't use binding variables. If it does, the problem lies in your code which makes it easier to troubleshoot (because you can slowly change one thing at a time until the error crops up.) If it doesn't something very odd is going on and a different type of troubleshooting will be needed.

[tom chansky]

well, i finally found out what the problem was. the following was the problem:

"firstName.toLowerCase()"

i didn't assign a String variable to hold the returned value from the above String function, so i never really passed "john" to the query; instead i kept passing "John" to it. Because of that side effect, the query always returned me an empty List!

mystery resolved!