CREATE PROCEDURE `booksdb`.`create_author` (IN name VARCHAR(45), email VARCHAR(45)) BEGIN DECLARE newAuthorID INT; INSERT INTO author (name, email) VALUES (name, email); SET newAuthorID = (SELECT author_id FROM author a WHERE a.name = name); INSERT INTO book (title, description, published, author_id, price, rating) VALUES (CONCAT('Life Story of ', name), CONCAT('Personal Stories of ', name), date('2016-12-30'), newAuthorID, 10.00, 0); END
call create_author('Patrick Maka', 'patrick@gmail.com')
Now, let’s see how to call this stored procedure using JDBC.Here are the steps to call a simple stored procedure from Java code with JDBC:CallableStatement statement = connection.prepareCall("{call procedure_name(?, ?, ?)}"); // setting input parameters on the statement object // statement.setString(parameterIndex, parameterValue); statement.execute(); statement.close();Notice the CALL statement syntax:
"{call procedure_name(?, ?, ?)}
The procedure’s parameters are denoted by the question marks, separated by comma. Then we use the setXXX()methods on the statement object to set value for the parameters, just like setting parameters for a PreparedStatement.Invoking execute() method on the statement object will run the specified stored procedure. This method returns true if the stored procedure returns a result set, false if not, and throw SQLException in cases of an error occurred.The following is a test Java program that calls the stored procedure create_author which we created previously:import java.sql.*; /** * A Java program demonstrates how to call a MySQL stored procedure * using JDBC. * * @author www.codejava.net */ public class StoredProcedureCallExample1 { public static void main(String[] args) { String dbURL = "jdbc:mysql://localhost:3306/booksdb"; String user = "root"; String password = "P@ssw0rd"; try ( Connection conn = DriverManager.getConnection(dbURL, user, password); CallableStatement statement = conn.prepareCall("{call create_author(?, ?)}"); ) { statement.setString(1, "Bill Gates"); statement.setString(2, "bill@microsoft.com"); statement.execute(); statement.close(); System.out.println("Stored procedure called successfully!"); } catch (SQLException ex) { ex.printStackTrace(); } } }Compile and run this program. You should see the following output:
Stored procedure called successfully!
Let’s verifying the database. Querying all rows from the table author we see a new row was added:And checking the table book also lets us see a new row added:import java.sql.*; /** * A Java program demonstrates how to create a MySQL stored procedure * using JDBC. * * @author www.codejava.net */ public class StoredProcedureCreateExample { public static void main(String[] args) { String dbURL = "jdbc:mysql://localhost:3306/booksdb"; String user = "root"; String password = "P@ssw0rd"; try ( Connection conn = DriverManager.getConnection(dbURL, user, password); Statement statement = conn.createStatement(); ) { String queryDrop = "DROP PROCEDURE IF EXISTS delete_book"; String queryCreate = "CREATE PROCEDURE delete_book (IN bookID INT) "; queryCreate += "BEGIN "; queryCreate += "DELETE FROM book WHERE book_id = bookID; "; queryCreate += "END"; // drops the existing procedure if exists statement.execute(queryDrop); // then creates a new stored procedure statement.execute(queryCreate); statement.close(); System.out.println("Stored procedure created successfully!"); } catch (SQLException ex) { ex.printStackTrace(); } } }Note that we have to execute two queries: the first one is to drop the stored procedure if exists; and the second actually creates the stored procedure.Running this program would produce the following output:
Stored procedure created successfully!
Switch to MySQL Workbench and refresh the Object Browser pane, you should see the newly created stored procedure appears there.CREATE PROCEDURE `summary_report`( IN title VARCHAR(45), OUT totalBooks INT, OUT totalValue DOUBLE, INOUT highPrice DOUBLE ) BEGIN DECLARE maxPrice DOUBLE; SELECT COUNT(*) AS bookCount, SUM(price) as total FROM book b JOIN author a ON b.author_id = a.author_id AND b.title LIKE CONCAT('%', title, '%') INTO totalBooks, totalValue; SELECT MAX(price) FROM book WHERE price INTO maxPrice; IF (maxPrice > highPrice) THEN SET highPrice = maxPrice; END IF; ENDThis stored procedure has 4 parameters:
void registerOutParameter(int parameterIndex, int sqlType)
For example, the following code registers 3 output parameters for the procedure summary_report above:CallableStatement statement = conn.prepareCall("{call summary_report(?, ?, ?, ?)}"); statement.registerOutParameter(2, Types.INTEGER); statement.registerOutParameter(3, Types.DOUBLE); statement.registerOutParameter(4, Types.DOUBLE);After the procedure has been called, we can use the getXXX() method on the CallableStatementobject to retrieve the values of the output parameters. For example, the following code gets values of the 3 output parameters returned by the procedure summary_report:
Integer totalBook = (Integer) statement.getObject(2, Integer.class); Double totalValue = statement.getDouble(3); Double highPrice = statement.getDouble("highPrice");As you can see, there are three ways to retrieve the values: by index and type; by index; and by parameter name.And following is full source code of a test program:
import java.sql.*; /** * A Java program demonstrates how to use JDBC to call a MySQL stored procedure * and retrieve values of the OUT and INOUT parameters. * * @author www.codejava.net */ public class StoredProcedureCallExample2 { public static void main(String[] args) { String dbURL = "jdbc:mysql://localhost:3306/booksdb"; String user = "root"; String password = "P@ssw0rd"; try ( Connection conn = DriverManager.getConnection(dbURL, user, password); CallableStatement statement = conn.prepareCall("{call summary_report(?, ?, ?, ?)}"); ) { statement.registerOutParameter(2, Types.INTEGER); statement.registerOutParameter(3, Types.DOUBLE); statement.registerOutParameter(4, Types.DOUBLE); statement.setString(1, "Java"); statement.setDouble(4, 50); statement.execute(); Integer totalBook = (Integer) statement.getObject(2, Integer.class); Double totalValue = statement.getDouble(3); Double highPrice = statement.getDouble("highPrice"); System.out.println("Total books: " + totalBook); System.out.println("Total value: " + totalValue); System.out.println("High price: " + highPrice); statement.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }Running this program would give the following output:
Total books: 7
Total value: 245.79000091552734
High price: 122.3499984741211
CREATE PROCEDURE `get_books`(IN rate INT) BEGIN SELECT * FROM book WHERE rating >= rate; ENDLet’s see how to retrieve this result set in Java. The following code snippet shows you how to retrieve and process a result set returned from a stored procedure using JDBC code:
CallableStatement statement = conn.prepareCall("{call get_books(?)}"); statement.setInt(1, 5); boolean hadResults = statement.execute(); while (hadResults) { ResultSet resultSet = statement.getResultSet(); // process result set while (resultSet.next()) { // retrieve values of fields String title = resultSet.getString("title"); } hadResults = statement.getMoreResults(); }And here is the full source code of a demo program:
import java.sql.*; /** * A Java program demonstrates how to use JDBC to call a MySQL stored procedure * that returns a result set and process this result set. * * @author www.codejava.net */ public class StoredProcedureCallExample3 { public static void main(String[] args) { String dbURL = "jdbc:mysql://localhost:3306/booksdb"; String user = "root"; String password = "P@ssw0rd"; try ( Connection conn = DriverManager.getConnection(dbURL, user, password); CallableStatement statement = conn.prepareCall("{call get_books(?)}"); ) { statement.setInt(1, 5); boolean hadResults = statement.execute(); // print headings System.out.println("| Title | Description | Rating |"); System.out.println("================================"); while (hadResults) { ResultSet resultSet = statement.getResultSet(); // process result set while (resultSet.next()) { String title = resultSet.getString("title"); String description = resultSet.getString("description"); int rating = resultSet.getInt("rating"); System.out.println( "| " + title + " | " + description + " | " + rating + " |"); } hadResults = statement.getMoreResults(); } statement.close(); } catch (SQLException ex) { ex.printStackTrace(); } } }Running this program would print the following output:
| Title | Description | Rating |
================================
| Thinking in Java | Teach you core Java in depth | 5 |
| Java Puzzlers | Java Traps, Pitfalls, and Corner Cases | 5 |
| Thinking in C++ | Mastering C++ | 5 |
NOTE: If you are using Spring framework, consider to use the SimpleJdbcCall class that greatly simplifies the code you need to write in order to call stored procedure.