Invalid Identifier while joining two tables

I have the following query which is yet to be modified for joining two tables. Whatever is there in the below query right now, works fine. However, as soon as I tried to join the VALUE column
of EMPLOYEEPROP table with the DB_ID column of the DB table just for testing purpose, I started getting following error :

Version of Hibernate that I am using : 4.3.5

org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "EMPLOYEEPROP0_"."DB_ID": invalid identifier

The error makes sense because there are only 4 columns in the EMPLOYEEPROP table which are EMPLOYEEPROP_ID, RANK,EMP_ID and DB_ID. And it’s trying to find 5th column with name
DB_ID in the following code :

@JoinColumn(name = "DB_ID")
private Db db;

But, in order to join the two tables on VALUE and DB_ID, I would have to define the code for @OneToOne annotation just like I have done below, right? Please let me know what I am doing wrong?

Query query = session.createQuery("FROM "
					+ " EmployeeProp cp "
					+ " WHERE cp.empId=1111 ");
public class EmployeeProp 
	@GeneratedValue(strategy=GenerationType.AUTO, generator="seqgen")
	@SequenceGenerator(name="seqgen", sequenceName="EMPLOYEEPROP_AUTOINC_SEQ")
	private int employeePropId;	
	private int rank;
	private int empId;
	private Integer typeId;
	private String value;
	// One to One joining table
	@JoinColumn(name = "DB_ID")
	private Db db;*/
public class Db 

	@GeneratedValue(strategy=GenerationType.AUTO, generator="seqgen")
	@SequenceGenerator(name="seqgen", sequenceName="DB_AUTOINC_SEQ")
	private int dbId;	
	private String name;
	private String description;
	private String urlPrefix;
	private String url;
	private EmployeeProp dbOwner;*/

The VALUE column is a String and DB_ID is an integer. How do you plan on joining that?

Short Answer: I don’t know how do I plan on joining it. Could you tell
me if it’s possible based n the following explanation?

I currently have this thing working through JDBC in my code and the SQL for the same is as follows :

SELECT * FROM employeeprop
JOIN db ON (db.db_id = employeeprop.value)
WHERE employeeprop.emp_id = 1111

There are few more clarification I would like to provide regarding the mapping thing. I asked the person who created the tables that what’s the relationship between the VALUE field in EMPLOYEEPROP table and db_id field in DB table and I got the following response.

There is no formal relationship between the VALUE field in the EMPLOYEEPROP table and anything else. That includes the DB table. The VALUE field is a generic column EAV style (–attribute–value_model).

After reading this response, it looks like @joinColumn(name = "DB_ID") approach I was following in my code is wrong because this annotation states that there is a column named DB_ID in the EMPLOYEEPROP table but
there is no such column. According to the mapping this column will hold the foreign keys to the DB entity.

I am little bit confused how to handle all this? Please let me know if there’s more information I can provide. Thanks

This SQL query is wrong as well:

SELECT * FROM employeeprop
JOIN db ON (db.db_id = employeeprop.value)
WHERE employeeprop.emp_id = 1111

So, before trying to figure out the mapping, you are better off clearing the database design. If you’re using EAV, there’s should be a key involved as well, not just the value itself.

Therefore, once the DB design is clear of doubts and you have a proper SQL query that defines the join, you can use either a non-PK @JoinColumn ora @JoinFormula if the join criteria is more exotic.

When you say this, you meant it’s wrong because the data type of db_id is NUMBER and value field is VARCHAR ?

If that’s the reason, then I asked the person who did this and I got the following answer:

The value field is a catchcall category that can mean anything, so it’s up to the users to figure out what the value field is being used for depending upon its context (emp_id and type_id). The reason value field needs to be a VARCHAR because we have no idea what could be in that value field.A varchar can support not just strings, but numbers (with a cast) or even images (with Base64 encoding).If we made it a number, we could not easily support having strings .

Would you still recommend making changes?


My previous answer already provides a solution to your problem. Once you figure out the join criteria based on the EAV context info, you can use the provided annotations mentioned in the linked resources.