Executing set of queries using JDBC Statement or PreparedStatement

Question

I need to execute a set of statements which uses user-defined variables using JDBC.

Below is my query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

I want some way to execute the above statements using JDBC. I stored the above statements in a Java string and tried to execute in the following way.

 String sqlString = //above statements
 Statement stmt = connection.prepareStatement(sqlString);
 System.out.println("executing query ***************************");
 ResultSet rs = stmt.executeQuery(sql);

But it throws the below error.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
  GROUP_CONCAT(DISTINCT
               CONCAT(
                   'IF(cv.' at line 2

FYI, the above query is to convert row data into columnar data (pivot functionality).

Is there a way to execute the above query using JDBC and MySQL?

You may be running into [this issue](http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement), since Statement is throwing an error on the second line in your query. — Thomas, Apr 24 '15 at 20:45
Well you can run the script through comand line using runtime but not with jdbc — Somnath Sarode, Apr 25 '15 at 14:28

score 0 · Answer 1 · answered Apr 25 '15 at 13:01

From my understanding you can't select into a variable like you are trying in mysql.

select
  *
into 
 @variable
from
  my_existing_table

If you want to select into a temporary table you have to create it manually. Like below.

CREATE TEMPORARY TABLE IF NOT EXISTS my_temp_table AS (SELECT * FROM my_existing_table)

score -2 · Answer 2 · edited Apr 01 '21 at 21:29

-2

stmt.executeUpdate("SET @sql = NULL");
...
stmt.executeUpdate("PREPARE stmt FROM @sql");
stmt.executeQuery("EXECUTE stmt");
stmt.executeUpdate("DEALLOCATE PREPARE stmt");

edited Apr 01 '21 at 21:29

weirdan

2,499
23
27

answered Apr 01 '21 at 08:43

mengjian.ke

1

2

Please add some comments about your code and how does it answers the question. – Cosmin Staicu Apr 01 '21 at 12:00

Executing set of queries using JDBC Statement or PreparedStatement

2 Answers2