How to insert NULL in mysql especially INT dataType

Question

I have 80000 recodes that are need to insert into database especially in table: temp(ts, temp) temp is INT.

The problem is almost 20000 recodes are null, so I am wondering how to insert NULL into DB when dataType is INT.

I tried this:

String val = null;

//insert(ts, val) into temp 
String sql = "INSERT INTO temp" + "(val)" + " VALUES" + "('" + val + "')";
Statement st = (Statement) conn.createStatement();
count  = st.executeUpdate(sql);

unfortunately insert is failure. Print out the exception message:

Incorrect integer value: 'null' for column 'val' at row 1"

Wish someone can help me with it. Thank you.

Mark Rotteveel · Accepted Answer · 2013-08-27 07:43:13Z

28

You should use a PreparedStatement and use setNull(int, int):

String sql = "INSERT INTO temp(val) VALUES (?)";
PreparedStatement st = con.prepareStatement(sql);
if (/* int value is not null */) {
   st.setInt(1, value);
} else {
   set.setNull(1, Types.INTEGER);
}
count  = st.executeUpdate();

answered Aug 27, 2013 at 7:43

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

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

1 Comment

Eric Over a year ago

Thanks a lot. Before you told me PrepardStatement, I didn't know it. It is the answer for my question. From API: void setNull(int parameterIndex, int sqlType) throws SQLException :sets the designated parameter to SQL NULL.

Xithias · Accepted Answer · 2017-03-03 13:40:48Z

7

As an alternative to Mark Rotteveel's answer, you can also use PreparedStatement.setObject(int, Object, int).

You specify the SQL Type (3rd Parameter), and if the object is null, it inserts a null automatically. It's faster and easier.

String sql = "INSERT INTO temp(val) VALUES (?)";
PreparedStatement st = con.prepareStatement(sql);
st.setObject(1, value, Types.INTEGER);
count  = st.executeUpdate();

answered Mar 3, 2017 at 13:40

Xithias

1,0011 gold badge15 silver badges33 bronze badges

Comments

Ed Gibbs · Accepted Answer · 2013-08-26 17:40:39Z

2

You should consider using prepared statements. If you do, you'll find information on how to deal with nulls here and elsewhere.

If you're 100% sure your val value is clean and won't cause SQL Injection (rare, but possible), then the "built string" approach needs to explicitly use null when defining the value:

String sql = "INSERT INTO temp (val) VALUES (";
if (val == null) {
  sql += "null";
} else {
  sql += val;
}
sql += ")";

answered Aug 26, 2013 at 17:40

Ed Gibbs

26.4k4 gold badges50 silver badges75 bronze badges

1 Comment

Eric Over a year ago

Thank you, you are right. I go a wrong way. If the value is null, it means the record is null, even it has timestamp. So I need to keep the records what are not null.

Eric · Accepted Answer · 2013-08-26 20:52:00Z

2

I have solved this problem. The codes update as following:

String sql= null;
if(val.isEmpty()){
System.out.println(val);
System.out.println("Insert ts: " + ts + " val: null");
sql= "INSERT INTO " + table + "(ts,val,pointId)" + " VALUES" + "(" + "'" + ts + "'" + ", " + "NULL" + " , " + "'" + pointId + "'" + ")";
}
else{
System.out.println("Insert ts: " + ts + " val: " + val);
sql= "INSERT INTO " + table + "(ts,val,pointId)" + " VALUES" + "(" + "'" + ts + "'" + ", " + "'" + val + "'" + ", " + "'" + pointId + "'" + ")";
}

Statement st = (Statement) conn.createStatement();  //create the instances of statement
count = st.executeUpdate(sql);

Basically, if insert null into database, just do insert into table(val) value(NULL).

answered Aug 26, 2013 at 20:52

Eric

1,2915 gold badges14 silver badges21 bronze badges

1 Comment

svarog Over a year ago

this is a perfectly legitimate solution (tho is can be shortened a bit), too bad it was downvoted without any explanation

Declan_K · Accepted Answer · 2013-08-26 17:39:15Z

0

You should explicitly CAST the NULL as an INT

...VALUES(...CAST(NULL AS INT)

answered Aug 26, 2013 at 17:39

Declan_K

6,8162 gold badges22 silver badges31 bronze badges

Comments

buzzsawddog · Accepted Answer · 2013-08-26 17:40:01Z

0

It sounds like your are sending "null" in when you should be sending an int. Perhaps try something like

(val == null ? "" : val)

answered Aug 26, 2013 at 17:40

buzzsawddog

67211 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to insert NULL in mysql especially INT dataType

6 Answers 6

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related