How to correctly write UTF-8 strings into MySQL through JDBC interface
Solution 1
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.
In your Java program connect like this:
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");
Solution 2
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&...";
denys
Updated on June 12, 2021Comments
-
denys almost 3 years
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!!! :)
- I added to my /etc/mysql/my.cnf lines suggested by Costis Aivalis
-
denys about 13 yearsI do not have [mysql] group at my my.cnf
-
denys about 13 yearsI added [mysql] group also... did not help :(
-
Costis Aivalis about 13 yearsWhat MySQL version and OS do you have?
-
denys about 13 yearsmysql-connector-java 5.1.14, mysql server version: 5.1.49
-
Costis Aivalis about 13 yearsGreat! My settings were from MySQL version 5.0.51.
-
sinoohe about 11 yearsmysql won't start if I add these configs
-
Costis Aivalis about 11 yearsIt depends on the MySQL version you are using. These settings were fine for 5.0.51. There has been a change for later versions. Thank you for pointing that out.
-
hqt over 8 yearsthanks :) last option work for me. (change database connection). Hope anyone meets problem should try this option first :)
-
Stijn de Witt almost 8 years
-
Costis Aivalis almost 8 years@Stijn de Witt: Fair enough, if you need to store characters beyond the Unicode Basic Multilingual Plane.
-
Seiya almost 7 yearsIn my case it is working correctly with just changing the mysql configuration (v5.7). No need to change jdbc url.