对具有重音字符的JDBC-ODBC Bridge查询失败 [英] JDBC-ODBC Bridge queries to Access fail when they have accented characters
问题描述
我通过JDBC-ODBC Bridge向Java发送查询到这样的Access数据库，如下所示：
I'm sending a query through the JDBC-ODBC Bridge to an Access database from Java, like this:
"SELECT * FROM localities WHERE locName='" + cityName + "'"
当cityName是普通字符串时没有重音字符，结果集是正确的。
但是当cityName恰好是LEÓN，SAHAGÚN，其中包含重音字符，然后我没有结果。在这些情况下，查询似乎失败了。
在MS Access中运行时相同的查询工作正常，我也尝试使用Ms Data Acces SKD并且这些查询工作正常。
When cityName is a normal string with no accented characters, the resultset is correct.
But when cityName happens to be something like LEÓN, SAHAGÚN, that is with accented characters in them, then I get no results. It seems like the query fails in these cases.
The same queries when run in MS Access work all right, I also tried with Ms Data Acces SKD and these queries work perfectly.
它们只在通过时失败通过JDBC-ODBC Bridge。
据我所知，Java使用UTF-8作为字符串，Access也是如此。他们都使用Unicode。
有没有人知道这个问题的任何解决方案？
They only fail when passing through the JDBC-ODBC Bridge. As I understand, Java uses UTF-8 for strings and so does Access. And they both use Unicode. Does anyone know any solution to this problem?
推荐答案
听起来你的Java源文件被编码为UTF- 8所以当 cityName 字符串包含LEÓN时，它被编码为
It sounds like your Java source file is encoded as UTF-8 so when the cityName string contains LEÓN it is encoded as
L E Ó N
-- -- ----- --
4C 45 C3 93 4E
这不是Access存储值的方式。 Access将字符存储为Unicode，但不使用UTF-8编码。它使用UTF-16LE编码的变体，其中代码点U + 00FF及以下的字符存储在单个字节中，代码点高于U + 00FF的字符存储为Null（0x0）值，后跟其UTF-16LE字节对。在这种情况下，Ó是U + 00D3，低于U + 00FF，因此Access将字符串的所有四个字符存储为单个字节：
That is not how Access stores the value. Access does store characters as Unicode, but it does not use UTF-8 encoding. It uses a variation of UTF-16LE encoding where characters with code points U+00FF and below are stored in a single byte, and characters with code points above U+00FF are stored as a Null (0x0) value followed by their UTF-16LE byte pair(s). In this case Ó is U+00D3, which is below U+00FF, so Access stores all four characters of the string as single bytes:
L E Ó N
-- -- -- --
4C 45 D3 4E
实际效果是Access数据库中字符串的编码与ISO 8859的编码相同-1字符集。
The net effect is that the encoding of the string in the Access database is the same as it would be for the ISO 8859-1 character set.
可以使用以下使用JDBC-ODBC Bridge的Java代码确认。当Java源文件编码为 UTF-8 时，它无法找到所需的记录，但是当Java源文件编码为 cp1252时，它可以正常工作Eclipse中的：
This can be confirmed with the following Java code which uses the JDBC-ODBC Bridge. It fails to find the desired record when the Java source file is encoded as UTF-8, but it works when the Java source file is encoded as cp1252 in Eclipse:
import java.sql.*;
public class accentTestMain {
public static void main(String[] args) {
String connectionString =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};" +
"DBQ=C:\\__tmp\\test\\accented.accdb;";
try {
Connection con = DriverManager.getConnection(connectionString);
PreparedStatement stmt = con.prepareStatement("SELECT * FROM localities WHERE locName=?");
String cityName = "LEÓN";
stmt.setString(1, cityName);
stmt.execute();
ResultSet rs = stmt.getResultSet();
if (rs.next()) {
System.out.println(String.format("Record found, ID=%d", rs.getInt("ID")));
}
else {
System.out.print("Record not found.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
如果你只能支持 cp1252 字符集中表示的重音字符，那么你应该能够只使用 cp1252 作为Java源文件的编码设置。
If you can make do with supporting only the accented characters represented in the cp1252 character set then you should be able to simply use cp1252 as the encoding setting for your Java source file(s).
另一方面，如果您确实需要Access数据库的完整Unicode字符支持，那么JDBC-ODBC Bridge不会为您完成任务。这是JDBC-ODBC Bridge和Access ODBC驱动程序之间长期存在的互操作性问题，并且不会修复。 （更多细节此处。）
On the other hand, if you really need full Unicode character support with an Access database then the JDBC-ODBC Bridge is not going to get the job done for you. This is a long-standing interoperability issue between the JDBC-ODBC Bridge and the Access ODBC driver, and it is not going to be fixed. (More details here.)
在这种情况下，您可能会想考虑使用 UCanAccess ，它是Access的纯Java JDBC驱动程序。使用带有UTF-8编码源文件的UCanAccess的相应代码将是
In that case you might want to consider using UCanAccess which is a pure-Java JDBC driver for Access. The corresponding code using UCanAccess with a UTF-8 encoded source file would be
// assumes...
// import java.sql.*;
Connection conn=DriverManager.getConnection(
"jdbc:ucanaccess://C:/__tmp/test/accented.accdb");
PreparedStatement ps = conn.prepareStatement(
"SELECT ID FROM localities WHERE locName=?");
ps.setString(1, "LEÓN");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
System.out.println(String.format(
"Record found, ID=%d",
rs.getInt("ID")));
}
else {
System.out.println("Record not found.");
}
有关使用UCanAccess的更多信息，请参阅相关问题其他。
For more information on using UCanAccess, see the related question here.
另一个解决方案是使用 Jackcess 像这样操纵Access数据库（同样，Java源文件被编码为UTF-8）：
Another solution would be to use Jackcess to manipulate the Access database like so (again, the Java source file is encoded as UTF-8):
import java.io.File;
import java.io.IOException;
import com.healthmarketscience.jackcess.*;
public class accentTestMain {
public static void main(String[] args) {
Database db;
try {
db = DatabaseBuilder.open(new File("C:\\__tmp\\test\\accented.accdb"));
try {
Table tbl = db.getTable("localities");
Cursor crsr = CursorBuilder.createCursor(tbl.getIndex("locName"));
if (crsr.findFirstRow(tbl.getColumn("locName"), "LEÓN")) {
System.out.println(String.format("Record found, ID=%d", crsr.getCurrentRowValue(tbl.getColumn("ID"))));
}
else {
System.out.println("Record not found.");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
这篇关于对具有重音字符的JDBC-ODBC Bridge查询失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
