A major difficulty in migrating Oracle database to MySQL database is the replacement of the primary key generation strategy. If Oracle's Sequence mechanism is used in previous programs to achieve the primary key self-increment, it is necessary to use TableGenerator for equivalent replacement in MySQL.

When replacing, there are three main areas that need to be revised:

• Entities that complete hibernate mapping by annotations;
• The entity that completes hibernate mapping in xml mode;
• Database stored procedure;

Annotation completes the entity modification of hibernate mapping

Using Oracle Sequence

If your previous program used Sequence, for example, a Sequence named SEQ, then the code to manipulate the id field should be as long as the following:

// Note: If sequence Name is not specified, the default is HIBERNATE_SEQUENCE, which also needs to be manually established in Oracle.
@SequenceGenerator(name = "generator", sequenceName = "SEQ")
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "generator")
public Long getId() {
    return id;
}

Using MySQL table to simulate Oracle Sequence

First, you need to create a table sys_sequence in MySQL. There are two fields in the table. One is seq_name, which represents the name of the Oracle sequence, and the other is current_value, which represents the current value of the sequence (note: you need to set this initial value to the current value of the corresponding sequence in the Oracle database). The table looks as follows:

> select * from sys_sequence;
+--------------+------------+
| seq_name     | curr_value |
+--------------+------------+
| SEQ          |      2809  |
+--------------+------------+

Then the program is written as follows:

/**
* allocationSize It is the difference between the first insert of each program and the previous maximum.
*/
@Id
@TableGenerator(name = "sequence", table = "sys_sequence", pkColumnName = "seq_name", valueColumnName = "curr_value", pkColumnValue = "SEQ", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "sequence")
private Long id;

Entity modification of hibernate mapping in xml mode

Using Oracle Sequence

Previously, when Sequence was used as the primary key generation strategy, the xml Mapping file fragments were as follows:

<id name="id">
    <generator class="sequence">
        <param name="sequence">SEQ</param>
    </generator>
</id>

Using MySQL table to simulate Oracle Sequence

The structure of the table is the same as that of the previous annotations. The generator we used in xml this time is org.hibernate.id.enhanced.TableGenerator:

<id name="id">
    <generator class="org.hibernate.id.enhanced.TableGenerator">
        <param name="table_name">sys_sequence</param>
        <param name="segment_column_name">seq_name</param>
        <param name="value_column_name">curr_value</param>
        <param name="segment_value">SEQ</param>
        <param name="increment_size">1</param>
    </generator>
</id>

Modification of stored procedures

Storage process mainly involves the replacement of Sequence's nextval() and other methods. We can also simulate in MySQL.

Using Oracle Sequence

Assuming that we now have a Sequence named SEQ, we usually use the following functions in the stored procedure to get the next value of SEQ:

SEQ.nextval

Using MySQL to simulate Oracle Sequence

First, we need to create a table to store the names, current values and incremental steps of all sequences. Here we continue to use the sys_sequence table described above, but we also need to add a field increment_by for the table. We manually set the initial increment_by values according to the Oracle database settings.

> select * from sys_sequence;
+--------------+------------+--------------+
| seq_name     | curr_value | increment_by |
+--------------+------------+--------------+
| SEQ          |      2809  |            1 |
+--------------+------------+--------------+

Then add two new functions in the database, currval, to get the current value of the simulated Sequence:

CREATE DEFINER=`root`@`%` FUNCTION `currval`(`v_seq_name` varchar(50)) RETURNS decimal(18,0)
BEGIN
DECLARE v_currval DECIMAL(18);
    SET v_currval = 1;  
    SELECT curr_value INTO v_currval FROM sys_sequence WHERE seq_name = v_seq_name;  
    RETURN v_currval;  
END

The other is nextval, where the user gets the next value of the simulated Sequence:

CREATE DEFINER=`root`@`%` FUNCTION `nextval`(`v_seq_name` varchar(50)) RETURNS decimal(18,0)
BEGIN
      UPDATE sys_sequence SET curr_value = curr_value + increment_by WHERE seq_name = v_seq_name;  
    RETURN currval(v_seq_name);  
END

Then, where Sequence is needed, use the following statement instead:

nextval('SEQ')

Summary

In this way, the purpose of using MySQL tables to simulate Oracle's Sequence is achieved. Later, whenever there is a sequence that needs to be replaced, a new line is added to the sys_sequence established earlier.