如何在Java项目中配置ODBC-Excel驱动连接以执行SQL查询

Hey there! Let's walk through how to leverage that already-configured Excel ODBC connection in your Java project to run SQL queries. I'll break this down into simple, actionable steps:

First off, driver availability depends on your Java version:

• Java 8 or earlier: The JDK comes with a built-in JDBC-ODBC bridge driver (sun.jdbc.odbc.JdbcOdbcDriver)—no extra dependencies needed.
• Java 9+: Oracle removed the built-in bridge driver. You'll need to add a third-party driver via your build tool (Maven/Gradle). For example, using Maven:

  <dependency>
      <groupId>org.openjdk.jdbc</groupId>
      <artifactId>odbc-jdbc</artifactId>
      <version>1.0</version>
  </dependency>
  

Since you already have an ODBC DSN set up, the connection string is straightforward:

jdbc:odbc:<Your_DSN_Name>

Replace <Your_DSN_Name> with the exact name of the ODBC data source you configured earlier. For example, if your DSN is named ExcelInventory, the string becomes jdbc:odbc:ExcelInventory.

Pro tip: If you ever want to skip using a DSN (for ad-hoc connections), you can use a direct connection string pointing to your Excel file, but since you already have a DSN set up, stick with that for simplicity.

Here's a complete, runnable example that connects to your ODBC source, runs a query, and processes results:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ExcelOdbcQueryRunner {
    public static void main(String[] args) {
        String dsnName = "ExcelInventory"; // Replace with your DSN name
        String jdbcUrl = "jdbc:odbc:" + dsnName;
        
        // Declare resources (we'll close these later!)
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            // Load the driver (required for Java 8; optional but safe for Java 9+)
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Use org.openjdk.jdbc.odbc.JdbcOdbcDriver for Java 9+
            
            // Connect to the ODBC source (most Excel ODBC setups don't need username/password)
            conn = DriverManager.getConnection(jdbcUrl, "", "");
            
            // Create a statement to run queries
            stmt = conn.createStatement();
            
            // Excel-specific SQL: Sheet names need a $ and square brackets
            String sqlQuery = "SELECT ProductName, Quantity FROM [Inventory$]";
            rs = stmt.executeQuery(sqlQuery);
            
            // Iterate through the results and print them
            while (rs.next()) {
                String productName = rs.getString("ProductName");
                int quantity = rs.getInt("Quantity");
                System.out.printf("Product: %s | Quantity: %d%n", productName, quantity);
            }
            
        } catch (Exception e) {
            // Handle errors gracefully (replace with your own error handling!)
            System.err.println("Error querying Excel data:");
            e.printStackTrace();
        } finally {
            // Always close resources to avoid leaks
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

• Excel SQL Syntax Quirks: Sheet names must be wrapped in square brackets and end with a $—e.g., [Sales Data$] for a sheet named "Sales Data".
• Data Type Handling: Excel's loose data types can cause surprises. Use rs.getDate(), rs.getDouble(), etc., appropriately based on your data.
• 32/64-bit Matching: If your ODBC DSN is 32-bit, your Java runtime must also be 32-bit (and vice versa). Mismatches will cause connection failures.
• File Locks: Ensure the Excel file isn't open in another program (like Microsoft Excel) when you run your Java code—locks will prevent access.

内容的提问来源于stack exchange，提问作者annyka