Can not issue data manipulation statements with executeQuery() [duplicate]

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

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.

Attribution
Source : Link , Question Author : Mediator , Answer Author : Vineet Reynolds

Leave a Comment