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!");
}
argCountis initialized to zero andargCount++is post increment.