Skip to content
BSWEN
Blog

Alternatives to LowercaseNamingStrategy: Quoted Identifiers and Database Views for MySQL 8 JPA

Cowrie
Cowrie
Dev @ Bswen

Database code Photo: Database and server code

The Problem: MySQL 8 Case Sensitivity Without Naming Strategy Control

I recently encountered a situation where implementing a custom PhysicalNamingStrategy wasn’t feasible. The existing codebase had naming conventions that couldn’t be easily modified, and the database schema was shared across multiple applications with different requirements.

The error was familiar to anyone who’s worked with MySQL 8 and JPA:

Table 'mydb.USER_ACCOUNT' doesn't exist

But when I checked the database, the table was there—it just had a different case: user_account (lowercase). MySQL 8 on Linux is case-sensitive by default, and Hibernate’s default naming strategy generates uppercase identifiers.

When you can’t modify the naming strategy configuration (perhaps due to shared infrastructure, legacy constraints, or other limitations), you need alternatives. Let me share three approaches that work.

Alternative 1: Quoted Identifiers with Backticks

The most straightforward approach is to use backticks (`) in your JPA annotations to explicitly quote identifiers. This tells Hibernate to use the exact case specified in the annotation.

UserAccount.java
@Table(name = "`USER_ACCOUNT`")
@Entity
public class UserAccount {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @Column(name = "`USER_NAME`")
    private String userName;


    // getters and setters
}

This approach requires creating database objects that match the quoted case exactly:

schema.sql
CREATE TABLE `USER_ACCOUNT` (
    `ID` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `USER_NAME` VARCHAR(100) NOT NULL
);

Why this works: The backticks force MySQL to treat the identifier as case-sensitive and preserve the exact case you specify. Hibernate will generate SQL that respects this quoted identifier.

When to use: This is ideal when you have control over the database schema and want to maintain a specific naming convention across your application. It’s also useful when migrating an existing schema where changing case would be disruptive.

Pros:

  • Explicit control over table and column names
  • Works with existing schemas that use specific case
  • Clear intent in the code

Cons:

  • Requires manual specification for every entity
  • Can lead to inconsistent naming if not applied consistently
  • Database schema must match the quoted names exactly

Alternative 2: Database Views

When you can’t modify the entity annotations or create new tables, database views offer a clever workaround. You create a view with lowercase names that maps to the actual table.

create_views.sql
-- Original table (uppercase)
CREATE TABLE USER_ACCOUNT (
    ID BIGINT AUTO_INCREMENT PRIMARY KEY,
    USER_NAME VARCHAR(100) NOT NULL
);


-- Create view with lowercase names
CREATE VIEW user_account AS
SELECT
    ID as id,
    USER_NAME as user_name
FROM USER_ACCOUNT;

Now your entity can use the default lowercase naming:

UserAccount.java
@Entity
@Table(name = "user_account")  // Maps to the view
public class UserAccount {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @Column(name = "user_name")
    private String userName;


    // getters and setters
}

Why this works: Hibernate generates queries using lowercase identifiers, which match the view. The view then delegates to the actual table. MySQL sees the lowercase view name and doesn’t have a case sensitivity conflict.

When to use: This approach shines when you have a legacy database schema that can’t be modified, or when multiple applications share the same database and have conflicting naming requirements. It’s also useful when you want to keep entity classes clean without numerous annotations.

Pros:

  • No modification to existing tables required
  • Clean entity classes without excessive annotations
  • Can be applied selectively to problematic tables only

Cons:

  • Adds database objects (views) to maintain
  • Potential minor performance overhead (usually negligible)
  • View management complexity for large schemas

Alternative 3: Native Queries with Explicit Names

When you need maximum control and can’t change annotations or schema, native queries give you complete control over the generated SQL.

UserAccountRepository.java
public interface UserAccountRepository extends JpaRepository<UserAccount, Long> {


    @Query(nativeQuery = true,
           value = "SELECT * FROM user_account WHERE user_name = :name")
    List<UserAccount> findByUserName(@Param("name") String name);


    @Query(nativeQuery = true,
           value = "INSERT INTO user_account (user_name) VALUES (:name)")
    @Modifying
    void insertUser(@Param("name") String name);
}

The entity still uses standard annotations:

UserAccount.java
@Entity
@Table(name = "USER_ACCOUNT")
public class UserAccount {


    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;


    @Column(name = "USER_NAME")
    private String userName;


    // getters and setters
}

Why this works: Native queries bypass Hibernate’s naming strategy entirely. You write the exact SQL that will be executed, including table and column names in the exact case required.

When to use: This is best for specific queries where you need precise control, or when dealing with particularly problematic queries. It’s not practical for all queries, but works well for frequently used or critical operations.

Pros:

  • Complete control over SQL
  • No schema changes required
  • Works with any existing table structure

Cons:

  • Loses JPA’s database independence
  • More verbose than JPQL
  • Must manually keep queries in sync with schema changes
  • Doesn’t solve issues with basic CRUD operations

Comparison Table

Approach Comparison
| Approach | When to Use | Pros | Cons |
|----------|-------------|------|------|
| Quoted Identifiers | Control over schema, specific naming convention needed | Explicit control, works with existing schemas | Manual for every entity, schema must match exactly |
| Database Views | Can't modify schema, multiple apps share database | No table modification, clean entities | Additional objects to maintain, slight overhead |
| Native Queries | Specific problematic queries, need exact control | Complete SQL control, no schema changes | Loses portability, verbose, manual maintenance |

The root cause of these case sensitivity issues is MySQL’s lower_case_table_names system variable. On Linux systems, it defaults to 0, meaning table names are case-sensitive. On Windows, it defaults to 1, meaning names are stored in lowercase and comparisons are case-insensitive.

You can check your current setting:

Check MySQL setting
SHOW VARIABLES LIKE 'lower_case_table_names';

Important: Changing this value on an existing database requires careful planning. MySQL documentation warns against changing this setting after database initialization, as it can lead to inconsistencies and data loss.

The approaches I’ve outlined let you work around case sensitivity issues without changing MySQL’s configuration—useful when you don’t have administrative access or when changing the setting would affect other applications.

Choosing the Right Approach

For my situation, I chose database views. They offered the best balance for a shared legacy database where I couldn’t easily modify table structures or application-wide configuration. The views are isolated to my application’s needs without affecting other users of the database.

If you’re starting fresh and have full control, quoted identifiers give you the most explicit control. For specific problematic queries, native queries provide surgical precision.

Remember: each alternative addresses specific constraints. Evaluate your environment’s limitations—schema control, application architecture, team conventions—before choosing.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Comments

Tags: