newim123 newim123 - 1 month ago 8
MySQL Question

Java update sql Parameter index out of range

I have problem with update sql command in java, i am using jdbc to connect to mysql database. When i want do this:

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);


It shows this error.

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 ...


I have 4 "?" and 4 Set function. But this dont work. Any idea? Thanks

This is class Databaze. Thank you

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();
}
}


empty word becasue It looks like your post is mostly code; please add some more details. It looks like your post is mostly code; please add some more details.

Answer Source

You don't using the correct PrepapredStatement you are using another API.

Why?

because

  1. there are a method prepareStatement and not CreatePreparedStatement
  2. there are setInt with lower s and not SetInt with upper S

Fot this i assume you are using another thing and not the correct PrepapredStatement

So to solve your problem you can use :

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);

Edit

How to explain this, your design is a little complicated, ok

When you call public PreparedStatement CreatePreparedStatement(String query) you pass your query in it, so it create a prepapred statement with this query and retun it which mean you have to put it in another variable PreparedStatement like this :

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

Then you call the setters (there are a spcific syntax of setters and getters, one of them should not start with upper letter), which set the parameters in the static Prepared Statement :

private static PreparedStatement preparedStatement;

In this case you can get NullPointException because the PreparedStatement is null, in your case you don't because you already call CreatePreparedStatement.
So you note that you don't set the attribute in the correct statement for that you get this error, instead you have to use it like this :

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();

No need to call SetInt(..), ...

Hope you get your problem.

Note

please do not use upper letters in the first letter of your methods, this not good practice.