Java中的MS Access删除查询抛出一个Java。sql。SQLException

Question

我正试图通过java做一个简单的删除查询。我使用的代码如下。所有其他查询（如选择和更新查询）都可以工作。ID是用户输入的值，已存储为字符串

int rs5 = fk.conn.createStatement().executeUpdate("DELETE * FROM tblKites WHERE tblKites.ID = " + ID + "");

错误消息是：

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Answer 1

有什么理由不使用PreparedStatement的吗

PreparedStatement preparedStatement = connection.prepareStatement("DELETE * FROM tblKites WHERE tblKites.ID = ?");
preparedStatement.setString(1,idValue);
ResultSet rs = preparedStatement.executeUpdate();

有关如何使用JDBC PreparedStatement删除记录的具体示例，请参见here

注:

由于上述链接中的JDBC-PraveReDealScript示例使用Oracle数据库，并且使用MSAccess，可以考虑使用JDBC连同^ {A4}连接到MSAccess数据库。

项目构建路径中需要的其他JAR包括：

commons-lang-2.6.jar
commons-logging-1.1.1.jar
hsqldb.jar
jackcess-2.1.0.jar
ucanaccess-2.0.9.5.jar

例如：

下面的示例（适用于JDK 8）初始化远程数据库连接，将记录插入MS Access数据库并关闭连接

/**
 * Connects to a remote MS-Access DB using JDBC/UCanAccess API.
 * 
 * ADDITIONAL JARS REQD:
 * 
 * commons-lang-2.6.jar
 * commons-logging-1.1.1.jar
 * hsqldb.jar
 * jackcess-2.1.0.jar
 * ucanaccess-2.0.9.5.jar
 * 
 */
package miscellaneous;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class ConnectRemoteDB {

    public static void main(String[] args) {
        initializeConnection();
    }

    /**
     * Initializes remote database connection and inserts a record and closes the connection.
     */
    private static void initializeConnection() {
        System.out.println("Attempting Database Connection...");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = DriverManager
                    .getConnection("jdbc:ucanaccess:////IP ADDRESS/shared-folder-name/TestDB.mdb"); // Replace IP ADDRESS with the actual IP ADDRESS
            System.out.println("CONNECTION ESTABLISHED....");
            String insertTableSQL = "INSERT INTO Table1" + "(Name) VALUES"
                    + "(?)";
            preparedStatement = connection.prepareStatement(insertTableSQL);
            preparedStatement.setString(1, "Sandeep");
            preparedStatement.executeUpdate();
            System.out.println("RECORD INSERTED...");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
                System.out.println("CONNECTION CLOSED...");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}