Java - Storing UTF-8 characters (specifically Russian characters) in mysql database using PreparedStatement

Question

Using a prepared statement I am trying to store Russian characters in a field on a particular tables. The table and field is using the utf8 character set and utf8_bin collation.

If I manually run an insert via an IDE or command line, the string saves as expected.

INSERT INTO utf8Table VALUES('Анатолий Солоницын');

I am then able to query that table and can get back the expected String as well.

The Connection is setup through a resource in tomcat's context.xml file with the following config:

<Resource 
    name="jdbc/DoolliDB"  
        auth="Container"   
        type="javax.sql.DataSource"   
        driverClassName="com.mysql.jdbc.Driver"
        useUnicode="true"
        characterEncoding="UTF8"
        ....
</Resource>

As I said, I am able to read the string/characters just fine, so I assume that both the table and the connection settings are set properly.

I am using a PreparedStatement/CallableStatement in the following way:

CallableStatement callableStmt = __mySqlConnector.getConnection().prepareCall("INSERT INTO utf8Table VALUES(?)");

callableStmt.setString(1, "Анатолий Солоницын");

callableStmt.executeUpdate();

Instead of Анатолий Солоницын, what is getting inserted into the database is: ???????? ?????????

Also note that "normal" utf-8 strings, such as über, are getting saved properly.

Both

System.getProperty("file.encoding") 

and

java.nio.charset.Charset.defaultCharset().name() 

are also both returning UTF-8.

Any help is appreciated. Thanks

Answer 1

You need to define the connection encoding in the database url, not in the attribute.

<Resource 
    name="jdbc/DoolliDB"  
        auth="Container"   
        type="javax.sql.DataSource"   
        driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/dbName?characterEncoding=UTF-8"
        ...
</Resource>

Please see http://dev.mysql.com/doc/refman/4.1/en/connector-j-reference-charsets.html

Warning Do not issue the query 'set names' with Connector/J, as the driver will not detect that the character set has changed, and will continue to use the character set detected during the initial connection setup.

Answer 2

There are 3 sql statement I use in PHP, maybe they will help here:

• set character_set_results=utf8
• set character_set_connection=utf8
• set character_set_client=utf8

For example:

CallableStatement callableStmt = __mySqlConnector.getConnection().prepareCall("set character_set_results=utf8");