How to join two tables on columns that have different names?

[Steve Vittoria]

Hi everyone,

I'm trying to join two tables that have different column names.

CREATE TABLE OWORD_CODE
(
  CODE			VARCHAR2(5)		NOT NULL,
  CODE_TYPE		VARCHAR2(255)	        NOT NULL
)

CREATE TABLE OWORD_LANGUAGE
(
  LANGUAGE_ID		INTEGER			NOT NULL,
  RESOURCE_KEY	VARCHAR2(255)	        NOT NULL,
  LANGUAGE		VARCHAR(10)		NOT NULL,
  VALUE			VARCHAR2(255) 	NOT NULL
)

These two tables need to be joined on OWORD_CODE.CODE = OWORD_LANGUAGE = RESOURCE_KEY, but I'm have difficulties doing that. If the columns were named the same, it works fine.

Here is my class for the OWORD_CODE table...

package ca.mcgill.muhc.oword.entities;

// Generated 24-May-2011 10:41:20 AM by Hibernate Tools 3.3.0.GA

import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

/**
 * OwordCode generated by hbm2java
 */
@Entity
@Table(name = "OWORD_CODE")
public class OwordCode implements java.io.Serializable {

	private static final long serialVersionUID = 651934879857311182L;

	private String code;
	private String codeType;
	private List<OwordLanguage> languages;

	public OwordCode() {
	}

	public OwordCode(String code, String codeType) {
		this.code = code;
		this.codeType = codeType;
	}

	@Id
	@Column(name = "CODE", unique = true, nullable = false, length = 5)
	public String getCode() {
		return this.code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	@Column(name = "CODE_TYPE", nullable = false)
	public String getCodeType() {
		return this.codeType;
	}

	public void setCodeType(String codeType) {
		this.codeType = codeType;
	}

	@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "code")
	public List<OwordLanguage> getLanguages() {
		return this.languages;
	}

	public void setLanguages(List<OwordLanguage> languages) {
		this.languages = languages;
	}

}

Here is my class for the OWORD_LANGUAGE table...

package ca.mcgill.muhc.oword.entities;

// Generated 25-May-2011 1:33:10 PM by Hibernate Tools 3.3.0.GA

import java.math.BigDecimal;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

/**
 * OwordLanguage generated by hbm2java
 */
@Entity
@Table(name = "OWORD_LANGUAGE")
public class OwordLanguage implements java.io.Serializable {

	private static final long serialVersionUID = 8433713813882442824L;

	private BigDecimal languageId;
	private OwordResources resource;
	private OwordCode code;
	private String language;
	private String value;

	public OwordLanguage() {
	}

	public OwordLanguage(BigDecimal languageId, OwordResources resource, OwordCode code,
			String language, String value) {
		this.languageId = languageId;
		this.resource = resource;
		this.code = code;
		this.language = language;
		this.value = value;
	}

	@Id
	@Column(name = "LANGUAGE_ID", unique = true, nullable = false, precision = 38, scale = 0)
	public BigDecimal getLanguageId() {
		return this.languageId;
	}

	public void setLanguageId(BigDecimal languageId) {
		this.languageId = languageId;
	}

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "RESOURCE_KEY", nullable = false)
	public OwordResources getResource() {
		return this.resource;
	}

	public void setResource(OwordResources resource) {
		this.resource = resource;
	}

	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "CODE", nullable = false)
	public OwordCode getCode() {
		return this.code;
	}

	public void setCode(OwordCode code) {
		this.code = code;
	}

	@Column(name = "LANGUAGE", nullable = false, length = 10)
	public String getLanguage() {
		return this.language;
	}

	public void setLanguage(String language) {
		this.language = language;
	}

	@Column(name = "VALUE", nullable = false)
	public String getValue() {
		return this.value;
	}

	public void setValue(String value) {
		this.value = value;
	}

}

Here is the code I'm using to retrieve the data...

package ca.mcgill.muhc.oword.entities;

import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.log4j.Logger;

@Stateless
public class OwordLanguageQueryBean implements OwordLanguageQuery {

	@PersistenceContext(unitName = "oword")
	EntityManager oword;

	private static Logger log = Logger.getLogger(OwordLanguageQueryBean.class);

	private static final String GET_ALL_UI_LANGUAGE = "from OwordLanguage where language = :userLanguage and code.codeType = :test";

	@SuppressWarnings("unchecked")
	public List<OwordLanguage> getUserInterfaceLanguage(String userLanguage) {
		log.debug("Start of OwordLanguageQueryBean.getUserInterfaceLanguage()");

		Query qry = oword.createQuery(GET_ALL_UI_LANGUAGE);
		qry.setParameter("userLanguage", userLanguage);
		qry.setParameter("test", "Status");

		return qry.getResultList();
	}

}

I get the following error...

ERROR [JDBCExceptionReporter] ORA-00904: "OWORDLANGU0_"."CODE": invalid identifier

and when I look at the logs, I see the select statement that was generated has the column code in the OWORD_LANGUAGE table, but that table does not have a column named code.

select
	owordlangu0_.LANGUAGE_ID as LANGUAGE1_1_,
	owordlangu0_.CODE as CODE1_,
	owordlangu0_.LANGUAGE as LANGUAGE1_,
	owordlangu0_.RESOURCE_KEY as RESOURCE5_1_,
	owordlangu0_.VALUE as VALUE1_
from
	OWORD_LANGUAGE owordlangu0_,
	OWORD_CODE owordcode1_
where
	owordlangu0_.CODE=owordcode1_.CODE and
	owordlangu0_.LANGUAGE=? and
	owordcode1_.CODE_TYPE=?

What am I doing wrong here?

[Waswani Naresh]

Hi Steve,

@ManyToOne(fetch = FetchType.LAZY)  
@JoinColumn(name = "CODE", nullable = false)  
public OwordCode getCode() {  
return this.code;  
}  

The above mapping conveys to the Hibernate framework that in the OWORD_LANGUAGE table, there is a column with the name "CODE" that will be used for joining with OWORD_CODE table.

But you do not have "CODE" column in the OWORD_LANGUAGE.

So the solution is to specify that column of the OWORD_LANGUAGE that will contain the OWORD_CODE.CODE value.


Regards,
Naresh Waswani