Cannot issue data manipulation statements with executeQuery()
JavaMysqlJdbcJava Problem Overview
In MySQL I have two tables, tableA
and tableB
. I am trying to execute two queries:
executeQuery(query1)
executeQuery(query2)
But I get the following error:
can not issue data manipulation statements with executeQuery().
What does this mean?
Java Solutions
Solution 1 - Java
To manipulate data you actually need executeUpdate()
rather than executeQuery()
.
Here's an extract from the executeUpdate()
javadoc which is already an answer at its own:
> 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.
Solution 2 - Java
When executing DML statement , you should use executeUpdate
/execute
rather than executeQuery
.
Here is a brief comparison :
Solution 3 - Java
If you're using spring boot, just add an @Modifying annotation.
@Modifying
@Query
(value = "UPDATE user SET middleName = 'Mudd' WHERE id = 1", nativeQuery = true)
void updateMiddleName();
Solution 4 - Java
For Delete query - Use @Modifying
and @Transactional
before the @Query
like:-
@Repository
public interface CopyRepository extends JpaRepository<Copy, Integer> {
@Modifying
@Transactional
@Query(value = "DELETE FROM tbl_copy where trade_id = ?1 ; ", nativeQuery = true)
void deleteCopyByTradeId(Integer id);
}
It won't give the java.sql.SQLException: Can not issue data manipulation statements with executeQuery()
error.
Edit:
Since this answer is getting many upvotes, I shall refer you to the documentation as well for more understanding.
By default, CRUD methods on repository instances are transactional. For read operations,
the transaction configuration readOnly flag is set to true.
All others are configured with a plain @Transactional so that default transaction
configuration applies.
Indicates a query method should be considered as modifying query as that changes the way
it needs to be executed. This annotation is only considered if used on query methods defined
through a Query annotation). It's not applied on custom implementation methods or queries
derived from the method name as they already have control over the underlying data access
APIs or specify if they are modifying by their name.
Queries that require a @Modifying annotation include INSERT, UPDATE, DELETE, and DDL
statements.
Solution 5 - Java
Use executeUpdate()
to issue data manipulation statements. executeQuery()
is only meant for SELECT queries (i.e. queries that return a result set).
Solution 6 - Java
That's what executeUpdate
is for.
Here's a very brief summary of the difference: <http://www.coderanch.com/t/301594/JDBC/java/Difference-between-execute-executeQuery-executeUpdate>
Solution 7 - Java
This code works for me: I set values whit an INSERT and get the LAST_INSERT_ID() of this value whit a SELECT; I use java NetBeans 8.1, MySql and java.JDBC.driver
try {
String Query = "INSERT INTO `stock`(`stock`, `min_stock`,
`id_stock`) VALUES ("
+ "\"" + p.get_Stock().getStock() + "\", "
+ "\"" + p.get_Stock().getStockMinimo() + "\","
+ "" + "null" + ")";
Statement st = miConexion.createStatement();
st.executeUpdate(Query);
java.sql.ResultSet rs;
rs = st.executeQuery("Select LAST_INSERT_ID() from stock limit 1");
rs.next(); //para posicionar el puntero en la primer fila
ultimo_id = rs.getInt("LAST_INSERT_ID()");
} catch (SqlException ex) { ex.printTrace;}
Solution 8 - Java
@Modifying
@Transactional
@Query(value = "delete from cart_item where cart_cart_id=:cart", nativeQuery = true)
public void deleteByCart(@Param("cart") int cart);
Do not forget to add @Modifying and @Transnational before @query. it works for me.
To delete the record with some condition using native query with JPA the above mentioned annotations are important.
Solution 9 - Java
executeQuery()
returns a ResultSet
. I'm not as familiar with Java/MySQL, but to create indexes you probably want a executeUpdate()
.
Solution 10 - Java
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/java_swing_db", "root", "root");
Statement smt = conn.createStatement();
String sql = "SELECT * FROM `users` WHERE `email` = " + email + " AND `password` = " + password + " LIMIT 1;";
String registerSql = "INSERT INTO `users`(`email`, `password`, `name`) VALUES ('" + email + "','" + password + "','" + name + "')";
System.out.println("SQL: " + registerSql);
int result = smt.executeUpdate(registerSql);
System.out.println("Result: " + result);
if (result == 0) {
JOptionPane.showMessageDialog(this, "This is alredy exist");
} else {
JOptionPane.showMessageDialog(this, "Welcome, Your account is sucessfully created");
App.isLogin = true;
this.dispose();
new HomeFrame().show();
}
conn.close();
Solution 11 - Java
Besides executeUpdate() on the parentheses, you must also add a variable to use an SQL statement.
For example:
PreparedStatement pst = connection.prepareStatement(sql);
int numRowsChanged = pst.executeUpdate(sql);