>-----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?
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).
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.
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).
>>
>> 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
