JDBC character encoding

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 i开发者_开发技巧ntegrity; '?' 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.

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

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

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

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

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.

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.