MySQL and reading non-English characters in UTF-8

[R Young]

Hi all. The topic is pretty straightforward, but I've been staring at it for quite some time.

I'm trying to retrieve non-English characters from a MySQL database in UTF-8. In my example below, I use "à" (U+00E0: Latin Small Letter A With Grave) but I've also tried with random Japanese characters (Hex=E7A798). I'm new to java/jdbc, so I may be missing something basic, but I've searched for quite a while, and not discovered anything.

I've made sure that my database was created in UTF-8, and that my connection is in UTF8. I've tried enabling all jdbc connection string options (see commented line below) and it makes no difference. I've also tried System.setProperty("file.encoding", "UTF-8");.

The odd thing is that somehow the code below works when I run it from a JUnit test. (the actual results match the expected ones)
Any help would be appreciated. Thanks in advance.

SQL to create data

CREATE DATABASE bug_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;
USE bug_utf8;
DROP TABLE IF EXISTS simple_table;
CREATE TABLE simple_table (name	varchar(255) NOT NULL);
INSERT INTO simple_table (name) VALUES ('à');
SELECT Hex(name) from simple_table; -- returns C3A0

Java code

import java.sql.*;
public class Bug_Utf8 {
	public static void main(String[] s) throws SQLException, java.io.UnsupportedEncodingException {
		// init connection
		Connection conn = DriverManager.getConnection
			( "jdbc:mysql://localhost/bug_utf8?useUnicode=true&characterEncoding=UTF8"
//			( "jdbc:mysql://localhost/bug_utf8?useUnicode=true&characterEncoding=UTF8&character_set_client=UTF8&character_set_database=UTF8&character_set_results=UTF8&character_set_server=UTF8&character_set_system=UTF8"
			, "root"
			, "yourpassword"
		);		
		
		// retrieve resultSet
		Statement cmd = conn.createStatement();
		cmd.execute("select * from simple_table;");
		ResultSet rdr = cmd.getResultSet();
		
		// get value
		rdr.next();
		String name = rdr.getNString("name");

		// output results
		// actual
		System.out.println("name=" + name + " len=" + name.getBytes().length + " 0=" + name.getBytes()[0]);
		// prints: name=à len=1 0=-32

		// expected
		System.out.println("name=" + name + " len=" + name.getBytes("UTF8").length + " 0=" + name.getBytes("UTF8")[0] + " 1=" + name.getBytes("UTF8")[1]);
		// prints: name=à len=2 0=-61 1=-96 (-61 <=> C3; -96 <=> A0)
	}
}

Environment Details

OS: Windows XP SP3
MySQL: Server version: 5.1.40-community MySQL Community Server (GPL)
Java: java version "1.6.0_20"
JDBC: 5.1.12
JUnit: 4_4.5 v20090824
IDE: Eclipse 20090920-1017 (not that it should matter)

[Paul Clapham]

I don't understand why you think there's a problem. You get a string from the database and it contains the "à" character. If you convert it to bytes using your system's default encoding then you get 1 byte which is 224. This is in fact how "à" is encoded in ISO-8859-1 so it's quite likely that is your system's default encoding. And if you convert it to bytes using UTF-8 then you get 2 bytes which are the byte representation of "à" in UTF-8. This is all perfectly normal and nothing to do with JDBC at all.

[R Young]

Okay. Thanks for the quick reply.

I think I understand your answer. However, I think that "à" was not such a good example.
Let me change the example to a Japanese character.

INSERT INTO simple_table (name) VALUES (UnHex('E7A798'));
SELECT Hex(name) from simple_table; -- returns E7A798

When I run the code below, I get a string of Length=1 and value of 63. Instead I'm expecting a String of Length=3 with values E7 A7 98.
I'm assuming it's 63 because ISO-8859-1 doesn't know anything about the E7 A7 98 character and decodes its as 63.

I assumed that JDBC was responsible for this conversion. You mention that it is dependent on my system's default encoding. Is this a setting in the java vm? (I've tried -Dfile.encoding=utf-8)

In the end, I need to be able to read the string as E7 A7 98. The 63 loses data.

[R Young]

Okay. After thinking further, I see what you're saying. In order for me to get the UTF-8 data, I need to request the UTF-8 data explicitly: by doing name.getBytes("UTF8"). I can't rely on doing name.getBytes().

You're right. It has nothing to do with JDBC. Please feel free to move to appropriate forum.

Thanks again.

reason for mod1: clarified logic
reason for mod2: answered own question: Charset.defaultCharset()

A final question: Is there a way I can change the default system encoding to UTF-8? Or detect what the default system encoding is?. I'm planning to write code like below, but I wouldn't want to convert if the encoding is not ISO-8859-1.

One other detail: I'm using this string to pass to a JNA function, and it seems to care about whether or not the string it gets is UTF-8 or ISO-8859-1.
In other words, if I pass it a string where name.getBytes()[0] = -32 it fails. If name.getBytes()[0] = -61 it works.

		Charset utf8charset = Charset.forName("UTF-8");
		Charset iso88591charset = Charset.forName("ISO-8859-1");

		ByteBuffer inputBuffer = ByteBuffer.wrap(name.getBytes());

		// decode ISO-8559-1
		CharBuffer data = iso88591charset.decode(inputBuffer);

		// encode UTF-8
		ByteBuffer outputBuffer = utf8charset.encode(data);
		byte[] outputData = outputBuffer.array();
		name = new String(outputData, "UTF-8");