Issue
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();
}
}
Solution
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
Answered By - Blue
Answer Checked By - Marilyn (JavaFixing Volunteer)
