From user-return-8214-apmail-geronimo-user-archive=geronimo.apache.org@geronimo.apache.org Tue Nov 06 16:16:57 2007 Return-Path: Delivered-To: apmail-geronimo-user-archive@www.apache.org Received: (qmail 98401 invoked from network); 6 Nov 2007 16:16:56 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 6 Nov 2007 16:16:56 -0000 Received: (qmail 65843 invoked by uid 500); 6 Nov 2007 16:16:42 -0000 Delivered-To: apmail-geronimo-user-archive@geronimo.apache.org Received: (qmail 65820 invoked by uid 500); 6 Nov 2007 16:16:42 -0000 Mailing-List: contact user-help@geronimo.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: user@geronimo.apache.org List-Id: Delivered-To: mailing list user@geronimo.apache.org Received: (qmail 65809 invoked by uid 99); 6 Nov 2007 16:16:42 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Nov 2007 08:16:42 -0800 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests= X-Spam-Check-By: apache.org Received-SPF: error (athena.apache.org: encountered temporary error during SPF processing of domain of jay@jnwd.net) Received: from [63.170.55.63] (HELO jdm) (63.170.55.63) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Nov 2007 16:16:46 +0000 Received: from localhost ([127.0.0.1]) by jdm (JAMES SMTP Server 2.3.1) with SMTP ID 175 for ; Tue, 6 Nov 2007 10:16:40 -0600 (CST) Message-ID: <47309368.7080405@jnwd.net> Date: Tue, 06 Nov 2007 10:16:40 -0600 From: "Jay D. McHugh" User-Agent: Thunderbird 2.0.0.6 (X11/20070728) MIME-Version: 1.0 To: user@geronimo.apache.org Subject: Re: Problem with JPA MySQL and Geronimo2.0.2 References: <13609313.post@talk.nabble.com> In-Reply-To: <13609313.post@talk.nabble.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org 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 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: > > TEST_XA > entities.orig.Country > entities.orig.Region > > > value="mysql(SupportsSubselect=true)" /> > > > > > When I run this I get the following exception from OpenJPA: > > 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 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="" referencedColumnName="") That should allow you to bypass the 'standard' JPA relationship mapping and get your one-to-many working. Jay