Java Can Not Issue Data Manipulation Statements With Executequery

Cannot issue data manipulation statements with executeQuery()

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?

Answer #1:

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.

Answer #2:

When executing DML statement , you should use executeUpdate/execute rather than executeQuery.

Here is a brief comparison :

Answer #3:

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();                      

Answer #4:

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.

@Transactional

            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.                      

@Modifying

            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.                      

Answer #5:

Use executeUpdate() to issue data manipulation statements. executeQuery() is only meant for SELECT queries (i.e. queries that return a result set).

Answer #7:

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;}                      

Answered By: Mati

Answer #8:

                          @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.

Answer #9:

executeQuery() returns a ResultSet. I'm not as familiar with Java/MySQL, but to create indexes you probably want a executeUpdate().

Answer #10:

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);                      

Java Can Not Issue Data Manipulation Statements With Executequery

Source: https://www.py4u.net/discuss/782062

Share :