Invalid Identifier while joining two tables

Hibernate ORM
1

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 :

@OneToOne
@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 ");

@Entity
@Table(name="EMPLOYEEPROP") 
public class EmployeeProp 
{		
	
	@Id
    @Column(name="EMPLOYEEPROP_ID")
	@GeneratedValue(strategy=GenerationType.AUTO, generator="seqgen")
	@SequenceGenerator(name="seqgen", sequenceName="EMPLOYEEPROP_AUTOINC_SEQ")
	private int employeePropId;	
	
	@Column(name="RANK")
	private int rank;
	
	@Column(name="EMP_ID")
	private int empId;
	
	@Column(name="TYPE_ID",nullable=true)
	private Integer typeId;
	
	@Column(name="VALUE")
	private String value;
	
	// One to One joining table
	/*@OneToOne
	@JoinColumn(name = "DB_ID")
	private Db db;*/
	
		
	
}

@Entity
@Table(name="DB") 
public class Db 
{		
	

	@Id
    @Column(name="DB_ID")
	@GeneratedValue(strategy=GenerationType.AUTO, generator="seqgen")
	@SequenceGenerator(name="seqgen", sequenceName="DB_AUTOINC_SEQ")
	private int dbId;	
	
	@Column(name="NAME")
	private String name;
		
	@Column(name="DESCRIPTION")
	private String description;
	
	@Column(name="URLPREFIX")
	private String urlPrefix;
	
	@Column(name="URL")
	private String url;
	
	/*@OneToOne(mappedBy="db")
	private EmployeeProp dbOwner;*/
	
}
2

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

3

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 (https://en.wikipedia.org/wiki/Entity–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

4

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.

5

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?

Thanks

6

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.