Solved

EJB QL 'LIKE' CLAUSE

Posted on 2003-12-04
6
1,397 Views
Last Modified: 2013-11-24
Hi. I am currently making a search module using EJB QL for the search Query (Oracle Database). I am having a problem on searching with NULL values. It goes this way:

This is the current EJB QL that we use:

select * from TABLE1 t1,TABLE2 t2 WHERE t1.deal_project_id=t2.project_id
AND t2.title LIKE ?1
AND t2.description LIKE ?2
AND t1.contract LIKE ?3
AND t1.deal_type_id LIKE ?4
and t1.network_id=?5

If the user did not specify any string in the textfield we just supply the %% escape character. For example:

select * from TABLE1 t1,TABLE2 t2 WHERE t1.deal_project_id=t2.project_id
AND t2.title LIKE '%%'
AND t2.description LIKE '%%'
AND t1.contract LIKE '%%'
AND t1.deal_type_id LIKE '%%'
and t1.network_id=1

But the problem is, instead of including the columns with NULL in them (Since '%%" means that the user did not specify a criteria in the front end), it ignores it. I know that the solutiom to this is to check if one or more of the criteria is NULL but this will require a lot of EJB finder methods. Does anyone have a good solution to this problem? Your help will be greatly appreciated. Thanks a lot.

Regards!
0
Comment
Question by:animor
  • 3
  • 2
6 Comments
 
LVL 30

Expert Comment

by:mayankeagle
ID: 9880091
I prefer using LIKE '%%' for all cases.... even in the first case, I would have used LIKE '%1'. Did you try printing the query and see what it is. Run the same query in Oracle and see the result.
0
 
LVL 40

Accepted Solution

by:
RQuadling earned 20 total points
ID: 9881861
Can you try ...

SELECT
      *
FROM
      TABLE1 t1,
      TABLE2 t2
WHERE
      t1.deal_project_id = t2.project_id
      AND (t2.title LIKE '%%' OR t2.title IS NULL)
      AND (t2.description LIKE '%%' OR t2.description IS NULL)
      AND (t1.contract LIKE '%%' OR t1.contract IS NULL)
      AND (t1.deal_type_id LIKE '%%' OR t1.deal_type_id IS NULL)
      AND (t1.network_id=1 or t1.network_id IS NULL);


Regards,

Richard.
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 9881880
A better way would be to build a query in code.

e.g.

Start with

SELECT * FROM TABLE1 t1, TABLE2, t2 WHERE t1.deal_project_id = t2.project_id

Then, for each of the search criteria the end user can input something into, check to see if it is not blank and then append the string ...

"AND field LIKE value_user_typed"

or

"AND field = value_user_typed"

depending upon what you want.

This way if no criteria entered, no filtering is done.

Richard.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:animor
ID: 9883119
Thanks for the reply guys. RQuadling's first suggestion is good enough to make it work. Since I will be using EJB and we would like to minimize the number of finder methods, it really helps if we can use only one SQL statement to execute the search.

(awards him the points)

Thank you very much =)
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 9883148
Thank you.

And I only stumbled into this area today as an accident, looking for the PHP questions!
0
 

Author Comment

by:animor
ID: 9883395
you're welcome. i just noticed that it wont work if the user provides a criteria and still stumbles upon null values, the query will return even those that have null values. Maybe i should check first the categories supplied as what you suggested in your second post.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
bigHeights  challenge 13 56
find a node in VST 2 47
create an incrementing variable name AutoHotKey 5 58
Modeling a class in java 5 34
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…
This is about my first experience with programming Arduino.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now