Can not issue data manipulation statements with executeQuery()

Question

I use com.mysql.jdbc.Driver

I need insert and get id. My query:

INSERT INTO Sessions(id_user) VALUES(1);
SELECT LAST_INSERT_ID() FROM Sessions LIMIT 1;

error -

Can not issue data manipulation statements with executeQuery()

How insert and get id?

Answer 1

You will need to use the executeUpdate() method to execute the INSERT statement, while you'll need to use the executeQuery() method to execute the SELECT statement. This is due to the requirements imposed by the JDBC specification on their usages:

From the Java API documentation for Statement.executeQuery():

Executes the given SQL statement, which returns a single ResultSet object.

Parameters:

sql - an SQL statement to be sent to the database, typically a static SQL SELECT statement

and from the Java API documentation for Statement.executeUpdate():

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Parameters:

sql - an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Your code (pseudo-code posted here) should appear as:

statement.executeUpdate("INSERT INTO Sessions(id_user) VALUES(1)"); // DML operation
statement.executeQuery("SELECT LAST_INSERT_ID()"); // SELECT operation

And of course, the MySQL documentation demonstrates how to perform the same activity for AUTO_INCREMENT columns, which is apparently what you need.

If you need to execute both of them together in the same transaction, by submitting the statements in one string with a semi-colon separating them like the following:

statement.execute("INSERT INTO Sessions(id_user) VALUES(1); SELECT LAST_INSERT_ID() FROM Sessions LIMIT 1;");

then you'll need to use the execute() method. Note, that this depends on the support offered by the Database and the JDBC driver for batching statements together in a single execute(). This is supported in Sybase and MSSQL Server, but I do not think it is supported in MySQL.

Answer 2

may be you are using executeQuery() but to manipulate data you actually need executeUpdate() rather than executeQuery()

Answer 3

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet generatedKeys = null;

    try {
        connection = m_Connection;
        preparedStatement = (PreparedStatement) connection.prepareStatement(qString, Statement.RETURN_GENERATED_KEYS);

        // ...

        int affectedRows = preparedStatement.executeUpdate();
        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        generatedKeys = preparedStatement.getGeneratedKeys();
        int id = -1;
        if (generatedKeys.next()) {
            id = generatedKeys.getInt(1);
            id = -1;
        } else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
    } finally {

    }

Answer 4

For non-select SQL statements you use ExecuteNonQuery();

To get the last inserted id, you can do this SQL statement.

SELECT LAST_INSERT_ID() AS last_id

Although there's probably an java wrapper for that select statement.

Links:
http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html
http://wiki.bibalex.org/JavaDoc/org/bibalex/daf/handlers/dbhandler/DBConnection.html