0

I'm trying to execute the following query:

DROP TABLE IF EXISTS TEMP_TBL_NAME;

USE `DB_NAME`;

CREATE TEMPORARY TABLE 
IF NOT EXISTS TEMP_TBL_NAME AS (
    SELECT `FIELD_1`, `FIELD_2` 
    FROM `TBL_NAME` 
    WHERE `FIELD_1` = ? AND `FIELD_2` = ?);

SELECT u.`user_type`, u.`user_id_rank` 
FROM TEMP_TBL_NAME st 
INNER JOIN `TBL_X` u ON st.`FIELD_A` = u.`FIELD_B` 
WHERE u.`FIELD_C` = ?;

(the query is ok. if i run it on mysql it works)

to run the query I use PreparedStatement and use:

set = statement.executeQuery();

I know that executeUpdate() is for queries with action like create/drop/etc. and executeQuery() is for SELECT queries. --> and i need the ResultSet.. in my case - its combined.

(I know I can use joins and solve this issue. but i want to use temp table in order to keep the performance better)

the exception I got is: java.sql.SQLException: Can not issue data manipulation statements with executeQuery().

Any idea what can I do in order to run the query with the temp (drop,create) and select in one query..

sorak
  • 2,607
  • 2
  • 16
  • 24
Shai Epstein
  • 179
  • 1
  • 3
  • No. You can't execute DML with executeQuery. You'll need to execute the DML with executeUpdate, and the queries with executeQuery. IOW, just as you said in your question. – Sloan Thrasher Mar 02 '18 at 22:29
  • That is not **one** question to be executed, but multiple ones – Nico Haase Mar 02 '18 at 23:48

0 Answers0