setting null to parameter in select sql statement

Question

I need help as I am struggling to assign null as a parameter to the select statement in my java program.

my script read values from the csv file and then selects the parameter based on the index provided. However, the values are sometime empty string e.g. (1,,3) so from index 1 it will pick the empty string and tries to set it as parameter in sql statement, this doesn't achieve the results I am looking for.

and as we know that sql handle null values in select statement with below syntax: e.g. select * from tablename where a IS NULL.

What is the best to set the value as null with below scenario:

select * from tableName where abc=? 
stmt2.setString(1, csvVal.get(index));

mypetlion · Accepted Answer · 2018-11-06 16:48:27Z

2

You have to use the separate PreparedStatement#setNull method to set a parameter value as NULL.

if(csvVal.get(index) == null){
    stmt2.setNull(1, java.sql.Types.NVARCHAR ); // Or whatever data type your column is
}else{
    stmt2.setString(1, csvVal.get(index));
}

EDIT: My original answer is incomplete. You'll also have to modify the query because NULL = NULL will always return false.

--use a variable to avoid multiple stmt2.set... calls
DECLARE @nullCheck NVARCHAR(MAX) = ? -- Or whatever data type your column is
SELECT *
FROM tableName
WHERE abc = @nullCheck -- if you don't want the null values
OR (abc IS NULL -- only get the records if both the column and the parameter are NULL
    AND
    @nullCheck IS NULL)

edited Nov 6, 2018 at 16:48

answered Nov 6, 2018 at 0:05

mypetlion

2,5445 gold badges21 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Rashid Khan Over a year ago

This doesn't seem to be working, as when the parameter is null, the sql result is returned false. However, in sql query editor the same sql fetches the results back. String dim=""; String crazyURL = "select * from tableName where dim10=?"; stmt2 = con.prepareStatement(crazyURL); if(dim.equals("")) { stmt2.setNull(1, java.sql.Types.NVARCHAR ); } else { stmt2.setString(1, dim ); }

mypetlion Over a year ago

@RashidKhan My apologies. My answer was incomplete. Please see my edits for a guide to modifying your SQL query.

Rashid Khan Over a year ago

Thanks @mypetlion for your response on this. Can you please advise where should I specify : DECLARE @ nullCheck NVARCHAR(MAX) = ? Java doesn't recognise the above syntax, unless I am missing something

mypetlion Over a year ago

@RashidKhan All that goes in the crazyURL variable. So it should look something like: String crazyURL = "DECLARE @nullCheck NVARCHAR(MAX).....(the rest of your query)";

mypetlion Over a year ago

@RashidKhan Sorry, I'm used to MSSQL, but I see now that you are using Oracle. I will edit now.

|

Collectives™ on Stack Overflow

setting null to parameter in select sql statement

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related