Invoking Stored Procedures using JDBC Callable Statement – Full Example

2 Flares Twitter 0 Facebook 0 Google+ 2 LinkedIn 0 Filament.io 2 Flares ×

Invoking a stored procedure is easily accomplished using the CallableStatement class provided by the JDBC API. Procedures can be invoked with IN parameters or OUT parameters. It is also easy to call a procedure that returns a Cursor object. I will explain all these cases via examples.

Example 1 – Procedure with IN Parameter

For the example(s) we create a procedure LOGDATA. This procedure just keeps track of who accessed a particular URL and at what time. The procedure takes three input parameters USERID, URL and the Date of access. These are the IN parameters.

Please note that for the examples we are using the Oracle Database. If you are using another database like MYSQL change the procedure syntax according that particular database’s format. Java Code remains the except the connection parameters, which changes according to the database.

Procedure

Code Snippet

Java Example

Example 2 – Procedure with IN and OUT Parameter

For this example our aim is to show case the usage of IN and OUT parameter. We create a procedure that accepts an IN parameter and returns two OUT parameters.

Procedure

Given a URL, the procedure returns the userid and the date of the access for that URL. For the sake of simplicity we are returning only a single record and hence put a DISTINCT in the Query in the procedure. In the next example we see how can we return and entire Resultset.

Code Snippet

Java Code

Example 3 – Procedure returning a Cursor Object

Let us see how can we return the entire ResultSet from the procedure and get it using the CallableStatement of JDBC. Please note that all the above example(s) including this one we are using the Oracle DB. If you are using another DB the Java Code remains the same only change is in how you create the procedure in that particular DB. Change the procedure Syntax according to the Database you are using.

Procedure

Above procedure takes the URL as the input and returns all records having that particular URL. That is details of users who accessed the URL and when.

Java Code