Ebean.save() get insert id
Daryl Stultz
I hate to ask such a basic question. Feel free to point me to some
docs that explain it:
I have the following unit test failing:
UserType ut = new UserType();
ut.setName("staff");
Ebean.save(ut);
assertNotNull(ut.getId()); // fails
I've searched for "insert id" but not found anything recent or
helpful. I'm using PostgreSQL 8.4 and Ebean 2.3.0. I expected the id
of the freshly inserted object to be loaded. Do I need to configure
Ebean to do this? My annotation for UserType.id is:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "usertypeid", unique = true, nullable = false)
private Integer id;
which is what I use for JPA/OpenJPA.
Thanks.
Rob Bygrave
You could try removing the @GeneratedValue(strategy = GenerationType.IDENTITY) .... Ebean will automatically choose the identity strategy based on the DB platform ... and this only really makes sense for DB2 and H2 which can support both Identity and Sequences.
If that works... then there is a bug with this @GeneratedValue setting and Postgres.
Cheers, Rob.
Rob Bygrave
H2 Identity (with getGeneratedKeys) doesn't work with JDBC batched preparedStatements ... so Ebean will use Sequences with H2 by default because batched statements can be a nice performance boost.
Similarly Oracle JDBC driver supports getGeneratedKeys ... but not when used with JDBC batched statements.
... so with Postgres, Oracle and H2 Sequence based id generation is used (and we don't use getGeneratedKeys).
Cheers, Rob.
Daryl Stultz
On Feb 8, 6:07 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> Hmmm. No, that looks all fine so I'm not sure why the ID isn't being
> returned.
>
> You could try removing the @GeneratedValue(strategy =
> GenerationType.IDENTITY) ....
I tried removing it, as well as AUTO and SEQUENCE. The first two get
me:
javax.persistence.PersistenceException: Error getting sequence nextval
at
com.avaje.ebean.config.dbplatform.SequenceIdGenerator.getMoreIds(SequenceIdGenerator.java:
204)
at
com.avaje.ebean.config.dbplatform.SequenceIdGenerator.loadMoreIds(SequenceIdGenerator.java:
157)
at
com.avaje.ebean.config.dbplatform.SequenceIdGenerator.nextId(SequenceIdGenerator.java:
114)
at
com.avaje.ebean.server.deploy.BeanDescriptor.nextId(BeanDescriptor.java:
838)
...
Caused by: org.postgresql.util.PSQLException: ERROR: relation
"public.usertypes_seq" does not exist
With SEQUENCE set up like this:
@Entity
@SequenceGenerator(name="Seq",
sequenceName="usertypes_usertypeid_seq")
@Table(name = "usertypes", schema = "public")
public class UserType {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator =
"Seq")
@Column(name = "usertypeid", unique = true, nullable = false)
private Integer id;
it works. So you see the problem is with the naming convention for the
sequence. I generated the table like so:
create table usertypes (
usertypeid serial not null,
name varchar(50) not null,
constraint usertypes_pk primary key (usertypeid)
);
which uses the default naming of "usertypes_usertypeid_seq" for the
sequence. Maybe if I had generated the DDL from Ebean it would have
named it "public.usertypes_seq"?
Thanks for the help.
-Daryl
Rob Bygrave
Interesting.
With GenerationType.IDENTITY ... sounds like a bug there so I'll have a look into that. In theory Ebean should be ignoring the IDENTITY in this case as we really want to use Sequences with Postgres. More investigation required here (almost certainly a bug here).
The 'default' com.avaje.ebean.config.NamingConvention ... just appends "_seq" to the table name. So yes that ends up as "public.usertypes_seq" ... as the table name is "usertypes" in the "public" schema.
So, yes without the explicit sequence name... we get "public.usertypes_seq" ... which is not found in your case.
http://www.avaje.org/static/javadoc/pub/com/avaje/ebean/config/NamingConvention.html#getSequenceName%28java.lang.String%29
So, if you had a big schema the suggestion would be to configure the NamingConvention and change the getSequenceName(...) method so that it matched your tablename_primarykeycolumn_seq ... naming convention. I'd suggest this would be less work than having all the @SequenceGenerator and @GeneratedValue annotations (which imo is pretty convoluted).
The bad news is that currently NamingConvention.getSequenceName(...) only takes the table name (and not the primary key column name) ... so right now that is not really an option for you. So, right now you end up having to define the sequence name as you have done using the annotations.
I'll log a bug ... to get change NamingConvention.getSequenceName() so that it additionally takes the DB column.
Cheers, Rob.
Daryl Stultz
On Feb 8, 9:08 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> Interesting.
>
> With GenerationType.IDENTITY ... sounds like a bug there
Bummer, my first hour into Ebean. :-)
> The bad news is that currently NamingConvention.getSequenceName(...) only
> takes the table name (and not the primary key column name) ... so right now
> that is not really an option for you.
Well, I could generate a map of table name to sequence name easy
enough.
/Daryl
Rob Bygrave
Logged as http://www.avaje.org/bugdetail-215.html
@GeneratedValue(strategy = GenerationType.IDENTITY) ... needs to be ignored by Database Platforms that do not support Identity / Autoincrement such as Postgres and Oracle.
Fixed in HEAD.
Daryl Stultz
On Feb 11, 5:02 am, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> FYI:
>
> Logged ashttp://www.avaje.org/bugdetail-215.html
>
> @GeneratedValue(strategy = GenerationType.IDENTITY) ... needs to be ignored
> by Database Platforms that do not support Identity / Autoincrement such as
> Postgres and Oracle.
I finally got back to this. I made a table/sequence map and plugged it
into a NamingConvention. With @GeneratedValue not specified it works,
with it specified it errors on null id.
I'm working on getting things running from the HEAD/trunk so I can
test the fix.
/Daryl
Rob Bygrave
Q: Are you are using Postgres for your test (ie. DB Platform with no Identity support)?
Q: Can you post you test code here. (or file upload to the google group) ?
Daryl Stultz
On Feb 21, 8:25 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> Hmmm, interesting. I did add a test for this... and reproduced and fixed
> against Postgres.
I thought I was just confirming the bug, but maybe there is still
something wrong. I am still running against 2.3.
> Q: Are you are using Postgres for your test (ie. DB Platform with no
> Identity support)?
Yes, PostgreSQL 8.4.
> Q: Can you post you test code here. (or file upload to the google group) ?
Sure, I'm just going to slap my whole project up. It's just a little
unit testing.
/Daryl
Rob Bygrave
If you run against HEAD ... it should be fixed.
That is, I have already reproduced and confirmed the behavior.. and have already fixed it in HEAD.
Daryl Stultz
On Feb 21, 9:03 pm, Rob Bygrave <robin.bygr...@gmail.com> wrote:
> If you run against v2.3 ... you will reproduce the bug.
> If you run against HEAD ... it should be fixed.
Fix confirmed.
/Daryl