On 7 September 2011 15:10, Franklin, Matthew B. <[email protected]> wrote:
> I don't think that strategy works with oracle (at least not with > eclipselink. Maybe OpenJPA implemented it differently. > It *should* be possible to configure that in the orm.xml but I haven't found out why this configuration is ignored when you create a new entity (at least for MySQL, the other Oracle database). I don't have an Oracle DB to test here, do you? > > > > Sent via a mobile device. Please excuse typos or brevity. > > -----Original Message----- > From: Jasha Joachimsthal [mailto:[email protected]] > Sent: Wednesday, September 07, 2011 08:23 AM Eastern Standard Time > To: [email protected] > Subject: Re: JPA sequence generation & MySQL > > Update: > The sequence table doesn't seem to be necessary at all. If you set the > generationtype to IDENTITY you get an autoincrement in MySQL and H2 will > create an internal sequence table for this. Also PostgreSQL seems to accept > it :) I had to rewrite the initial_data.sql. > Got almost everything working except the population of the DB in MySQL. > When I'm done I'll create a patch so others can review before I might break > things in DB systems I don't have on my machine. > > On 6 September 2011 19:58, Jasha Joachimsthal > <[email protected]>wrote: > > > > > On 6 September 2011 17:10, Ciancetta, Jesse E. <[email protected]> wrote: > > > >> > >> > >> >-----Original Message----- > >> >From: Jasha Joachimsthal [mailto:[email protected]] > >> >Sent: Tuesday, September 06, 2011 8:21 AM > >> >To: [email protected] > >> >Subject: Re: JPA sequence generation & MySQL > >> > > >> >Bit weird but to reply to myself: > >> > > >> >On 5 September 2011 11:32, Jasha Joachimsthal > >> ><[email protected]>wrote: > >> > > >> >> Hi, > >> >> > >> >> So far Rave has only used the in memory H2 database. I want to > >> configure > >> >> Rave to use a MySQL database. The sequence generation annotation we > >> >use is > >> >> not compatible with MySQL. > >> >> > >> >> As an example the o.a.r.portal.model.Page: > >> >> @SequenceGenerator(name="pageIdSeq", sequenceName = > >> >"page_id_seq") > >> >> /* ... */ > >> >> public class Page implements BasicEntity, Serializable { > >> >> /* ... */ > >> >> @Id @Column(name="id") > >> >> @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = > >> >"pageIdSeq" > >> >> private Long id; > >> >> > >> >> This is the kind of error you get: > >> >> > >> >> Error instantiating named sequence "page_id_seq": Your database > >> >dictionary does not support native sequences. To tell the dictionary > how > >> to > >> >select sequence values, use: > >> >> openjpa.jdbc.DBDictionary: NextSequenceQuery="SELECT NEXT VALUE FOR > >> >"page_id_seq" > >> >> > >> >> One way to solve this is to create a orm.xml file that overrides the > >> >> GeneratedValue strategy: > >> >> <entity class="org.apache.rave.portal.model.Page"> > >> >> <attributes> > >> >> <id name="id"> > >> >> <generated-value strategy="SEQUENCE" generator=""/> > >> >> > >> > > >> >This should be strategy="IDENTITY" to use the autoincrement in MySQL > >> > > >> > > >> >> </id> > >> >> </attributes> > >> >> </entity> > >> >> > >> >> But this means that if you want to deploy Rave against a MySQL > database > >> >you > >> >> have to check all the beans if they use the @SequenceGenerator and > >> >update > >> >> your orm.xml. > >> >> > >> > > >> >Plus that for some unknown reason it doesn't work as expected. The > >> >database > >> >schema is created, all tables have an autoincrement id field, which > means > >> >the generated-value from the orm.xml has been read and used. However > >> >when I > >> >want to add a new user, I get the following error: > >> >"<openjpa-2.1.1-r422266:1148538 fatal user error> > >> >org.apache.openjpa.persistence.ArgumentException: Error instantiating > >> >named > >> >sequence "user_id_seq": Your database dictionary does not support > native > >> >sequences. To tell the dictionary how to select sequence values, use: > >> >openjpa.jdbc.DBDictionary: NextSequenceQuery="SELECT NEXT VALUE FOR > >> >"user_id_seq" > >> >Where the above string is replaced with the proper SQL for your > >> database." > >> > > >> >This is strange because during creation of the schema it used the > orm.xml > >> >but when the entitymanager persists, it suddenly falls back to the > >> >annotation. I created a sample app but haven't found this behaviour. > I'm > >> a > >> >bit clueless now. > >> > > >> > > >> > > >> >> > >> >> Another way is to remove the @SequenceGenerator and the reference in > >> >> @GeneratedValue (generator="...") > >> > >> So are you saying that you'd end up with just the bare @GeneratedValue > >> annotation and JPA would use a default strategy of GenerationType.AUTO? > >> > > > > I'm still puzzled why overriding the annotation value in the orm.xml > > doesn't always work in Rave, but I'm not an expert on JPA so maybe I've > > missed some tiny detail. > > > > > >> > >> If that works it seems like it would be ok to me (although I was just > >> talking with Matt and he tells me AUTO doesn't work well with Oracle). > >> > > > > Every database comes with its own limitations :( I don't have an Oracle > > database to test, do you? > > > > > >> If that doesn't work then I think the only other alternative which is > >> guaranteed to be database agnostic would be to use GenerationType.TABLE > like > >> this: > >> > >> public class Foo { > >> @Id > >> @GeneratedValue(strategy=GenerationType.TABLE, > >> generator="FooIdGenerator") > >> @TableGenerator(name="FooIdGenerator", table="SEQUENCE", > >> pkColumnName="SEQ_NAME", > >> valueColumnName="SEQ_COUNT", pkColumnValue="Foo", > >> allocationSize = 1, initialValue = 1) > >> @Column(name = "FOO_ID", nullable = false) > >> private Long fooId; > >> > >> ... > >> } > >> > >> public class Bar { > >> @Id > >> @GeneratedValue(strategy=GenerationType.TABLE, > >> generator="BarIdGenerator") > >> @TableGenerator(name="BarIdGenerator", table="SEQUENCE", > >> pkColumnName="SEQ_NAME", > >> valueColumnName="SEQ_COUNT", pkColumnValue="Bar", > >> allocationSize = 1, initialValue = 1) > >> @Column(name = "BAR_ID", nullable = false) > >> private Long barId; > >> > >> ... > >> } > >> > >> With that configuration you end up with a SEQUENCE table with a SEQ_NAME > >> and SEQ_COUNT column and a row in that table for each entity we're > >> generating id's for. > >> > > > > I'll give that a try tomorrow, thanks. It shouldn't be necessary for > MySQL > > using autoincrement, but I'm not sure about other databases. So far the > > attempt to add an abstraction layer in JPA... > > > > > >> > >> Actually -- I just went to look something up in the Javadoc for > >> TableGenerator and noticed they have a complete example there too: > >> > >> > >> > http://java.sun.com/javaee/5/docs/api/javax/persistence/TableGenerator.html > >> > >> I was going to take another look at the allocationSize attribute -- we > >> might need to think about tuning it if we were to go with the table > based > >> strategy. With an allocation size of 1 the entity manager ends up > having to > >> query/increment the count on every new insert which could be a > performance > >> issue for high volume inserts (which I don't think we actually have > though), > >> but with a larger allocation size you end up potentially using up id > numbers > >> quicker (I assume the entity manager does something like reads the value > at > >> initialization, increments it by the allocation size and then writes it > back > >> thereby reserving a block of id's -- so if that's the default of 50 then > >> after the first insert the counter is at 50 -- then if you shut down the > >> application before you use up the other 49 id's they end up wasted). > >> > > > > Good point. The default OPENJPA_SEQUENCE_TABLE uses a TINYINT in MySQL to > > store the sequence value which has a maximum value of 127 > > (onehundredtwentyseven). So it took me 1 restart and 2 tables to crash my > > application. I'll continue tomorrow, but thanks for the help! > > > > > >> > >> >> > >> >> Is there a reason why we explicitly specify the @SequenceGenerator? > If > >> >not, > >> >> can we remove this from all the beans that use it? > >> >> > >> > > >> >The H2 demo content doesn't load (and all tests fail) if the strategy > is > >> set > >> >to INCREMENT. > >> > > >> >Has someone else successfully run Rave against a different DB than H2 > >> >(preferably MySQL)? > >> > > >> >Jasha > >> > > > > >
