How to differentiate between 0 and null in an INT column in MySQL
If there is a null value stored in a MySQL INT column, it will return 0 when accessed by technoligies like JPA开发者_Python百科. If 0 value also stored in the column, how can I differentiate between null and 0?
I can't believe, that it is so.
Change primitive type for object type in your entity(Example: int -> Integer)
To differentiate between 0 and NULL you should use ResultSet.wasNull() method, like here:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
public static void main(String[] args) throws Exception {
Connection conn = getConnection();
Statement st = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
st.executeUpdate("create table survey (id int,name varchar(30));");
st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");
st.executeUpdate("insert into survey (id,name ) values (2,null)");
st.executeUpdate("insert into survey (id,name ) values (3,'Tom')");
st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM survey");
while (rs.next()) {
String name = rs.getString(2);
if (rs.wasNull()) {
System.out.println("was NULL");
} else {
System.out.println("not NULL");
}
}
rs.close();
st.close();
conn.close();
}
The way I solved it is the following:
Integer id;
Object o = rs.getObject("ID_COLUMN");
if(o!=null){
id = (Integer) o;
} else {
id = null;
}
精彩评论