Primary key not updating when inserting data to database using hibernate

Question

I'm new to hibernate and postgres. I need help regarding primary key in hibernate. This is the problem: When I inserted data using persist() for the first time, it was a success. Now, when I inserted the next data, my IDE gave me this error:

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique
constraint "test1_tbl2_pkey"
Detail: Key (id)=(0) already exists.

It seems like the value in the sequence is not used. Because the initial value of a sequence in postgres is 1. Also, when I looked at the tables, their primary key values are 0. How to tell hibernate to use the sequence in my postgres database? I'm new to both technologies thus, I'm having a hard time solving this problem.

tbl1_mappings.hbm.xml

<?xml version = "1.0" encoding = "utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC 
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 
<hibernate-mapping>
<class name="pojo.Test1Tbl1" table="test1_tbl1" schema="public">
  
  <id name="id" type="integer" column="id">
     <generator class="increment"/>
  </id>
  
  <property name = "name" column = "tbl1_name" type = "string"/>
  <one-to-one name="tbl2"></one-to-one>
  
</class>
</hibernate-mapping>

tbl2_mappings.hbm.xml

<?xml version = "1.0" encoding = "utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC 
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 
<hibernate-mapping>
<class name="pojo.Test1Tbl2" table="test1_tbl2" schema="public">
  
  <id name = "id" type = "integer" column = "id">
     <generator class="foreign"/>
  </id>
  
  <property name = "name" column = "tbl2_name" type = "string"/>
  
</class>
</hibernate-mapping>

Event code of my button

button.addActionListener(new ActionListener() {

        @Override
        public void actionPerformed(ActionEvent e) {
            try {
                Test1Tbl2 tbl2 = new Test1Tbl2(field2.getText());
                Test1Tbl1 tbl1 = new Test1Tbl1(field1.getText(), tbl2);
                
                Session session = factory.openSession();
                Transaction tx = session.beginTransaction();
                session.persist(tbl2);
                session.persist(tbl1);
                tx.commit();
            }
            catch(HibernateException ex) {
                ex.printStackTrace();
            }
            catch(Exception ex) {
                ex.printStackTrace();
            }
        }

Blue · Accepted Answer · 2022-08-27T03:00:29.150

After I've done my intensive research, I found a solution. Although, this solution doesn't use the persist() method. However, this solution does update the primary key via postgres sequence. Btw, I'm using Hibernate 6.1.2.Final version.

This is the code that I came up with:

Transaction tx = null;
            try (Session session = factory.openSession()){
                tx = session.beginTransaction();
                String query = "INSERT INTO test1_tbl1(tbl1_name)"  + 
                         "VALUES ('"+field1.getText()+"');";
                MutationQuery mq = session.createNativeMutationQuery(query);
                int result = mq.executeUpdate();
                System.out.println("Rows affected: " + result);
                tx.commit();
            }
            catch(Exception ex) {
                if (tx!=null) tx.rollback();
                ex.printStackTrace();
            }

Take note that I severed the relationship between test1_tbl1 and test1_tbl2 tables and I only used test1_tbl1 to make my answer more clear. This article helps me to create the solution above: MutationQuery and SelectionQuery

Other articles propose to use the 'sequence' generator class in the mapping file to fix the problem. Ex:

<id name="id" type="integer" column="id">
     <generator class="sequence">
       <param name="sequence_name">my_sequence</param>
     </generator>
  </id>

Although, this solution doesn't work for me. I also tried using different types of generator classes like native, identity and auto; still they didn't work.

These are some articles that I've stumbled upon that are related to the problem mentioned in this thread:

Hibernate doesn't use PostgreSQL sequence to generate primary key

JPA and PostgreSQL with GenerationType.IDENTITY

How To Use Sequence In Hibernate As A Property In XML Mapping

HIbernate 5: generator class="sequence" not working

Migrating Hibernate 3 to 5: relation hibernate_sequence does not exist

JPA GenerationType.AUTO not considering column with auto increment

Edit:

I decided to play around and I found the solution that I'm looking for. In your cfg.xml, remove hibernate.transform_hbm_xml.enabled property. This property is messing up the generator classes. However, removing the property will make this warning appear in your logs:

WARN: HHH90000028: Support for `<hibernate-mappings/>` is deprecated       [RESOURCE : resources/mappings/tbl1_mappings.hbm.xml]; migrate to orm.xml or mapping.xml, or enable `hibernate.transform_hbm_xml.enabled` for on the fly transformation

Switching to JPA annotation style with persistence.xml fixes this warning for me.

Btw, this is my cfg.xml:

<?xml version = "1.0" encoding = "utf-8"?>

<!DOCTYPE hibernate-configuration SYSTEM 
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
    <property name = "hibernate.dialect">
    org.hibernate.dialect.PostgreSQLDialect
    </property>
    
    <property name = "hibernate.connection.driver_class">
     org.postgresql.Driver
    </property>
    
    <property name = "hibernate.connection.url">
     jdbc:postgresql://localhost:3308/hibernate_test
    </property>
    
    <property name="hibernate.default_schema">
    public
    </property>
    
    <property name = "hibernate.connection.username">
     postgres
    </property>
  
    <property name = "hibernate.connection.password">
     password
    </property>
    
    <property name = "hibernate.id.new_generator_mappings">
     true
    </property>
    
    <!-- List of XML mapping files -->
    <mapping resource="resources/mappings/tbl1_mappings.hbm.xml"/>
    
</session-factory>
</hibernate-configuration>

The hibernate.id.new_generator_mappings property is a flag that sets the generator mapping style to old style if it's set to false. Otherwise, sets the mapping style to new style.

I think this article has simple explanation about the property: HIbernate 5: generator class="sequence" not working

Primary key not updating when inserting data to database using hibernate

1 Answers1