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.

What is the database and what connection string are you using? — Pascal Thivent, Jun 14 '10 at 20:47
I'm using MySQL and the connection string is: `jdbc:mysql://localhost:3306/administer` — Daniel Szalay, Jun 14 '10 at 21:10
A link for those who are looking to use utf8mb4: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-charsets.html — Daniel Szalay, Oct 15 '14 at 08:28

score 53 · Accepted Answer · answered Jun 15 '10 at 05:07

53

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

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

answered Jun 15 '10 at 05:07

Bozho

588,226
146
1,060
1,140

4

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` – Daniel Szalay Aug 22 '14 at 21:02
For newer versions of jdbc driver (>= 5.1.13), do not specify "characterEncoding". Have a look: http://stackoverflow.com/a/18059663/967062 – Nitin Bansal Feb 07 '17 at 08:40

Daniel Szalay · Answer 2 · 2014-07-30T07:23:46.040

12

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

edited Jul 30 '14 at 07:23

answered Jun 14 '10 at 21:23

Daniel Szalay

4,041
12
57
103

score 5 · Answer 3 · answered Mar 18 '16 at 09:11

5

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

answered Mar 18 '16 at 09:11

fjjiaboming

191
1
3
11

score 0 · Answer 4 · answered Dec 29 '15 at 15:40

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&amp;amp;characterEncoding=UTF-8

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

score 0 · Answer 5 · answered Jun 08 '22 at 09:58

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.

JDBC character encoding

5 Answers5

Linked