cofactor
asked on
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 ?
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 ?
See a the bottom of previous link which i posted:
select * from employee where IFNULL(ID,999) = 999;
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?
to select all records which either have name matching to your staring
or where name is null - is that your wish?
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
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
try this:
query = "select * from tableA where name is null
query = "select * from tableA where name is null
And use a PreparedStatement
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.
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.
ifnull() is not intended for what you are doing
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
}
query = "select * from tableA"; // get all records
if(name!=null) {
query += " where name=?"; // get records which matches the name
}
thats probably the least complex you can make your source, you've only got one optional path that way
> 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
ifnull() does not validate the value, it lets you specify a default value when it is null.
Which does not appear to help you
ASKER
>>>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.
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.
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
The main result is that you don't want to use IFNull, read my post above which explains for waht situation ifnull was designed
ASKER
>>>And use a PreparedStatement
Can I write this way ? Do you see any potential drawbacks here ?
where name = ifNull(?,name)
Can I write this way ? Do you see any potential drawbacks here ?
where name = ifNull(?,name)
> 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
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
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
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
and that doesn't help in your query
ifnull is not for your case, you have straightforward situation - why would you want to
blur it with ifnull
blur it with ifnull
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
you would not get what you want - it is better than to try to convince you
ASKER
>>>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 .
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 .
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
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
I guess you can use ...:...?...
something luiike that
query = "select * from tableA " + (name==null?"": "where name=' "+name+" ')
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...
most readable and undestandable - who counts this cylcometric...
ASKER
>>>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.
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.
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.. :)
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.. :)
> query = "select * from tableA " + (name==null?"": "where name=' "+name+" ')
think you'll find that is more complex
think you'll find that is more complex
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
but if to count number of if's then it has the minimum number
its not about counting the number of if's.
I'm just quoting : "Too many if conditions in my source making cyclometric complexity high"
Buts thats not the only thing that increases cyclometric complexity.
And a ternary is just shorthand for if/then/else anyway
And a ternary is just shorthand for if/then/else anyway
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
good work :)
Great!
But simplicity is still the best thing.
But simplicity is still the best thing.
ASKER
excellent
http://www.java2s.com/Tutorial/MySQL/0360__Control-Flow-Functions/UsingIFNULLinwhereclause.htm