Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

java and mysql issue

I need help in java and mysql here.

MySQL has a inbuilt function

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.


I want to use IFNULL function in  where clause of my query.

For example:
I have a java code like this ..
String query=null
if(name==null)
query = "select * from tableA";  // get all records

if(name!=null)
query = "select * from tableA where name=' "+name+" ' "; // get records which matches the name.

// several of  this kind of blocks

Can I reduce the code  with the help of  MySQL in-built IFNULL  function.  My primary objective is to reduce the cyclometric complexity contribution generated out of this approach.

However, when I try to code ,my hunch is ,I dont understand where to put column name in IFNULL ?  IFNULL   accepts expression but not column name. ...right ?   How do I validate the value of a column with the help of this function then ?



0
cofactor
Asked:
cofactor
  • 15
  • 13
  • 6
1 Solution
 
for_yanCommented:
0
 
for_yanCommented:

See a the bottom of previous link which i posted:
 select * from employee where IFNULL(ID,999) = 999;
0
 
for_yanCommented:
You logic is not quite clear, so you want
to select all records which either have name matching to your staring
or where name is null - is that your wish?
0
Industry Leaders: 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!

 
for_yanCommented:
If you want to select all rows if your String name is undefined
and only rows which match name if String name is not null,
then probably the way you wrote it

if(name==null)
query = "select * from tableA";  // get all records
else
query = "select * from tableA where name=' "+name+" ' ";

ResultSet rs = stmt.executeQuery(query);


seems to be a better solution than to use IFNull
0
 
objectsCommented:
try this:

query = "select * from tableA where name is null
0
 
objectsCommented:
And use a PreparedStatement
0
 
for_yanCommented:
I think this function is usefull when you have two kind of similar columns
and you want to select one of them or is this one is null thn value of another one
will suit you.
This is rather common situation, but deos not seem to match your case.
0
 
objectsCommented:
ifnull() is not intended for what you are doing
0
 
objectsCommented:
you could simplify your code a little to:

query = "select * from tableA";  // get all records
if(name!=null) {
   query += " where name=?"; // get records which matches the name
}

0
 
objectsCommented:
thats probably the least complex you can make your source, you've only got one optional path that way
0
 
objectsCommented:
> How do I validate the value of a column with the help of this function then ?

ifnull() does not validate the value, it lets you specify a default value when it is null.
Which does not appear to help you
0
 
cofactorAuthor Commented:
>>>You logic is not quite clear

logic was like this..

if the user select a "name" from the drop down box in the GUI and submit  then I'll  fire this ..

query = "select * from tableA where name=' "+name+" ' "; // get records which matches the name.

if the user DOES NOT select  "name" from the drop down box in the GUI and submit  then I'll  fire this ..
query = "select * from tableA";  // get all records

I hope , this is clear now.
0
 
for_yanCommented:
Yes, so that is what all answers will do for you
The main result is that you don't want to use IFNull, read my post above which explains for waht situation ifnull was designed
0
 
cofactorAuthor Commented:
>>>And use a PreparedStatement

Can I write this way ? Do you see any potential drawbacks here ?

where name = ifNull(?,name)
0
 
objectsCommented:
> I hope , this is clear now.

See the code I posted earlier, think thats the best you'll do

> Can I write this way ? Do you see any potential drawbacks here ?
> where name = ifNull(?,name)

no that won't work, see my earlier comment explaining how the function works

0
 
for_yanCommented:
but then if it null it would want to match null, and you want to print everything - not waht you want even if it wirks
0
 
objectsCommented:
objects> ifnull() does not validate the value, it lets you specify a default value when it is null.

and that doesn't help in your query
0
 
for_yanCommented:
ifnull is not for your case, you have straightforward situation - why would you want to
blur it with ifnull
0
 
for_yanCommented:
But if you want so much to use ifnull - try to use it - and you'll see for youself that
you would not get what you want - it is better than to try to convince you
0
 
cofactorAuthor Commented:
>>>why would you want to  blur it with ifnull

I'm trying to reduce number of if statements in the code . Too many if conditions  in my source  making cyclometric complexity high.  You know I have 3 such blocks.

I'm trying to reducing complexity by using ifNull  .  ifNull  is a if killer and  that helps to reduce the cyclometric complexity .
0
 
objectsCommented:
I understand what you are trying to do.
The code I posted earlier I believe is the best you can do

gets rid of one if, but you can't get tid of the other

>   ifNull  is a if killer and  that helps to reduce the cyclometric complexity .

It is, but its not applicable in your case
Its used where you have a default value you want to use in a query instead of null
in your case you have a conditional where clause
0
 
for_yanCommented:

I guess you can use ...:...?...
something luiike that
query = "select * from tableA " + (name==null?"": "where name=' "+name+" ')
0
 
for_yanCommented:
The line above will saev you if, but if you ask me with if and else - is the best construction
most readable and undestandable - who counts this cylcometric...
0
 
cofactorAuthor Commented:
>>>no that won't work

I think that will work. Here is my reasoning . This is just my reasoning ..not tested :)

>>where name = ifNull(?,name)

lets take ,for example,  you pass null  data in the   "?" placeholder. so ,  it will set the default value to name.

The query now becomes  
where name = name   <----This is a boolean true  in SQL  and this is going to fetch all records .

your commnets please.
0
 
for_yanCommented:
Well, try it - it is easier to try than to discuss.

In general when you have such construction when you need five minuted to think what it will mean
that is far wiorse than any cyclometric.. :)
0
 
objectsCommented:
> query = "select * from tableA " + (name==null?"": "where name=' "+name+" ')

think you'll find that is more complex
0
 
for_yanCommented:
In my mind it is also more complex - the simplest as alwys is to have if -- else,
but if to count number of if's then it has the minimum number

0
 
objectsCommented:
its not about counting the number of if's.
0
 
for_yanCommented:
I'm just quoting : "Too many if conditions  in my source  making cyclometric complexity high"
0
 
objectsCommented:
Buts thats not the only thing that increases cyclometric complexity.
And a ternary is just shorthand for if/then/else anyway
0
 
cofactorAuthor Commented:
>>>>where name = ifNull(?,name)
guys , this works . Tested .
0
 
objectsCommented:
good work :)
0
 
for_yanCommented:
Great!
But simplicity is still the best thing.
0
 
cofactorAuthor Commented:
excellent
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 15
  • 13
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now