Java更新sql参数索引超出范围

Question

我在 java 中使用 update sql 命令有问题，我正在使用 jdbc 连接到 mysql 数据库。当我想这样做时:

db.CreatePreparedStatement("UPDATE kontakty SET Telefon = ?,Mobil = ?,Email = ? WHERE `idKontakty` = ?");
db.SetInt(1, Integer.parseInt(jTextFieldTelefon.getText()));
db.SetInt(2, Integer.parseInt(jTextFieldMobil.getText()));
db.SetString(3, jTextFieldEmail.getText());
db.SetInt(4, 1);

它显示了这个错误。

    Srp 20, 2017 8:10:42 ODP. autoservis.SpravaZamestnancu.SpravaZamestnancu updateKontakt
SEVERE: null
java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.PreparedStatement.checkBounds(PreparedStatement.java:3327)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3312)
    at com.mysql.jdbc.PreparedStatement.setInternal(PreparedStatement.java:3351)
    at com.mysql.jdbc.PreparedStatement.setInt(PreparedStatement.java:3302)
    at autoservis.DatovaVrstva.Databaze.SetInt(Databaze.java:69)
    at autoservis.SpravaZamestnancu.SpravaZamestnancu.updateKontakt(SpravaZamestnancu.java:682)
    at autoservis.SpravaZamestnancu.SpravaZamestnancu.jButtonUpravitActionPerformed(SpravaZamestnancu.java:361)
    at autoservis.SpravaZamestnancu.SpravaZamestnancu.access$200(SpravaZamestnancu.java:23)
    at autoservis.SpravaZamestnancu.SpravaZamestnancu$3.actionPerformed(SpravaZamestnancu.java:123)
    at ...

我有 4 个“?”和 4 设置功能。但这不起作用。

这是 Databaze 类。

package autoservis.DatovaVrstva;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Databaze {

    private static Databaze instance;
    private final String pripojovaciRetezec = "jdbc:mysql://localhost:3306/Autoservis";
    private final String uzivatelDB= "pripojenikDB";
    private final String hesloDB = "pripojenikDB";
    private static Connection connection;
    private static Statement statement;
    private String query;
    private Savepoint savePoint;
    private static PreparedStatement preparedStatement;

    private Databaze() {
        try {
            connection = DriverManager.getConnection(pripojovaciRetezec, uzivatelDB, hesloDB);
            if (connection != null) {
        System.out.println("You made it, take control your database now!");
    } else {
        System.out.println("Failed to make connection!");
    }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            Logger.getLogger(Databaze.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static Databaze GetInstance() {
        if (instance == null) {
            instance = new Databaze();
        }
        return instance;
    }

    public Statement CreateStatement() throws SQLException {
        statement = connection.createStatement();
        return statement;
    }

    public PreparedStatement CreatePreparedStatement(String query) throws SQLException {
        preparedStatement = connection.prepareStatement(query);
        return preparedStatement;
    }
    public void SetString(int pozice, String hodnota) throws SQLException{
        preparedStatement.setString(pozice, hodnota);
    }
    public void SetInt(int pozice, int hodnota) throws SQLException{
        preparedStatement.setInt(pozice, hodnota);
    }
    public void SetDate(int pozice, Date hodnota) throws SQLException{
        preparedStatement.setDate(pozice, hodnota);
    }
    public void SetNull(int pozice,int typ) throws SQLException{
        preparedStatement.setNull(pozice, typ);
    }
    public void SetDouble(int pozice, double hodnota) throws SQLException{
        preparedStatement.setDouble(pozice, hodnota);
    }
    public ResultSet ExecuteQuery(String query) throws SQLException{
        return statement.executeQuery(query);
    }
    public boolean Execute(String query) throws SQLException{
        return statement.execute(query);
    }
    public int ExecuteUpdate(String query) throws SQLException{
        return statement.executeUpdate(query);
    }
    public ResultSet ExecutePreparedQuery() throws SQLException{
        return preparedStatement.executeQuery();
    }
    public boolean ExecutePrepared() throws SQLException{
        return preparedStatement.execute();
    }
    public int ExecutePreparedUpdate() throws SQLException{
        return preparedStatement.executeUpdate();
    }
    public boolean IsClosed() throws SQLException{
        return connection.isClosed();
    }
    public void Close() throws SQLException{
        connection.close();
    }
    public void Commit() throws SQLException{
        connection.commit();
    }
    public void SetAutoCommit(boolean commit) throws SQLException{
        connection.setAutoCommit(commit);
    }
    public void RollBack() throws SQLException{
        connection.rollback(savePoint);
    }
    public void SetSavePoint() throws SQLException{
        savePoint = connection.setSavepoint();
    }
}

Answer 1

您没有使用正确的 PreparedStatement您正在使用另一个 API。

为什么？

因为

1. 应该有方法 prepareStatement 而不是 CreatePreparedStatement
2. 应该有一个方法 setInt 是小写的 s 而不是 SetInt 是大写的 S

出于这个原因，我假设您正在使用另一个东西而不是正确的 PreparedStatement

所以要解决您的问题，您可以使用:

Connection db = DriverManager.getConnection(DB_URL, DB_username, DB_password);

PreparedStatement ps = db.prepareStatement("UPDATE kontakty SET Telefon = ?, Mobil = ?, Email = ? WHERE `idKontakty` = ?");

ps.setInt(1, Integer.parseInt(jTextFieldTelefon.getText()));
ps.setInt(2, Integer.parseInt(jTextFieldMobil.getText()));
ps.setString(3, jTextFieldEmail.getText());
ps.setInt(4, 1);

---

编辑

这个怎么解释，你的设计有点复杂，ok。

当您调用 public PreparedStatement CreatePreparedStatement(String query) 时，您会在其中传递您的查询，因此它会使用此查询创建一个准备好的语句并返回它，这意味着您有将其放入另一个变量 PreparedStatement 中，如下所示:

PreparedStatement pst = db.CreatePreparedStatement(
        "UPDATE kontakty SET Telefon = ?, Mobil = ?, Email = ? WHERE `idKontakty` = ?");

然后调用 setter(setter 和 getter 有特定的语法，其中一个不应该以大写字母开头)，它在静态 Prepared Statement 中设置参数:

private static PreparedStatement preparedStatement;

在这种情况下，您可以获得NullPointException，因为PreparedStatement 为空，在您的情况下，您不会获得，因为您已经调用了CreatePreparedStatement.
所以你注意到你没有在正确的语句中设置属性，因为你得到这个错误，而是你必须像这样使用它:

PreparedStatement pst = db.CreatePreparedStatement(
            "UPDATE kontakty SET Telefon = ?, Mobil = ?, Email = ? WHERE `idKontakty` = ?");

pst.setInt(1, Integer.parseInt(jTextFieldTelefon.getText()));
pst.setInt(2, Integer.parseInt(jTextFieldMobil.getText()));
pst.setString(3, jTextFieldEmail.getText());
pst.setInt(4, 1);
pst.executeUpdate();

无需调用 SetInt(..), ...

希望你现在明白你的问题了。

注意

请不要在方法的首字母中使用大写字母，这不是好的做法。