Wednesday, September 25, 2019

JDBC Examples for Calling Stored Procedures (MySQL)

This Java tutorial provides various examples that help you understand how to work with stored procedure using JDBC. You will learn how to:
  • Calling a simple stored procedure which has only IN parameters.
  • Creating a stored procedure from Java.
  • Calling a stored procedure which has IN, OUT and INOUT parameters and retrieve the values of these parameters.
  • Calling a stored procedure which returns a result set and process this result set.
We will use MySQL database for the examples, and suppose that you know how to create a stored procedure using MySQL Workbench tool. Here’s the database structure:
booksdb updated structure
Imagine we have some dummy data for the table author as following:
author dummy data
And dummy data for the table book as following:
book dummy data

1. Calling a Simple Stored Procedure from Java

In MySQL Workbench, create a new routine (expand the database and you see a node called Routines. Right click and select Create Routine…) and paste the following code:
1
2
3
4
5
6
7
8
9
10
11
12
13
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
As you can see, this stored procedure is named as create_author. It has two input parameters name and email.
In the body of the procedure (code between BEGIN and END), we insert a row into the table author. Then select the ID value of this recently inserted row (author_id), store it into a variable named newAuthorID. Then we insert a new row into the table book, in which we use the author name for the title and description of the book. Notice that the variable newAuthorID is used in the second INSERT statement to set foreign key for the new row.
Within the workbench, you can call this stored procedure by executing the following query:
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:
1
2
3
4
5
6
7
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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:
java call sp result in author table
And checking the table book also lets us see a new row added:
java call sp result in book table

2. Creating a Stored Procedure from Java

Besides using a database tool like MySQL Workbench, we can create a stored procedure from within a Java program by executing the “CREATE PROCEDURE” SQL statement, just like executing a normal SQL statement.
The following Java program creates a simple MySQL stored procedure called delete_book which removes a row from the table book based on the specified book ID:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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.

3. Calling a Stored Procedure Having OUT and INOUT parameters from Java

Consider the following stored procedure:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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;
END
This stored procedure has 4 parameters:
  • IN title VARCHAR(45): input parameter. The procedure searches for books whose titles contain the words specified by this parameter.
  • OUT totalBooks INT: The procedure counts total of the matching books and stores the value into this output parameter.
  • OUT totalValue DOUBLE: The procedure counts total value of the matching books and stores the value into this output parameter.
  • INOUT highPrice DOUBLE: This is both input/output parameter. The procedure selects the max price in all books and if it is greater than the parameter value, assigns it to the parameter.
Now, let’s see how to execute this stored procedure using JDBC code.
To retrieve the values of the OUT and INOUT parameters, JDBC requires these parameters must be registered before calling the procedure, by invoking the following method on CallableStatementobject:
void registerOutParameter(int parameterIndex, int sqlType)
For example, the following code registers 3 output parameters for the procedure summary_report above:
1
2
3
4
5
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:
1
2
3
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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

4. Calling a Stored Procedure Returning a Result Set from Java

A stored procedure can returns a result set. Consider the following procedure:
1
2
3
4
CREATE PROCEDURE `get_books`(IN rate INT)
BEGIN
    SELECT * FROM book WHERE rating >= rate;
END
Let’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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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 |
at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

How to DROP SEQUENCE in Oracle?

  Oracle  DROP SEQUENCE   overview The  DROP SEQUENCE  the statement allows you to remove a sequence from the database. Here is the basic sy...