MySQLSyntaxErrorException: Unknown column xxx in 'field list'

Question

I am using Java 8 with JPA/Hibernate 5.2.1 and a MySQL database.

Tables:

+--------+    +----------------+    +----------+ 
| Rating |    | Rating_Employee|    | Employee |
+--------+    +----------------+    +----------+ 
| ID     |    | RAT_ID         |    | ID       |
| REVIEW |    | EMP_ID         |    +----------+ 
+--------+    +----------------+

I am trying to save a row in a Rating table, but get the following:

MySQLSyntaxErrorException: Unknown column 'employee0_.distance' in 'field list'

The reason is because the Employee model object has a DISTANCE field, but no matching column on the database. I need the DISTANCE field because when I do a SELECT on the Employee table, I calculate the DISTANCE based on other columns with a nativeQuery. However, when Hibernate does the select itself when updating the joined table RATING, it wants to match DISTANCE, which does not exist (I get the above error).

When I persist or merge the Employee object, I use the following successfully:

@Column(insertable=false, updatable=false)
private BigDecimal distance;

Question:

How do I get Hibernate to ignore the DISTANCE when doing a non native query select? (When it updates RATING table)

UPDATE

I was advised to possibly use @Formula("..."), but my method has a pretty complex nativeQuery, which also order by distance. If I put the formula into @Formula("..."), it needs to also receive parameters currently passed to the method.

Any ideas?

You can use a Hibernate @Formula column to retrieve the distance via a native SQL query: http://stackoverflow.com/questions/2986318/calculated-property-with-jpa-hibernate — Alan Hay, Aug 22 '16 at 16:49
Hi Alan, my problem with using `@Formula`, is that I need to order the result set by `distance`. I cannot retrieve it, and sort it in java because I am only getting a limited number of rows at a time. Do you have any ideas? Is it possible to `order by` the `@Formula` column? — Richard, Aug 22 '16 at 17:07
Is it possible to toggle a field `@Transient` dynamically via reflection at runtime? His anyone had experience with [Javassist](http://ayoubelabbassi.blogspot.co.za/2011/01/how-to-add-annotations-at-runtime-to.html)? — Richard, Aug 22 '16 at 17:30
Javassist won't work for me because at runtime, I would have to create a new class loader to get its effects. — Richard, Aug 22 '16 at 18:14

score 1 · Answer 1 · answered Aug 22 '16 at 13:59

1

Hibernate only ignore fields annotated with @Transient. Thus, you can use this option to make the field "distance" not managed by hibernate

@Transient
private BigDecimal distance;

from java doc: http://docs.oracle.com/javaee/5/api/javax/persistence/Transient.html

answered Aug 22 '16 at 13:59

SEY_91

1,615
15
26

Hi SEY_91, thanks but, `@Transient` would normally work, but I need to do this dynamically. When I do a `select` (`nativeQuery`) on the Emplyee table, I need the `distance` field. So I can't just make it `@Transient`. – Richard Aug 22 '16 at 14:23
If you mean by "the distance field for only native query" that you want to get "distance column" created when hibernate create the schema and after that it will not be mapped by hibernate query. then you can create manually the column "distance" because doing that dynamically(at runtime ignoring specific fields) is not possible. – SEY_91 Aug 22 '16 at 15:22
Yes, I think I am going to write a native query to do the INSERT. – Richard Aug 22 '16 at 15:27
I am having problems with a native query, because the save is done in a transaction. I cannot retrieve the rating `id` because it has not been committed yet by the the time I need to know what the `id` is. (I need the `id` to `insert` into the join tables) – Richard Aug 22 '16 at 16:07

score 0 · Answer 2 · answered Aug 22 '16 at 18:31

My solution:

I make a second Employee object that does not have @Transient on distance. I then use that and copy it to the Employee object.

List<Employee> employees = new <Employee>ArrayList();
List<EmployeeWithDistance> employeesWD = (List<EmployeeWithDistance>) q.getResultList();
for (EmployeeWithDistance employeeWD : employeesWD) {
    Employee employee = new Employee();
    BeanUtils.copyProperties(employeeWD, employee);
    employees.add(employee);
}
return employees;

MySQLSyntaxErrorException: Unknown column xxx in 'field list'

2 Answers2