CODEWITHSUNDEEP
javamysqljdbc
Mr. Boy
Mr. Boy

Reputation: 63718

Can Java batch calls to SQL stored procedures?

Java: Insert multiple rows into MySQL with PreparedStatement covers batching multiple INSERTs into one operation. I wondered if it is possible to do the same with calls to stored procedures, more specifically with MySQL? If so, what statement class would be used?

Upvotes: 3

Views: 8402

Answers (2)

belzebubele
belzebubele

Reputation: 86

If you have your stored procedure like this: 

JDBC CallableStatement Stored procedure IN parameter example.
CREATE OR REPLACE PROCEDURE insertEMPLOYEE(
   e_id IN EMPLOYEE.EMPLOYEE_ID%TYPE,
   e_name IN EMPLOYEE.NAME%TYPE,
   e_salary IN EMPLOYEE.SALARY%TYPE)
IS
BEGIN

  INSERT INTO EMPLOYEE ("EMPLOYEE_ID", "NAME", "SALARY") 
  VALUES (e_id, e_name, e_salary);

  COMMIT;

END;

You can just use executeBatch() to do as you intend. Example:

Connection conn = null;
CallableStatement callableStatement = null;
String proc = "{call insertEMPLOYEE(?,?,?)}";
try{            
    //get connection
    conn = JDBCUtil.getConnection();

    //create callableStatement
    callableStatement = conn.prepareCall(proc);

    callableStatement.setInt(1, 7);
    callableStatement.setString(2, "Harish Yadav");
    callableStatement.setInt(3, 50000);
    callableStatement.addBatch();

    callableStatement.setInt(1, 8);
    callableStatement.setString(2, "Abhishek Rathor");
    callableStatement.setInt(3, 50000);
    callableStatement.addBatch();

    //execute query
    callableStatement.executeBatch();

    //close connection
    callableStatement.close();
    conn.close();

     System.out.println("Records inserted successfully.");
}catch(Exception e){
    e.printStackTrace();
}

Upvotes: 3

kosa
kosa

Reputation: 66637

You can use executeBatch(). See this example

Note: I haven't validated example by running in my local, but as per documentation it should work.

Upvotes: 2

Related Questions