使用 JDBC 将 Microsoft Access 数据库连接到 Java 并进行编译

Connecting a Microsoft Access Database to Java using JDBC and compiling

提问by Moogle

for a school database project we are making a database program (user GUI and the database). Using Microsoft Access 2010 I created the database and populated it with some sample data, and saved it in .mdb format and placed it in my project folder.

对于学校数据库项目，我们正在制作一个数据库程序（用户 GUI 和数据库）。我使用 Microsoft Access 2010 创建了数据库并用一些示例数据填充它，然后将其保存为 .mdb 格式并将其放置在我的项目文件夹中。

When running it in eclipse the following code works fine, connects and even retrieves the query. However I find that I am unable to export the code to a jar and run it (which is required for the project, give them a working copy of your program on a CD or flash drive), and I'm also unable to port the code over to Netbeans to have it work, as well as trying to compile on a Linux machine.

在 eclipse 中运行它时，以下代码工作正常，连接甚至检索查询。但是，我发现我无法将代码导出到 jar 并运行它（这是项目所必需的，在 CD 或闪存驱动器上为他们提供程序的工作副本），而且我也无法移植将代码交给 Netbeans 使其工作，并尝试在 Linux 机器上编译。

I assume this is a problem with including drivers or trying to use Microsoft access. The error I get when running the jar or running on Netbeans is given below the code. So I ask either how do I include drivers to make the program portable, or how else can I approach this problem?

我认为这是包含驱动程序或尝试使用 Microsoft 访问的问题。我在运行 jar 或在 Netbeans 上运行时遇到的错误在代码下方给出。所以我问我如何包含驱动程序以使程序可移植，或者我还能如何解决这个问题？

Thanks in advance

提前致谢

import java.sql.*;

public class JDBCTest {
    static Connection connection;
    static Statement statement;

    public static void main(String args[]){

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=TLDATABASEDBM.mdb";
            connection = DriverManager.getConnection( database ,"",""); 

            buildStatement();
            executeQuery();

        }catch(Exception e){
            e.printStackTrace();
            System.out.println("Error!");
        }
    }

    public static void buildStatement() throws SQLException {
        statement = connection.createStatement();
    }

    public static void executeQuery() throws SQLException {

        boolean foundResults = statement.execute("SELECT * FROM tblStaff  AS x WHERE City='Calgary'");
        if(foundResults){
            ResultSet set = statement.getResultSet();
            if(set!=null) displayResults(set);
        }else {
            connection.close();
        }
    }

    public static void displayResults(ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columns=metaData.getColumnCount();
        String text="";

        while(rs.next()){
            for(int i=1;i<=columns;++i) {
                text+=""+metaData.getColumnName(i)+":\t";
                text+=rs.getString(i);
                //text+="</"+metaData.getColumnName(i)+">";
                text+="\n";
            }
            text+="\n";
        }

        System.out.println(text);

    }
}

The error mentioned above:

上面提到的错误：

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
        at sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(JdbcOdbc.java:3073)
        at sun.jdbc.odbc.JdbcOdbcConnection.initialize(JdbcOdbcConnection.java:323)
        at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:174)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at tldatabase.DataConnect.makeConnection(DataConnect.java:35)
        at tldatabase.Main.main(Main.java:24)

回答by Micha? Niklas

I think that your app do not see TLDATABASEDBM.mdbin current directory. You can give full path to this file in connection string or add system DSN in ODBC Manager and then connect to it with connection string like: jdbc:odbc:TLDATABASEDBM

我认为您的应用程序TLDATABASEDBM.mdb在当前目录中看不到。您可以在连接字符串中提供此文件的完整路径，或在 ODBC 管理器中添加系统 DSN，然后使用连接字符串连接到它，例如：jdbc:odbc:TLDATABASEDBM

回答by Mark Darwin Nacionales

I know the post was years ago but I felt like answering the question for those who are just experiencing this right now. It took me a while to know the answer to the question so here's the solution:

我知道这个帖子是几年前的，但我想为那些现在刚刚经历过这个问题的人回答这个问题。我花了一段时间才知道问题的答案，所以这是解决方案：

http://wiki.netbeans.org/FaqSettingHeapSize

http://wiki.netbeans.org/FaqSettingHeapSize

Follow the "Running the 32-bit JVM".

遵循“运行 32 位 JVM”。

All you have to do is find the netbeans.conf in the installation folder of your netbeans and change the directory from something like this:

您所要做的就是在您的 netbeans 的安装文件夹中找到 netbeans.conf 并从以下内容更改目录：

netbeans_jdkhome="C:\Program Files\Java\jdk1.6.0_24"

netbeans_jdkhome="C:\Program Files\Java\jdk1.6.0_24"

to this:

对此：

netbeans_jdkhome="C:\Program Files (x86)\Java\jdk1.6.0_21"

netbeans_jdkhome="C:\Program Files (x86)\Java\jdk1.6.0_21"

The problem is netbeans might be running in 64 bit but MS Access only support 32-bit. So doing this would hopefully solve the problem. Also make sure to install this:

问题是 netbeans 可能在 64 位上运行，但 MS Access 仅支持 32 位。所以这样做有望解决问题。还要确保安装这个：

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734

回答by Vasu

Honestly, I dont like what I am going to say... but, it solved the same issue for me... mysteriously... :(((

老实说，我不喜欢我要说的话......但是，它为我解决了同样的问题......神秘地...... :(((

on the line where you are defining the database variable, I changed ...(.mdb)... into ...(.mdb, *.accdb)...

在您定义数据库变量的行上，我将 ...( .mdb)...更改为 ...(.mdb, *.accdb)...

All the best for figuring out what difference that made!

最好弄清楚这有什么不同！

回答by Anil_irocks88

The main problem lies in the line:

主要问题在于这一行：

String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=TLDATABASEDBM.mdb";

1. Make sure that the .mdb file is in the correct directory.
2. Check the file extension as .mdb or .mdbacc.

1. 确保 .mdb 文件位于正确的目录中。
2. 将文件扩展名检查为 .mdb 或 .mdbac。

Also, if you want to use the same DSN every time, it is better to add the DSN(Data Source Name) into the respective system on which the mdb is stored.

此外，如果您想每次都使用相同的 DSN，最好将 DSN（数据源名称）添加到存储 mdb 的相应系统中。

回答by Joseph Dominguez

package javaapplication1;

import java.sql.*;

public class MSaccess_archive {
public static void main(String[] args) {

    try {

       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// set this to a MS Access DB you have on your machine
String filename = "mdbTEST.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;}"; // add on to the end 
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection( database ,"","");

        Statement stmt = con.createStatement();

        stmt.execute("select * from student"); // execute query in table student

        ResultSet rs = stmt.getResultSet(); // get any Result that came from our query

        if (rs != null)
         while ( rs.next() ){

            System.out.println("Name: " + rs.getInt("Age") + " ID:       "+rs.getString("Course"));
            }

            stmt.close();
            con.close();
        }
        catch (Exception err) {
            System.out.println("ERROR: " + err);
        }
   }

}