Executing multiple procedure calls at once in jdbc

Question

FYI, None of the solutions mentioned in this answer have worked for me. I intend to execute multiple procedure call in one sql query. The Mysql code is:

SET @SYSTEM_ID = (SELECT `id` FROM `users_admin` WHERE `username`='my_username');
SET @PAYMENT_MODE = 0;
CALL payment_mode_add(@SYSTEM_ID, 'TEST', TRUE, @PAYMENT_MODE);
CALL payment_add(@SYSTEM_ID, @PAYMENT_MODE, 
'receipt/00',1000.50,@TEMP_ID);

The way it's supposed to work is, Procedure "payment_mode_add" sets out an output parameter which is supposed to be used as an input parameter by the procedure "payment_add".I know that executing multiple queries at once is not possible in Java, but the method i intend to use here works well in languages like PHP. Definition for payment_mode_add is:

# -- PAYMENT-MODE ADD
DELIMITER //
DROP PROCEDURE IF EXISTS `payment_mode_add` //
# -- remove above
CREATE PROCEDURE `payment_mode_add`(IN _author INT, IN _name VARCHAR(20), IN _active BOOLEAN, OUT _id INT)
BEGIN
    # -- declare
    IF NOT EXISTS (SELECT `id` FROM `users_admin` WHERE `id`=_author AND `active`=TRUE) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Author'
    ELSEIF EXISTS (SELECT `id` FROM `gym_form_hhq` WHERE `name`=_name) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '\'Payment Mode\' already exists'
    ELSE
        SET _active = IFNULL(_active,FALSE)
        INSERT INTO `payment_mode`(`name`, `active`, `author`) 
                        VALUES ( _name , _active , _author )
        SET _id = LAST_INSERT_ID()
        SELECT * FROM `payment_mode` WHERE `id`=_id
    END IF
END //
DELIMITER ;

Definition for "payment_add":

# -- PAYMENT ADD
DELIMITER //
DROP PROCEDURE IF EXISTS `payment_add` //
# -- remove above
CREATE PROCEDURE `payment_add`(IN _author INT, IN _mode INT, IN _receipt VARCHAR(50), IN _amount FLOAT, OUT _id INT)
BEGIN
    # -- declare
    IF NOT EXISTS (SELECT `id` FROM `users_admin` WHERE `id`=_author AND `active`=TRUE) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Author'
    ELSEIF NOT EXISTS (SELECT `id` FROM `payment_mode` WHERE `id`=_mode AND `active`=TRUE) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Payment Mode'
    ELSE
        SET _receipt = IFNULL(_receipt, NOW())
        SET _amount = IFNULL(_amount, 0)
        INSERT INTO `payment`(`mode`, `reciept`, `amount`, `author`) 
                    VALUES   (_mode , _receipt , _amount , _author)
        SET _id = LAST_INSERT_ID()
        SELECT * FROM `payment` WHERE `id`=_id
    END IF
END

This is just a basic example for some more complex problems that i'm facing in JDBC. Is there any way that i can execute all those 4 queries at once though JDBC or any other method that can give me the output i intend to achieve?

Could you create a procedure to encapsulate this logic and then call that one through JDBC? — Mick Mnemonic, Jul 26 '18 at 08:34
as i've mentioned, i used PHP to run these queries expecting them to work flawlessly in JDBC too. So, i've already written like 50 plus such queries. If there's no option i'll have to change my entire structure and that will be hell lot of work that i intend to avoid, if possible. — lenikhilsingh, Jul 26 '18 at 08:40
There's probably nothing out-of-the-box in JDBC for this. You could have a look at [Running a .sql script using MySQL with JDBC](https://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc) for alternatives. — Mick Mnemonic, Jul 26 '18 at 08:45
I guess, this method will force me to insert all my queries in separate files. That will make my project structure very messy. Also, it's very time consuming. — lenikhilsingh, Jul 26 '18 at 08:53
I don't think the flagged duplicate is correct @MarkRotteveel. This question is about executing multiple procedural SQL statements (MySQL dialect) through JDBC. In Oracle, you can do this by wrapping the contents inside an anonymous PL/SQL block but apparently MySQL doesn't have the same feature. — Mick Mnemonic, Jul 26 '18 at 12:06
@MickMnemonic Executing stored procedures or executing other sql statements are one and the same thing. AFAIK, this should work for stored procedures as well, assuming they are executed using `Statement`. — Mark Rotteveel, Jul 26 '18 at 12:14
@Mark, but OP isn't just executing stored procedures; instead they are attempting to run an (anonymous) procedural block/script, including variable declarations etc. — Mick Mnemonic, Jul 26 '18 at 12:25
Marking for Reopen. This is not the same as the question marked as Duplicate. It's quite a bit different due to the content of the queries. — Andrew T Finnell, Jul 26 '18 at 12:43
@lenikhilsingh Please update this question with the JDBC code you are trying to use. Boil it down to a simple set of code which fails so we can modify it to get it working. I suggest you even make a simple schema and two small StoreProcedure's as a test for us to work with. I suspect if this works in PHP we can get it to work with a JDBC driver. — Andrew T Finnell, Jul 26 '18 at 12:46
https://spring.io/projects/spring-batch perhaps? And do you intend to fire off 3 concurrently - or do the still have to execute sequentially one after the other? — JGFMK, Aug 07 '18 at 18:10

score 0 · Accepted Answer · answered Mar 28 '19 at 00:43

Since this question was getting many upvotes and i had found a solution to it, i'm answering it.

So all you have to do is write a method that takes in an array of string sql queries as its parameter, executes all the queries(in the array) one by one, and returns the last ResultSet. Example:

public ResultSet runMutltipleQueries(String [] arrayOfQueries) throws SQLException{
    ResultSet rs = null;
    Statement stmnt= con.createStatement();
    boolean rsReturned=false;
    for(int i=0; i<arrayOfQueries.length;i++){
       rsReturned = stmnt.execute(arrayOfQueries[i]);
    }
    if(rsReturned){
        rs= stmnt.getResultSet();
    }

    return rs;

}

And while calling the method,

queries= new String [] {"SET @SYSTEM_ID = (SELECT `id` FROM `users_admin` WHERE `username`='"+model.getUserName()+"');",
                 "SET @TRANSACTION_ID = (select `user_data`.`transaction` from `user_data` where id= "+idText.getText()+");",
                 "SET @FREEZE_ID = NULL;",
                 // set payment id to 0 for now, update it in pay operation.
                 "SET @PAYMENT_ID = NULL;",
                 "CALL user_freeze_add(@SYSTEM_ID, @TRANSACTION_ID, '"+FreezeStartDate.getValue()+"', '"+FreezeStartDate.getValue().plusDays(Integer.parseInt(freezeAvailabletext.getText()))+"', @PAYMENT_ID, @FREEZE_ID);"
};

ResultSet rs= DatabaseHandler.getInstance().runMutltipleQueries(queries);

Executing multiple procedure calls at once in jdbc

1 Answers1