In this video we will learn how to call MySQL Stored Procedures with Java.
Please SUBSCRIBE to this channel
—
Transcript
Time – 00:00
Hi, this is Chad (Shod) with luv2code.com. Welcome back to another tutorial on Java JDBC. In this video, we’ll learn how to use stored procedures. We’ll first define what stored procedures are, next we’ll use callable statements. Then we’ll learn how to pass parameters to stored procedures. I’ll actually break this up over 4 different videos. I’ll show each parameter type in a separate video, so in this section we’ll cover IN parameters and then in the follow on sections, I’ll cover IN OUT, OUT and also result sets. Please stay tuned and check out the following videos for this.
Time – 00:39
A stored procedure is a group of SQL statements that perform a particular task. They are normally created by a DBA. The stored procedures are created in a SQL language that supported by the native database. Each database has its own language. For example, MySQL has stored procedures language, Oracle uses PL/SQL and Microsoft SQL Server uses Transact SQL. Finally, the stored procedures can also have any combination of input and output parameters. For this tutorial, we’ll use the following table employees. The table has sample data for testing. I have a SQL zip file that’s you can download. It will create the table, add sample data and also set up the stored procedures. You can download it from the link below.
Time – 01:22
All right, let’s go and switch over to the MySQL workbench and what I’m going to do is open up that SQL file and run it. This is our JDBC stored procedures directory. I have a subdirectory called SQL, and has this file, table set up that SQL. I’ll open this file, what this file does it basically creates a table as we see here and then it goes through and inserts a lot of sample data. Then, it also goes through and creates all of the stored procedures that we need for this tutorial, right. I’ll just go ahead and select execute and this will run and so now should I have a new table over here called employees and this table also has a collection or this database has a collection of stored procedures for this tutorial series, so get count employees, greet and increase salaries and we’ll go through all these stored procedures during the actual tutorial itself.
Time – 02:24
In JDBC, you make use of callable statements to make a call to the stored procedures. There is a special syntax to call stored procedures. You make use of curly braces inside of the string. You also make use of the JDBC keyword call and that’s followed by the name of the stored procedure. In this example, we’re not passing any params, so we’ll just use the open and closed parentheses. Finally, we execute the stored procedure. Let’s talk about the parameters on the next slide. The JDBC API support different types of parameters. You can make use of the IN parameter, INOUT and also the OUT. The stored procedures can also return a result set, during the code examples in this video, I’ll show you how to register different parameters types and also values.
Time – 03:09
Let’s start with IN parameters. For this parameter, we’re only passing an input parameter and the parameter is read-only. Our DBA has created a stored procedure on the database. This stored procedure will increase the salary for everyone in the department, which is kind of cool. The procedure takes two parameters. The first parameter is the department name. The second parameter is the increase amount. For example, we can call this stored procedure and increase the salaries of employees in the engineering department by $10,000. In terms of Java coding, we need to make a call to the stored procedure. First, we prepare a callable statement, since the stored procedure takes two parameters, we’ll make use of our question mark place orders for the parameters.
Time – 03:54
Then we’ll set the parameter values by calling the appropriate setter methods. For this example, we’re increasing salaries of employees in the engineering department by $10,000. Once the parameter values are set, then we call the execute method on this statement and that’s it. All right, so let’s switch over to eclipse and let’s see this in action. I have a simple Java program called increase salaries for department. Let’s walk through the code. Basically, what I’ll do is, I start off by getting a connection to a database. Then I’ll also setup variables for the department name and the increase amount. Then also what I’d like to do is, I’d like to show the salaries before we call the stored procedure, just so we can have a before and after example. What I’d like to do now is actually prepare the call to stored procedure, so I’ll set up this prepared call that gives call increase salaries.
Time – 04:46
I use the question mark for the placeholders. Now, actually set the parameter values. I pass in for the first parameter, I pass in the department. Then for the second parameter, I’ll pass in the increase amount. Now do the dirty work, I’ll actually call the stored procedure by calling my statement dot execute and then just how we can see the after results. I’ll call show salaries again and this will simply display the salaries for the employees, just so we can verify that the salaries were actually updated. Okay, let’s go ahead and run the application, so run as Java application and we’ll take a look at the output. We see the salaries before and then we call the stored procedure and then we see the salaries after.
Time – 05:36
We can verify that the increase in salaries of the engineering department was increased by 10,000 for each employee. Mission accomplished. Well, that wraps up our initial discussion on stored procedures. We were successful in calling stored procedures and passing in parameters. State tuned for part two of the video, I’ll show more examples on using stored procedures and parameters, be sure to watch the next video by clicking the link here. Also please subscribe to our channel to view more videos on Java. Click the thumbs up to like our video. Finally, visit our website https://luv2code.com to download the Java source code used in this video.
