如何通过JDBC接口将UTF-8字符串正确写入MySQL [英] How to correctly write UTF-8 strings into MySQL through JDBC interface
问题描述
连接数据库:
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();
}
表创建代码:
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);
}
行插入:
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();
}
检查数据:
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);
}
}
输出:
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.
期望的字符串是:Апарати
结果:???????
这是某种魔法?
我解决了一个问题......但如果有人能向我解释它仍然会很感激.
I resolved an issue... But still will appreciate if somebody can explain it to me.
所以.
- 我添加了由 Costis Aivalis 建议的/etc/mysql/my.cnf 行
结果一样 - 我从代码中删除了几行:
query = "set character set utf8";
stat.execute(query);
它正在工作!!!:)
推荐答案
确保您的 MySQL 配置编码被正确定义.使用以下命令检查您的设置和修改的正确性:
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%';
和 显示变量,如 'collation%';
将这些行添加到 my.cnf 或 my.ini:
Add these lines to either my.cnf or my.ini:
对于 MySQL 5.1.nn 和更高版本 5.5.29,您只需要这两行:
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
对于 MySQL 5.0.nn 及更早版本,请使用以下设置:
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
使用 MySQL-Workbench 进行设置可能更方便.5+ 版本非常好.
It is probably more convenient to use MySQL-Workbench for your settings. Versions 5+ are excellent.
在你的 Java 程序中像这样连接:
In your Java program connect like this:
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDatabase?useUnicode=true&characterEncoding=UTF-8","user","passwd");
这篇关于如何通过JDBC接口将UTF-8字符串正确写入MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!