How to correctly insert utf-8 characters into a MySQL table using jpa

Question

I want to insert in MySQL database information by my web application. I do it by OpenJPA at TomEE server for example:

public void addLecturer(String name, String surname) {
    Lecturer lect = new Lecturer(name,surname);

    em.persist(lect);
}

where Lecturer is entity class. The problem is that application insert data in latin1 encoding. I want to insert it in UTF-8 encoding.

When I insert data by simple MySQL command in command line terminal i get information in UTF-8 encoding.

I have looked at this and I see character_set_server is set as latin1 but I don't know if this is the problem or how to change it:

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Edit

I have same problem like there:

JPA utf-8 characters not persisted

and

UTF - 8 with JPA and Glassfish 4.0

I add useUnicode=yes, characterEncoding=utf8 and characterSetResults=utf8 to my property javax.persistence.jdbc.url but it doesn't help.

score 7 · Answer 1 · answered Dec 21 '13 at 16:44

Ok, I find resolve:

I get data by JTA so I get the data for persistence.xml from datasource defined in tomee.xml:

<?xml version="1.0" encoding="UTF-8"?>
<tomee>
  <Resource id="database" type="DataSource">
    JdbcDriver com.mysql.jdbc.Driver
    JdbcUrl jdbc:mysql://localhost:11080/db?zeroDateTimeBehavior=convertToNull&amp;characterEncoding=utf8&amp;characterSetResults=utf8
    UserName my
    Password pass
  </Resource>
</tomee>

So I had to add &characterEncoding=utf8&characterSetResults=utf8 to JdbcUrl parametr in tomee.xml file (note that to valid xml I had to use & instead of &). I don't have to add this informatation to persistence.xml (only because i use JTA).

Now everything works great. Thank's to arievanwi for direct me to resolve of problem.

score 6 · Accepted Answer · answered Aug 06 '14 at 12:01

6

2 Steps

Append the query string to jdbc url

?characterEncoding=utf-8

Run the following sql command on the table

ALTER TABLE <tablename> CONVERT TO CHARACTER SET utf8 COLLATE
utf8_unicode_ci;

answered Aug 06 '14 at 12:01

Sorter

9,704
6
64
74

score 2 · Answer 3 · edited May 23 '17 at 12:15

2

I don't think this has anything to do with your application, container, etc. unless of course the values for name and surname in your example are already wrong (which I don't assume). Therefore, it is indeed probably due to the encoding setting. Did you check the information provided in this post?

edited May 23 '17 at 12:15

Community

1
1

answered Dec 19 '13 at 21:35

Arie van Wijngaarden

1,156
6
7

I checked it, but i don't have access to my.cfn file. I want to change encoding only for my database. it's very strange that I can enter national characters by console or mysql workbench and everything is ok, but I can not do this by my application – kuba44 Dec 20 '13 at 11:12
1

And did you check this? http://stackoverflow.com/questions/18163328/jpa-utf-8-characters-not-persisted – Arie van Wijngaarden Dec 20 '13 at 11:52
1

Furthermore, I would check what is actually present in `name` and `surname` before the `persist()` action. It may still be a binding issue from your front-end to the values passed to `addLecturer()`. – Arie van Wijngaarden Dec 20 '13 at 12:20
i have same problem, i try to add `useUnicode=yes&characterEncoding=UTF-8` but it doesn't help. I debug my app and check that before `persist()` `name` and `surname` have strings with utf-8 char – kuba44 Dec 20 '13 at 12:33

score 0 · Answer 4 · answered Dec 19 '13 at 14:16

0

Is yours a Web Application??? If so check your application server is configured to handle UTF-8 request. In tomcat, for instance, may be done in the server.xml in the Connector section.

answered Dec 19 '13 at 14:16

angelodelia

69
7

I use Tomcat (TomEE) and in server.xml i have only `` – kuba44 Dec 19 '13 at 14:28
So you have to add the URIEncoding attribute like this: [code][code] – angelodelia Dec 19 '13 at 14:44
it doesn't help, i add URIEncoding attribute, but i get information in latin1 (i thing so) :( – kuba44 Dec 19 '13 at 15:11

score 0 · Answer 5 · answered Oct 07 '17 at 10:11

In order to implement unicode correctly and support many different languages, you need 2 configures:

Mysql table or database schema should support utf8
- While db creation use below:
CREATE SCHEMA mytestdb DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
- OR alter table to some thing like:
ALTER TABLE myTableName CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Now you need correct JDBC url and specify utf-8 character set, remember to escape any special characters, as below: spring.datasource.url=jdbc\:mysql\://localhost\:3306/${SERVER_DB_NAME}\?useUnicode=true\&characterEncoding=utf\-8\&characterSetResults=utf\-8

This is how I implemented it and worked for me.

How to correctly insert utf-8 characters into a MySQL table using jpa

5 Answers5

2 Steps