JDBC character encoding

Question

I have a Java Web application running on GlassFish 3 and JPA (EclipseLink) on MySQL. The problem I'm facing is that if I'm saving entities to the database with the update() method, String fields lose integrity; '?' is shown instead of some characters.

The server, pages and database are configured to use UTF-8.

After I post form data, the next page shows the data correctly. Furthermore it "seems" in NetBeans debug that the String property of the current entity stores the correct value too. I don't know if NetBeans debug can be trusted; might be that it decodes correctly, however it's incorrect.

Pat

What is the database and what connection string are you using? – Dialogize

I'm using MySQL and the connection string is: jdbc:mysql://localhost:3306/administer – Pat

A link for those who are looking to use utf8mb4: dev.mysql.com/doc/connector-j/en/… – Pat

Answer 1

It's JDBC, not JPA that determines the encoding:

jdbc:mysql://localhost:3306/administer?characterEncoding=utf8

Transmittance

Just a sidenote for those who come across the same problem: if you want to chain parameters in the JDBC url in your persistence.xml, you have to rewrite & as &. Example: jdbc:mysql://localhost:3306/administer?rewriteBatchedStatements=true&characterEncoding=UTF-8 – Pat

For newer versions of jdbc driver (>= 5.1.13), do not specify "characterEncoding". Have a look: https://mcmap.net/q/535698/-jdbc-url-for-mysql-configuration-to-use-utf8-character-encoding – Tempting

Answer 2

I solved it with the following: I used the GlassFish admin interface to add this property to my connection pool's settings:

characterEncoding = UTF-8

Pat

Answer 3

New version JDBC driver auto detect the characterEncoding. You do not need to set it explicitly.

Edulcorate

How new needed? – Yocum

Answer 4

I had to add useUnicode=true as well, so I had to concat the parameters with '&' so it looks like this:

jdbc:mysql://127.0.0.1:3306/warranteer?useUnicode=true&characterEncoding=UTF-8

If you're using maven profiles to set the MySQL URL, like me, make sure you put & instead & because maven unescape the url when writing the persistence.xml file to classes folder.

Bathesda

Answer 5

I faced a similar issue where a special character was getting translated to ? on staging environment but same was working on production environment. These are the MySQL version details:

Prod: 5.5.62
Staging: 5.7.25

I checked the collations and charset, there were differences and eventually aligned all of them on staging with same collation and charset values. For reference used the following commands to compare and update:

SHOW CREATE TABLE dbname.tablename;
select*from information_schema.schemata;
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;    
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tablename CHARACTER SET utf8, COLLATE utf8_general_ci;

There were jdk version mismatches on both the environment, however, the logging was able to read the special characters, hence we identified that either issue is with hibernate flushing or MySQL. But after syncing MySQL, it was ruled out from the probable root causes. So we changed the jdbc-url paramter with characterEncoding=utf8 and it worked.

Forsta