java insert null but not primitive default value

Question

I want insert a null value into database instead of inserting default primitive value.

I have a class

class Test
(
    private int first;
    private int second;
    public Test() {}  
    public void setFirst(int val) {first = val;}
    public void setSecond(int val) {second = val;}
    public int getFirst() {return first;}
    public int getSecond() {return second;}
)

I have some code that initialize Test class:

Test my = new Test(); my.setFirst(100);

so we have now: first = 100; second = 0; -- since I don't initialize it and it default to zero "0" for int.

Now I insert the data into database table Test ...

Create table Test
(
  first number,
  second number
)

Java call ...

Test my = new Test();
my.setFirst(100);
String sql = "INSERT INTO TEST VALUES(?,?)";
PrepareStatement ps = connection.prepareStatement(sql);
ps.setInt(1, my.getFirst());    // will insert 100
ps.setInt(2, my.getSecond());   // will insert 0

I want insert NULL into "second" field since Test.second is not set in my code. I use the following trick:

class Test
(
    private int first = -999;
    private int second = -999;
    public Test() {}  
    public void setFirst(int val) {first = val;}
    public void setSecond(int val) {second = val;}
    public int getFirst() {return first;}
    public int getSecond() {return second;}
)


Test my = new Test();
my.setFirst(100);
String sql = "INSERT INTO TEST VALUES(?,?)";
PrepareStatement ps = connection.prepareStatement(sql);

if (my.getFirst() == -999)
   ps.setNull(1, java.sql.Types.INTEGER) // will insert NULL
else
   ps.setInt(1, my.getFirst());    // will insert 100


if (my.getSecond() == -999)
   ps.setNull(2, java.sql.Types.INTEGER) // will insert NULL
else
   ps.setInt(2, my.getSecond());    // will insert 0

Does somebody has the best/nice solution to implement that ?

I get NullPointerException when use Integer, this is right since var is not initialized or I missed something here:

java.lang.NullPointerException
at UserQuiz.getFrom_flags_challenge_nid(UserQuiz:113)

UserQuiz. java
...
private Integer from_flags_challenge_nid;
public int getFrom_flags_challenge_nid() {
    return from_flags_challenge_nid;
}

public void setFrom_flags_challenge_nid(int from_flags_challenge_nid) {
    this.from_flags_challenge_nid = from_flags_challenge_nid;
}
...

Where is the NullPointerException when using Integer, can you copy/paste the stack-trace in this case? — Sylvain Bugat
– Sylvain Bugat, Commented Mar 23, 2015 at 21:58
It will throw an exception on ps.setInt. See stackoverflow.com/a/9542866/203117 for a similar question where this is explained. — deyur
– deyur, Commented Mar 23, 2015 at 22:05

Stack Exchange Broke The Law · Accepted Answer · 2015-03-23 23:07:32Z

2

Change your int to Integer (everywhere - the field, parameter of setter and return type of getter) and then you can just try this:

ps.setObject(1, my.getFirst()/*may return null*/, java.sql.Types.INTEGER);
ps.setObject(2, my.getSecond()/*may return null*/, java.sql.Types.INTEGER);

Note that code like this:

private Integer from_flags_challenge_nid;
public int getFrom_flags_challenge_nid() {
   return from_flags_challenge_nid;
}

will throw a NullPointerException if from_flags_challenge_nid contains null.

edited Mar 23, 2015 at 23:07

Stack Exchange Broke The Law

59.2k7 gold badges57 silver badges104 bronze badges

answered Mar 23, 2015 at 22:18

Ben

3,52834 silver badges49 bronze badges

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

5 Comments

Dumitru Gutu Over a year ago

got it.... so if changing Integer in getter and setter and variable from int to Integer, still get NullPointerException. this work fine for me: ps.setObject(2, my.getSecond()/*may return null*/, java.sql.Types.INTEGER);

Ben Over a year ago

did you change the return type? the Exception must have an understandable reason...

Dumitru Gutu Over a year ago

yes, public Integer getFrom_flags_challenge_nid() { return from_flags_challenge_nid; }. So now the question why ps.setInt(7,userquiz.getFrom_flags_challenge_nid()); failed when it return NULL and setObject work

Ben Over a year ago

ps.setInt(int, int) takes an int as value argument....by unboxing Integer (null) to int, you will get an NULLPOINTER

Dumitru Gutu Over a year ago

yes, i guess this a reason try convert Integer(null) to int +1

deyur · Accepted Answer · 2015-03-23 22:20:37Z

1

If you want to add this kind of behavior, why not extend PreparedStatement and provide your own implementation?

class MyPreparedStatement extends PreparedStatement {

    public void setNullableInt(int parameterIndex, int x)
        throws SQLException
        if (x == 0) {
            setNull(parameterIndex, java.sql.Types.INTEGER)
        }
        else {
            setInt(parameterIndex, x);
        }
    }
}

Now, the problem with this is that 0 is not logically equivalent to null, and really shouldn't be treated as such. A common method if you're dealing with positive integers in general is to use something like -1 to represent a 'non-value'.

edited Mar 23, 2015 at 22:20

answered Mar 23, 2015 at 22:11

deyur

5672 silver badges14 bronze badges

5 Comments

Dumitru Gutu Over a year ago

not sure if that work since int is not null, default is 0. so my getSecond will return 0 and not null, according that logic it call setInt, instead of setNull

deyur Over a year ago

Sorry, I edited my answer as it shouldn't have been checking for null.

Dumitru Gutu Over a year ago

hm ... see zero "0" can be a good int value, so zero is not NULL.

deyur Over a year ago

I agree. But you're initializing second to 0 and then trying to treat that as null already. Really if you want to use primitive integers, there is no such thing as null.

Dumitru Gutu Over a year ago

i guess can change: public void setNullableInt(int parameterIndex, Integer x) and check if (x == null) the setNull

Collectives™ on Stack Overflow

java insert null but not primitive default value

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related