JPA, Postgresql and auto increment id attribute


I'm still writing this stupid frontend for database. Really it's confusing when have to write piece of software only for student project. Never mind. I've left idea with web app written in jsp/jsf (god's know what else) and 've started a new project. It's also would be tv schedule, but now it would be a desktop app written using netbeans, swing, postgresql and jpa.
So, todays problem. How to insert new object into database? I've written the front controller for a model, with method addStacja that get a Stacjtv object as an argument.



public boolean addStacjatv(Stacjatv st) {
em = getEntityManager();
try {
em.getTransaction().begin();
em = getEntityManager();
try {
em.getTransaction().begin();
em.persist(st);
em.getTransaction().commit();
return true;
} finally {
em.close();
} em.persist(st);
em.getTransaction().commit();
return true;
} finally {
em.close();
}
}


simple code, nothing special. But every time, when I invoke it, I get exception:


Internal Exception: org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
Error Code: 0
Call: INSERT INTO stacjatv (id, url, opis, nazwa) VALUES (?, ?, ?, ?)
bind => [null, fff, fff, ff]

well, Postgresql make auto increment using sequence. JPA doesn't know about it. I've googled and found solution in JPA Toplink Documentation. We have to tell JPA that we want to use sequence to generate next id value of primary key, so to solve the problem, we have to make some modification in model class.
I had to change Stacjatv.java which was generated by Netbeans creator. New version looks like it:

...
@Id
// start modyfication
@GeneratedValue(generator="stacjatv_id_seq",strategy=GenerationType.SEQUENCE)
// end
@Column(name = "id", nullable = false)
private Integer id;
...

5 comments:

Anonymous said...

I had a lot of difficulties with working with postgresql and JPA - especially with automatically generating primary keys.
Most important is the case senstiveness!! E.g.: If you sequence table in the database is named tabel_columnID_seq, you will get the error message

ERROR: relation "table_columnid_seq" does not exist
Error Code: 0
Call: select currval('TABLE_COLUMNID_seq')

As soon as you change the database sequence table to table_columnid_seq, it works fine.

When you refere to the name of a column by using @Column(name = "COLUMNID") you should always use upper case, although your database columns might be lower case.

Furthermore JDeveloper automatically generates Entites from the database refering the tables by @Table(name = "\"table\""). I had to change this to @Table(name = "table") to solve my primary key problems.

marcino_p said...

Hi
I agree with You. I'm working on JPA with toplink. I have encountered the same preblem relating to postgres. There is no way to solve it without overwritting base classes like "GenerateValue". For example, hibernate provide some interfaces but it is too complicated to implement them. Like was said, senstiveness is most important. Database names should be written in lower case.

marcino_p said...

Hi
I agree with You. I'm working on JPA with toplink. I have encountered the same preblem relating to postgres. There is no way to solve it without overwritting base classes like "GenerateValue". For example, hibernate provide some interfaces but it is too complicated to implement them. Like was said, senstiveness is most important. Database names should be written in lower case.

Anonymous said...

I'm also having problems with TopLink JPA Sequence and PostgreSQL and came across this thread which you guys might find useful:

http://forums.oracle.com/forums/thread.jspa?messageID=2325330&#2325330

I haven't verified if it works though.

Anonymous said...

thanks, solved my problem!