I have some entities, repositories, and a predicate to handle a dynamic query generator. When I run a simple query I get an exception.
StackTrace (Edited to reduce post size)
2016-11-11 11:52:05,405 [http-bio-8080-exec-13] ERROR com.etisoftware.lib.spring.web.controller.GlobalExceptionHandlerAdvice:34 - An uncaught exception occurred
org.springframework.orm.jpa.JpaSystemException: Error accessing field [private java.lang.Integer com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner.number] by reflection for persistent property [com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner#number] : 1; nested exception is org.hibernate.property.access.spi.PropertyAccessException: Error accessing field [private java.lang.Integer com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner.number] by reflection for persistent property [com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner#number] : 1
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
Caused by: org.hibernate.property.access.spi.PropertyAccessException: Error accessing field [private java.lang.Integer com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner.number] by reflection for persistent property [com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner#number] : 1
at org.hibernate.property.access.spi.GetterFieldImpl.get(GetterFieldImpl.java:71)
at org.hibernate.tuple.entity.AbstractEntityTuplizer.getIdentifier(AbstractEntityTuplizer.java:224)
... 89 more
Caused by: java.lang.IllegalArgumentException: Can not set java.lang.Integer field com.etisoftware.workorderprinting.beans.cborg.WorkOrderOwner.number to java.lang.String
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:167)
at sun.reflect.UnsafeFieldAccessorImpl.throwSetIllegalArgumentException(UnsafeFieldAccessorImpl.java:171)
at sun.reflect.UnsafeFieldAccessorImpl.ensureObj(UnsafeFieldAccessorImpl.java:58)
at sun.reflect.UnsafeObjectFieldAccessorImpl.get(UnsafeObjectFieldAccessorImpl.java:36)
at java.lang.reflect.Field.get(Field.java:393)
at org.hibernate.property.access.spi.GetterFieldImpl.get(GetterFieldImpl.java:67)
... 133 more
WorkOrder Entity
@Entity
@Table(name = "workorders")
public class WorkOrder
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "workorder_nbr")
private Integer number;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "subscriber_nbr")
private Subscriber subscriber;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "wo_type")
private WorkOrderType type;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "pool_nbr")
private WorkerPool pool;
@JoinColumn(name = "wo_status_nbr")
@ManyToOne(fetch = FetchType.LAZY)
private WorkOrderStatus status;
@JoinColumn(name = "wo_owner_nbr")
@ManyToOne(fetch = FetchType.LAZY)
private WorkOrderOwner owner;
@Column(name = "sched_date")
private LocalDate scheduleDate;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "slot_nbr")
private TimeSlot slot;
@Column(name = "auto_complete_date")
private LocalDate autoCompleteDate;
@Column(name = "override")
private Boolean override;
@JoinColumn(name = "wo_location_nbr")
@ManyToOne(fetch = FetchType.LAZY)
private Location location;
/*
* Fetch the notes later in the service using this number because connecting the notes table is a pain
*/
@Column(name = "wo_note_nbr")
private Integer noteNumber;
WorkOrderOwner Entity
@Entity
@Table(name = "wo_owners")
public class WorkOrderOwner
{
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "wo_owner_nbr")
private Integer number;
@Column(name = "owner_id")
private String name;
@Column(name = "email_address")
private String email_address;
WorkOrderPredicates
@Component
public class WorkOrderPredicates
{
private static DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("MM/dd/yyyy");
private static DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("hh:mm:ss");
private static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("MM/dd/yyyy hh:mm:ss");
public Predicate buildQueryFromSearchCriteria(PrintWorkOrderFormDto queryDto)
{
BooleanBuilder builder = new BooleanBuilder();
List<Predicate> predicates = new ArrayList<>();
/*
* TODO I think think this string "entity" which is a variable name is going to give us a problem.
*/
PathBuilder<WorkOrder> entityPath = new PathBuilder<>(WorkOrder.class, "workOrder");
for (SelectedCriteria criteria : queryDto.getCriteria().getRules())
{
assignValueAndOperator(predicates, entityPath, criteria);
}
/*
* Per Chris, the queries will only have one group. That is, there will be a single condition [AND | OR] and then a
* list of criteria.
*/
if (queryDto.getCriteria().getCondition().equals(SelectionFilterCondition.AND))
{
for (Predicate p : predicates)
builder.and(p);
}
else if (queryDto.getCriteria().getCondition().equals(SelectionFilterCondition.OR))
{
for (Predicate p : predicates)
builder.or(p);
}
return builder;
}
@SuppressWarnings("unchecked")
protected static void assignValueAndOperator(List<Predicate> predicates, PathBuilder<?> entityPath, SelectedCriteria criteria)
{
switch (criteria.getOperator())
{
case BETWEEN:
List<LocalDate> values = (List<LocalDate>) criteria.getValue();
LocalDate from = values.get(0);
LocalDate to = values.get(1);
// TODO how do we handle Time separately from Date.
// TODO how do we handle DateTime
predicates.add(entityPath.getDate(criteria.getField(), LocalDate.class).between(from, to));
break;
case BEGINS_WITH:
predicates.add(entityPath.getString(criteria.getField()).like(criteria.getValue() + "%"));
break;
case NOT_BEGINS_WITH:
predicates.add(entityPath.getString(criteria.getField()).notLike(criteria.getValue() + "%"));
break;
case CONTAINS:
predicates.add(entityPath.getString(criteria.getField()).like("%" + criteria.getValue() + "%"));
break;
case NOT_CONTAINS:
predicates.add(entityPath.getString(criteria.getField()).notLike("%" + criteria.getValue() + "%"));
break;
case ENDS_WITH:
predicates.add(entityPath.getString(criteria.getField()).like("%" + criteria.getValue()));
break;
case NOT_ENDS_WITH:
predicates.add(entityPath.getString(criteria.getField()).notLike("%" + criteria.getValue()));
break;
case EQUAL:
predicates.add(entityPath.get(criteria.getField()).eq(extractAndCreateValueOfType(criteria.getValue(), criteria.getType())));
break;
case NOT_EQUAL:
predicates.add(entityPath.get(criteria.getField()).ne(criteria.getValue()));
break;
case GREATER:
// TODO this casting might not be right
predicates.add(entityPath.getNumber(criteria.getField(), Integer.class).gt((Integer) criteria.getValue()));
break;
case LESS:
// TODO this casting might not be right
predicates.add(entityPath.getNumber(criteria.getField(), Integer.class).lt((Integer) criteria.getValue()));
break;
case LESS_OR_EQUAL:
// TODO this casting might not be right
predicates.add(entityPath.getNumber(criteria.getField(), Integer.class).loe((Integer) criteria.getValue()));
break;
case GREATER_OR_EQUAL:
// TODO this casting might not be right
predicates.add(entityPath.getNumber(criteria.getField(), Integer.class).goe((Integer) criteria.getValue()));
break;
case IN:
/*
* TODO this is causing a hibernate error because its trying to map an integer to a string. This type setting
* below needs to be dynamic based on the selectedcriteria type
*/
List<String> inValues = (List<String>) criteria.getValue();
predicates.add(entityPath.get(criteria.getField(), String.class).in(inValues));
break;
case NOT_IN:
List<String> notInValues = (List<String>) criteria.getValue();
predicates.add(entityPath.get(criteria.getField(), String.class).in(notInValues).not());
break;
case IS_EMPTY:
predicates.add(entityPath.getList(criteria.getField(), List.class).isEmpty());
break;
case IS_NOT_EMPTY:
predicates.add(entityPath.getList(criteria.getField(), List.class).isNotEmpty());
break;
case IS_NOT_NULL:
predicates.add(entityPath.get(criteria.getField()).isNotNull());
break;
case IS_NULL:
predicates.add(entityPath.get(criteria.getField()).isNull());
break;
default:
// TODO implement the default case
break;
}
}
@SuppressWarnings("cast")
private static Object extractAndCreateValueOfType(Object valueObject, SelectionDataType selectionDataType)
{
Object value = null;
switch (selectionDataType)
{
case BOOLEAN:
// TODO
break;
case DATE:
value = LocalDate.parse((String) valueObject, dateFormatter);
break;
case TIME:
value = LocalDateTime.parse((String) valueObject, timeFormatter);
break;
case DATETIME:
value = LocalDateTime.parse((String) valueObject, dateTimeFormatter);
break;
case DOUBLE:
value = (Double) valueObject;
break;
case INTEGER:
value = (Integer) valueObject;
break;
case STRING:
value = (String) valueObject;
break;
default:
break;
}
return value;
}
WorkOrderService fetch method
@Transactional(transactionManager = PersistenceConfigCbOrgAbstract.txMgrName, readOnly = true)
protected List<WorkOrderDto> fetchWorkOrders(PrintWorkOrderFormDto queryParams, PrintStatus printStatus)
{
List<WorkOrderDto> dtos = new ArrayList<>();
printStatus.setMessage("Fetching work orders");
logger.debug("Query Params: " + queryParams);
for (WorkOrder bean : workOrderRepository.findAll(workOrderPredicates.buildQueryFromSearchCriteria(queryParams)))
{
logger.debug(ToStringBuilder.reflectionToString(bean));
WorkOrderDto dto = modelMapper.map(bean, WorkOrderDto.class);
fetchAndAssignNotes(dto, bean.getNoteNumber());
fetchAndAssignCreateDate(dto, bean.getNumber());
fetchAndAssignPendingDeviceDtos(dto, bean.getNumber());
fetchAndAssignPendingServiceDtos(dto, bean.getNumber());
fetchAndAssignBilling(dto, bean.getNumber());
try
{
dto.setCustomer(new CustomerDto());
fetchAndAssignCustomer(dto);
}
catch (JsonParseException e)
{
logger.error("Could not parse customer information", e);
printStatus.setMessage("Cannot fetch work orders. Please check the application log");
}
catch (JsonMappingException e)
{
logger.error("Could not map customer information", e);
printStatus.setMessage("Cannot fetch work orders. Please check the application log");
}
catch (IOException e)
{
logger.error("Got an IO exception while trying to map customer information", e);
printStatus.setMessage("Cannot fetch work orders. Please check the application log");
}
logger.debug(ToStringBuilder.reflectionToString(dto, ToStringStyle.MULTI_LINE_STYLE, Boolean.TRUE));
printStatus.setRecordCount(printStatus.getRecordCount() + 1);
dtos.add(dto);
}
return dtos;
}
wo_owners table
CREATE TABLE wo_owners (
wo_owner_nbr serial NOT NULL,
owner_id nchar(20) NOT NULL,
email_address nchar(80),
PRIMARY KEY (wo_owner_nbr)
);
workorders
CREATE TABLE cborg2001:workorders (
workorder_nbr serial NOT NULL,
subscriber_nbr int,
wo_type int,
pool_nbr int,
wo_status_nbr int,
wo_owner_nbr int,
wo_note_nbr int,
sched_date date,
slot_nbr int,
auto_complete_date date,
override int DEFAULT 0,
wo_location_nbr int,
PRIMARY KEY (workorder_nbr)
);
QueryDSL Query
com.querydsl.jpa.impl.JPAQuery:233 - select workOrder from WorkOrder workOrder where workOrder.scheduleDate = ?1
Generated Query
select
workorder0_.workorder_nbr as workorde1_28_,
workorder0_.auto_complete_date as auto_com2_28_,
workorder0_.wo_location_nbr as wo_locat6_28_,
workorder0_.wo_note_nbr as wo_note_3_28_,
workorder0_.override as override4_28_,
workorder0_.wo_owner_nbr as wo_owner7_28_,
workorder0_.pool_nbr as pool_nbr8_28_,
workorder0_.sched_date as sched_da5_28_,
workorder0_.slot_nbr as slot_nbr9_28_,
workorder0_.wo_status_nbr as wo_stat10_28_,
workorder0_.subscriber_nbr as subscri11_28_,
workorder0_.wo_type as wo_type12_28_
from
workorders workorder0_
where
workorder0_.sched_date=?
I used the following query to verify the datatypes of the columns.
SELECT ST.tabname, SC.colname, SC.coltype
FROM systables ST
JOIN syscolumns SC ON SC.tabid = ST.tabid
JOIN sys
WHERE ST.tabname="wo_owners";
It returns a result of:
tabname wo_owner_nbr coltype
wo_owners wo_owner_nbr 262
wo_owners owner_id 271
Informix coltypes are listed here.
262 - 256 = 6 = SERIAL
271 - 256 = 15 = NCHAR
Note* In DB-Access, an offset value of 256 is always added to these coltype codes because DB-Access sets SERIAL, SERIAL8, and BIGSERIAL columns to NOT NULL. source
"Informix® supports the SERIAL, SERIAL8 and BIGSERIAL data types to produce automatic integer sequences. SERIAL is based on INTEGER (32 bit)" source
The exact problem has nothing to do with Hibernate or Spring Data, its a problem with the way I was casting ArrayLists derived from Select elements on the UI. In WorkOrderPredicates I did this:
List<String> inValues = (List<String>) criteria.getValue();
That makes the values a String and it quite rightly cannot be assigned to an integer.
To make matters worse I later changed it to be something like this:
if (criteria.getType().equals(SelectionDataType.INTEGER))
{
ArrayList<Integer> values = criteria.getValue();
predicates.add(entityPath.get(criteria.getField()).in((values)));
}
else if (criteria.getType().equals((SelectionDataType.STRING)))
{
ArrayList<String> values = criteria.getValue();
predicates.add(entityPath.get(criteria.getField()).in((evalues)));
}
Here, because the generics are erased at RunTime the ArrayList is still an ArrayList < String > even through it looks like I am casting it.
My solution was to build new arrays like this:
if (criteria.getType().equals(SelectionDataType.INTEGER))
{
predicates.add(entityPath.get(criteria.getField()).in((extractArrayListInteger(criteria))));
}
else if (criteria.getType().equals((SelectionDataType.STRING)))
{
predicates.add(entityPath.get(criteria.getField()).in((extractArrayListString(criteria))));
}
Methods to create new ArrayLists
@SuppressWarnings("unchecked")
private static ArrayList<Integer> extractArrayListInteger(SelectedCriteria criteria)
{
ArrayList<Integer> values = new ArrayList<>();
for (Object v : (ArrayList<Object>) criteria.getValue())
{
values.add(Integer.valueOf(v.toString()));
}
return values;
}
@SuppressWarnings("unchecked")
private static ArrayList<String> extractArrayListString(SelectedCriteria criteria)
{
ArrayList<String> values = new ArrayList<>();
for (String v : (ArrayList<String>) criteria.getValue())
{
values.add(v);
}
return values;
}