Foreign Key Relationship Problem [Spring+Hibernate with annotation]

[leenabora]

Hi there,

I am quite new to Hibernate Annotations and I am facing a problem while using foreign key relationships using Hibernate Annotation. I request the group to please help me to solve the issue. I am providing all the possible information below.

Please find the attached file, "Foreign Key Relationship problem-Problem Description" which describes Table definition, their relationship and the exception.

Please find the other files for the definitions of the respective hibernate classes for the above tables.

Thanks
--

[ishaan]

hi leenabora,

if possible rather then attaching files please use code tag.

exception 
org.springframework.web.util.NestedServletException: Request processing failed;
 nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not insert: [com.xyz.abc..domain.Schedule];
 nested exception is org.hibernate.exception.SQLGrammarException: could not insert: [com.xyz.abc..domain.Schedule]

root cause 
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'counterParty' in 'field list'
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)

for the above excpetion
i think u need to make changes on the following entity.

@Entity
@Table(name="Schedules")
public class Schedule implements Serializable{

	private static final long serialVersionUID = 1L;
	private Long Id;
	private String description;

	private Long ownLegalEntityId;
	private Long counterPartyId;
	private Long marketId;

	@ManyToOne
	@JoinColumn(name="ID", table="OWN_LEGAL_ENTITIES", nullable = false, updatable = false, insertable = false)
	private OwnLegalEntity ownLegalEntity;


	@ManyToOne
	@JoinColumn(name="ID", table="COUNTERPARTIES", nullable = false, updatable = false, insertable = false)
	private CounterParty counterParty;


	@ManyToOne
	@JoinColumn(name="ID", table="MARKETS", nullable = false, updatable = false, insertable = false)
	private Market market;

	@Id @GeneratedValue(strategy=GenerationType.IDENTITY)
	public Long getId() {
		return Id;
	}
	public void setId(Long id) {
		Id = id;
	}

	@Column(name="DESCRIPTION")
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}


	public Long getOwnLegalEntityId() {
		return ownLegalEntityId;
	}

	public void setOwnLegalEntityId(Long ownLegalEntityId) {
		this.ownLegalEntityId = ownLegalEntityId;
	}

	public Long getCounterPartyId() {
		return counterPartyId;
	}

	public void setCounterPartyId(Long counterPartyId) {
		this.counterPartyId = counterPartyId;
	}

	public Long getMarketId() {
		return marketId;
	}

	public void setMarketId(Long marketId) {
		this.marketId = marketId;
	}


	public OwnLegalEntity getOwnLegalEntity() {
		return ownLegalEntity;
	}

	@Transient
	public void setOwnLegalEntity(OwnLegalEntity ownLegalEntity) {
		this.ownLegalEntity = ownLegalEntity;
	}

	public CounterParty getCounterParty() {
		return counterParty;
	}

	@Transient
	public void setCounterParty(CounterParty counterParty) {
		this.counterParty = counterParty;
	}

	public Market getMarket() {
		return market;
	}

	@Transient
	public void setMarket(Market market) {
		this.market = market;
	}
}

please make sure that do you really need the highlighted field type Long, As per my knowledge this field type should be objects fo respective entities.
for e.g. private CounterParty counterParty;
then mapped this one with respective foreign key.

with best regards,
ishaan

[psbedi]

Hi Ishaan,

I work with Leena.

The highlighted properties represents the fields defined in the Table "Schedules". If we remove that how we will be able to map the values in the class Schedule for those fields.

Are you suggesting something like this?

Remove
private Long ownLegalEntityId;
private Long counterPartyId;
private Long marketId;

Don't Remove
@ManyToOne
@JoinColumn(name="ID", table="OWN_LEGAL_ENTITIES", nullable = false, updatable = false, insertable = false)
private OwnLegalEntity ownLegalEntity;


@ManyToOne
@JoinColumn(name="ID", table="COUNTERPARTIES", nullable = false, updatable = false, insertable = false)
private CounterParty counterParty;


@ManyToOne
@JoinColumn(name="ID", table="MARKETS", nullable = false, updatable = false, insertable = false)
private Market market;

Modify the definitions of the following methods as follow:

public Long getOwnLegalEntityId() {
return ownLegalEntity.getId();
}

public Long getCounterPartyId() {
return counterParty.getId();
}

public Long getMarketId() {
return market.getId();
}


Looking forward for your quick reply.

Regards,
Prabhpreet

[Marten Deinum]

Forget the database and think in Objects NOT in database tables, rows and relationships. Think in objects and their respective relationsships.

If you have a relation between Foo and Bar make a getFoo on Bar and not a getFooId you want to Foo. Id is useless in this case. The ORM mapping tool will fill that for you and take care of the correct mapping.

[psbedi]

Martien,

I understand your point. But I am facing problem with annotations. I have to ultimately fill in the value in the database column through an object.

Can you review my annotation declaration also?

Regards,
Prabhpreet

[Marten Deinum]

As I stated think in objects NOT in databases. Your mapping can express your database. Your mapping however is of.

You are using the @JoinColumn with the wrong purpose. This would mean that the ID column in the COUNTERPARTIES table is the ID of the Schedule (it is the foreign key).

So start by removing the table property and point to the correct column (i.e. OWN_LEGAL_ENTITY_ID for the ownLegalEntity relationship).

[psbedi]

Thanks Martien,

Do I need to have the following attributes defined for the @JoinColumn annotation?

nullable = false, updatable = false, insertable = false

Can you tell me the significance of the above attributes?

Regards,
Prabhpreet

[psbedi]

Martien,

I did the following changes:

@JoinColumn(name="OWN_LEGAL_ENTITY_ID")
@ManyToOne
private OwnLegalEntity ownLegalEntity;


@JoinColumn(name="COUNTERPARTY_ID")
@ManyToOne
private CounterParty counterParty;


@JoinColumn(name="MARKET_ID")
@ManyToOne
private Market market;

But when I try to save te 'Schedule' object I get the following error:

<Code>exception
org.springframework.web.util.NestedServletExceptio n: Request processing failed;
nested exception is org.springframework.dao.InvalidDataAccessResourceU sageException: could not insert: [com.xyz.abc..domain.Schedule];
nested exception is org.hibernate.exception.SQLGrammarException: could not insert: [com.xyz.abc..domain.Schedule]

root cause
com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: Unknown column 'counterParty' in 'field list'
com.mysql.jdbc.SQLError.createSQLException(SQLErro r.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.ja va:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:16 31)
</Code>

Regards,
Prabhpreet