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
– Nick Vanderhoven, Commented Nov 25, 2016 at 11:46
Did you take a look at stackoverflow.com/questions/2123438/… ? — ttulka
– ttulka, Commented Nov 25, 2016 at 11:51
Instead of using named parameter you can try using the positional parameter. Please give it a try. — W-S
– W-S, Commented Nov 25, 2016 at 12:30

Rubasace · Accepted Answer · 2016-11-25 12:09:18Z

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, 2016 at 12:09

answered Nov 25, 2016 at 11:53

Rubasace

9798 silver badges21 bronze badges

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

10 Comments

Nick Vanderhoven Over a year ago

What is it in the database? NUMBER? I'd just try Integer and give it a go

sTg Over a year ago

i am using Query query1. I didnt find those signatures.

sTg Over a year ago

@Rubasace If i am passing null it is throwing sqlgrammarexception bro.

Rubasace Over a year ago

Can you please give the complete classname of Query so I can check it?

sTg Over a year ago

javax.persistence.Query;

|

Collectives™ on Stack Overflow

How to pass null to SQL parameter in java?

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related