Hibernate Annotation Non Primary Key Foreign Key Mapping with VARCHAR data type

[Marc Heruela]

Forgive my ignorance, but i've been trying to google/yahoo this thing for 2 days in a row already.
Hope you can shed me some light.

I have these database objects/tables below:

  
CREATE TABLE `issuingbank` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `banknamecn` varchar(50) DEFAULT NULL,
  `banknameen` varchar(50) DEFAULT NULL,
  `bankcode` varchar(10) DEFAULT NULL,
  `banknametw` varchar(50) DEFAULT NULL,
  `flag` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `bankcode` (`bankcode`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8

CREATE TABLE `bankaccount` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accountname` varchar(50) DEFAULT NULL,
  `bankcode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bankcode` (`bankcode`),
  CONSTRAINT `bankaccount_ibfk_1` FOREIGN KEY (`bankcode`) REFERENCES `issuingbank` (`bankcode`)
) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8

As you can see i have issuingbank table with a child table bankaccount with a onetomany relationship (wrt to issuingbank).
bankaccount.bankcode has a foreign key defined that references to issuingbank.bankcode.

I did my Hibernate Annotation mapping below:

  
@Table(name = "issuingbank")
@Entity
@NamedQueries({
	@NamedQuery(
			name = "activeBanks", 
			query = "from IssuingBank where flag = :flag"
	)
})
public class IssuingBank extends BaseObject {

	private Integer id;
	private String bankNameCn;
	private String bankNameEn;
	private String bankNameTw;
	private Integer flag;
	private Collection<BankAccount> bankAccounts;
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id")
	public Integer getId() {
		return id;
	}
	
	@Column(name = "banknamecn", length = 50)
	public String getBankNameCn() {
		return bankNameCn;
	}
	
	@Column(name = "banknameen", length = 50)
	public String getBankNameEn() {
		return bankNameEn;
	}
	
	@Column(name = "banknametw", length = 50)
	public String getBankNameTw() {
		return bankNameTw;
	}
	
	//Added column flag in the database issuingbank
	@Column(name = "flag")
	public Integer getFlag() {
		return flag;
	}
	
	@OneToMany(fetch = FetchType.EAGER)
	@JoinColumn(name = "bankcode")
	public Collection<BankAccount> getBankAccounts() {
		return bankAccounts;
	}
	
	public void setId(Integer id) {
		this.id = id;
	}
	
	public void setBankNameCn(String bankNameCn) {
		this.bankNameCn = bankNameCn;
	}
	
	public void setBankNameEn(String bankNameEn) {
		this.bankNameEn = bankNameEn;
	}
	
	public void setBankNameTw(String bankNameTw) {
		this.bankNameTw = bankNameTw;
	}
	
	public void setFlag(Integer flag) {
		this.flag = flag;
	}
	
	public void setBankAccounts(Collection<BankAccount> bankAccounts) {
		this.bankAccounts = bankAccounts;
	}
}

//------- bankaccount
@Table(name = "bankaccount")
@Entity
public class BankAccount extends BaseObject {

	private Integer id;
	private IssuingBank issuingBank;
	private String accountName;
	
	@Id
	@Column(name = "id")
	@GeneratedValue(strategy = GenerationType.AUTO)
	public Integer getId() {
		return id;
	}
	
	@ManyToOne
	@JoinColumn(name = "bankcode")
	public IssuingBank getIssuingBank() {
		return issuingBank;
	}
	
	@Column(name = "accountname", length = 50)
	public String getAccountName() {
		return accountName;
	}
	
	public void setId(Integer id) {
		this.id = id;
	}
	
	public void setIssuingBank(IssuingBank issuingBank) {
		this.issuingBank = issuingBank;
	}
	
	public void setAccountName(String accountName) {
		this.accountName = accountName;
	}
}

When I try to load all issuingbank data EAGERLY using getHibernateTemplate.loadAll(IssuingBank.class),
I was able to load all IssuingBank data, and only some BankAccount data, even though the FETCHTYPE is EAGER. Further investigation (or maybe I am wrong),
is that I noticed that all data with issuingbank.bankcode value parsable to Integer
are loaded, while all data with issuingbank.bankcode value not parsable to integer were not loaded.
ANd when I try to load all BankAccount using getHibernateTemplate(BankAccount.class), I get and exception:

Caused by: java.sql.SQLException: Invalid value for getInt() -

It tried accessing all bankcode value as an int, but since it cant be cast it's an error.
Why did it tried to access the value via getInt() when in fact in the database it is varchar? Have I done something wrong in my map?

Also, how come the generated query is like this:

  
select this_.id as id20_1_, this_.accountname as accountn2_20_1_, this_.bankcode as bankcode20_1_, issuingban2_.id as id19_0_, 
issuingban2_.banknamecn as banknamecn19_0_, issuingban2_.banknameen as banknameen19_0_, 
issuingban2_.banknametw as banknametw19_0_, issuingban2_.flag as flag19_0_ 
from bankaccount this_ left outer join issuingbank issuingban2_ on this_.bankcode=issuingban2_.id

it tried to join via bankaccount.bankcode and issuingbank.id; i have explicitly define that the mapping should be issuingbank.bankcode to bankaccount.bankcode.

I would be very glad if you tell me what I've don wrong.

Thanks
-marckun

[Marc Heruela]

Additional Info:
Im using Hibernate, Spring Struts (Using Hibernate Annotation), on MySQL 5.1 RDBMS.

Furthermore:

my foreign key bankaccount.bankcode references to issuingbank.bankcode which is not a primary key, but a unique key.

(i think the above is the caused of the problem since the primary key of issuingbank is an integer; explains the getInt() part,
but i dont know why)

[Marc Heruela]

i think i found the answer. i dont know why but adding referenceColumn name inside @JoinColumn annotation solves the problem.

  

/* in issuingbank */
	@OneToMany(fetch = FetchType.EAGER)
	@JoinColumn(name = "bankcode", referencedColumnName = "bankcode")
	public Collection<BankAccount> getBankAccounts() {
		return bankAccounts;
	}

/* in bankaccount */

	@ManyToOne
	@JoinColumn(name = "bankcode", referencedColumnName = "bankcode")
	public IssuingBank getIssuingBank() {
		return issuingBank;
	}

please advise if my solution is correct.

Thanks
-marckun

[Charan kumar sarvepalli]

/* in issuingbank */
@OneToMany(mappedBy = "issuingBank", cascade = CascadeType.ALL,fetch = FetchType.EAGER)
@org.hibernate.annotations.Cascade(value = org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
public Collection<BankAccount> getBankAccounts() {
return bankAccounts;
}

/* in bankaccount */

@ManyToOne
@JoinColumn(name = "bankcode", referencedColumnName = "bankcode")
public IssuingBank getIssuingBank() {
return issuingBank;
}

try the above code.
@joincolumn is not needed both sides
"mappedby" should be used at owner side of both entities (nothing but who is responsible for querying).