Menu
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?
576
executeQuery() : This method is used to retrieve data from database using SELECT query. This method returns the ResultSet object that returns the data according to the query.
executeQuery : Returns one ResultSet object. executeUpdate : Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT , DELETE , or UPDATE SQL statements.
Whereas the execute( ) and executeUpdate( ) methods discussed in previous sections return primitive data types -- a boolean and int , respectively -- the method normally used with a SELECT statement, executeQuery( ) , returns a ResultSet object.
When executing select queries we should use executeQuery method so that if someone tries to execute insert/update statement it will throw java. sql. SQLException with message “executeQuery method can not be used for update”.
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.
69
may be you are using executeQuery() but to manipulate data you actually need executeUpdate() rather than executeQuery()
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
