如何通过 Java 从 MS Access 2007 数据库中读取 Unicode 字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2797277/
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
How do I read Unicode characters from an MS Access 2007 database through Java?
提问by Peter
In Java, I have written a program that reads a UTF8 text file. The text file contains a SQL query of the SELECT kind. The program then executes the query on the Microsoft Access 2007 database and writes all fields of the first row to a UTF8 text file.
在 Java 中，我编写了一个读取 UTF8 文本文件的程序。文本文件包含 SELECT 类型的 SQL 查询。然后，程序在 Microsoft Access 2007 数据库上执行查询，并将第一行的所有字段写入 UTF8 文本文件。
The problem I have is when a row is returned that contains unicode characters, such as "?". These characters show up as "?" in the text file.
我遇到的问题是当返回包含 unicode 字符（例如“？”）的行时。这些字符显示为“？” 在文本文件中。
I know that the text files are read and written correctly, because a dummy UTF8 character ("◎") is read from the text file containing the SQL query and written to the text file containing the resulting row. The UTF8 character looks correct when the written text file is opened in Notepad, so the reading and writing of the text files are not part of the problem.
我知道文本文件被正确读取和写入，因为从包含 SQL 查询的文本文件中读取了一个虚拟的 UTF8 字符（“◎”）并将其写入包含结果行的文本文件。在记事本中打开写入的文本文件时，UTF8 字符看起来是正确的，因此文本文件的读取和写入不是问题的一部分。
This is how I connect to the database and how I execute the SQL query:
这是我连接到数据库的方式以及我执行 SQL 查询的方式：
Connection c = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/database.accdb;Pwd=temp");
ResultSet r = c.createStatement().executeQuery(sql);
I have tried making a charSet property to the Connection but it makes no difference:
我曾尝试为 Connection 创建一个 charSet 属性，但它没有区别：
Properties p = new Properties();
p.put("charSet", "utf-8");
p.put("lc_ctype", "utf-8");
p.put("encoding", "utf-8");
Connection c = DriverManager.getConnection("...", p);
Tried with "utf8"/"UTF8"/"UTF-8", no difference. If I enter "UTF-16" I get the following exception:
用“utf8”/“UTF8”/“UTF-8”试过，没有区别。如果我输入“UTF-16”，则会出现以下异常：
java.lang.IllegalArgumentException: Illegal replacement
Been searching around for hours with no results and now turn my hope to you. Please help!
找了几个小时没有结果，现在把我的希望寄托在你身上。请帮忙！
I also accept workaround suggestions. =) What I want to be able to do is to make a Unicode query (for example one that searches for posts that contain the "あ" character) and to have results with Unicode characters receieved and saved correctly.
我也接受解决方法的建议。=) 我想要做的是进行 Unicode 查询（例如搜索包含“あ”字符的帖子），并正确接收和保存带有 Unicode 字符的结果。
Thank you!
谢谢！
Update. Here is a self-contained example of the issue:
更新。这是该问题的一个独立示例：
package test;
import java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStreamReader;import java.io.OutputStreamWriter;import java.nio.charset.Charset;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.util.Properties;
public class Standalone {
public static void main(String[] args) {
try {
Properties p = new Properties();
p.put("charSet", "UTF8");
Connection c = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=./dummy.accdb;Pwd=pass", p);
ResultSet r = c.createStatement().executeQuery("SELECT TOP 1 * FROM main;");
r.next();
OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(new File("results.txt")), Charset.forName("UTF-8"));
osw.write(new BufferedReader(new InputStreamReader(new FileInputStream("utf8.txt"), Charset.forName("UTF-8"))).readLine() +" : "+ r.getString("content"));
osw.close();
c.close();
System.out.println("Done.");
} catch (Exception e) {
e.printStackTrace();
}
}
}What the example does is that it opens the database "dummy.accdb" encrypted with the password "pass" and pulls the first post out of the table "main". It then reads the text file "utf8.txt" and writes a text file "results.txt" which will contain the first row of "utf8.txt" plus the value of the field "content" it got from the database.
该示例的作用是打开使用密码“pass”加密的数据库“dummy.accdb”，并从“main”表中取出第一篇文章。然后它读取文本文件“utf8.txt”并写入一个文本文件“results.txt”，该文件将包含“utf8.txt”的第一行加上它从数据库中获得的字段“content”的值。
In the file "utf8.txt" I have stored "??????????????????". In the database's "main" table's "content" field I have stored "??あ??????∞?♀♂".
在文件“utf8.txt”中，我存储了“?????????????????????”。在数据库的“主”表的“内容”字段中，我存储了“??あ??????∞?♀♂”。
After the application has finished running the "results.txt" has the following content: "?????????????????? : ?????Moe?8???".
应用程序运行完毕后，“results.txt”的内容如下：“?????????????????? : ???????Moe?8???”。
It successfully read and write the UTF8 characters of the "utf8.txt" text file, but failed to obtain the correct characters from the database. This is where the problem lies.
它成功读写了“utf8.txt”文本文件的UTF8字符，但未能从数据库中获取正确的字符。这就是问题所在。
Update. Thought I should mention that the field in the database is of the type "memo", I have tried havig "Unicode Compression" set both to "No" and to "Yes" (recreating the post between tries to make sure no compression were there when "No" was selected). To my understanding Access uses UTF-16 when it saves Unicode characters, however with compression on it changes to UTF-8. In any case this did not make any difference.
更新。我想我应该提到数据库中的字段是“备忘录”类型，我尝试将 havig“Unicode 压缩”设置为“否”和“是”（在尝试之间重新创建帖子以确保没有压缩）选择“否”）。据我了解，Access 在保存 Unicode 字符时使用 UTF-16，但压缩后会更改为 UTF-8。无论如何，这没有任何区别。
Bonus question, anyone know how to connect to the database using a pure ODBC provider in Java? Or any other kind of method? This would provide me with a good workaround.
额外的问题，有人知道如何使用 Java 中的纯 ODBC 提供程序连接到数据库吗？或者有什么其他的方法？这将为我提供一个很好的解决方法。
Update. I have been trying to feed these four to getConnection:
更新。我一直在尝试将这四个喂给 getConnection：
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb"
"jdbc:odbc:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb"
"jdbc:odbc:Driver={Microsoft.Jet.OLEDB.4.0};Data Source=./dummy.accdb"
"jdbc:odbc:Provider=Microsoft.ACE.OLEDB.12.0;Data Source=./dummy.accdb"The first give the error "java.sql.SQLException: No suitable driver found for Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb" and the two in the middle gets "java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". The last one gets "java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name too long".
第一个给出错误“java.sql.SQLException：找不到适用于Provider=Microsoft.Jet.OLEDB.4.0;Data Source=./dummy.accdb的驱动程序”，中间的两个得到“java.sql.SQLException： [Microsoft][ODBC 驱动程序管理器] 未找到数据源名称且未指定默认驱动程序”。最后一个得到“java.sql.SQLException: [Microsoft][ODBC Driver Manager] 数据源名称太长”。
I don't understand what getConnection wants. The parameter description is as follows: "url - a database url of the form jdbc:subprotocol:subname". Huh? I clearly don't get what that means.
我不明白 getConnection 想要什么。参数说明如下：“url - 形式为 jdbc:subprotocol:subname 的数据库 url”。嗯？我显然不明白这意味着什么。
Anyone know any alternative working ways of connecting to the Access 2007 database through Java? Maybe the providers I tried aren't supported but some other might be?
任何人都知道通过 Java 连接到 Access 2007 数据库的任何替代工作方式？也许我尝试过的提供程序不受支持，但其他一些可能是？
回答by jtahlborn
An alternative to using jdbc with Access is the Hymancesslibrary. the pro of using this library is that there are no problematic translation layers (like odbc) or any external system dependencies. the con of using this library is that it does not support executing queries.
将 jdbc 与 Access 结合使用的替代方法是Hymancess库。使用这个库的优点是没有有问题的翻译层（如 odbc）或任何外部系统依赖项。使用这个库的缺点是它不支持执行查询。
回答by Gord Thompson
Now that the JDBC-ODBC Bridge has been dropped from Java SE 8 and Oracle has confirmed that this issue will never be fixed (ref: here) a good alternative would be to use UCanAccess. For more information, see
既然 JDBC-ODBC Bridge 已从 Java SE 8 中删除，并且 Oracle 已确认此问题将永远不会得到修复（参考：此处），一个不错的选择是使用UCanAccess。有关更多信息，请参阅
回答by Daniel
Since you mentioned switching to some other DB than Access is possible, I urge you to do so. Making your software on Microsoft Office products has always been a maintenance nightmare for me, so choose anything else from this list: http://java-source.net/open-source/database-engines.
由于您提到可以切换到 Access 以外的其他数据库，因此我敦促您这样做。在 Microsoft Office 产品上制作软件对我来说一直是维护的噩梦，因此请从此列表中选择其他任何内容：http: //java-source.net/open-source/database-engines。
I would go with Apache Derby for this, or just use the Java Database JavaDB that comes preinstalled with any current Sun Java Installation (and is in fact a repackaged Derby DB)
为此，我会使用 Apache Derby，或者只使用随任何当前 Sun Java 安装一起预装的 Java 数据库 JavaDB（实际上是重新打包的 Derby DB）
