0

Im using MySQL and decided to write my own database helper class that would give me android-like syntactic sql, however, when i try execute an update statement using a PreparedStatement, when i run the code, no change takes place in the database, but no exception is thrown. I double-checked my connection string, made sure my .jars were in order, triple checked my syntax, but nothing. Code:

DatabaseHelper.java:

public class DatabaseHelper {

String dbHost = "localhost";
String dbPort = "3306";
String dbName = "nakomangdb";
String dbUsername = "root";
String dbPassword = "sqldb";
String connectionString = "jdbc:mysql://" + dbHost + ":" + dbPort + "/" + dbName;
Connection connection;
PreparedStatement preparedStatement;

public DatabaseHelper() {
}

public void closeDatabase() {
    try {
        if (!connection.isClosed())
            connection.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

}

public void openDatabase() {
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(connectionString, dbUsername, dbPassword);
        connection.setAutoCommit(true);
    } catch (ClassNotFoundException | SQLException ex) {
        ex.printStackTrace();
    }
}

public void update(String table, String[] set, Object[] to, String whereClause, Object[] whereArgs) {
    StringBuilder sql = new StringBuilder("update ");
    sql.append(table);
    sql.append(" set ");

    for (int i = 0; i < set.length; i++) {

        sql.append(set[i]);
        sql.append(" = ?");

        if (i != (set.length - 1)) {
            sql.append(", ");
        }

    }
    sql.append(" ");

    int argCount = to.length;

    try {

        if (whereClause != null) {
            sql.append(whereClause);
            sql.append(";");

            preparedStatement = connection.prepareStatement(sql.toString());

            for (Object s : whereArgs) {
                preparedStatement.setObject(argCount++, s);
            }

        } else {

            sql.append(";");
            preparedStatement = connection.prepareStatement(sql.toString());

        }

        argCount = 0;

        for (Object s : to) {
            preparedStatement.setObject(argCount++, s);
        }

        preparedStatement.executeUpdate();

        preparedStatement.close();

    } catch (SQLException e) {
        e.printStackTrace();
    }

}

}

Calling Code:

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    DatabaseHelper dbHelper = new DatabaseHelper();

    dbHelper.openDatabase();
    dbHelper.update("customer", 
            new String[]{"fullName"}, 
            new Object[]{"vernon"}, 
            "where ID=?", 
            new Object[]{1});
    dbHelper.closeDatabase();
    response.getWriter().append("Done!");
}
3
  • Have you check your server log to stacktrace? Commented Jun 14, 2016 at 1:18
  • I just did, and im getting a java.sql.SQLException: Parameter index out of range (0 < 1 ), which makes no sense because argCount is never 0, i checked, does the order in which the PrepaaredStatement.setXXX methods are called matter? do i strictly have to set arg positions 1,2,3 etc in that order, or can i set 3,4,1,2? Commented Jun 14, 2016 at 1:23
  • 1
    "argCount is never 0" -- sorry, this is incorrect. argCount is initialized to zero and argCount++ is post increment. Commented Jun 14, 2016 at 3:29

1 Answer 1

1

Parameter index out of range (0 < 1 ), which makes no sense because argCount is never 0

Oh yes it is. The exception does not lie.

I checked

Check again. Read your code. I quote:

argCount = 0;

does the order in which the PrepaaredStatement.setXXX methods are called matter?

No.

do i strictly have to set arg positions 1,2,3 etc in that order

No.

or can i set 3,4,1,2?

Yes.

The problem is that argCount should be initialized to 1, not zero, or else you should use ++argCount rather than argCount++.

Your helper class would be a lot simpler if the database was opened in the constructor. You could also pass the statement-related stuff to the constructor and have it prepared there as well.

autoCommit=true is the default. The Class.forName() line hasn't been needed since 2007.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.