SQLException: executeQuery method can not be used for update

Question

I am trying to insert user information taken from a registration form into Derby DB using a java servlet class.

I get connected to the DB on NetBeans right after the user clicks the submit button with the user's information filled out. Then it should run this method:

public void insertNewUser(String userName, String passWord, String lastName, String firstName, String age, char sex, String email) {
    try {
        stmt = conn.createStatement();
        String insertNewUserSQL = "INSERT INTO " + studentsTable + " VALUES ('" + userName + "', '" + passWord + "', '" + lastName + "', '" + firstName + "', " + age + ", '" + sex + "', '" + email + "')";
        System.out.println(insertNewUserSQL);
        stmt.executeQuery(insertNewUserSQL);
        stmt.close();
    } catch(SQLException sqlExcept) {
        sqlExcept.printStackTrace();
    }
}

But I keep getting the following exception:

java.sql.SQLException: executeQuery method can not be used for update.

What does this mean exactly?

The SQL command is correct as I can do it manually on NetBeans SQL Command window.

Are there restrictions for servlets or something I don't know about?

Thanks in advance!

Answer 1

Since you are inserting a record, you should be using executeUpdate() not executeQuery().

Here are some methods that are usually misused:

---

boolean execute()

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

ResultSet executeQuery()

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

int executeUpdate()

Executes the SQL statement in this PreparedStatement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement.

---

One more thing, your query is weak as it is vulnerable with SQL Injection. Please do parameterized by using PreparedStatement.

Sample Code Snippet:

String insertNewUserSQL = "INSERT INTO " + studentsTable + " VALUES (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = con.prepareStatement(insertNewUserSQL);
pstmt.setString(1, userName);
// ... repeat this step until the last parameter ....
pstmt.setString(7, email);
pstmt.executeUpdate();

• Java PreparedStatement

Answer 2

To update values you need to use an updatable ResultSet, as follows:

ResultSet res = preparedStatement.executeQuery(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
res.first();
res.updateInt("id", 2);
res.updateRow();

Alternatively, you can use the executeUpdate method of statement, as follows: statement.executeUpdate("update table set id = 2");