Hibernate Criteria API在Oracle中如何转义&特殊字符？

Got it, this is a super common Oracle-specific quirk—by default, the & character is treated as a substitution variable, so Oracle will try to prompt for a value for whatever comes after & instead of treating it as literal text. Let's walk through the best fixes for your Criteria query:

1. Use Parameter Binding with sqlRestriction (Recommended) #

The issue with your original code is that when using ignoreCase(), Hibernate might generate SQL that directly embeds the string containing & into the LOWER() function, triggering Oracle's substitution variable behavior. Explicit parameter binding avoids this entirely:

// Import StringType if needed: import org.hibernate.type.StringType;
Criterion criterion = Restrictions.sqlRestriction(
    "LOWER(company_name) = LOWER(?)", 
    "a&b Inc", 
    StringType.INSTANCE
);
cr.add(criterion);

By using a parameter (?), Hibernate uses a prepared statement, so Oracle won't interpret the & in the parameter value as a substitution variable. This also keeps your query safe from SQL injection—double win.

2. Escape & as && in the Input String #

If you want to stick with Restrictions.eq().ignoreCase(), you can escape each & in your input by doubling it to &&. Oracle parses two consecutive & characters as a single literal &:

String companyName = "a&b Inc".replace("&", "&&");
Criterion criterion = Restrictions.eq("company_name", companyName).ignoreCase();
cr.add(criterion);

This works because Oracle treats && as a literal & when substitution variables are enabled.

3. Disable Oracle's Substitution Variable Behavior (Session-Wide) #

You can turn off Oracle's substitution variable handling for the entire database session. Add this initialization SQL to your Hibernate configuration:

For Hibernate XML Config (hibernate.cfg.xml): #

<property name="hibernate.connection.init_sql">ALTER SESSION SET DEFINE OFF;</property>

For Spring Boot (application.properties): #

spring.jpa.properties.hibernate.connection.init_sql=ALTER SESSION SET DEFINE OFF;

This tells Oracle not to treat & as a substitution variable at all. Just note: if any other parts of your app rely on Oracle substitution variables, they'll break—only use this if you don't need that functionality.

内容的提问来源于stack exchange，提问作者hasha