CODEWITHSUNDEEP
mysqlhibernatejpaquerydsl
Xelian
Xelian

Reputation: 17228

Can't persist UTF-8 Symbols using MySQL, JPA, Hibernate

there a lot of simillar questions but non of them solve my problem. I use JPA with hibernate and want to save UTF-8 characters.

My maven dependencies are:

       <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.0.Final</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>5.1.1.Final</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>4.3.6.Final</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
        </dependency>

       <dependency>
            <groupId>com.mysema.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>3.6.0</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.mysema.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>3.6.0</version>
        </dependency>

So I use JPA 2.1 and QueryDSL 3.6.0. My persistance.xml is:

<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/persistence


http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">


<persistence-unit name="thePersistenceUnit" transaction-type="RESOURCE_LOCAL">
        <properties>

            <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
            <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/newsDB?useUnicode=true&amp;characterEncoding=UTF-8"/>
            <property name="javax.persistence.jdbc.user" value="root"/>
            <property name="javax.persistence.jdbc.password" value="root"/>

            <!--Hibernate properties-->
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="false"/>
            <property name="hibernate.hbm2ddl.auto" value="create"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>

        </properties>
    </persistence-unit>
</persistence>

Then I use XAMPP MySQL and Apache enter image description here enter image description here

And add

port= 3306
[client]
default-character-set = utf8

[mysqld]
init-connect='SET NAMES utf8'
character-set-server = utf8
collation-server = utf8_general_ci

[mysql]
default-character-set = utf8

My DB Collation is: enter image description here

And my table: enter image description here

When I turn on the logger I saw:

TRACE [main] (BasicBinder.java:81) - binding parameter [1] as [VARCHAR] - [Xelian]
TRACE [main] (BasicBinder.java:81) - binding parameter [2] as [VARCHAR] - [Описание]
TRACE [main] (BasicBinder.java:81) - binding parameter [3] as [CLOB] - [Content of the text .Чирипаха.]
TRACE [main] (BasicBinder.java:81) - binding parameter [4] as [BIGINT] - [112]
TRACE [main] (BasicBinder.java:81) - binding parameter [5] as [TIMESTAMP] - [Thu Jan 22 19:44:59 EET 2015]
TRACE [main] (BasicBinder.java:81) - binding parameter [6] as [VARCHAR] - [Title]
TRACE [main] (BasicBinder.java:81) - binding parameter [7] as [TIMESTAMP] - [Thu Jan 22 19:44:59 EET 2015]
TRACE [main] (BasicBinder.java:81) - binding parameter [8] as [BIGINT] - [0]

You can see that the values are strange Чирипаха.. And throught phpmyadmin I see: enter image description here

But I when I edit it manually enter image description here

You can see that my DB shows Cyrilic sumbols properlly. So I am wondering where is the problem.

Upvotes: 1

Views: 3891

Answers (2)

Anton P
Anton P

Reputation: 11

I think you have solved this problem but my advice might be useful to other users.

I had a similar problem. I tried to use many of the recommendations from stackoverflow but to not avail. The problem was in the encoding of my Project in my IDE(Intellij IDEA). My Project has windows-1251 encoding and my database has utf8 encoding. You need to change your project encoding in according to your database. In Intellij : Settings->Editor->File Encodings->Project Encoding.

Upvotes: 1

Xelian
Xelian

Reputation: 17228

So. I uninstall XAMPP an isntal MySQL Server on my machine. Then run MySQL command Line Client :

show variables like "%character%";show variables like "%collation%";

enter image description here

And everything seems to be OK. utf-8 every where. Then results in the DB was not encoded properlly, still strange symbols were there. When I edit them Cyrillic was inserted ok. SO I suspect the Hibernate persistance.xml. I edit it and put:

<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
 http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">


<persistence-unit name="thePersistenceUnit" transaction-type="RESOURCE_LOCAL">

    <properties>
        <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/dnesdb?useUnicode=true&amp;characterEncoding=UTF-8" />
        <property name="javax.persistence.jdbc.user" value="root" />
        <property name="javax.persistence.jdbc.password" value="root" />

        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
        <property name="hibernate.show_sql" value="true" />
        <property name="hibernate.hbm2ddl.auto" value="update" />

        <property name="hibernate.connection.characterEncoding" value="utf8"/>
        <property name="hibernate.connection.useUnicode" value="true"/>
        <property name="hibernate.connection.charSet" value="UTF-8"/>
    </properties>
</persistence-unit>
</persistence>

And now UTF-8 (Cyrillic) is persisted OK.

!!! And do not forget BEFORE creating the DB to set java property: -Dfile.encoding=UTF-8 If the tables is created and after that you set -Dfile.encoding=UTF-8, the problem will stay. So drop the table set the java encoding property, then recreate table again.

Upvotes: 1

Related Questions