Confusion over @Id property - error with postgresql database

1,341 views
Skip to first unread message

metafedora

unread,
Nov 29, 2010, 2:54:14 AM11/29/10
to play-framework
I am somewhat new to play, having already created an entire
application using it, just by reading the website documentation, which
is very good.
But I had been manually creating my queries using things like
DB.getConnection().createStatement().executeQuery(query);
Now I am trying to convert my models to JPA @Entity's, and I am
running into a lot of problems with persistance. I am using
Postgresql 8.4.

Here's a simplified version of one of my tables:

CREATE TABLE webuser
(
id bigserial NOT NULL,
some_data character varying(255),
CONSTRAINT pk_webuser PRIMARY KEY (id)
)

Here's the corresponding model:

package models;

import javax.persistence.*;
import play.db.jpa.Model;

@Entity
public class WebUser extends Model {
public String some_data;
}

And here's a unit test:

public class BasicTest extends UnitTest {

@Test
public void CreateFacebookUser() {

WebUser user = new WebUser();
user.some_data = "123456";
user.save(); //<-- Fails here
}
}

In addition, I have the following .conf settings:

db.url=jdbc:postgresql://localhost/my_app
db.driver=org.postgresql.Driver
jpa.dialect=org.hibernate.dialect.PostgreSQLDialect

But I keep getting an error like this:

A javax.persistence.PersistenceException has been caught,
org.hibernate.exception.SQLGrammarException: could not get next
sequence value

I have tried changing the id field to be bigint.
I'm not sure what else to try. I'm not a Java programmer, I'm coming
from the .NET worked. I have no experience with JPA. Are you not
supposed to use an id field on your tables? Does anyone have an
example schema for postgressql?

Any help would be greatly appreciated!

metafedora

unread,
Nov 29, 2010, 2:16:01 PM11/29/10
to play-framework
I noticed that in the definition for play.db.jpa.Model, the id field
is decorated with attributes @javax.persistence.Id and
@javax.persistence.GeneratedValue
The default strategy for GeneratedValue is
javax.persistence.GenerationType.AUTO
In my other non-Model inherited @Entity classes that were working fine
with JPA and Postgresql, I was using
@GeneratedValue(strategy=GenerationType.IDENTITY)
That worked fine with bigserial data type.

I am going to try to make my WebUser table have separate id field -
maybe the one I have is conflicting with play?

Guillaume Bort

unread,
Nov 29, 2010, 4:00:05 PM11/29/10
to play-fr...@googlegroups.com
If the default @Id field provided by Model doesn't fit your needs, you
can extends GenericModel.

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

--
Guillaume Bort, http://guillaume.bort.fr

For anything work-related, use g...@zenexity.fr; for everything else,
write guillau...@gmail.com

TonyC

unread,
Nov 29, 2010, 4:28:25 PM11/29/10
to play-framework
Hi,

The Model class has it's own id.

If you want to define your own Id then you need to use the
GenericModel class ie

@Entity
public class WebUser extends GenericModel {

@Id
public [ a type ] myid;

public String some_data;

}

You might find the following useful

http://en.wikibooks.org/wiki/Java_Persistence

Regards, TonyC

Alexander Strebkov

unread,
Nov 30, 2010, 12:59:38 AM11/30/10
to play-framework
I'm also using postgresql with play framework and I also had to solve
some problems with sequences. Will share my experience - maybe it will
help you.

I think your problem is you do not have a sequence defined in postgres
which Hibernate expects you to have. If you enable jpa.ddl=update in
application.conf - Hibernate will create/update database schema for
you and it will create one postgres sequence to use to get a "next
value" for id fields of ALL tables. So by default you will never get
records with the same id in any table in your database. You can try to
enable jpa.ddl=update with empty database and see a schema Hibernate
will create for you (and Hibernate will expect your scheme to be
compatible with it, including sequence). You also do not need to use
bigserial - just bigint, because Hibernate will use sequences
"manually" anyway.

I didn't enjoyed this "one sequence per database" Hibernate's default
so I found custom dialect which make it "one sequence per table":
http://grails.1312388.n4.nabble.com/One-hibernate-sequence-is-used-for-all-Postgres-tables-td1351722.html
(second message)

So your problem is mostly Hibernate and PostgreSQL related, not Play
problem.

Best regards,
Alexander

metafedora

unread,
Dec 5, 2010, 11:09:41 PM12/5/10
to play-framework
Thanks everyone for your comments.

As it turns out, the main issue for me was that my database
configurations for running play in "test" mode (i.e. "sudo play test")
were all set to the defaults (db=mem etc.)
Once I created these configurations...

%test.application.mode=dev
%test.db.url=jdbc:postgresql://localhost/octopus
%test.db.driver=org.postgresql.Driver
%test.db.user=andrew
%test.db.pass=**********
%test.db.default.schema=public
%test.jpa.ddl=create-drop
%test.jpa.dialect=org.hibernate.dialect.PostgreSQLDialect

Then JPA actually drops/creates the tables and I can extend my model
classes from play.db.jpa.Model and save() now works.
The tables which Hibernate generates are fine, but as Alexander
mentioned, a single sequence called hibernate_sequence is created and
used as the source for *all* id's - not really ideal since each table
could have its own id space. Neither are the default settings for
@SequenceGenerator which use allocationSize=50, so your id goes from
1 .. 51 .. 101 .. clearly this is not meant for identity-like
autogenerated primary keys.

I am still not out of the woods. I'm plagued with bizarre errors,
such as these:

Query query = JPA.em().createQuery("select * from WebUser"); /* <--
Fails with: "org.hibernate.hql.ast.QuerySyntaxException: unexpected
token: * near line 1, column 8 [select * from public.WebUser]" */

List<WebUser> founds = WebUser.findAll(); /* <-- Fails with: "A
java.lang.NoSuchFieldError has been caught, NONE" */

WebUser found = WebUser.findById(1L); /* Works for some reason... :/
*/



On Nov 29, 11:59 pm, Alexander Strebkov <alexanderstreb...@gmail.com>
wrote:
> I'm also using postgresql with play framework and I also had to solve
> some problems with sequences. Will share my experience - maybe it will
> help you.
>
> I think your problem is you do not have a sequence defined in postgres
> which Hibernate expects you to have. If you enable jpa.ddl=update in
> application.conf - Hibernate will create/update database schema for
> you and it will create one postgres sequence to use to get a "next
> value" for id fields of ALL tables. So by default you will never get
> records with the same id in any table in your database. You can try to
> enable jpa.ddl=update with empty database and see a schema Hibernate
> will create for you (and Hibernate will expect your scheme to be
> compatible with it, including sequence). You also do not need to use
> bigserial - just bigint, because Hibernate will use sequences
> "manually" anyway.
>
> I didn't enjoyed this "one sequence per database" Hibernate's default
> so I found custom dialect which make it "one sequence per table":http://grails.1312388.n4.nabble.com/One-hibernate-sequence-is-used-fo...
Message has been deleted

metafedora

unread,
Dec 12, 2010, 3:05:31 AM12/12/10
to play-framework
Just in closing, I took a step back and rebuilt my entire solution
from scratch, and all the mysterious errors went away.
> Query query = JPA.em().createQuery("select * fromWebUser"); /* <--
> Fails with: "org.hibernate.hql.ast.QuerySyntaxException: unexpected
> token: * near line 1, column 8 [select * from public.WebUser]" */
>
> List<WebUser> founds =WebUser.findAll(); /* <-- Fails with: "A
> java.lang.NoSuchFieldError has been caught, NONE" */
>
> WebUserfound =WebUser.findById(1L); /* Works for some reason... :/
> > > public classWebUserextends Model {
Reply all
Reply to author
Forward
0 new messages