CODEWITHSUNDEEP
javamysqlweb-servicesglassfish-3cjk
user273565
user273565

Reputation: 215

Java Web Service under Glassfish 3.0.1 not inserting Chinese characters into MySQL database

I created a web service using NetBeans 6.9.1 running on the Mac OS under Glassfish 3.0.1 app server. It's method takes a String arg of Chinese characters. It inserts the String into a MySQL database table. When stepping through the server code in debug mode, the characters arrive correctly. A debugger watch shows the query parameter below having the Chinese characters.

Query query = entityManager.createNativeQuery(QueryInsertWord);
query.setParameter(1, word);
query.executeUpdate();

HOWEVER, upon insert, the database table column has ??? instead of the Chinese characters. I assume the MySQL database is UTF-8 compliant because I can successfully manually cut and paste Chinese characters into that very same column using the Navicat database management tool.

The PU looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" 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_2_0.xsd">
  <persistence-unit name="MyPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>javaapplication1.Registration</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/myDatabase"/>
      <property name="javax.persistence.jdbc.password" value=""/>
      <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
      <property name="javax.persistence.jdbc.user" value="root"/>
    </properties>
  </persistence-unit>
</persistence>

I configured ALL Glassfish jdbc connection pools with the additional properties useUnicode=true and characterEncoding=utf8.

Please help me.

Upvotes: 1

Views: 1221

Answers (3)

Raugaral
Raugaral

Reputation: 1323

Try with this, Append the query string to jdbc url

&characterEncoding=utf8&characterSetResults=utf8

At the end should show as this:

<property name="javax.persistence.jdbc.url" value="jdbc:mysql://127.0.0.1:3306/DATABASE_NAME?zeroDateTimeBehavior=convertToNull&amp;characterEncoding=utf8&amp;characterSetResults=utf8"/>

Upvotes: 0

user273565
user273565

Reputation: 215

The problem is solved. The character encoding of the web service is indeed UTF-8. The MySQL database is also UTF-8. The problem SEEMS to have been in the persistence unit which uses the Eclipse JPA 2.0 library. I suspect this because when I composed the native query with the parameter in question as a byte[] as opposed to a string, the correct characters were persisted in the database. In summary, I now pass the expression "data.getBytes(Charset.forName("UTF-8"))" to the query parameter object, as opposed to the string "data". Since the data is indeed in UTF-8, the getBytes call interprets the byte array correctly. The overloaded parameter object passes the byte array to the entity manager and its persistence library before it finally reaches the MySQL database. The MySQL database is UTF-8 with a column of type LongText. The bytes are then inserted correctly into that column. Please feel free to comment further if anyone feels that I am assuming anything incorrectly. I needed to move on with the project and, therefore, am not delving into the matter further at this particular time. Thank you all for your help, especially Bozho who put me looking in the right direction.

Upvotes: 0

Bozho
Bozho

Reputation: 597204

The problem can be in at least two places:

  • your connection is not using utf-8. Try passing characterEncoding=utf8 with the connection string
  • your web service is not accepting UTF-8 (for what to do here you have to provide more information)

Also make sure your database is utf_general_ci (or similar)

Upvotes: 0

Related Questions