Jpa Criteria equals Predicate for a table's String property (with newLine character '\n') doesn't work when in MySQL Workbench it works

Question

as the title suggests I am having a problem trying to use JPA Criteria with Spring Boot for a specific case.

Generally everything works but trying to search for stored data with a String property having newLine character embedded ( \n ) doesn't seem to work.

I am able to Get the data, edit them, save them through my front end, create new with multiple lines etc. but trying to search for them when for example a column is equals with 'hello\nworld' it wont work even though running this query in MySQL Workbench works returning the desired data :

select * from kerkinidb.ct_thhlastika_press_threats where description_en = 'hello\nworld';

To clarify, the way I do the search is by waiting in a Get request an argument called search which has all the properties that the user filtered. I am matching it with a Regex (which also has inside the Java 8.0 new Regex \\\\R for matching with multilines (and it works) ) then I am giving the the Service layer the Search Criteria that I matched which then passes to the Jpa Criteria Repository to parse them and generating the Predicates (matching again with Regex and \\\\R to create a final Predicate with OR and ANDs for the filtering) then triggering the query, then making another query called count to implement Pagination and finally mapping to a custom object and returning it.

I debugged every step and the final Predicate does generate the query I want, but the db doesn't return the expected data. So I am really confused since as I said the query does work in MySQL Workbench.

This is an example of the logging (I turned Spring Boot logging for MySQL logs on) generated when the Request is being triggered (in this case the stored data I have in my Table ct_thhlastika_press_threats in column description_en is a\ns\ndd so I am searching for this one as you can see instead of the example I said earlier hello\nworld :

2019-02-12 16:01:01.929 DEBUG 18368 --- [nio-8080-exec-2] org.hibernate.SQL                        : select ctthhlasti0_.id as col_0_0_, ctthhlasti0_.act_code as col_1_0_, ctthhlasti0_.description_en as col_2_0_, ctthhlasti0_.remarks as col_3_0_ from ct_thhlastika_press_threats ctthhlasti0_ where 1=1 and ctthhlasti0_.description_en=? order by ctthhlasti0_.id asc limit ?
2019-02-12 16:01:01.933 TRACE 18368 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a\ns\ndd]
2019-02-12 16:01:01.944 DEBUG 18368 --- [nio-8080-exec-2] org.hibernate.SQL                        : select count(ctthhlasti0_.id) as col_0_0_ from ct_thhlastika_press_threats ctthhlasti0_ where 1=1 and ctthhlasti0_.description_en=?
2019-02-12 16:01:01.944 TRACE 18368 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [a\ns\ndd]
2019-02-12 16:01:01.946 TRACE 18368 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [BIGINT]) - [0]

For anyone interested to look farther into the code you could find it Github repo. The project is for my University thesis. I made comments for adding to the Regexes (both of them) for ctThhlastikaPressThreats Controller and Repository. The db (the standard as well as the secondary for testing) need to be generated (can be generated by changing the auto-dll in application.properties).

Update

I tried using System.lineSeparator() (as suggested by @Hermann Steidel in the answer bellow) replacing the text newLine \n. But even though in the logs we can see now clearly that the value for the equals Predicate has line separations it still doesn't return the data from the DB.

Farther Explanation of my implementation

A correct Get request for the dynamic searching is like this :

http://localhost:8080/v1/ctThhlastikaPressThreats/search?search=descriptionEn~hello\nworld;@&size=10&page=0&sort=Asc

As you can see I am using a path variable called search (which has all the properties filtering requested from the user) along with 3 more for the size, page and sort.

For the search variable I am using 3 distinct characters to implement OR and AND Predicates for the final query. Those are the ~ which is to know that the property before it needs to use an equal predicate, the ; which is to be able to have multiple values for each property requested by the user and finally the @ which triggers the end of this property's filtering.

Those 3 characters are being Regexed in two places. In the Controller and in the SearchRepository of (for example since we specifically started with this one -> the CtThhlastasikaPressThreats)

Finally in the SearchRepository you can see that I am triggering 2 queries, one is to get the filtered data from the db and another is to get the count of the data for Pagination purposes while also in the end mapping to a custom DTO.

Steps to reproduce :

After generating the db, change for the 2 Regexes of CtThhlastikaPressThreats. For the Controller to be (\w+?)(~|<|>)([(!-/.\\\\R 0-9\p{L});]+)?@ and for the SearchRepository to be ([(!-/.\\\\R 0-9\p{L})]+).

Then you can use the example of the request I have above when having in the db saved for the specific table and for column descriptionEn with value of hello\nworld for example or whatever value you put (also change it into the request).

Final thing I tried but wasn't the solution :

Put in the CtThhlastikaPressThreatsSearchRepository in the method search (it is after line 61) above the :

predicate = builder.equal(root.get(param.getKey()), match.toString());

Make it as :

match = match.toString().replace("\\n", System.lineSeparator()); predicate = builder.equal(root.get(param.getKey()), match.toString());

This will basically change the value from being hellow\nworld to become hello\r\nworld so I guess it still isn't the desired solution. Thinking that in the db it is stored as \n for the lineSeparators.

Now in the logs you can see that when you trigger the Get Request again the VARCHAR value of descriptionEn is indeed now with line separations instead of the \n (which still should be recognized by MySQL) text.

Final thoughts

I believe since even this

select * from kerkinidb.ct_thhlastika_press_threats where description_en = 'hello\nworld';

works in MySQL Workbench, that something in between might be ruining the request when trying to also include newLine char or lineSeparators.

If there is any idea of why it doesn't work as intended please share to try it out.

Thank you for your time

Answer 1

I got it to work. Probably not the answer you wanted but, if you replace your incoming "\n"s with the system line separator, it will get you want you want.

 search = search.replace("\\n", System.getProperty("line.separator"));
 try {
    return ctThhlastikaPressThreatsService.searchCtThhlastikaPressThreats(producedSearchCriterias(search), size, page, sort);

If you look at the params value logging, you'll see the param is now

extracted value ([col_2_0_] : [VARCHAR]) - [hello
world]

instead of

extracted value ([col_2_0_] : [VARCHAR]) - [hello\nworld]

It's like I suspected, somewhere in search pipeline, it's escaping the "\n". I'm not suggesting you do what I suggest right there on the controller, I just wanted to show you that this is what needs to happen (somewhere) for it to work.