How to correctly write UTF-8 strings into MySQL through JDBC interface

Question

Connect to db:

public DBSource(ConnectionInfo ci) throws
        ClassNotFoundException, InstantiationException,
        IllegalAccessException, SQLException
{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String dbPath = String.format(
        "jdbc:mysql://%s:%d/%s?user=%s&password=%s&characterEncoding=utf-8&" + 
        "useUnicode=true", ci.host, ci.port, ci.dbName, ci.user, ci.password);
    conn = java.sql.DriverManager.getConnection(dbPath);
    prepareTables();
}

Table creation code:

private void prepareTables() throws SQLException
{
    java.sql.Statement stat = conn.createStatement();

    String query = "set names utf8";
    stat.execute(query);

    query = "set character set utf8";
    stat.execute(query);

    query = "show variables like '%char%'";
    stat.execute(query);
    java.sql.ResultSet rs = stat.getResultSet();
    while (rs.next())
    {
        String k = rs.getString(1);
        String v = rs.getString(2);
        System.out.println(k + " - " + v);
    }
    
    query = "drop table if exists clt";
    stat.execute(query);
    query = "create table clt"
            + "("
            + "  id bigint not null"
            + ", text varchar(50) not null"
            + ") default character set utf8";
    stat.execute(query);
}

rows insertion:

public void visit(Insert i) throws SQLException
{
    String query = "insert into clt"
            + " (id, text) values (?, ?)";
    java.sql.PreparedStatement stmt = conn.prepareStatement(query);
    if (i.rowData.id == 12656697)
    {
        String toOut = "<<< " + Long.toString(i.rowData.id) + " - " + i.rowData.text;
        System.out.println(toOut);
    }
    int it = 0;
    stmt.setLong(++it, i.rowData.id);
    stmt.setString(++it, i.rowData.text);
    stmt.execute();
    stmt.close();
}

check data:

 public void checkText() throws SQLException
{
    java.sql.Statement stmt = conn.createStatement();
    String query = "select id, text from clt where id = '12656697'";
    stmt.execute(query);
    java.sql.ResultSet rs = stmt.getResultSet();
    while (rs.next())
    {
        String k = rs.getString(1);
        String v = rs.getString(2);
        String toOut = ">>> " + k + " - " + v;
        System.out.println(toOut);
    }
}

output:

character_set_client - utf8
character_set_connection - latin1
character_set_database - latin1
character_set_filesystem - binary
character_set_results - utf8
character_set_server - utf8
character_set_system - utf8
character_sets_dir - /usr/share/mysql/charsets/

<<< 12656697 - Апарати
>>> 12656697 - ???????

Problem: In table I have "???????????" symbols at the text field.

expected string is: Апарати
result: ???????

It is some kind of magic?

I resolved an issue... But still will appreciate if somebody can explain it to me.

So.

I added to my /etc/mysql/my.cnf lines suggested by Costis Aivalis
result the same
I removed lines from my code:
query = "set character set utf8";
stat.execute(query);

it is working!!! :)

stackoverflow.com/questions/14456313/…

ervishal
– ervishal

2017-04-13 10:27:16 +00:00
Commented Apr 13, 2017 at 10:27 — ervishal
– ervishal, Commented Apr 13, 2017 at 10:27

Costis Aivalis · Accepted Answer · 2013-03-30 17:14:50Z

30

Ensure that your MySQL configuration encoding is defined correctly. Check your settings and the correctness of the modifications with these commands:

show variables like 'character%';

and show variables like 'collation%';

Add these lines to either my.cnf or my.ini:

For MySQL 5.1.nn, and later versions 5.5.29 you just need these two lines:

[mysqld]
character-set-server = utf8
character-set-filesystem = utf8

For MySQL 5.0.nn and older use these settings:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8

It is probably more convenient to use MySQL-Workbench for your settings. Versions 5+ are excellent.

enter image description here

In your Java program connect like this:

con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");

edited Mar 30, 2013 at 17:14

answered Mar 23, 2011 at 12:46

Costis Aivalis

13.8k3 gold badges49 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

denys Over a year ago

I do not have [mysql] group at my my.cnf

denys Over a year ago

I added [mysql] group also... did not help :(

Costis Aivalis Over a year ago

What MySQL version and OS do you have?

denys Over a year ago

mysql-connector-java 5.1.14, mysql server version: 5.1.49

Costis Aivalis Over a year ago

Great! My settings were from MySQL version 5.0.51.

|

try-catch-finally · Accepted Answer · 2016-12-20 09:13:58Z

9

If you get ????????? add the parameter characterEncoding=utf-8 instead of useUnicode=true. See the following examples.

For english text:

String url =  "jdbc:mysql://localhost:" + port + "/DBName?useUnicode=true&...";

For text in russian laguage:

String url = "jdbc:mysql://localhost:" + port + "/DBName&characterEncoding=utf-8&...";

edited Dec 20, 2016 at 9:13

try-catch-finally

7,6606 gold badges49 silver badges71 bronze badges

answered Dec 20, 2016 at 8:50

User

1012 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to correctly write UTF-8 strings into MySQL through JDBC interface

2 Answers 2

11 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related