Hi all,
Recently, I have been developing and using this tool, however, I have had problems with values to search.
It is necessary to clarify before continue… the character “” (escape) is special in regard to development. Where for each language it could mean something different, where if a string contained the character “” it should consult “\” to ignore the first and put the second escape as is.
For example, if I have a column in the varchar database and I save values with the special character “” (escape) through a java process. Suppose the records in my database are “TEST\00001”, “TEST\00002”, “TEST\00003”, “TEST\00003”, “TEST\00005” … “TEST\74309” when using hibernate to obtain the registration with a Criteria-Restriction.Eq it works correctly, but when using a like it does not find it, since it probably makes the query as is to the database and for this case “” ignores the case and it goes on to the following, leaving the string “TEST0001” to search, which does not find the required value.
So, if I have this:
String consult = "TEST\0000";
List values = sess.createCriteria(Value.class).add(Restrictions.like("column",consult,MatchMode.ANYWHERE));
I should filter the first five cases. Remembering that MatchMode.ANYWHERE makes it work like an ilike by adding “%” at the beginning and end of the string.
If the query is made as is to the database:
select column from table where column like '%TEST\0000%';
values are not obtained either.
Works with:
select column from table where column like '%TEST\\0000%';
In order to solve this in my codes I have used a replace of “” with “\” if the string contained it.
Should hibernate consider itself? Or is it up to the developer to receive these types of values? I mean that the hibernate query is supposed to go directly with the exact string that I want to get as a result, I don’t think I should check it in my code and do the following to consider it:
String consult = "TEST\0000";
consult = consult.replace ("\\", "\\\\");
List values = sess.createCriteria(Value.class).add(Restrictions.like("column",consult,MatchMode.ANYWHERE));
and get the 5 results.
I’m right? Has anyone else faced a similar problem? Can someone help me regarding this?
Because I agree that it could be a data interpretation bug, I mean… I should analyze in my code (if necessary) if a character should be ignored or not if it has the value “” in its string but not make sure that you consider it exactly as it happens. As for sql in the LIKE values, it is clarified that the “” character is special, you can check it by searching for “escape in sql like” and it has been cleared for some time.
In this case, if I received the value “T\Example” and would like to ignore the “E”, I should check this for myself in my code and tell Hibernate that I really want to look for “Texample” because I am sending the exact string to search.