That's what I meant by tough EJB-QL

[Andrew Perepelytsya]

Ok, now my turn to ask a mock question. I will build on Kimberly's one, hope she doesn't mind
Given a database table:

EmpID   Salary   FirstName    LastName       Gender  DepartmentID
101     40000    'George'     'Smith'        'M'         'SUP'
102     22000    'Sally'      'Jones'        'F'         'PLN'
103     25000    'Bob'        'Smythe'       'M'         'MRK'
104     60000    'Julie'      'Rodriguez'    'F'         'IT'

Employee has a 1:1 unidirectional relationship with department. What would be the correct EJB-QL query for the following finder method? (select one):
java.util.Collection findSalaryInDepartment(int minSalary, DepartmentLocal dep) throws FinderException;

A.
==
SELECT emp.salary
FROM Employee AS emp
WHERE emp.salary > ?1 AND emp.department = ?2
B.
==
SELECT emp.salary
FROM Employee AS emp
WHERE emp.salary > ?1 AND emp.departmentID = ?2
C
==
SELECT DISTINCT emp.salary
FROM Employee AS emp
WHERE ?1 < emp.salary AND ?2 = department
D
==
SELECT DISTINCT emp.salary
FROM Employee AS emp
WHERE ?1 < emp.salary AND ?2 = departmentID
E
==
These are not valid finder queries.

Not for exam, but for the sake of learning, please elaborate on your answer.
Happy EJB-QL'ing

[Karthik Guru]

Tell me something, if an emp has 1:1 relationship with a department,w'd i get a Collection fom a query.
EMP1 --> DEP1
now can I have
EMP2 --> DEP1 ?
i guess no?
now what does unidirectional mean here?
i can get to a department from an employee but not otherway around ? (it's more of a question of navigation right?) But hope that does'nt necessarily allow me to map multiple employees to a dep?

[Andrew Perepelytsya]

Thanks for stating this. In fact, the relation should be M:1. (In real-life, I would make it M:N and use relation table, anyway).
The whole question is generally not about navigation, but EJB-QL, and I'm not an exam writer So, please, treat the original question less strictly.
I hope, if we drop this line:

Employee has a 1:1 unidirectional relationship with department. What would be the correct EJB-QL query for the following finder method? (select one):

to read as follows:

What would be the correct EJB-QL query for the following finder method? (select one):

Now, I hope, it makes up a better question. Though, a lot is involved, it's still an EJB-QL question.
Answers?

[Andrew Perepelytsya]

Forseeing your questions about this line:

(In real-life, I would make it M:N and use relation table, anyway)

Imagine that we want to track the employees history in the company, and the employee herself moves from department to department during her career growth. This table would require dates added, and so on. This is only to show that many options are possible. But let's concentrate on the query!

[Karthik Guru]

oh no i was'nt trying to be strict.
I was trying to understand the question and that's when i noticed that.
OK with a M:1 ,
i w'd go with A). :-)
I do assume that department is the CMR field on Employee that maps to DepartmentLocal.
So ,emp.department = ?2 w'd result in comparison of the keys and it will get me the correct ones.
B)No. I dont think i can compare departmentID with department Local.
C)uhhm. not sure here . but i w'd want to reject it bcos the XML might not understand < tag ..lol. I might want to escape the character.
D)No. Same reason as B)

And one more question, does a finder allow me access to CMP fields like salary here. I thought they always return the interface references and that only ejbSelects can return such fields.
I mean s'dnt it be like this??:
SELECT emp
FROM Employee AS emp
WHERE emp.salary > ?1 AND emp.department = ?2
But i will anyways got with A) :-)
thanks!

[Andrew Perepelytsya]

I like your way of thinking, but I will refrain from posting the answer till today's evening (I'm currently residing in GMT zone). Just to let US guys come into play as well. With your kind permission

[Karthik Guru]

Originally posted by Andrew Perepelytsya:
I like your way of thinking, but I will refrain from posting the answer till today's evening (I'm currently residing in GMT zone). Just to let US guys come into play as well. With your kind permission

that's bad! :-). I wanted to know the answer
right now. Being a resident of India i w'd'nt be able to wait till then.
Catch up with you all tomorrow!.

[Kimberly Bobrow Jennery]

Good one!
That's actually a pretty decent representative question, too! You may well get something not too unlike that!

[Andrew Perepelytsya]

Ok, guys. Now, after seeing the 'Matrix Reloaded' and having 3 pints, a can unveil my Zion-must-fall tricks
The correct answer is

E
==
These are not valid finder queries.

karthik, as they say 'had a flash-royal, but couldn't take the bank' (nothing personal )
While half of the options are valid EJB-QL queries, the question was about finder queries. Again, the keyword is 'finder', and as we know, finders cannot return CMP fields (which emp.salary actually is).
if it were like karthik suggested:

SELECT emp
FROM Employee AS emp
WHERE emp.salary > ?1 AND emp.department = ?2

, this one would be a legal finder query. But the catch was about select methods. They are allowed to return field values, unlike finder methods. But they can only be called as internal methods of the bean and cannot be exposed in the interface (whatever).
I will step through the answers.

A: absolutely legal EJB-QL query, but applicable only to 'select' methods. If you try to specify it as a finder definition, the container will.. well, say many bad things
B: No, the beauty of relationships in CMP 2.0 is you just specify logical entities, not physical database mappings, like departmentID.
C: While DISTINCT was introduced to confuse you, you have to remember that input parameters have a RHS (Right-Hand-Side) rule.
D: same as C + B.
The task was not to prove you wrong, but only to give you some insight on the experience I had with EJB-QL. It's more like knowing what you can't do than how to do it. Hope I'll have time to throw some more EJB-QL questions at you