title: How to execute multiple stored procedure queries from a file in Java?
body: <p>I've used multiple sql queries to create stored procedures related to my project. Here is the sql file(consisting of multiple stored procedure queries), that i want to execute in java:</p>
  
  <pre><code># -- USER-ADMIN ADD
  DELIMITER //
  DROP PROCEDURE IF EXISTS `users_admin_add` //
  # -- remove above
  CREATE PROCEDURE `users_admin_add`(
  IN _author INT,
  IN _name VARCHAR(100),
  IN _mobile VARCHAR(20),
  IN _email VARCHAR(100),
  IN _address TEXT,
  IN _username VARCHAR(30),
  IN _password TEXT,
  IN _pin INT,
  IN _preferred VARCHAR(20),
  IN _active BOOLEAN,
  IN _fired BOOLEAN,
  IN _access INT,
  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 `users_admin` WHERE `username`=_username) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Username Already exists';
  ELSEIF EXISTS (SELECT `id` FROM `users_admin` WHERE `mobile`=_mobile) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Mobile Already registered';
  ELSE
  # -- null checks
  SET _active = IFNULL(_active,FALSE);
  SET _fired = IFNULL(_fired,FALSE);
  SET _preferred = IFNULL(_preferred,'PASSWORD');
  SET _access = IFNULL(_access,(SELECT `level` FROM `core_access_types` WHERE `name`='RECEPTIONIST'));
  
  # -- main
  INSERT INTO `users_admin`(`name`, `mobile`, `email`, `address`, `username`, `password`, `pin`, `preferred`, `active`, `fired`, `access`, `author`) 
  VALUES ( _name,  _mobile,  _email,  _address,  _username,  _password,  _pin,  _preferred,  _active,  _fired,  _access,  _author); 
  SET _id = LAST_INSERT_ID();
  SELECT * FROM `users_admin` WHERE `id`=_id;
  END IF;
  END //
  DELIMITER ;
  
  # -- USER-ADMIN DELETE
  DELIMITER //
  DROP PROCEDURE IF EXISTS `users_admin_delete` //
  # -- remove above
  CREATE PROCEDURE `users_admin_delete`(
  IN _author INT,
  IN _user 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 `users_admin` WHERE `fired`=TRUE AND `id`=_user) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This account has been disabled permanently';
  ELSEIF NOT EXISTS (SELECT `id` FROM `users_admin` WHERE `id`=_user) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid User';
  ELSE
  UPDATE `users_admin` SET `fired`=TRUE, `modified_at`=NOW(), `modifier`=_author WHERE `id`=_user;
  SELECT * FROM `users_admin` WHERE `id`=_user;
  END IF;
  END //
  DELIMITER ;
  </code></pre>
  
  <p>I've used the methods like addBatch() and executeBatch() available in java, but they don't seem to work.
  Here is my java code that i used last. i've tried many more methods,  i'd say, none of them worked:</p>
  
  <pre><code>private static DatabaseHandler handler = null;
  String uname="root";
  String pass="";
  String url="jdbc:mysql://localhost:17770/wdms_db?zeroDateTimeBehavior=convertToNull&amp;amp;autoReconnect=true&amp;amp;allowMultiQueries=true";
  private static Statement stmnt=null;
  private static Connection con=null;
  
  static String readFile(String path, Charset encoding) throws IOException 
  {
    byte[] encoded = Files.readAllBytes(Paths.get(path));
    return new String(encoded, encoding);
  }
  private DatabaseHandler() throws SQLException {
      createsConnection();
  
      InputStream is = null;
      try {
          System.err.println("at procedures.sql---------------------------------------------------------------------");
          is = new FileInputStream("C:\\Users\\procedures.sql");
      } catch (FileNotFoundException ex) {
          Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
      }
  
      executeScript(con,is);
  }
  </code></pre>
  
  <p>definition of executeScript(con, is):</p>
  
  <pre><code>static void executeScript(Connection conn, InputStream in)
      throws SQLException
  {
  //     System.out.println(in);
      Scanner s = new Scanner(in);
      s.useDelimiter("/\\*[\\s\\S]*?\\*/|--[^\\r\\n]*|;");
  
      Statement st = null;
  
      try
      {
      st = conn.createStatement();
  
      while (s.hasNext())
      {
          String line = s.next().trim();
  
          if (!line.isEmpty())
              System.out.println(line);
              st.execute(line);
      }
      // st.executeBatch();
      }
      finally
      {
      if (st != null)
          st.close();
      }
  }
  </code></pre>