Integrating JPA and PostgreSQL Null Column

Question

I typed out the JPA code from Java Persistence with Hibernate.

Below follows the Main method, Message.java object and PostgreSQL table description.

App.java (Main method)

  // First unit of work
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction tx = session.beginTransaction();
    Message message = new Message("Hello World");
    Long msgId = (Long) session.save(message);

    tx.commit();
    session.close();

    // Second unit of work
    Session newSession = HibernateUtil.getSessionFactory().openSession();
    Transaction newTransaction = newSession.beginTransaction();

    List<Message> messages = newSession.createQuery("from Message m order
 by m.text asc").list();

    System.out.println(messages.size() + " message(s) found:" );

    for(Message m : messages) {
        System.out.println(m.getText());
    }

    newTransaction.commit();
    newSession.close();

    // Third unit of work
    Session thirdSession = HibernateUtil.getSessionFactory().openSession();
    Transaction thirdTransaction = thirdSession.beginTransaction();

    //msgId holds the identifier value of the first message
    message = (Message) thirdSession.get(Message.class, msgId);

    message.setText("greetings Earthling");
    message.setNextMessage(
            new Message("take me to your leader(please)")
    );

    //Shutting down the application
    HibernateUtil.shutdown();

Message.java

@Entity
@Table(name = "MESSAGES")
public class Message {
    @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE, 
generator = "messages_message_id_seq")
        @SequenceGenerator(name = "messages_message_id_seq", 
sequenceName = "messages_message_id_seq", allocationSize = 1)
    @Column(name = "MESSAGE_ID")
    private Long id;

    @Column(name = "MESSAGE_TEXT")
    private String text;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name="NEXT_MESSAGE_ID")
    private Message nextMessage;

    // getters and setters

PostgreSQL Table

messages=# \d messages
                                         Table "public.messages"
     Column      |         Type          |                           Modifiers
-----------------+-----------------------+---------------------------------------------
 message_id      | integer               | not null default                            
                                           nextval('messages_message_id_seq'::regclass)
 message_text    | character varying(25) | not null
 next_message_id | integer               |

When I run App.main, only the first "unit of work" data gets inserted into the database. Here's the messages table after running the main method:

Why isn't the next_message_id column getting filled out? And why is the second record added to the database?

messages=# select * from messages;
 message_id | message_text | next_message_id
------------+--------------+-----------------
          1 | Hello World  |

*Why isn't the next_message_id column getting filled out?* Where in your code do you set that value? I can't see it. *And why is the second record added to the database?* What second record? There's only one record in your post. — dic19, Jan 28 '14 at 03:14
When I had used the Hibernate config XML file (`Message.hbm.xml`), I did not have to set the `next_message_id`, but rather it was set when I added another Message in the "third unit of work" in my Main method code. — Kevin Meredith, Jan 28 '14 at 04:11
Third section of work is never inserted as it doesnt have a commit of the transaction. Try thirdTransaction.commit() before shutdown hibernate — Koitoer, Jan 28 '14 at 07:40
To fill out the third column you should add a valid Message to the nextMessage field in your code, as you did in the third section, but I think commit is missing. — Koitoer, Jan 28 '14 at 07:44
Yes, @Koitoer, thank you for pointing out the missing transaction's commit and session's close. Additionally, I posted an answer that included a second fix - updating the `@SequenceGenerator` annotation. — Kevin Meredith, Jan 28 '14 at 18:18
Also, thanks @DataNucleus, for pointing out that this `JPA` API used here is actually Hibernate's, not the actual `JPA` API. — Kevin Meredith, Jan 28 '14 at 18:26

score 1 · Answer 1 · answered Jan 28 '14 at 10:19

1

It really seems that the missing commit is the cause of your problem in your third unit of work. Because your application opens the transaction, it is its duty to commit or roll-back it. The another scenario would be a container managed transaction (CMT) where the Java EE container (or Spring) deals with all transactions.

answered Jan 28 '14 at 10:19

V G

18,822
6
51
89

I added the `thirdTransaction.commit()` and `thirdSession.close()`, but then saw these exceptions: `Caused by: org.hibernate.exception.DataException: Could not execute JDBC batch update Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into MESSAGES (NEXT_MESSAGE_ID, MESSAGE_TEXT, MESSAGE_ID) values (NULL, 'take me to you r leader(please)', '2') was aborted.` – Kevin Meredith Jan 28 '14 at 15:26
I think there is a problem in your sequencegenerator: was ID=2 already assigned in Messages? – V G Jan 28 '14 at 15:51
Not sure, but I just re-ran `CreateTable.sql` that drops & adds the table. Then I re-ran my Main method and got: `Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into MESSAGES (NEXT_MESSAGE_ID, MESSAGE_TEXT, MESSAGE_ID) values (NULL, 'take me to you r leader(please)', '2') was aborted.` – Kevin Meredith Jan 28 '14 at 17:45

score 0 · Accepted Answer · edited May 23 '17 at 10:31

I updated my App#Main's third unit of work to call transaction.commit() and session.close() per koitoer's and Andrei I's suggestions. Additionally, I modified my @GeneratedValue annotation (shown below).

    // Third unit of work
    Session thirdSession = HibernateUtil.getSessionFactory().openSession();
    Transaction thirdTransaction = thirdSession.beginTransaction();

    ...

    thirdTransaction.commit();   // <---- added
    thirdSession.close();        // <---- added

    //Shutting down the application
    HibernateUtil.shutdown();

Then, I changed my Message.java's's @GeneratedValue annotation to use Identity per this answer: https://stackoverflow.com/a/10628341/409976.

public class Message {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "MESSAGE_ID")
    private Long id;

When I tested out this change, I got an error:

rg.postgresql.util.PSQLException: ERROR: value too long for type 
character varying(25)

As a result, I updated my CreateTable.sql script:

DROP TABLE IF EXISTS messages;
create table messages(
    message_id serial,
    message_text varchar(50) NOT NULL,
    next_message_id int
);

Finally, I re-ran the Main method of App.java successfully:

messages=# select * from messages;
 message_id |          message_text          | next_message_id
------------+--------------------------------+-----------------
          2 | take me to your leader(please) |
          1 | greetings Earthling            |               2
(2 rows)

Integrating JPA and PostgreSQL Null Column

2 Answers2