How to insert NULL in mysql especially INT dataType
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();
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 += ")";
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();