0

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));
Scary Wombat
  • 44,617
  • 6
  • 35
  • 64
Rashid Khan
  • 29
  • 2
  • 8

1 Answers1

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)
mypetlion
  • 2,415
  • 5
  • 18
  • 22
  • 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 ); } – Rashid Khan Nov 06 '18 at 12:08
  • 1
    @RashidKhan My apologies. My answer was incomplete. Please see my edits for a guide to modifying your SQL query. – mypetlion Nov 06 '18 at 16:48
  • 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 – Rashid Khan Nov 06 '18 at 20:31
  • 1
    @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 Nov 06 '18 at 21:29
  • @ mypetlion, still no luck with that, I have declared the variable as you suggested : String crazyURL = "DECLARE @nullCheck VARCHAR(MAX)= ? select * from tableName where dim10= @nullCheck"; and getting below error: java.sql.SQLException: ORA-06550: line 1, column 15: PLS-00103: Encountered the symbol "@" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior – Rashid Khan Nov 06 '18 at 22:39
  • 1
    @RashidKhan Sorry, I'm used to MSSQL, but I see now that you are using Oracle. I will edit now. – mypetlion Nov 06 '18 at 22:54
  • Found solution by using NVL e.g. select * from tableName where TABLE_NAME=? and nvl(language,'4d4d')=? when the code was getting parameter from CSV, and if it found empty string the setString method was set to : } – Rashid Khan Nov 08 '18 at 10:33
  • if(csvVal.get(paramForSqlArrayIntForm[4]).equals("")) { stmt2.setString(1, "4d4d" ); } else { stmt2.setString(1, csvVal.get(paramForSqlArrayIntForm[4])); //2nd param holds the parameter values which is in csv – Rashid Khan Nov 08 '18 at 10:33