java.sql.SQLException: Column 'JOB_ID' not found

Question

I am using Java8 with JPA2/Hibernate5, Spring4 and mySQL.

If anyone can help, I would appreciate it.

I have the following tables.

+--------+          +------------+        +---------+
| job    |          | person_job |        | person  |
+--------+          +------------+        +---------+
|  ID    |          |  JOB_ID    |        |   ID    |
|        |          |  PER_ID    |        |         |
+--------+          +------------+        +---------+

A person can have many jobs.

Job.java

@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "person_job", joinColumns = {
        @JoinColumn(name = "PER_ID", referencedColumnName = "ID") }, inverseJoinColumns = {
                @JoinColumn(name = "JOB_ID", referencedColumnName = "ID", unique = true) })
private Person person;

The following SQL works fine when I run it directly against the database:

select
         e.*, p.*
     from
         www.job as e  
     inner join
         www.person_job as pj 
             on e.id=pj.JOB_ID  
     inner join
         www.person as p 
             on pj.PER_ID=p.ID  
...

When I run a native query (with the above sql), I get the following error:

java.sql.SQLException: Column 'JOB_ID' not found.

In Job.java, if I swap the JOB_ID and PER_ID around in the @JoinTable, then I get:

java.sql.SQLException: Column 'PER_ID' not found.

Because the above SQL runs fine against the database, I think the problem is with join configuration in Job.java.

UPDATE

I also have the following that works correctly:

+--------+          +--------------+        +----------+
| job    |          | job_location |        | location |
+--------+          +--------------+        +----------+
|  ID    |          |  JOB_ID      |        |   ID     |
|        |          |  LOC_ID      |        |          |
+--------+          +--------------+        +----------+

A job can have many locations.

jobs.java

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "job_location", joinColumns = {
        @JoinColumn(name = "JOB_ID", referencedColumnName = "ID") }, inverseJoinColumns = {
                @JoinColumn(name = "LOC_ID", referencedColumnName = "ID", unique = true) })
private Set<Location> locations;

UPDATE

I only get this error when I execute a native query. When I do the following fr example, it works perfectly.

    return (T) entityManager.find(entityClass, id);

Is there something wrong with my native query? (This query works if I don't have the person column on Jobs.java).

    StringBuilder sb = new StringBuilder();
    sb.append(" select e.* ");
    sb.append(" from ");
    sb.append("    www.job as e ");
    //sb.append(" inner join www.person_job as pj on e.id = pj.JOB_ID ");
    //sb.append(" inner join www.person as p on pj.PER_ID = p.id ");
    sb.append("  where e.id = :id ");
    Query q = entityManager.createNativeQuery(sb.toString(), JobWithDistance.class);
    q.setParameter("id", id);
    List<Job> jobs = (List<Job>) q.getResultList();

Adding the 2 commented out lines makes no difference.

score 2 · Answer 1 · answered Aug 31 '16 at 17:50

2

SOLUTION:

I needed to change:

sb.append(" select e.* ");

to

sb.append(" select * ");

answered Aug 31 '16 at 17:50

Richard

8,193
28
107
228

Predrag Maric · Answer 2 · 2016-08-31T14:20:30.110

0

Try with this

// in Job entity
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(name = "person_job", joinColumns = { @JoinColumn(name = "JOB_ID") }, 
                                inverseJoinColumns = { @JoinColumn(name = "PER_ID") }
)
private Person person;

...

// in Person entity
@OneToMany(mappedBy = "person")
private List<Job> jobs;

edited Aug 31 '16 at 14:20

answered Aug 31 '16 at 14:02

Predrag Maric

23,938
5
52
68

OK, I've edited my answer. Can you try with these changes? – Predrag Maric Aug 31 '16 at 14:20
I get: `SQLException: Column 'PER_ID' not found.` I think this is doing the same as my original set up. – Richard Aug 31 '16 at 14:26
Anything else in the stacktrace? Anything that would indicate where is Hibernate looking for `PER_ID` column? – Predrag Maric Aug 31 '16 at 14:34
`(default task-2) Column 'PER_ID' not found. SQLGrammarException: could not execute query java.sql.SQLException: Column 'PER_ID' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)` – Richard Aug 31 '16 at 14:39
I would think it is coming from: `@JoinColumn(name = "PER_ID")` because if I remove the `person` column from the `Jobs.java` I get no errors. – Richard Aug 31 '16 at 14:40
The example with `locations` which works for you, is it a unidirectional or bidirectional relationship? (regarding `PER_ID`) Yes, but it is not clear in which table Hibernate looks for that column, because if it was looking for it in `person_job` it would have found it. Maybe that's not even that important but it would indicate a bug IMHO. – Predrag Maric Aug 31 '16 at 14:43
I just tested it with only having the `job` column on the `person.java` and I works with no errors. My problem is I need the `person` in the `jobs` object. `@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER) @JoinTable(name = "person_job", joinColumns = { @JoinColumn(name = "PER_ID", referencedColumnName = "ID") }, inverseJoinColumns = { @JoinColumn(name = "JOB_ID", referencedColumnName = "ID", unique = true) }) private Set jobs;` seems like the `@OneToMany` is working, but `@ManyToOne` is not. – Richard Aug 31 '16 at 14:49
`locations` is unidirectional and `@OneToMany`. I will investigate and then try answer your question about the `PER_ID`. In my case I only need a unidirectional `@ManyToOne` join, but this is what is not working. – Richard Aug 31 '16 at 14:51
I cannot see where Hibernate is looking for the `PER_ID`. There is no indication apart from the error above. – Richard Aug 31 '16 at 14:57
Check out [this](http://stackoverflow.com/questions/9360626/bidirectional-one-to-many-with-jointable) and [this](http://stackoverflow.com/questions/27671776/how-to-make-a-one-to-many-bidirectional-relation-with-jpa-annotations-where-the). It is possible that this type of bidirectional relation is not supported, but in these threads there are answers where `@JoinTable` is put on both sides of the relation. To me, this is strange, because I think Hibernate will treat this as two separate mappings. – Predrag Maric Aug 31 '16 at 15:07
Thanks, will have a look. – Richard Aug 31 '16 at 15:17
I try the same as them but still get the error. I also tested this on a mere, where it doesn't use a native query, and it inserts to the database correctly. Maybe it means there is a problem with my native query sql. But this runs fine directly against the database, and also when I remove the column from the Jobs object. - very confused. – Richard Aug 31 '16 at 15:37

java.sql.SQLException: Column 'JOB_ID' not found

2 Answers2

Linked