1

I've got a database with names in it. These names sometimes contain non ascii characters e.g. González. I've got the collation settings such that if I search for WHERE LastName LIKE '%Gonzalez%' I get González's record back. In Management Studio I can search for both WHERE LastName LIKE '%Gonzalez%' and WHERE LastName LIKE '%González%' and both return the correct value. However when I use JPA / Hibernate the query that gets sent to the database clearly doesn't represent the á character correctly as I get 0 results.

When utilising the show_sql attribute I can see the actual query is fine, and If I copy and paste that query and replace the ? characters with '%González%' I get the correct results. Likewise if I search for Gonzalez through the web interface I get results, so I'm confident it's the á that is causing me problems, and it's only JPA / Hibernate that is causing the issue. (Having said that the issue could be the AJAX submission to the servlet that is causing the issue, but the parameter is sent as ?LastName=Gonz%C3%A1lez which I think is right?)

So if it's JPA / Hibernate how do I diagnose / fix the issue?

Matt Fellows
  • 6,512
  • 4
  • 35
  • 57

1 Answers1

0

The show_sql logging configuration attribute only lets you see the formatted SQL statement, generated by Hibernate. To troubleshoot the problem further, you need to make sure the values, hibernate replaces the *'?'*s with, are actually correct. Look at the thread on how to see param values in hibernate log and adjust your application log settings.

The second step I'd suggest to add - is in your AJAX request, encode all your params as Base64 string, and then decode it back to UTF-8 string on the controller, handling the request.

The flow of logic should be as follow:

  1. Client receives input 'González'
  2. client encodes the input into 'R29uesOhbGV6' and passes it in AJAX request
  3. controller, handling the request, decodes the parameter back to 'González'
  4. controller passes the value down to hibernate logic, where hibernate generates SQL and executes it
  5. in the application log, you see that hibernate actually passes 'González' parameter down to the database
Community
  • 1
  • 1
Seego
  • 106
  • 6
  • Indeed it was (kind of) the AJAX that was the problem. It was actually JBoss that was not decoding in UTF-8 correctly - there was a setting which "fixed" it that needed to be set. If I hard coded the correct value in the servlet it worked fine... – Matt Fellows Apr 08 '14 at 11:04