MySQL to Oracle migration challenges? | JPA & Hibernate

Q: What changes with the Hibernate dialect?

Hibernate generates databasespecific SQL through its dialect. On MySQL, it uses LIMIT for pagination and AUTO_INCREMENT for identifiers. On Oracle, it uses FETCH FIRST n ROWS ONLY (or ROWNUM on older versions) and SEQUENCE for identifiers. Changing the dialect flips all generated SQL. Before: MySQL

Q: What should a senior engineer know?

A senior engineer audits every native query for MySQLonly constructs before switching. They test the emptystringisnull behaviour on all NOT NULL varchar columns. They keep identifier naming consistent to avoid Oracle's casesensitivity issues. They set ddlauto to validate against the real Oracle sche

Definition

MySQL to Oracle means swapping dialect, driver/URL, switching IDENTITY to SEQUENCE, and handling type differences like VARCHAR2, NUMBER, no native BOOLEAN, and empty-string-is-null.

Dialect change – switch from a MySQL dialect to OracleDialect so Hibernate generates Oracle SQL (FETCH FIRST for pagination, sequence calls, Oracle functions).
Driver/URL change – swap MySQL Connector/J for Oracle ojdbc and change the URL to jdbc:oracle:thin:@//host:1521/service.
Identifier generation – MySQL AUTO_INCREMENT (IDENTITY) does not exist the same way in Oracle; switch to SEQUENCE with @SequenceGenerator, which also enables batch inserts.
Type differences – VARCHAR2 instead of VARCHAR, NUMBER(p,s) instead of DECIMAL, no native BOOLEAN in older Oracle (use NUMBER(1) converter), CLOB/BLOB for large data.
Empty string equals NULL – Oracle treats '' as NULL, which changes behaviour for NOT NULL columns and equality comparisons.
Identifier case – Oracle folds unquoted identifiers to UPPERCASE; quoted identifiers are case-sensitive.

Moving National Bank of India's data from one vault manufacturer (MySQL) to another (Oracle) means the lock mechanism is different (driver), the clerk must speak the new vault's dialect of filing instructions (dialect), the way new file numbers are issued changes from a built-in counter to a separate number-dispenser (sequences instead of auto-increment), and some box shapes differ: there is no true yes/no box, text boxes are a different type, and an empty box is treated as if it were missing.

Understanding the Concepts

What changes with the Hibernate dialect?

Hibernate generates database-specific SQL through its dialect. On MySQL, it uses LIMIT for pagination and AUTO_INCREMENT for identifiers. On Oracle, it uses FETCH FIRST n ROWS ONLY (or ROWNUM on older versions) and SEQUENCE for identifiers. Changing the dialect flips all generated SQL.

YAML

# Before: MySQL
spring:
  jpa:
    database-platform: org.hibernate.dialect.MySQLDialect
  datasource:
    url: jdbc:mysql://db-host:3306/bankdb
    driver-class-name: com.mysql.cj.jdbc.Driver

# After: Oracle
spring:
  jpa:
    database-platform: org.hibernate.dialect.OracleDialect
  datasource:
    url: jdbc:oracle:thin:@//db-host:1521/ORCLPDB1
    driver-class-name: oracle.jdbc.OracleDriver

TEXT

Pagination SQL difference:
  MySQL:  SELECT * FROM accounts LIMIT 20 OFFSET 0
  Oracle: SELECT * FROM accounts FETCH FIRST 20 ROWS ONLY

Hibernate handles this automatically when the dialect is set correctly.

What changes with the JDBC driver and URL?

Replace the MySQL Connector/J Maven dependency with the Oracle JDBC driver and change the URL format.

XML

<!-- Before: MySQL --syn-kw">>
syn-kw"><dependencysyn-kw">>
    syn-kw"><groupIdsyn-kw">>com.mysqlsyn-kw"></groupIdsyn-kw">>
    syn-kw"><artifactIdsyn-kw">>mysql-connector-jsyn-kw"></artifactIdsyn-kw">>
syn-kw"></dependencysyn-kw">>

<!-- After: Oracle --syn-kw">>
syn-kw"><dependencysyn-kw">>
    syn-kw"><groupIdsyn-kw">>com.oracle.database.jdbcsyn-kw"></groupIdsyn-kw">>
    syn-kw"><artifactIdsyn-kw">>ojdbc11syn-kw"></artifactIdsyn-kw">>
syn-kw"></dependencysyn-kw">>

TEXT

URL format:
  MySQL:  jdbc:mysql://host:3306/database
  Oracle: jdbc:oracle:thin:@//host:1521/service_name

Port:
  MySQL:  3306 (default)
  Oracle: 1521 (default)

What changes with identifier generation?

MySQL AUTO_INCREMENT uses GenerationType.IDENTITY, which prevents batch inserts because Hibernate needs each id immediately. Oracle uses SEQUENCE objects. Switch to GenerationType.SEQUENCE with a @SequenceGenerator, and create the sequence in the Oracle schema. As a bonus, sequences enable batch inserts.

JAVA

// Before: MySQL (IDENTITY / auto-increment)
package com.bank.model;

import jakarta.persistence.*;

@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
}

JAVA

// After: Oracle (SEQUENCE)
package com.bank.model;

import jakarta.persistence.*;

@Entity
public class Account {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "acc_seq")
    @SequenceGenerator(name = "acc_seq", sequenceName = "account_seq", allocationSize = 50)
    private Long id;
}

SQL

-- Oracle DDL: create the sequence
CREATE SEQUENCE account_seq START WITH 1 INCREMENT BY 50;

TEXT

Benefit of switching to SEQUENCE:
- IDENTITY: 1 round trip per INSERT (no batching)
- SEQUENCE: 1 round trip per 50 IDs (batching works!)
- Migration to Oracle actually IMPROVES insert performance

What are the schema and type differences?

Oracle has different data types and behaviours that cause subtle runtime surprises.

| MySQL Type | Oracle Type | Notes |
|---|---|---|
| VARCHAR(n) | VARCHAR2(n) | Hibernate dialect handles mapping |
| DECIMAL(p,s) | NUMBER(p,s) | BigDecimal maps correctly to both |
| BOOLEAN | NUMBER(1) | Older Oracle has no native BOOLEAN |
| TEXT | CLOB | Large text storage |
| BLOB | BLOB | Binary data |
| AUTO_INCREMENT | SEQUENCE | Must create sequence objects |
| '' (empty string) | NULL | Oracle treats '' as NULL |

JAVA

package com.bank.model;

import jakarta.persistence.*;
import java.math.BigDecimal;

@Entity
@Table(name = "accounts")
public class Account {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "acc_seq")
    @SequenceGenerator(name = "acc_seq", sequenceName = "account_seq", allocationSize = 50)
    private Long id;

    @Column(name = "holder_name", nullable = false, length = 100)
    private String holderName;     // VARCHAR2(100) on Oracle

    @Column(name = "balance", precision = 15, scale = 2)
    private BigDecimal balance;    // NUMBER(15,2) on Oracle

    @Column(name = "active")
    private boolean active;        // NUMBER(1) on Oracle: 0 or 1

    @Column(name = "notes", columnDefinition = "CLOB")
    private String notes;          // CLOB on Oracle
}

What about the empty-string-is-null problem?

Oracle treats an empty string ('') as NULL. Code or queries that rely on distinguishing an empty string from null will behave differently. This is a common source of bugs after migration.

JAVA

// WRONG: relying on empty string being different from null
package com.bank.service;

public class SearchService {
    public boolean hasMiddleName(String middleName) {
        return middleName != null && !middleName.isEmpty();
        // On MySQL: middleName = "" -> returns false (correct)
        // On Oracle: middleName stored as '' -> stored as NULL
        //           middleName retrieved as null -> returns false
        // But: queries like WHERE middle_name = '' will find NOTHING on Oracle
    }
}

JAVA

// CORRECT: treat empty string and null the same way
package com.bank.service;

public class SearchService {
    public boolean hasMiddleName(String middleName) {
        return middleName != null && !middleName.isBlank();
        // Works on both MySQL and Oracle
    }
}

TEXT

Oracle empty-string-is-null:
  INSERT INTO accounts (holder_name, middle_name) VALUES ('Rajesh', '')
  -> middle_name is stored as NULL in Oracle
  -> SELECT * WHERE middle_name = '' returns NO rows
  -> SELECT * WHERE middle_name IS NULL returns the row

What about Oracle identifier case sensitivity?

Oracle folds unquoted identifiers to UPPERCASE. If your Hibernate naming strategy produces lowercase or mixed-case names, they become uppercase in Oracle. Quoted identifiers are case-sensitive, so avoid mixing quoted and unquoted names.

JAVA

// Table name in entity
@Table(name = "accounts")   // Oracle: unquoted -> ACCOUNTS
@Table(name = "\"Accounts\"")  // Oracle: quoted -> Accounts (exact case)
// Keep it simple: use lowercase unquoted names, let Oracle fold to uppercase

TEXT

Oracle case rules:
  Unquoted: accounts -> ACCOUNTS (uppercase)
  Quoted:   "Accounts" -> Accounts (exact case, case-sensitive)
  Best practice: use lowercase unquoted names consistently

How do you handle the BOOLEAN type?

Older Oracle has no native BOOLEAN column type. A Java boolean field maps to NUMBER(1) through the Hibernate dialect or an AttributeConverter. Oracle 23c adds a real BOOLEAN type, but for compatibility use a converter.

JAVA

package com.bank.converter;

import jakarta.persistence.AttributeConverter;
import jakarta.persistence.Converter;

@Converter(autoApply = true)
public class BooleanToNumberConverter implements AttributeConverter<Boolean, Integer> {

    @Override
    public Integer convertToDatabaseColumn(Boolean value) {
        return value != null && value ? 1 : 0;
    }

    @Override
    public Boolean convertToEntityAttribute(Integer value) {
        return value != null && value == 1;
    }
}

TEXT

Java: boolean active = true
Oracle: NUMBER(1) = 1
Java: boolean active = false
Oracle: NUMBER(1) = 0

What is the migration process?

Run the migration in stages. First, make the application dialect/driver-agnostic by removing MySQL-specific native queries. Then switch the dialect, driver, and identifier strategy. Finally, run the full regression against a real Oracle instance.

YAML

# Stage 3: validate schema against Oracle
spring:
  jpa:
    hibernate:
      ddl-auto: validate    # verify entity mappings match Oracle schema
    database-platform: org.hibernate.dialect.OracleDialect

TEXT

Migration stages:
1. Audit native queries for MySQL-only syntax (LIMIT, MySQL functions)
2. Rewrite native queries to be portable or Oracle-compatible
3. Switch dialect, driver, URL in config
4. Switch IDENTITY to SEQUENCE in entities
5. Create Oracle sequences and DDL
6. Run Flyway/Liquibase with Oracle-compatible migrations
7. Set ddl-auto: validate to verify mappings
8. Run full regression + integration tests against real Oracle

What should a senior engineer know?

A senior engineer audits every native query for MySQL-only constructs before switching. They test the empty-string-is-null behaviour on all NOT NULL varchar columns. They keep identifier naming consistent to avoid Oracle's case-sensitivity issues. They set ddl-auto to validate against the real Oracle schema.

JAVA

// Senior checklist before migration:
// 1. Audit @Query(nativeQuery=true) for MySQL-specific SQL
// 2. Replace LIMIT with Pageable (Hibernate handles FETCH FIRST)
// 3. Replace AUTO_INCREMENT with SEQUENCE
// 4. Test empty string vs null on all String columns
// 5. Add BooleanToNumberConverter for boolean fields
// 6. Run integration tests against Oracle Docker container

Visual Overview

TEXT

  MySQL to Oracle Migration Layers
  ============================================

  Layer 1: DIALECT
  +---------------------------+       +---------------------------+
  | MySQLDialect              |  -->  | OracleDialect             |
  | LIMIT 20                  |       | FETCH FIRST 20 ROWS ONLY  |
  | AUTO_INCREMENT            |       | SEQUENCE                  |
  +---------------------------+       +---------------------------+

  Layer 2: DRIVER + URL
  +---------------------------+       +---------------------------+
  | mysql-connector-j         |  -->  | ojdbc11                   |
  | jdbc:mysql://host:3306/db |       | jdbc:oracle:thin:@//host  |
  +---------------------------+       |   :1521/service           |
                                      +---------------------------+

  Layer 3: IDENTIFIER GENERATION
  +---------------------------+       +---------------------------+
  | IDENTITY (auto-increment) |  -->  | SEQUENCE + allocationSize |
  | No batching               |       | Batch inserts enabled!    |
  +---------------------------+       +---------------------------+

  Layer 4: SCHEMA / TYPES
  +---------------------------+       +---------------------------+
  | VARCHAR, DECIMAL, BOOLEAN |  -->  | VARCHAR2, NUMBER, NUM(1)  |
  | TEXT, ''!=NULL             |       | CLOB, ''==NULL            |
  | lowercase identifiers     |       | UPPERCASE (unquoted)      |
  +---------------------------+       +---------------------------+

Key Takeaway

Migrating a JPA application from MySQL to Oracle touches four layers. First the dialect: Rajesh changes Hibernate's dialect to OracleDialect so it generates Oracle SQL with FETCH FIRST for pagination and sequence calls for identifiers. Second the driver and URL: he swaps the MySQL Connector/J for Oracle's ojdbc and changes the JDBC URL format. Third, and this is the biggest code change, identifier generation: MySQL's AUTO_INCREMENT with IDENTITY is replaced by SEQUENCE with a @SequenceGenerator and a created Oracle sequence, which as a bonus enables batch inserts that IDENTITY never allowed.

Fourth, the schema and type differences at National Bank of India cause the subtle runtime surprises. VARCHAR becomes VARCHAR2, DECIMAL becomes NUMBER with precision and scale for money, older Oracle has no native BOOLEAN so a Java boolean maps through a converter to NUMBER(1), large text becomes CLOB, and crucially Oracle treats an empty string as NULL. Pankaj audits every NOT NULL varchar column and every comparison against empty strings before migration.

The senior point is that the dialect only fixes Hibernate's generated SQL, not any native SQL the team wrote by hand. Before flipping the dialect, Pankaj audits every native query for MySQL-only constructs, keeps identifier naming consistent because Oracle folds unquoted names to uppercase, and runs the migration in stages with ddl-auto set to validate against a real Oracle instance.

Remember one sentence: MySQL to Oracle means switching dialect, driver, and IDENTITY to SEQUENCE, plus handling VARCHAR2, NUMBER, no BOOLEAN, and empty-string-is-null.

---