Regular expressions (Regex) are one of the most powerful ways to search and validate text, allowing developers to perform pattern matching instead of relying on exact string comparisons. Hibernate Query Language (HQL) provides a database-independent way to query entities; however, one common question among Hibernate developers is whether HQL directly supports regular expressions. The short answer is that HQL does not provide a built-in REGEXP operator. Instead, regex support depends on the underlying database. Hibernate enables developers to invoke database-specific regex functions using the function() syntax, and native SQL queries can also be used when advanced regular expression functionality is required. In this article, we’ll explore how regular expression support works in HQL, when it is available, and how to use it effectively.
1. Understanding Regular Expression Support in HQL
Unlike SQL dialects such as MySQL, PostgreSQL, Oracle, and MariaDB, Hibernate Query Language (HQL) does not define a built-in REGEXP keyword or regular expression operator as part of its query syntax. HQL is designed to be database-independent, providing a consistent abstraction over different relational databases rather than exposing vendor-specific SQL features. As a result, attempting to write a query using a regex operator directly in HQL is not supported. For example, the following query is invalid because REGEXP is not recognized by the HQL parser:
FROM Employee e WHERE e.email REGEXP '.*@gmail.com'
Executing this query results in a syntax or query parsing exception since REGEXP is not part of the HQL grammar. To overcome this limitation, Hibernate provides the function() method, which allows developers to invoke database-specific SQL functions directly from HQL. If the underlying database supports regular expression matching, the corresponding regex function can be called using function(). For example, Oracle provides the REGEXP_LIKE() function, which can be invoked as shown below:
FROM Employee e WHERE function('regexp_like', e.email, '.*@gmail.com') = true
Similarly, databases such as MySQL and MariaDB expose a REGEXP function or operator, which can be invoked using:
FROM Employee e WHERE function('regexp', e.email, '.*@gmail.com') = 1
The exact function name, return type, and supported regular expression syntax vary across database vendors. Hibernate simply forwards the function call to the underlying database without interpreting the regular expression itself. This approach allows developers to take advantage of powerful database-native regex capabilities while continuing to write HQL queries, although it introduces database-specific behavior that may reduce query portability across different database systems.
1.1 Regex Support by Popular Databases
| Database | Regex Support | Supported Function / Operator | Notes |
|---|---|---|---|
| MySQL | Yes | REGEXP or REGEXP_LIKE() (MySQL 8.0+) | Supports POSIX regular expressions. REGEXP_LIKE() is available in newer versions. |
| PostgreSQL | Yes | ~, ~*, !~, !~* | Provides case-sensitive and case-insensitive regex operators. |
| Oracle | Yes | REGEXP_LIKE() | Offers comprehensive regex support with additional functions such as REGEXP_REPLACE() and REGEXP_SUBSTR(). |
| MariaDB | Yes | REGEXP / RLIKE | Supports regular expression matching similar to MySQL. |
| Microsoft SQL Server | No (Native) | Custom CLR functions or LIKE/PATINDEX() | Does not provide built-in regular expression functions. |
| H2 Database | Limited | REGEXP | Suitable for testing and development, but regex capabilities are more limited than enterprise databases. |
1.2 How Hibernate Executes Regex Queries
When an HQL query invokes a database function using the function() syntax, Hibernate does not attempt to interpret or execute the regular expression itself. Instead, it recognizes the function call and translates it into the equivalent SQL statement for the configured database dialect. The actual pattern matching is entirely delegated to the underlying database engine, allowing Hibernate to remain database-independent while still supporting vendor-specific functionality. Consider the following HQL query that uses Oracle’s REGEXP_LIKE() function:
String hql = """FROM Employee e WHERE function('regexp_like', e.email, '.*@gmail.com')""";
When this query is executed, Hibernate generates SQL similar to the following:
SELECT * FROM employee WHERE REGEXP_LIKE(email, '.*@gmail.com')
Notice that Hibernate simply replaces the function() call with the corresponding database function and generates the appropriate SQL statement. It does not parse, validate, or execute the regular expression. Instead, the database evaluates the regex pattern against the specified column and returns only the matching rows. Hibernate then maps the resulting records back into Employee entity objects before returning them to the application.
1.3 When Should You Use Regex in HQL?
Regular expressions are ideal when your filtering logic involves complex text patterns that cannot be expressed efficiently using standard string operations. However, because regex evaluation is generally more computationally expensive than operators such as LIKE, it should be reserved for advanced pattern matching. For simple searches such as checking prefixes, suffixes, or partial text matches, traditional HQL functions typically provide better performance and improved database portability.
| Scenario | Recommended Approach | Reason |
|---|---|---|
| Email or domain validation | Use Regex | Accurately matches email patterns and specific domains. |
| Phone number filtering | Use Regex | Supports country codes, separators, and digit validation. |
| Product or employee code matching | Use Regex | Useful for validating alphanumeric formats and naming conventions. |
| Input containing only numbers or letters | Use Regex | Ideal for validating character sets and data formats. |
| Simple prefix search | Use LIKE 'ABC%' | Faster and can leverage database indexes. |
| Simple suffix search | Use LIKE '%XYZ' | More readable and avoids unnecessary regex processing. |
| Contains text search | Use LIKE '%text%' | Suitable for straightforward substring matching. |
| Case-insensitive search | Use LOWER() + LIKE | Provides better portability across databases. |
| Exact value comparison | Use = | Most efficient option for matching a single value. |
1.4 Performance Considerations
Regular expressions are generally more expensive than standard string operations. Consider the following recommendations:
- Use LIKE whenever a simple wildcard search is sufficient.
- Reserve regex for complex pattern matching requirements.
- Be aware that regex predicates may prevent the use of indexes, leading to full table scans.
- For large datasets, benchmark regex queries and consider precomputing searchable values if performance becomes an issue.
- Keep regex patterns as specific as possible to reduce unnecessary processing.
1.5 Limitations
- HQL has no native REGEXP keyword.
- Regex support is entirely database-dependent.
- Different databases expose different function names and regex syntax.
- Moving between databases may require changing HQL function calls.
- Complex regex operations reduce query portability.
2. Code Example
The following example demonstrates how to use a database-specific regular expression function from HQL to retrieve all employees whose email addresses belong to the gmail.com domain. The example includes the entity, sample data, HQL query, explanation, and expected output.
2.1 Employee Entity
The first step is to define a Hibernate entity that maps to the employees table. This entity contains the employee’s identifier, name, and email address, which will later be queried using a regular expression in HQL.
import jakarta.persistence.*;
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String email;
public Employee() {
}
public Employee(String name, String email) {
this.name = name;
this.email = email;
}
public Long getId() {
return id;
}
public String getName() {
return name;
}
public String getEmail() {
return email;
}
public void setName(String name) {
this.name = name;
}
public void setEmail(String email) {
this.email = email;
}
}
The Employee class is a Hibernate entity that represents the employees database table. It is annotated with @Entity to indicate that Hibernate should manage it as a persistent entity, while the @Table(name = "employees") annotation maps it to the corresponding database table. The id field serves as the primary key and is annotated with @Id and @GeneratedValue(strategy = GenerationType.IDENTITY), allowing the database to automatically generate unique identifier values. The name and email fields are mapped to table columns and store the employee’s name and email address, respectively. A no-argument constructor is provided because it is required by Hibernate for creating entity instances, while the parameterized constructor simplifies object creation when inserting new records. Finally, the getter and setter methods provide controlled access to the entity’s fields and enable Hibernate to read and update the entity state during persistence operations. This entity serves as the foundation for the HQL regex query, where the email property is evaluated using a database-specific regular expression function to retrieve matching employee records.
2.2 Application Code
The following application demonstrates how to bootstrap Hibernate, insert a few sample records, and execute an HQL query that invokes the database-specific REGEXP_LIKE() function using Hibernate’s function() syntax. The matching employee records are then retrieved as entity objects and printed to the console.
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class HibernateRegexExample {
public static void main(String[] args) {
SessionFactory sessionFactory = new Configuration()
.configure("hibernate.cfg.xml")
.addAnnotatedClass(Employee.class)
.buildSessionFactory();
// Insert sample data
insertEmployees(sessionFactory);
// Execute HQL regex query
fetchGmailEmployees(sessionFactory);
sessionFactory.close();
}
private static void insertEmployees(SessionFactory sessionFactory) {
Session session = sessionFactory.openSession();
Transaction transaction = session.beginTransaction();
session.persist(new Employee("John", "john@gmail.com"));
session.persist(new Employee("Alice", "alice@yahoo.com"));
session.persist(new Employee("David", "david@gmail.com"));
session.persist(new Employee("Mike", "mike@company.org"));
transaction.commit();
session.close();
}
private static void fetchGmailEmployees(SessionFactory sessionFactory) {
Session session = sessionFactory.openSession();
String hql = """
FROM Employee e
WHERE function(
'regexp_like',
e.email,
'.*@gmail\\.com'
) = true
""";
List<Employee> employees =
session.createQuery(hql, Employee.class)
.getResultList();
System.out.println("Employees having Gmail accounts");
System.out.println("--------------------------------");
employees.forEach(employee -> System.out.println(
employee.getId() + " | " +
employee.getName() + " | " +
employee.getEmail()));
session.close();
}
}
The HibernateRegexExample class demonstrates how to use a database-specific regular expression function within an HQL query. The main() method creates a SessionFactory using the hibernate.cfg.xml configuration file and registers the Employee entity before invoking two helper methods: one to insert sample employee records and another to execute the regex query. The insertEmployees() method opens a Hibernate session, begins a transaction, persists four Employee objects into the database using session.persist(), commits the transaction, and closes the session. The fetchGmailEmployees() method opens a new session and defines an HQL query that uses Hibernate’s function() method to invoke the database-specific REGEXP_LIKE() function. The regular expression .*@gmail\\.com matches all email addresses ending with @gmail.com, where .* matches any sequence of characters before the ‘@’ symbol and \\. represents a literal dot in the domain name. Hibernate translates this HQL statement into the corresponding SQL query for the configured database and delegates the regular expression evaluation to the database engine. The matching rows are automatically mapped back into Employee objects, stored in a List<Employee>, and printed to the console using a lambda expression that displays each employee’s ID, name, and email address. Finally, both the session and the SessionFactory are closed to release database resources.
2.3 Code Run and Code Output
When the application starts, Hibernate creates a SessionFactory using the configuration specified in hibernate.cfg.xml. It then opens a session, begins a transaction, and inserts four sample Employee records into the employees table. Once the data is committed, a new session is opened to execute the HQL query. The query invokes the database-specific REGEXP_LIKE() function through Hibernate’s function() method to match email addresses ending with @gmail.com. Hibernate translates the HQL statement into the corresponding SQL query for the configured database, executes it, and maps the matching rows back into Employee entity objects. Finally, the application iterates over the resulting list and prints the matching employee details to the console.
select
e.id,
e.email,
e.name
from
employees e
where
REGEXP_LIKE(e.email, '.*@gmail\.com')
The output shows that only the employees whose email addresses satisfy the regular expression are returned by the query. Records such as alice@yahoo.com and mike@company.org are excluded because they do not match the specified regex pattern. This demonstrates how Hibernate can seamlessly leverage the regular expression capabilities of the underlying database while still allowing developers to write queries using HQL.
Employees having Gmail accounts -------------------------------- 1 | John | john@gmail.com 3 | David | david@gmail.com
3. Conclusion
Although HQL does not include built-in regular expression support, Hibernate provides a flexible way to leverage the regex capabilities of the underlying database through the function() syntax or native SQL queries. This approach enables powerful pattern matching for use cases such as email validation, product code filtering, and complex text searches while still working with Hibernate-managed entities. However, because regex support is vendor-specific, developers should be mindful of portability and performance. Whenever a simple LIKE expression is sufficient, it is generally the better choice due to its simplicity and better optimization. For advanced text matching requirements, invoking database regex functions through Hibernate offers an effective and maintainable solution.
