Repository: jtablesaw/tablesaw
Pull request #563 (2019-08-03 01:25:49)

added null support for SqlResultSetReader
Thanks for contributing.

- [x ] Tick to sign-off your agreement to the [Developer Certificate of Origin (DCO) 1.1](https://developercertificate.org)

## Description

During my use of Tablesaw, I found that when reading from a database, if a nullable double/integer column contained a null, the default value would instead be read into the Table.  After inspecting SqlResultSetReader, I saw that it did not utilize resultSet.wasNull() on the primitive type columns.  I added a separate function to check .wasNull() and utilize column.appendMissing() if it returned true.

Thanks for the great library!  It has helped my work tremendously and I enjoy utilizing it.

## Testing

I expanded SqlResultSetReaderTest.testSqlResultSetReader to include a test on another table ("NullValues" that I created in TestDb.java) that contained sporadic nulls on primitive typed columns.  I then asserted that the appropriate non-missing values existed in the columns.


lwhite1: @albalkum Thank you for this. The changes look good and it's an important fix. Should it include BooleanColumn as well? I suspect that might default to false given the way the numerics are handled.
albalkum: Sqlresultsetreader didn't have an else if for a booleancolumn. It's handled by resultset.getObject() which properly return nulls. Part of my addition to the test covers Boolean values and nulls are correctly processed. 
albalkum: Your comment made me realize that resultset.getObject() might be able to be used for all column cases. Wouldn't change anything but remove the if complexity. I might take a look when i get back to my computer. 
albalkum: I tried just utilizing resultSet.getObject() but there were issues when appending BigDecimal objects.  I kept the existing if else blocks and added a BooleanColumn check for clarity's sake, per your suggestion.
benmccann: I'm not sure I'd add a case for `BooleanColumn` if it's not necessary. It's a bit confusing to me that it'd be singled out and not the other types such as `Date`, etc. I think adding a test to make sure it works properly is enough.

Patch:
diff --git a/core/src/main/java/tech/tablesaw/api/DoubleColumn.java b/core/src/main/java/tech/tablesaw/api/DoubleColumn.java
index 0a9e91db..32d72580 100644
--- a/core/src/main/java/tech/tablesaw/api/DoubleColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/DoubleColumn.java
@@ -16,6 +16,7 @@ import tech.tablesaw.columns.Column;
 import tech.tablesaw.columns.AbstractColumnParser;
 import tech.tablesaw.columns.numbers.DoubleColumnType;
 import tech.tablesaw.columns.numbers.FloatColumnType;
+import tech.tablesaw.columns.numbers.NumberColumnFormatter;
 import tech.tablesaw.columns.numbers.NumberFillers;
 import tech.tablesaw.columns.numbers.fillers.DoubleRangeIterable;
 import tech.tablesaw.selection.Selection;
@@ -42,6 +43,7 @@ public class DoubleColumn extends NumberColumn<Double> implements NumberFillers<
 
     protected DoubleColumn(final String name, final DoubleArrayList data) {
         super(DoubleColumnType.instance(), name);
+        setPrintFormatter(NumberColumnFormatter.floatingPointDefault());
         this.data = data;
     }
 
@@ -51,7 +53,7 @@ public class DoubleColumn extends NumberColumn<Double> implements NumberFillers<
         if (DoubleColumnType.isMissingValue(value)) {
             return "";
         }
-        return String.valueOf(printFormatter.format(value));
+        return String.valueOf(getPrintFormatter().format(value));
     }
 
     @Override
@@ -71,6 +73,7 @@ public class DoubleColumn extends NumberColumn<Double> implements NumberFillers<
 
     protected DoubleColumn(final String name) {
         super(DoubleColumnType.instance(), name);
+        setPrintFormatter(NumberColumnFormatter.floatingPointDefault());
         this.data = new DoubleArrayList(DEFAULT_ARRAY_SIZE);
     }
 
diff --git a/core/src/main/java/tech/tablesaw/api/FloatColumn.java b/core/src/main/java/tech/tablesaw/api/FloatColumn.java
index 3f411555..750f2e42 100644
--- a/core/src/main/java/tech/tablesaw/api/FloatColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/FloatColumn.java
@@ -14,6 +14,7 @@ import it.unimi.dsi.fastutil.shorts.ShortArrayList;
 import tech.tablesaw.columns.Column;
 import tech.tablesaw.columns.AbstractColumnParser;
 import tech.tablesaw.columns.numbers.FloatColumnType;
+import tech.tablesaw.columns.numbers.NumberColumnFormatter;
 import tech.tablesaw.selection.Selection;
 
 import java.nio.ByteBuffer;
@@ -34,6 +35,7 @@ public class FloatColumn extends NumberColumn<Float> {
 
     private FloatColumn(final String name, FloatArrayList data) {
         super(FloatColumnType.instance(), name);
+        setPrintFormatter(NumberColumnFormatter.floatingPointDefault());
         this.data = data;
     }
 
@@ -43,7 +45,7 @@ public class FloatColumn extends NumberColumn<Float> {
         if (FloatColumnType.isMissingValue(value)) {
             return "";
         }
-        return String.valueOf(printFormatter.format(value));
+        return String.valueOf(getPrintFormatter().format(value));
     }
 
 
diff --git a/core/src/main/java/tech/tablesaw/api/IntColumn.java b/core/src/main/java/tech/tablesaw/api/IntColumn.java
index 02ad14eb..451fa68b 100644
--- a/core/src/main/java/tech/tablesaw/api/IntColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/IntColumn.java
@@ -36,7 +36,7 @@ public class IntColumn extends NumberColumn<Integer> implements CategoricalColum
 
     protected IntColumn(final String name, IntArrayList data) {
         super(IntColumnType.instance(), name);
-        this.printFormatter = NumberColumnFormatter.ints();
+        setPrintFormatter(NumberColumnFormatter.ints());
         this.data = data;
     }
 
@@ -272,7 +272,7 @@ public class IntColumn extends NumberColumn<Integer> implements CategoricalColum
         if (IntColumnType.isMissingValue(value)) {
             return "";
         }
-        return String.valueOf(printFormatter.format(value));
+        return String.valueOf(getPrintFormatter().format(value));
     }
 
     @Override
diff --git a/core/src/main/java/tech/tablesaw/api/LongColumn.java b/core/src/main/java/tech/tablesaw/api/LongColumn.java
index 8dfb6696..9a46ba07 100644
--- a/core/src/main/java/tech/tablesaw/api/LongColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/LongColumn.java
@@ -38,7 +38,7 @@ public class LongColumn extends NumberColumn<Long> implements CategoricalColumn<
 
     private LongColumn(final String name, LongArrayList data) {
         super(LongColumnType.instance(), name);
-        this.printFormatter = NumberColumnFormatter.ints();
+        setPrintFormatter(NumberColumnFormatter.ints());
         this.data = data;
     }
 
@@ -87,7 +87,7 @@ public class LongColumn extends NumberColumn<Long> implements CategoricalColumn<
         if (LongColumnType.isMissingValue(value)) {
             return "";
         }
-        return String.valueOf(printFormatter.format(value));
+        return String.valueOf(getPrintFormatter().format(value));
     }
 
     public static boolean valueIsMissing(long value) {
diff --git a/core/src/main/java/tech/tablesaw/api/NumberColumn.java b/core/src/main/java/tech/tablesaw/api/NumberColumn.java
index dea3550b..17e2ff61 100644
--- a/core/src/main/java/tech/tablesaw/api/NumberColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/NumberColumn.java
@@ -11,7 +11,7 @@ import java.util.function.DoublePredicate;
 
 public abstract class NumberColumn<T extends Number> extends AbstractColumn<T> implements NumericColumn<T> {
 
-    protected NumberColumnFormatter printFormatter = new NumberColumnFormatter();
+    private NumberColumnFormatter printFormatter = new NumberColumnFormatter();
 
     protected Locale locale;
 
@@ -59,6 +59,10 @@ public abstract class NumberColumn<T extends Number> extends AbstractColumn<T> i
         this.printFormatter = formatter;
     }
 
+    protected NumberColumnFormatter getPrintFormatter() {
+        return printFormatter;
+    }
+
     /**
      * Returns the largest ("top") n values in the column
      * TODO(lwhite): Consider whether this should exclude missing
diff --git a/core/src/main/java/tech/tablesaw/api/ShortColumn.java b/core/src/main/java/tech/tablesaw/api/ShortColumn.java
index 0516f6a3..49716b10 100644
--- a/core/src/main/java/tech/tablesaw/api/ShortColumn.java
+++ b/core/src/main/java/tech/tablesaw/api/ShortColumn.java
@@ -35,7 +35,7 @@ public class ShortColumn extends NumberColumn<Short> implements CategoricalColum
 
     protected ShortColumn(final String name, ShortArrayList data) {
         super(ShortColumnType.instance(), name);
-        this.printFormatter = NumberColumnFormatter.ints();
+        setPrintFormatter(NumberColumnFormatter.ints());
         this.data = data;
     }
 
@@ -241,7 +241,7 @@ public class ShortColumn extends NumberColumn<Short> implements CategoricalColum
         if (ShortColumnType.isMissingValue(value)) {
             return "";
         }
-        return String.valueOf(printFormatter.format(value));
+        return String.valueOf(getPrintFormatter().format(value));
     }
 
     @Override
diff --git a/core/src/main/java/tech/tablesaw/columns/numbers/NumberColumnFormatter.java b/core/src/main/java/tech/tablesaw/columns/numbers/NumberColumnFormatter.java
index a914c830..9241863d 100644
--- a/core/src/main/java/tech/tablesaw/columns/numbers/NumberColumnFormatter.java
+++ b/core/src/main/java/tech/tablesaw/columns/numbers/NumberColumnFormatter.java
@@ -1,6 +1,7 @@
 package tech.tablesaw.columns.numbers;
 
 import java.text.DecimalFormat;
+import java.text.DecimalFormatSymbols;
 import java.text.NumberFormat;
 import java.util.Locale;
 
@@ -17,6 +18,24 @@ public class NumberColumnFormatter {
         return new NumberColumnFormatter(format);
     }
 
+    /**
+     * Returns a formatter that prints floating point numbers with all precision
+     */
+    public static NumberColumnFormatter floatingPointDefault() {
+        NumberFormat format = new DecimalFormat("0", DecimalFormatSymbols.getInstance(Locale.getDefault()));
+        format.setMaximumFractionDigits(340);
+        format.setMaximumIntegerDigits(340);
+        format.setGroupingUsed(false);
+        return new NumberColumnFormatter(format);
+    }
+
+    /**
+     * Formats numbers using java default, so sometimes in scientific notation, sometimes not
+     */
+    public static NumberColumnFormatter standard() {
+        return new NumberColumnFormatter();
+    }
+
     public static NumberColumnFormatter ints() {
         NumberFormat format = new DecimalFormat();
         format.setGroupingUsed(false);
diff --git a/core/src/main/java/tech/tablesaw/io/jdbc/SqlResultSetReader.java b/core/src/main/java/tech/tablesaw/io/jdbc/SqlResultSetReader.java
index 2e984b79..a40e048f 100644
--- a/core/src/main/java/tech/tablesaw/io/jdbc/SqlResultSetReader.java
+++ b/core/src/main/java/tech/tablesaw/io/jdbc/SqlResultSetReader.java
@@ -16,6 +16,8 @@ package tech.tablesaw.io.jdbc;
 
 import com.google.common.base.Preconditions;
 import com.google.common.collect.ImmutableMap;
+
+import tech.tablesaw.api.BooleanColumn;
 import tech.tablesaw.api.ColumnType;
 import tech.tablesaw.api.DoubleColumn;
 import tech.tablesaw.api.FloatColumn;
@@ -108,15 +110,17 @@ public class SqlResultSetReader {
             for (int i = 1; i <= metaData.getColumnCount(); i++) {
                 Column<?> column = table.column(i - 1); // subtract 1 because results sets originate at 1 not 0
                 if (column instanceof ShortColumn) {
-                    column.appendObj(resultSet.getShort(i));
+                	appendToColumn(column, resultSet, resultSet.getShort(i));
                 } else if (column instanceof IntColumn) {
-                    column.appendObj(resultSet.getInt(i));
+                	appendToColumn(column, resultSet, resultSet.getInt(i));
                 } else if (column instanceof LongColumn) {
-                    column.appendObj(resultSet.getLong(i));
+                	appendToColumn(column, resultSet, resultSet.getLong(i));
                 } else if (column instanceof FloatColumn) {
-                    column.appendObj(resultSet.getFloat(i));
+                	appendToColumn(column, resultSet, resultSet.getFloat(i));
                 } else if (column instanceof DoubleColumn) {
-                    column.appendObj(resultSet.getDouble(i));
+                	appendToColumn(column, resultSet, resultSet.getDouble(i));
+                } else if (column instanceof BooleanColumn) {
+                	appendToColumn(column, resultSet, resultSet.getBoolean(i));                
                 } else {
                     column.appendObj(resultSet.getObject(i));
                 }
@@ -125,6 +129,14 @@ public class SqlResultSetReader {
         return table;
     }
     
+    protected static void appendToColumn(Column<?> column, ResultSet resultSet, Object value) throws SQLException {
+    	if (resultSet.wasNull()) {
+    		column.appendMissing();
+    	} else {
+    		column.appendObj(value);
+    	}
+    }
+    
     protected static ColumnType getColumnType(int columnType, int scale, int precision) {
         ColumnType type = SQL_TYPE_TO_TABLESAW_TYPE.get(columnType);
         // Try to improve on the initial type assigned to 'type' to minimize size/space of type needed.


Test patch:
diff --git a/core/src/test/java/tech/tablesaw/api/TableTest.java b/core/src/test/java/tech/tablesaw/api/TableTest.java
index 7232dda7..0a6c70a2 100644
--- a/core/src/test/java/tech/tablesaw/api/TableTest.java
+++ b/core/src/test/java/tech/tablesaw/api/TableTest.java
@@ -66,7 +66,7 @@ public class TableTest {
         Table result = table.summarize("Injuries", mean, stdDev).by("State");
         assertEquals(49, result.rowCount());
         assertEquals(3, result.columnCount());
-        assertEquals("4.580805569368455", result.column(1).getString(0));
+        assertEquals("4.580805569368455", result.doubleColumn(1).getString(0));
     }
 
     @Test
diff --git a/core/src/test/java/tech/tablesaw/io/jdbc/SqlResultSetReaderTest.java b/core/src/test/java/tech/tablesaw/io/jdbc/SqlResultSetReaderTest.java
index ff5f0b4d..f5f22d58 100644
--- a/core/src/test/java/tech/tablesaw/io/jdbc/SqlResultSetReaderTest.java
+++ b/core/src/test/java/tech/tablesaw/io/jdbc/SqlResultSetReaderTest.java
@@ -65,6 +65,9 @@ public class SqlResultSetReaderTest {
         // Build the OracleNumbers table.
         TestDb.buildNumbersTable(conn);
         
+        // Build the NullValues table.
+        TestDb.buildNullValuesTable(conn);
+        
         try (Statement stmt = conn.createStatement()) {
             String sql;
 
@@ -107,6 +110,20 @@ public class SqlResultSetReaderTest {
                 assertTrue(numbers.column("NumDouble7_8").type() instanceof DoubleColumnType);
                 assertTrue(numbers.column("NumDouble7_16").type() instanceof DoubleColumnType);
             }
+            
+            sql = "SELECT * FROM NullValues";
+            try (ResultSet rs = stmt.executeQuery(sql)) {
+                Table nullValues = SqlResultSetReader.read(rs);
+                assertEquals(8, nullValues.columnCount());
+                assertEquals(3, nullValues.rowCount());
+                assertEquals(2, nullValues.column("StringValue").removeMissing().size());
+                assertEquals(1, nullValues.column("DoubleValue").removeMissing().size());
+                assertEquals(2, nullValues.column("IntegerValue").removeMissing().size());
+                assertEquals(1, nullValues.column("ShortValue").removeMissing().size());
+                assertEquals(1, nullValues.column("LongValue").removeMissing().size());
+                assertEquals(1, nullValues.column("FloatValue").removeMissing().size());
+                assertEquals(1, nullValues.column("BooleanValue").removeMissing().size());
+            }
 
         }
     }
diff --git a/core/src/test/java/tech/tablesaw/io/string/DataFramePrinterTest.java b/core/src/test/java/tech/tablesaw/io/string/DataFramePrinterTest.java
index 2eeb9379..0084344d 100644
--- a/core/src/test/java/tech/tablesaw/io/string/DataFramePrinterTest.java
+++ b/core/src/test/java/tech/tablesaw/io/string/DataFramePrinterTest.java
@@ -2,7 +2,9 @@ package tech.tablesaw.io.string;
 
 import org.junit.jupiter.api.Test;
 import tech.tablesaw.api.DoubleColumn;
+import tech.tablesaw.api.FloatColumn;
 import tech.tablesaw.api.Table;
+import tech.tablesaw.columns.numbers.NumberColumnFormatter;
 
 import static org.junit.jupiter.api.Assertions.assertTrue;
 
@@ -30,5 +32,65 @@ public class DataFramePrinterTest {
         assertTrue(out.contains("fox"));
     }
 
-    
+    @Test
+    public void printWithSmallDoubleNumber() {
+        DoubleColumn col = DoubleColumn.create("testCol");
+        col.append(0.000003);
+        Table table = Table.create("small decimal table", col);
+        String out = table.print();
+        assertTrue(out.contains("0.000003"));
+    }
+
+    @Test
+    public void printWithSmallFloatNumber() {
+        FloatColumn col = FloatColumn.create("testCol");
+        col.append(0.000003f);
+        Table table = Table.create("small float table", col);
+        String out = table.print();
+        assertTrue(out.contains("0.000003"));
+    }
+
+    @Test
+    public void printWithExponent() {
+        DoubleColumn col = DoubleColumn.create("testCol");
+        col.append(0.000003);
+        col.append(123.000003);
+        col.setPrintFormatter(NumberColumnFormatter.standard());
+        Table table = Table.create("small float table", col);
+        String out = table.print();
+        assertTrue(out.contains("3.0E-6"));
+        assertTrue(out.contains("123.000003"));
+    }
+
+    @Test
+    public void printWithLargeDoubleNumber() {
+        DoubleColumn col = DoubleColumn.create("testCol");
+        col.append(33.333333333333328);
+        col.append(9007199254740992d);
+        col.append(900719925474099.1d);
+        col.append(90071992547409.11d);
+        Table table = Table.create("large float table", col);
+        String out = table.print();
+        assertTrue(out.contains("33.3333333333333"));
+        assertTrue(out.contains("9007199254740992"));
+        assertTrue(out.contains("900719925474099.1"));
+        assertTrue(out.contains("90071992547409.11"));
+    }
+
+    @Test
+    public void printWithLargeFloatNumber() {
+        FloatColumn col = FloatColumn.create("testCol");
+        col.append(33.333333333333328f);
+        col.append(900719925474f);
+        col.append(9007199254.1f);
+        col.append(90071992.11f);
+        col.append(90071.11f);
+        Table table = Table.create("large float table", col);
+        String out = table.print();
+        assertTrue(out.contains("33.33333206176758"));
+        assertTrue(out.contains("900719902720"));
+        assertTrue(out.contains("9007199232"));
+        assertTrue(out.contains("90071992"));
+        assertTrue(out.contains("90071.109375"));
+    }
 }
diff --git a/core/src/test/java/tech/tablesaw/util/TestDb.java b/core/src/test/java/tech/tablesaw/util/TestDb.java
index 23d443d9..79a2360b 100644
--- a/core/src/test/java/tech/tablesaw/util/TestDb.java
+++ b/core/src/test/java/tech/tablesaw/util/TestDb.java
@@ -101,6 +101,14 @@ public class TestDb {
                 // No need to report an error.
                 // The table simply did not exist.
             }
+            
+            try {
+                // Drop the NullValues table.
+                stmt.execute("DROP TABLE NullValues");
+            } catch (SQLException ex) {
+                // No need to report an error.
+                // The table simply did not exist.
+            }
         } catch (SQLException ex) {
             System.out.println("ERROR: " + ex.getMessage());
             ex.printStackTrace();
@@ -390,5 +398,50 @@ public class TestDb {
         }
     }
 
+    /**
+     * The buildNullValues method creates the
+     * NullValues table and adds some rows to it.
+     */
+    public static void buildNullValuesTable(Connection conn) {
+        try {
+            // Get a Statement object.
+            Statement stmt = conn.createStatement();
+
+            // Create the table.
+            stmt.execute("CREATE TABLE NullValues (" +
+                    "StringValue CHAR(25), " +
+                    "PrimaryValue CHAR(10) NOT NULL PRIMARY KEY, " +
+                    "DoubleValue DOUBLE, " +
+                    "IntegerValue INTEGER, " +
+                    "ShortValue SMALLINT, " +
+                    "LongValue BIGINT, " +
+                    "FloatValue FLOAT, " +
+                    "BooleanValue Boolean" +
+                    ")");
+
+            // Insert row #1.
+            stmt.execute("INSERT INTO NullValues VALUES ( " +
+                    "'Non Null Description', " +
+                    "'001', " +
+                    "8.95, " +
+                    "1, " +
+                    "1, " +
+                    "1, " +
+                    "3.14, " +
+                    "TRUE )");
+
+            // Insert row #2.
+            stmt.execute("INSERT INTO NullValues (PrimaryValue, IntegerValue) VALUES ( " +
+                    "'002', " +
+                    "2 )");
+
+            // Insert row #3.
+            stmt.execute("INSERT INTO NullValues (StringValue, PrimaryValue) VALUES ( " +
+                    "'Non Null Description', " +
+                    "'003')");
+        } catch (SQLException ex) {
+            System.out.println("ERROR: " + ex.getMessage());
+        }
+    }
 
 }
\ No newline at end of file
diff --git a/html/src/test/java/tech/tablesaw/io/html/HtmlWriterTest.java b/html/src/test/java/tech/tablesaw/io/html/HtmlWriterTest.java
index c8dc895f..05641c36 100644
--- a/html/src/test/java/tech/tablesaw/io/html/HtmlWriterTest.java
+++ b/html/src/test/java/tech/tablesaw/io/html/HtmlWriterTest.java
@@ -51,12 +51,12 @@ public class HtmlWriterTest {
                 " </thead>" + LINE_END +
                 " <tbody>" + LINE_END +
                 "  <tr>" + LINE_END +
-                "   <td>1.0</td>" + LINE_END +
-                "   <td>1.0</td>" + LINE_END +
+                "   <td>1</td>" + LINE_END +
+                "   <td>1</td>" + LINE_END +
                 "  </tr>" + LINE_END +
                 "  <tr>" + LINE_END +
-                "   <td>2.0</td>" + LINE_END +
-                "   <td>2.0</td>" + LINE_END +
+                "   <td>2</td>" + LINE_END +
+                "   <td>2</td>" + LINE_END +
                 "  </tr>" + LINE_END +
                 "  <tr>" + LINE_END +
                 "   <td></td>" + LINE_END +
@@ -87,12 +87,12 @@ public class HtmlWriterTest {
                 " </thead>" + LINE_END +
                 " <tbody>" + LINE_END +
                 "  <tr class=\"even\">" + LINE_END +
-                "   <td>1.0</td>" + LINE_END +
-                "   <td>1.0</td>" + LINE_END +
+                "   <td>1</td>" + LINE_END +
+                "   <td>1</td>" + LINE_END +
                 "  </tr>" + LINE_END +
                 "  <tr class=\"odd\">" + LINE_END +
-                "   <td>2.0</td>" + LINE_END +
-                "   <td>2.0</td>" + LINE_END +
+                "   <td>2</td>" + LINE_END +
+                "   <td>2</td>" + LINE_END +
                 "  </tr>" + LINE_END +
                 "  <tr class=\"even\">" + LINE_END +
                 "   <td></td>" + LINE_END +