Java JDBC Tutorial - Part 6.1: Calling MySQL Stored Procedures with Java

---

Closed-Captioning and English subtitles available.

---

In this video we will learn how to call MySQL Stored Procedures with Java.

----

Follow luv2code:

---

Join my mailing list: Get advance notice on new tutorials. Emails about 1 per week.

---

Questions or problems? Post them in the comments section below.

---

Want to suggest a video? Leave a comment below. I'm always looking for new video ideas.

Let me know what video you'd like for me to create.

---

Transcript

Time - 00:00

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.

[snip] ... See Transcripts tab

[QIBusinessgroup]

OMG this was a great tutorial.  I have search high and low for programming videos where there is not a strong language barrier.

[MalamIbnMalam]

Your videos are the best on YouTube, by far the clearest and most understandable. You make everything seem so easy.

[pranavlotlikar]

Do the download source code links still work? They seem to be giving 404

[syednoman]

amazing stuff. pretty clean and full with comments source code you have provided in your downloadable files. God bless you.

[leylamm]

Thank you very much, I am from Azerbaijan, your lessons is really helpful for beginners :-)

[avdheshpalliwal]

Awesome videos with great clarity, explanation. Hope student get teachers like you everywhere.

[QF]

all of your tutorial are great :) ... really help me a lot, thank you :)

[bishwajitnepali]

Like this one man..so simple and easy to understand

[romanterendiy]

Great tutorials, thank you very much. The spring one is the best there is. Please make more)

[luvcodetv]

@chetan:  thanks for the kind words :-)

[PujaSingh-ch]

Thanks for the great content! Although, I am not able to download any of the source code from your website, it gives 404 everytime I try.

[clopes]

what is the purpose of the two static close methods at the bottom of the java source code. When I run it with the calls to close() commented out, the program still works fine.

[narravulalakshman]

In the above example, How we are using "showSalaries()" directly without creating any object for showSalaries(). when i am executing I am facing with the errors in these two lines before and after show salaries(). The same with the close keyword in finally body.

[sangwaemmanuel]

want to update two tables using callablestatement but getting an error of "Uknown column profile.name in the field list". kindly help

[HelloKitty-vowd]

Clear and precise, thanks for sharing.

[sriteshxyz]

great Presentation and explanation :-)

[psonney]

Hi There, i am using sybase database and while calling storedProcedure using namedParameterJdbcTemplate, i need to process all the resultSet returned by SP. which are different tables with one emplty row in between. would you please suggest suitable way to achieve this functionality. Thanks in Advance.

Hey...!Your tutorials are really helpful.I really love to watch your videos. I have small doubt Why did you initialize null to myConn & myStmt....?

[oneness]

Good day Sir! can I know where i can get the sql codes

[z图奇大帝]

I don't know what the thirtieth line means. Do you mean to create a SQL statement in the database before you can execute the code? I'm using Navicat, and I've used this code, [call= (CallableStatement) conn.prepareCall ("{call (?)});"

Ecception:
PROCEDURE does not exist

I have the same employe database table as you do, but he's not right. Thank you