title: mysql select in java on a column with accented utf8 string
body: <p>I have encountered a problem when trying to select data from a table in MySQL in Java by a text column that is in utf-8. The interesting thing is that with code in Python it works well, in Java it doesn't.</p>
  
  <p>The table looks as follows:</p>
  
  <pre><code>CREATE TABLE `x` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `text` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  </code></pre>
  
  <p>The query looks like this:</p>
  
  <pre><code>SELECT * FROM x WHERE text = 'ěščřž'"
  </code></pre>
  
  <p>The Java code that doesn't work as exptected is the following:</p>
  
  <pre><code>public class test {
      public static void main(String [] args) {
          java.sql.Connection conn = null;
          System.out.println("SQL Test");
          try {
              Class.forName("com.mysql.jdbc.Driver").newInstance();
              conn = java.sql.DriverManager.getConnection(
                      "jdbc:mysql://127.0.0.1/x?user=root&amp;password=root&amp;characterSet=utf8&amp;useUnicode=true&amp;characterEncoding=utf-8&amp;characterSetResults=utf8");
          } catch (Exception e) {
              System.out.println(e);
              System.exit(0);
          }
  
          System.out.println("Connection established");
  
          try {
              java.sql.Statement s = conn.createStatement();
              java.sql.ResultSet r = s.executeQuery("SELECT * FROM x WHERE text = 'ěščřž'");
              while(r.next()) {
                  System.out.println (
                          r.getString("id") + " " +
                          r.getString("text")
                  );
              }
          } catch (Exception e) {
              System.out.println(e);
              System.exit(0);
          }
      }
  }
  </code></pre>
  
  <p>The Python code is:</p>
  
  <pre><code># encoding: utf8
  
  import MySQLdb
  
  conn = MySQLdb.connect (host = "127.0.0.1",
                          port = 3307,
                          user = "root",
                          passwd = "root",
                          db = "x")
  cursor = conn.cursor ()
  cursor.execute ("SELECT * FROM x where text = 'ěščřž'")
  row = cursor.fetchone ()
  print row
  cursor.close ()
  conn.close ()
  </code></pre>
  
  <p>Both are stored on the filesystem in utf8 encoding (checked with hexedit). I have tried different versions of mysql-connector (currently using 5.1.15). Mysqld is 5.1.54.</p>
  
  <p>Mysqld log for the Java code and Python code respectively:</p>
  
  <pre><code>110427 12:45:07     1 Connect   root@localhost on x
  110427 12:45:08     1 Query     /* mysql-connector-java-5.1.15 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
                      1 Query     /* mysql-connector-java-5.1.15 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
                      1 Query     SHOW COLLATION
                      1 Query     SET autocommit=1
                      1 Query     SET sql_mode='STRICT_TRANS_TABLES'
                      1 Query     SELECT * FROM x WHERE text = 'ěščřž'
  110427 12:45:22     2 Connect   root@localhost on x
                      2 Query     set autocommit=0
                      2 Query     SELECT * FROM x where text = 'ěščřž'
                      2 Quit      
  </code></pre>
  
  <p>Does anybody have any suggestions what might be the cause why the Python code works and why the Java code does not? (by not working I mean not finding the desired data -- the connection works fine)</p>
  
  <p>Many thanks.</p>