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 >
