CODEWITHSUNDEEP
sql-serverhibernatewildcardsql-likemaxdb
Benjamin Schmidt
Benjamin Schmidt

Reputation: 11

Configure hibernate to process like with [] (square brackets) against maxDB and sql-server in the same way

I have the following jpql with a LIKE

SELECT someField FROM someEntity WHERE otherField LIKE '%foo[bar]'

I want to retrieve all rows ending with 'foo[bar]'. For maxDb this is OK. But for sql-server the square brackets are interpreted as wildcards (usually in sql server [a-d] would match all rows containg a,b,c or d inside the brackets). I wonder why hibernate is not interpreting the String 'foo[bar]' as String and excaping the square brackets by itself. The following hibernate dialects are used: for maxDB hibernate.ExtSAPDBDialect and for sql-server org.hibernate.dialect.SQLServer2012Dialect. Right now I have to differentiate in Java between my destination systems, so I can decide if to escape the brackets or not. I wonder why I need to do this in code. Is there a possibility to teach hibernate to consider this, so I can use only the select.

Upvotes: 1

Views: 216

Answers (2)

CoderMSL
CoderMSL

Reputation: 89

Today I encounter same/similar issue (I know that this question is almost 2 years old). Below I will share my research outcome which ended in a working code, properly handling [ character . Maybe someone will find it useful.

Just replace single bracket [ with double one [[ with following code:

String preparedQuery = query.replaceAll("\\[", "\\[\\[");
List<Element> elements = repository.findByName(preparedQuery);

If you use org.hibernate.dialect.SQLServerDialect it also supports mentioned by @gotqn ESCAPE operation, I tested it as a part of HQL in @Query annotation. For example:

@Query("FROM elements WHERE name LIKE %:preparedQuery% ESCAPE '['") 
List<Element> findByName(@Param("preparedQuery") String preparedQuery);

I tried with different drivers/dialects without a success, so I doubt that there is an automatic solution for that (but maybe I'm wrong). With a little effort you can achieve what you want and use easily MS SQL Server / Azure SQL Server with Hibernate / HQL.

Upvotes: 0

gotqn
gotqn

Reputation: 43636

It will add additional overhead as it's one more operation and index seek can't be used, but you can replace these the ] and [ with something else. For example:

REPLACE(someColumn, '[', CHAR(31)) LIKE '%foo' + CHAR(30) + 'bar]'

or you can try using ESCAPE option as it's supported in maxdb, too. In T-SQL, it will be:

DECLARE @someTable TABLE
(
    [someColumn] NVARCHAR(128)
);

INSERT INTO @someTable ([someColumn])
VALUES ('test foo[bar]')
      ,('test foo[bar');

SELECT * 
FROM @someTable 
WHERE someColumn LIKE '%foo|[bar]' ESCAPE '|';

Upvotes: 0

Related Questions