geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jay D. McHugh" <...@jnwd.net>
Subject Re: Problem with JPA MySQL and Geronimo2.0.2
Date Tue, 06 Nov 2007 16:16:40 GMT
maho77 wrote:
> Hello,
> I have connected a MySQL Database to Geronimo via MySQL XA Pool. Further I
> have two entities:
> Country and Region. In the country Entity I mapped the Region as follows:
>
> 	@OneToMany(cascade = ALL, targetEntity = entities.orig.Region.class,
> mappedBy = "country") 
> 	@OrderBy("name ASC")
> 	public Collection<Region> getRegion() {
> 		return region;
> 	}
>
> In Region I mapped Country:
> 	@ManyToOne(targetEntity=entities.orig.Country.class, cascade = { MERGE,
> REFRESH })
> 	@JoinColumn(name="country_id", referencedColumnName = "id", table =
> "region")
> 	public Country getCountry() {
> 		return country;
> 	}
>
> I have the following schema for region:
>
> CREATE TABLE region (
> 	id INT NOT NULL AUTO_INCREMENT,
> 	country_id INT DEFAULT 0 NOT NULL,
> 	name VARCHAR(255) DEFAULT '' NOT NULL,
> 	info_id INT DEFAULT 0 NOT NULL,
> 	default_region BIT DEFAULT 0 NOT NULL,
> 	PRIMARY KEY (id)
> ) ENGINE=MyISAM; //I tried ENGINE=InnoDB too with the same results
> alter table region add foreign key(country_id) references country (id)
>
> CREATE TABLE country (
> 		id INT(4) NOT NULL AUTO_INCREMENT,
> 		countrycode CHAR(2) DEFAULT '' NOT NULL,
> 		name VARCHAR(255) DEFAULT '' NOT NULL,
> 		default_region_id INT(2) DEFAULT 0 NOT NULL,
> 		UNIQUE KEY(countrycode),
> 		PRIMARY KEY (id)
> 	) ENGINE=MyISAM // and also InnoDB;
>
> This is my persistence.xml:
> <persistence-unit name="JPATest">
> 		<jta-data-source>TEST_XA</jta-data-source>
> 		<class>entities.orig.Country</class>
>                 <class>entities.orig.Region</class>
> 		<properties>
> 			<property name="openjpa.jdbc.SynchronizeMappings" value="false" />
> 			<property name="openjpa.jdbc.DBDictionary"
> value="mysql(SupportsSubselect=true)" />
> 			<property name="openjpa.Log" value="DefaultLevel=TRACE,SQL=TRACE" />
> 		</properties>
> 	</persistence-unit>
>
> When I run this I get the following exception from OpenJPA:
> <openjpa-1.0.0-r420667:568756 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: Not unique table/alias:
> 't0' {prepstmnt 10955888 SELECT t0.id, t0.changed, t0.created,
> t0.default_region, t1.id, t1.changed, t1.created, t1.infotext,
> t1.user_changed, t1.user_created, t0.name, t0.user_changed, t0.user_created
> FROM bo_test.region t0 INNER JOIN bo_test.region t0 ON t0.id = t0.Region_id
> LEFT OUTER JOIN bo_test.info t1 ON t0.info_id = t1.id WHERE t0.country_id =
> ? [params=(int) 43]} [code=1066, state=42000]
>
> When I use a mapping table and change the mapping it works:
> Country:
> 	@OneToMany(cascade = REFRESH, targetEntity = entities.Region.class, fetch =
> EAGER) 
> 	@OrderBy("name ASC")
> 	@JoinTable(name="country_region", joinColumns =
> @JoinColumn(name="country_id", referencedColumnName = "id"),
> inverseJoinColumns = @JoinColumn(name="region_id", referencedColumnName =
> "id"))
> 	public Collection<Region> getRegion() {
> 		return region;
> 	}
>
> Region:
> 	@ManyToOne(targetEntity=entities.Country.class)
> 	@JoinColumn(name = "country_id", referencedColumnName = "id", table =
> "country_region")
> 	public Country getCountry() {
> 		return country;
> 	}
>
> What it going wrong here? Can anybody help me?
>
> Thanks,
> Mark
>
>
>   
Hey Mark,

That one caught me too.

The JPA spec says that you have to use a mapping table to do a 
one-to-many relationship.

But, OpenJPA has a proprietary annotation that you can use if you know 
that you will not be using any other persistence engines:

@ElementJoinColumn(name="<child table ref column>" 
referencedColumnName="<parent table ref column>")

That should allow you to bypass the 'standard' JPA relationship mapping 
and get your one-to-many working.

Jay




Mime
View raw message