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.

Answer 1

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();

Answer 2

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();