I am having an issue getting values that are NULL in MySQL to actually return as NULL in a column that is of type Double. When running a simple select in clients such as HeidiSQL or DBeaver, the returned results are shown as (NULL) which is correct. However, when running the same statement in Java or via a custom SQL in Wordpress wpDataTables plugin, any NULL values are returning as 0.0.
My table looks like this:
CREATE TABLE `Test` (
`ID` INT(11) NOT NULL,
`Time` DECIMAL(20,1) NOT NULL DEFAULT '0.0',
`Test1` DOUBLE NULL DEFAULT NULL
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;
INSERT INTO Test (ID, Time, Test1) VALUES (1, 0.1, NULL)
select * FROM Test
Running that select, SQL Clients will correctly display NULL, but Java and Wordpress display NULL as 0.0
Is there something i am doing wrong on the table create or in the Select Statement that would cause this issue?
Java Code
String testSQL = "Select * from Test";
ResultSet rs = null;
Statement stmt = createDbStatement(conn);
try {
rs = stmt.executeQuery(testSQL);
while (rs.next()) {
System.out.println(rs.getDouble("Test1"));
}
} catch (Exception ex) {
System.out.println(ex);
}
public static Statement createDbStatement(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
return stmt;
}
public static void createDbConnection() {
//Connection conn = null;
try {
main.conn = DriverManager.getConnection("jdbc:mysql://xx.xx.xx.xx/DBName?user=xxxx&password=xxxx");
} catch (SQLException ex) {
// handle any errors
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
Output:
0.0
