Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-11
6
Medium Priority
?
803 Views
Last Modified: 2013-11-24
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.
0
Comment
Question by:ldbkutty
6 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 12563261
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
 
LVL 13

Expert Comment

by:petmagdy
ID: 12563478
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
 
LVL 32

Author Comment

by:ldbkutty
ID: 12563748
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Accepted Solution

by:
petmagdy earned 800 total points
ID: 12563852
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
 
LVL 35

Assisted Solution

by:girionis
girionis earned 800 total points
ID: 12563871
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
 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 400 total points
ID: 12565647
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This video teaches viewers about errors in exception handling.
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question