java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Boolean, using Table.addRow() with Jackcess

Question

I am trying to write some values in a MS Access database using Jackcess. My values are originally represented using String. The code I am using is the following:

int nColumns = 0;

// get table from internal representation
ModelDatabaseTable table = this.DB.getTable(tableName);

// create new table
TableBuilder DBTableBuilder = new TableBuilder(tableName);

// get table's columns and their Jackcess datatype    
Map<String, DataType> columns = table.getColumns();

// for each column, insert it in the actual database
for (String columnName : columns.keySet()) {
    DataType dt = columns.get(columnName);
    ColumnBuilder cb = new ColumnBuilder(columnName).setType(dt);
    if (dt.isVariableLength()) {
        cb.setMaxLength();
    }
    DBTableBuilder.addColumn(cb);
    nColumns += 1;
}

// if columns were inserted
if (nColumns > 0) {
    // write table to actual database
    Table DBTable = DBTableBuilder.toTable(this.DBConnection);

    // for each row
    for (ModelDatabaseRow row : table.getRows()) {

        // get list of values (represented using String)
        List<String> values = new ArrayList<String>();

        // for each column get its value and insert it in values
        for (String columnName : columns.keySet()) {
            String columnValue = row.getColumn(columnName);
            values.add(columnValue);
        }

        // print current row
        System.out.println(values.toString());

        // insert row in database table. Exception rises here:
        // java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Boolean
        DBTable.addRow(values.toArray());
    }
}

A basic example which does not work is the following (routine data is described using JSON). In this case the routine stops when trying to insert BOOLEAN values (HasM, HasZ) but it is able to insert DOUBLE values - which are all given as parameters to Table.addRow() function as a String array.

{
  "tables": {
    "Table1": {
      "rows": [
      {
        "items": {
        "ExtentBottom": "45.050715999999994",
        "ExtentLeft": "7.644834000000003",
        "ExtentRight": "7.670400999999998",
        "ExtentTop": "45.07392899999999",
        "FieldName": "Shape",
        "HasM": "false",
        "HasZ": "false",
        "IdxGridSize": "3.7252903001966386E-7",
        "IdxOriginX": "0.0",
        "IdxOriginY": "0.0",
        "MHigh": "NaN",
        "MLow": "NaN",
        "SRID": "1",
        "ShapeType": "4",
        "TableName": "GDB_Items",
        "ZHigh": "NaN",
        "ZLow": "NaN"
        }
      },
      {
        "items": {
        "ExtentBottom": "4989476.8181",
        "ExtentLeft": "393329.1171000004",
        "ExtentRight": "395300.25320000015",
        "ExtentTop": "4992023.569399999",
        "FieldName": "Shape",
        "HasM": "false",
        "HasZ": "false",
        "IdxGridSize": "0.009311329524584121",
        "IdxOriginX": "0.0",
        "IdxOriginY": "0.0",
        "MHigh": "NaN",
        "MLow": "NaN",
        "SRID": "2",
        "ShapeType": "4",
        "TableName": "Building_DIMMER_01",
        "ZHigh": "NaN",
        "ZLow": "NaN"
        }
      }
      ],
      "columns": {
      "ExtentBottom": "DOUBLE",
      "ExtentLeft": "DOUBLE",
      "ExtentRight": "DOUBLE",
      "ExtentTop": "DOUBLE",
      "FieldName": "TEXT",
      "HasM": "BOOLEAN",
      "HasZ": "BOOLEAN",
      "IdxGridSize": "DOUBLE",
      "IdxOriginX": "DOUBLE",
      "IdxOriginY": "DOUBLE",
      "MHigh": "DOUBLE",
      "MLow": "DOUBLE",
      "SRID": "LONG",
      "ShapeType": "LONG",
      "TableName": "TEXT",
      "ZHigh": "DOUBLE",
      "ZLow": "DOUBLE"
      } 
    }
  }
}

The preceding JSON represents the internal representation of data used by my program and it is structured in this way:

{
  "tables": {
    "TableName": {
      "rows": [
        {
          "items: {
            "columnName1": "columnValue1",
            ...
            "columnNameN": "columnValueN"
           }
        },
        {
          "items: {
            "columnName1": "columnValue1",
            ...
            "columnNameN": "columnValueN"
           }
        }
      ],
      "columns": {
        "columnName1": "columnDataType1",
        ...
        "columnNameN": "columnDataTypeN"
      }
    }
  }
}

In case it is not clear ask me,

Thanks

Solution

Jackcess normally expects to work with strongly-typed Java objects. In this case Jackcess must be trying to be helpful by automatically converting a String to a Double when adding the Row. For whatever reason, Jackcess is not willing to automatically convert a String to a Boolean^*, even if that conversion seems obvious (to us), so for a table with fields

ID: AutoNumber, Primary Key
DoubleField: Numeric(Double)
YesnoField: Yes/No

the following code will throw the exception you are seeing

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, doubleFieldValue, yesnoFieldValue);

However, this will work

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, doubleFieldValue, Boolean.parseBoolean(yesnoFieldValue));

As a general rule it is best to try and use objects of the correct type when working with Jackcess to avoid little "surprises" like this, so by rights we really should be using

String doubleFieldValue = "3.14159";
String yesnoFieldValue = "true";
tbl.addRow(Column.AUTO_NUMBER, Double.parseDouble(doubleFieldValue), Boolean.parseBoolean(yesnoFieldValue));

^* Edit: 2015-03-21

Automatic conversion of String values to Boolean was added to Jackcess 2.0.9, released today.

Comments

[Elliott Frisch]

The error seems pretty clear to me. Parse the Boolean columns from String before attempting to insert them into your Access table. What isn't clear about the Exception? You mentioned that Double worked as you expected, but that's got to be how your database schema is defined (note, you did not post your schema).

[Bryan Devaney]

hmm, well its kind of hacky but you could, on the line before you insert, test the bool condition and depending on the case, insert a string 'true' or 'false' instead? edit: i see you are inserting 2 booleans, in that case* a 4 way switch startement (true,true.......False,False) and insert the strings. hacky but should get you going. *heh