How to pass null to SQL parameter in java?

Question

I have a scenario where in i have to pass null to a SQL parameter through java but i am getting SQLGrammarException. If i am passing some value it works fine. Please guide if i am wrong somewhere.

Below is my code:

StringBuffer query;
    query = new StringBuffer(
    "SELECT * "+ 
         "FROM table(package.func(travel_type => travel_search_type("+
             "travel_place_no => :travelPlaceNo"+
             ")))" );
        Query query1 = em.createNativeQuery(query.toString());
    query1.setParameter("travelPlaceNo",null);

    searchresults = query1.getResultList();

Exception:

org.hibernate.exception.SQLGrammarException

This is what i do through SQL Developer and it works fine:

    SELECT *
 FROM table(package.func(travel_type => travel_search_type(
     travel_place_no => NULL))) ;

Please guide.

there is a , after the :travelPlaceNo - is that a copy paste error or not? — Nick Vanderhoven, Nov 25 '16 at 11:46
Did you take a look at http://stackoverflow.com/questions/2123438/hibernate-how-to-set-null-query-parameter-value-with-hql ? — ttulka, Nov 25 '16 at 11:51
I have just set null same as that was mentioned in that answer from the link but it didnt work out — sTg, Nov 25 '16 at 11:52
Please that question has not helped my problem. Dont mark as duplicate — sTg, Nov 25 '16 at 11:54
@Nick it was during identation. I am not able to pass null . if i am passing value it works fine. how to pass null. i have also shown example how i am doing in sql developer. — sTg, Nov 25 '16 at 12:00
What database? PostgreSQL? https://hibernate.atlassian.net/browse/HHH-9165 — Nick Vanderhoven, Nov 25 '16 at 12:01
Instead of using named parameter you can try using the positional parameter. Please give it a try. — W-S, Nov 25 '16 at 12:30

Rubasace · Answer 1 · 2016-11-25T12:09:18.043

3

While calling the 2 argument signatures of the method, null is not an allowed value. You can use, instead, the 3 argument signature setParameter(String name, Object val, Type type) specifying the data type and it should work.

EDIT:

Ok, I see there is also another problem: even if the replacemente worked, what you are trying to execute is a >= NULL. In this case, I'm afraid that you are going to have to handle mannually the StringBuffer creation. Maybe just force an always false condition without parameters if its null, like `1!=2', and otherwise just handle it as you are doing on your sql example (write mannually 'NULL' instead of the parameter placeholder).

edited Nov 25 '16 at 12:09

answered Nov 25 '16 at 11:53

Rubasace

939
8
18

What is it in the database? NUMBER? I'd just try Integer and give it a go – Nick Vanderhoven Nov 25 '16 at 11:58
i am using Query query1. I didnt find those signatures. – sTg Nov 25 '16 at 11:58
@Rubasace If i am passing null it is throwing sqlgrammarexception bro. – sTg Nov 25 '16 at 12:01
Can you please give the complete classname of Query so I can check it? – Rubasace Nov 25 '16 at 12:04
javax.persistence.Query; – sTg Nov 25 '16 at 12:05
According to http://stackoverflow.com/questions/28829818/how-to-create-a-native-query-with-named-parameters named parameters are not supported for native queries. As @Rubasace points out, writing "NULL" directly in your query string should do the trick. – Oxolotl Nov 25 '16 at 12:06
writing null gives sqlgrammarexception. I have mentioned what i have tried – sTg Nov 25 '16 at 12:09
"I have mentioned what i have tried". can you please give as the stacktrace and not just the exception name? Maybe that way we can help you better. – Rubasace Nov 25 '16 at 12:14
have you tried writting IS NULL / IS NOT NULL instead of >= NULL? – Rubasace Nov 25 '16 at 12:22
@Rubasace query1.setParameter("travelPlaceNo",null); this is working fine. Is this the correct way or any better approach still there? – sTg Nov 25 '16 at 13:01

How to pass null to SQL parameter in java?

1 Answers1