Error Is Reported When Verification Is Enabled for Data Insertion in the Hibernate Framework

Symptom

The customer migrates data from the ORA-compatible database to GaussDB. The table structure is migrated using the DRS tool. After the migration, the original service code of the customer is unavailable, and an error is reported when the Hibernate framework verifies the table structure.

Schema-validation: wrong column type encountered in column [execute_time] in table [abnormal_event]; found [int8 (Types#BIGINT)], but expecting [int4 (Types#INTEGER)]

Example:

• The structure of the Student table in the ORA-compatible database is as follows:

  id number(15,0)
  sid number(15,0)
  age number(10,0)

• After GaussDB is migrated using DRS, the Student table structure is as follows:

  id bigint
  sid bigint
  age Integer

• When the Student entity class information in the actual database is as follows:

  Long id
  Long sid
  Long age -- An error is reported when the Hibernate framework verifies the table structure.

<hibernate-configuration>
    <session-factory>
        <!-- GaussDB connection information -->
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://x.x.x.x:xx/postgres?currentSchema=public</property>
        <property name="connection.username">xxxxxx</property>
        <property name="connection.password">xxxxxx</property>

        <!-- The following configurations are optional: -->

        <!-- Specify whether to support dialects. -->
        <!-- In pgSQL-compatible mode, the following configuration must exist: -->
         <property name="dialect">org.hibernate.dialect.PostgreSQL92Dialect</property>
        <!-- In ORA-compatible mode, you are advised to change the configuration as follows: -->
<!--        <property name="dialect">org.hibernate.dialect.OracleDialect</property>-->

        <property name="met"></property>

        <!-- Specify whether to print SQL statements when CURD is executed. -->
        <property name="show_sql">true</property>
        <!-- The table is created automatically. -->
<!--         <property name="hbm2ddl.auto">create</property>-->
        <!-- Enable the verification when inserting data. -->
        <property name="hbm2ddl.auto">validate</property>
        <!-- Disable the verification. -->
<!--         <property name="hbm2ddl.auto">none</property>-->

        <!-- Resource registration (entity class mapping file) -->
        <mapping resource="student.xml"/>
    </session-factory>
</hibernate-configuration>

Main function for inserting data:

// Create the object to be tested.
Student student = new Student();
student.setId(20L);
student.setAge(222L);
student.setSid(222L);

// Start a transaction and obtain data based on the session.
Configuration conf = new Configuration().configure();
SessionFactory sessionFactory = conf.buildSessionFactory();
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();

// Save data using sessions.
session.save(student);

// Commit a transaction.
transaction.commit();

// After the operation is complete, close the session connection object.
session.close();

Cause Analysis

1. If the data can be successfully inserted into the ORA-compatible database and passes the verification: When verifying data, the Hibernate framework compares TypeCode of the inserted data type java.lang.long with that of the number type in the table structure. If they are inconsistent, the Hibernate framework compares sqlType of the inserted data of the long type with Type of the table structure and the long type is converted to number (19,0) (the mapping is maintained by the org.hibernate.dialect.OracleDialect). The type of the table structure is number, and number(19,0) starts with number. Therefore, the verification is successful. When the ORA-compatible database is used, the service code involves data of the integer type. You can use the long type in Java to insert the data.
2. If the GaussDB verification is different and the data is inserted successfully after the verification is disabled: When the Hibernate framework verifies the inserted data, the service code is of the java.lang.long type, which corresponds to TypeCode of the table data type bigint. Therefore, no error is reported. If the table data type is integer, the system checks TypeCode of the java.lang.long type in the service code and compares it with that of the integer type in the table data. If they are inconsistent, the system converts the long type to int8 based on the org.hibernate.dialect.PostgreSQLDialect mapping, the table data type integer is converted to int4, which cannot be matched. As a result, the verification fails. When GaussDB is used, the service code involves data of the integer type. Therefore, the Java integer corresponding to the table type must be used.

Solution

You can use the following methods to rectify the fault:

• Disable the verification function.
• Modify the service code at the customer side and use the corresponding Java integer for different table data types.