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
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.