How to set Integer parameter as null in Hibernate query?

Question

I've got an entity:

    @Entity
    @Table(name = "smsc.security_users")
    public class User {

        @Id
        @Column(name = "id")
        private int id;

        @Column(name = "username")
        private String username;

        @Column(name = "password")
        private String password;

        @Column(name = "enabled")
        private int enabled;

        @Column(name = "master_id", nullable = true)
        private Integer master_id;

        @Temporal(TemporalType.TIMESTAMP)
        @Column(name = "passwordExpiration")
        private Date passwordExpiration;

        public String getUsername() {
            return username;
        }
        ...

I'm talking about master_id. And this is my DAO method:

public void add(String userName, String password, Integer master_id) {
        Session session = null;
        session = this.sessionFactory.getCurrentSession();
        Query query = session
                .createSQLQuery(
                        "INSERT INTO smsc.security_users(id,username,password,enabled,passwordExpiration,master_id) VALUES(smsc.SeqDictID.nextval+1,:userName,:password,1,:passwordExpiration,:master_id)")
                .setString("userName", userName)
                .setString("password", Sha1.getHash(password))
                .setTimestamp("passwordExpiration", DateUtil.getTomorrow())
                .setInteger("master_id", master_id);
        int updated = query.executeUpdate();

    }

Log says:

Request processing failed; nested exception is java.lang.NullPointerException

and puts pointer at this line:

.setInteger("master_id", master_id);

master_id must be null sometimes. So how can I set null?

@DmitryTsechoev, `ora-00932 inconsistent datatypes expected number got binary` — Tony, Sep 04 '14 at 14:31
a workaround for this would be to create 2 separate methods and specify null inside SQL query string — WeMakeSoftware, Sep 04 '14 at 14:38
Maybe it will help (look at answer): http://stackoverflow.com/questions/14077721/inconsistent-datatypes-expected-number-got-binary — DmitryKanunnikoff, Sep 04 '14 at 14:40
@Funtik, ok. But I thought we can put a java reference as parameter that might be null. — Tony, Sep 04 '14 at 14:41
Issuing an `insert` statement via Hibernate is odd. Why don't you do: `User user = new User(/* parameters */); session.save(user);`? — Étienne Miret, Sep 04 '14 at 14:43
I think the problem with master_id is solved, and now there is problem in .setTimestamp("passwordExpiration", DateUtil.getTomorrow()) - see the link above. — DmitryKanunnikoff, Sep 04 '14 at 14:45
@EtienneMiret, this method handles web-form parameters. I don't want to transfrom web-form object to entity object. — Tony, Sep 04 '14 at 14:48

score 26 · Accepted Answer · edited May 23 '17 at 11:53

26

If you check the documentation of the Query class, setInteger() method,

Query setInteger(String name,int val)

It takes a name and a primitive type int, as parameters.

When you pass a wrapper type Integer, whose value is null, during autoboxing, a null pointer Exception occurs.

For example:

Integer x = null;
int y = x;  // gives a nullpointer exception.

You could use

Query setParameter(String name,
                   Object val,
                   Type type);

method to set null values.

See Also:

Hibernate: How to set NULL query-parameter value with HQL?

edited May 23 '17 at 11:53

Community

1
1

answered Sep 04 '14 at 14:40

BatScream

19,260
4
52
68

Well, I know that part with null. My Integer migth be null. That's why I chose wrapper as it's type. – Tony Sep 04 '14 at 14:43
Did you try the setParameter() with the three arguments? According to the document it should definitely work. – BatScream Sep 04 '14 at 14:48
What type it should be? – Tony Sep 04 '14 at 16:02
Can you try IntegerType.INSTANCE, i guess Hibernate.INTEGER is deprecated. Have updated my answer with a link, that's just a workaround though. – BatScream Sep 04 '14 at 16:05

score 13 · Answer 2 · edited Jun 15 '17 at 21:54

13

For a null object, specify it as:

query.setParameter("name", object, StandardBasicTypes.INTEGER);

StandardBasicTypes.INTEGER replaced Hibernate.INTEGER (etc)

edited Jun 15 '17 at 21:54

davnicwil

28,487
16
107
123

answered Sep 08 '15 at 12:22

Roger

131
1
2

How to set Integer parameter as null in Hibernate query?

2 Answers2

Linked