CODEWITHSUNDEEP
javahibernatejpaoracle10g
sTg
sTg

Reputation: 4434

How to pass null to SQL parameter in java?

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.

Upvotes: 2

Views: 4058

Answers (1)

Rubasace
Rubasace

Reputation: 969

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).

Upvotes: 3

Related Questions