Java + Mysql UTF8 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3275524/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Java + Mysql UTF8 Problem
提问by Marcx
as the title said, I have a problem between java and mysql
正如标题所说，我在java和mysql之间有问题
The mysql DB, tables, and columns are utf8_unicode_ci. I have an application that took some input from an xml, then compose the query...
mysql 数据库、表和列是 utf8_unicode_ci。我有一个应用程序从 xml 中获取一些输入，然后编写查询...
public String [] saveField(String xmltag, String lang){
NodeList nodo = this.doc.getElementsByTagName(xmltag);
String [] pos = new String[nodo.getLength()];
for (int i = 0 ; i < nodo.getLength() ; i++ ) {
Node child = nodo.item(i);
pos[i] = "INSERT INTO table (id, lang, value) VALUES (" +
child.getAttributes().getNamedItem("id").getNodeValue().toString() + " , " +
lang + " , " +
"'" + child.getFirstChild().getTextContent() + "'" +
");";
}
return pos;
}
this method return an array of String that contains one or more SQL insert Query... then
此方法返回一个包含一个或多个 SQL 插入查询的字符串数组...然后
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql:///dbname", "user", "pass");
.....
Statement s; s =
this.con.createStatement ();
s.execute(query);
both with s.execyteand s.executeUpdatethe special characters are stored as ?
withs.execyte和s.executeUpdate特殊字符都存储为 ?
so special char are not stored correctly:
?????? ?????is stored as ?????????
所以特殊字符没有正确存储：
?????? ?????存储为?????????
Hi!is stored as Hi!
Hi!存储为 Hi!
Any advice?
有什么建议吗？
Thanks
谢谢
采纳答案by Marcx
Solved, I forgot to add the encoding when initializing Connection:
解决了，我在初始化Connection时忘记添加编码了：
before was:
之前是：
con = DriverManager.getConnection("jdbc:mysql:///dbname", "user", "pass");
con = DriverManager.getConnection("jdbc:mysql:///dbname", "user", "pass");
now (working):
工作中）：
con = DriverManager.getConnection("jdbc:mysql:///dbname?useUnicode=true&characterEncoding=utf-8", "user", "pass");
con = DriverManager.getConnection("jdbc:mysql:///dbname?useUnicode=true&characterEncoding=utf-8", "user", "pass");
回答by Daniel Martin
AUGH!
啊！
Okay, so, this isn't directlythe thing you asked for, but this:
好的，所以，这不是您要求的直接内容，而是：
pos[i] = "INSERT INTO table (id, lang, value) VALUES (" +
child.getAttributes().getNamedItem("id").getNodeValue().toString() + " , " +
lang + " , " +
"'" + child.getFirstChild().getTextContent() + "'" +
");";
Set off all my internal "DON'T DO THIS" alarms.
触发我所有的内部“不要这样做”警报。
Do you have absolute and complete control over the incoming text? Are you sure someone won't have an apostrophe in the incoming text, even by accident?
你对传入的文本有绝对和完全的控制吗？你确定有人在传入的文本中不会有撇号，即使是偶然的？
Instead of creating SQL text, please refactor your code so that you end up calling:
请不要创建 SQL 文本，而是重构您的代码，以便您最终调用：
PreparedStatement pstmt =
con.prepareStatement("INSERT INTO table (id, lang, value) VALUES (?,?,?)");
// then, in a loop:
pstmt.setString(0, child.getAttributes().getNamedItem("id").getNodeValue().toString());
pstmt.setString(1, lang);
pstmt.setString(2, child.getFirstChild().getTextContent());
pstmt.execute();
That is, let the DB escape the text. Please, unless someday you want to have a conversation like this one. As an advantageous side effect, this approach maysolve your problem, assuming that the string values are still correct when you read them from the XML. (As someone else mentioned, it's very possible that things are getting messed up when you read from the XML)
也就是说，让 DB 转义文本。请，除非有一天你想有一个像交谈这一个。作为一个有利的副作用，这种方法可以解决您的问题，假设您从 XML 中读取字符串值时仍然正确。（正如其他人提到的，当您从 XML 读取时，事情很可能会变得一团糟）
