EJB QL - can i reduce the number queries ...

hello,

I have these finders:

 * @ejb.finder
 *       signature = "java.util.Collection findPlanValueForAllProjectsByDepartmentAndYear(java.lang.Integer departmentId, java.lang.Integer year)"
 *       query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.year = ?2)"
 *
 * @ejb.finder
 *       signature = "java.util.Collection findPlanValueForAllProjectsByDepartmentAndQuarter(java.lang.Integer departmentId, java.lang.Integer quarter, java.lang.Integer year)"
 *       query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.quarter = ?2) AND (c.year = ?3)"
 *
 * @ejb.finder
 *       signature = "java.util.Collection findPlanValueForAllProjectsByDepartmentAndMonth(java.lang.Integer departmentId, java.lang.Integer month, java.lang.Integer year)"
 *       query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.month = ?2) AND (c.year = ?3)"
 *
 * @ejb.finder
 *       signature = "java.util.Collection findPlanValueForAllProjectsByDepartmentAndDay(java.lang.Integer departmentId, java.lang.Integer date, java.lang.Integer month, java.lang.Integer year)"
 *       query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.date = ?2) AND (c.month = ?3) AND (c.year = ?4)"



Any idea how can i write this as a single finder query !?

Because i have similarly 4 finders like    findPlanValueForAllTasksByDepartmentAnd.....
& 4 finders like    findPlanValueForAllCustomersByDepartmentAnd...

I dont want to make too much queries, if  can integrate this 4 querues as a single query, i should be able to call it accordingly.

I hope you understand what is going in the query.
LVL 32
ldbkuttyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

girionisCommented:
You could have only one findPlanValueForAllProjectsByDepartmentAndDay method and pass all parameters in there, like

findPlanValueForAllProjectsByDepartmentAndDay(java.lang.Integer departmentId, java.lang.Integer year, java.lang.Integer quarter,  java.lang.Integer month, java.lang.Integer date ) and then only have one query, somethign like:

 "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.date = ?2) AND (c.month = ?3) AND (c.year = ?4) AND (c.quarter=?5)"

and so on. YOu will have to combine all possible parameters passed in the method with one query.
0
petmagdyCommented:
Idbutty.

can u add another Field to be of type Date? things will be much easier, so for example if u added a CMP field called completeDate of type java.sql.Date then u will have only one finder works with data range like this:

signature = java.util.Collection findPlanValueForAllProjectsByDepartmentDateRange(java.lang.Integer departmentId, Date startDate, Date endDate)
query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1)  AND c.completeDate >= ?1 AND c.completeDate <= ?2"
0
ldbkuttyAuthor Commented:
petmagdy,
but i need to retrieve the records based on month, day, dekade or year only. i dont need the range here. :)

girinois,
The finder:

 * @ejb.finder
 *      signature = "java.util.Collection findPlanValueForAllProjectsByDepartmentAndYear(java.lang.Integer departmentId, java.lang.Integer year)"
 *      query = "select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.year = ?2)"

does not care about any values of the month and day. If i use in this format,

 * @ejb.finder
 *     signature = "java.util.Collection  findPlanValueForAllProjectsByDepartmentAndDay(java.lang.Integer departmentId, java.lang.Integer year, java.lang.Integer quarter,  java.lang.Integer month, java.lang.Integer date )"
 "      query = " select object(c) from Planung as c where c.currentEntry = true and c.projectId is not null AND (c.departmentId = ?1) AND (c.date = ?2) AND (c.month = ?3) AND (c.year = ?4) AND (c.quarter=?5)"

how can i make sure that fields like quarter, date, month are not taken into account  (for my first query.)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

petmagdyCommented:
ok, the way of thinking is like this, based on ur application use cases developer create the appropiate finders, if my reports or inquires have multiple types it is ok to create a finder for each type of report, because finders is declarative it is ok to have many of them, I also encourge to utilize between operations, for example if a query is based on year (from to), month (from - to), day (from -to) then this is Date from to a finder like i suggested will be ok
if it is for example for month 4 year 2001 until month 6 year 2003 this means my finder will work if the date range is from 1/4/2001 to 30/6/2003
other example if it is Quarter 1 2003 to Quarter 3 2004 then this means 1/1/2003 to 30/9/2004 for example
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
girionisCommented:
So you need a dynamically constructed query? I do not think you can do it with EJB QL. YOu will have to define all finder methods ahead.
0
grim_toasterCommented:
One way to do it would be to be dependent on each parameter being null if not required, here's a sample query...

SELECT object(c) FROM Planung AS c WHERE c.currentEntry = TRUE AND c.projectId IS NOT NULL AND (c.departmentId = ?1) AND (c.year = ?2)
AND    (   (?3 IS NULL AND ?4 IS NULL AND ?5 IS NULL)
          OR (?3 IS NULL AND ?4 IS NULL AND c.quarter = ?5)
            OR (c.month = ?3 AND ?4 IS NULL AND ?5 IS NULL)
            OR (c.month = ?3 AND c.DATE = ?4 AND ?5 IS NULL))  

However, performance may be a consideration for this one!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java EE

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.