Syntax error in Java code, but running in MySQL workbanch

Question

My query running perfect in workbanch but not working with Java sending exception that MySQL syntax error:

Set @total=0;
SET @amountDue=0;
SELECT (l.paid_date),c.customer_name,IF(@total=0,(SELECT total_amount FROM 
payment_loan WHERE loan_id=99)-(SELECT (installment_amount_month) FROM
payment_loan WHERE loan_id=99)*(SELECT (total_installments) 
FROM payment_loan
   WHERE loan_id=99),
       (SELECT (installment_amount_month) FROM payment_loan WHERE
       loan_id=99))AS AmountDue,
           @amountDue:=@amountDue+(SELECT IF(@total=0,(SELECT (total_amount)
          FROM payment_loan WHERE loan_id=99)-(SELECT (installment_amount_month) FROM payment_loan 
        WHERE loan_id=99)*(SELECT (total_installments) FROM payment_loan 
        WHERE loan_id=99),(SELECT (installment_amount_month) FROM 
      payment_loan WHERE loan_id=99))) as ComulativeDue,
     l.amount AS AmountPaid, @total := @total +l.amount AS comulativePaid, 
     (@total/@amountDue ) as percentage FROM payments_details l Join 
     customer c on (c.customer_id=l.customer_id) WHERE l.customer_id=115 
    GROUP BY l.paid_date ORDER BY   l.paid_date DESC LIMIT 1  ;

and my java code is:

     PreparedStatement stmt = (PreparedStatement) connection.Connect.con.prepareStatement(query);
        rs = stmt.executeQuery();
        while (rs.next()) {
            cName = rs.getString(2);
            amountDue = rs.getInt(3);
       }

Error:

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 'SET @amountDue=0; SELECT (l.paid_date),c.customer_name,IF(@total=0,(SELECT total' at line 2

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 'SET @amountDue=0; SELECT (l.paid_date),c.customer_name,IF(@total=0,(SELECT total' at line 2 — Jhaman Das, Sep 17 '15 at 17:49

score 0 · Answer 1 · edited May 23 '17 at 12:29

By default, multiple queries in a single call are not allowed. This is described very well in Multiple queries executed in java in single statement:

While sending a connection request, you need to append a connection property allowMultiQueries=true to the database url. This is additional connection property to those if already exists some, like autoReConnect=true, etc.. Acceptable values for allowMultiQueries property are true, false, yes, and no. Any other value is rejected at runtime with an SQLException.
String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";
...

score -1 · Accepted Answer · answered Sep 17 '15 at 17:54

modify your query so:

SELECT (l.paid_date),c.customer_name,IF(@total=0,(SELECT total_amount FROM 
payment_loan WHERE loan_id=99)-(SELECT (installment_amount_month) FROM
payment_loan WHERE loan_id=99)*(SELECT (total_installments) 
FROM payment_loan, (SELECT @total:=0, @amountDue:=0) setup
   WHERE loan_id=99),
       (SELECT (installment_amount_month) FROM payment_loan WHERE
       loan_id=99))AS AmountDue,
           @amountDue:=@amountDue+(SELECT IF(@total=0,(SELECT (total_amount)
          FROM payment_loan WHERE loan_id=99)-(SELECT (installment_amount_month) FROM payment_loan 
        WHERE loan_id=99)*(SELECT (total_installments) FROM payment_loan 
        WHERE loan_id=99),(SELECT (installment_amount_month) FROM 
      payment_loan WHERE loan_id=99))) as ComulativeDue,
     l.amount AS AmountPaid, @total := @total +l.amount AS comulativePaid, 
     (@total/@amountDue ) as percentage FROM payments_details l Join 
     customer c on (c.customer_id=l.customer_id) WHERE l.customer_id=115 
    GROUP BY l.paid_date ORDER BY   l.paid_date DESC LIMIT 1  ;

Syntax error in Java code, but running in MySQL workbanch

2 Answers2