Java JDBC Tutorial – Part 6.4: Calling MySQL Stored Procedures with Java

---

---

In this video tutorial we will learn how to call MySQL Stored Procedures with Java. This video covers stored procedures that return result sets.

---

Transcript

Time - 00:00

Time - 00:11
In this video, we're going to focus on stored procedures that return result sets. Check out my other videos that covered different types of parameters, such as IN, and INOUT.

Time - 00:22
For this tutorial we will use the following table, employees. The table has a sample data for testing. I havea a SQL zip file that you can download, it'll create the table, add sample data and also set up the stored procedures. You can download it from the link below.

Time - 00:39
We'll call a stored procedure that returns a result set for us to process. Our DBA created a stored procedure called "Get Employees for Department." The first parameters the department main, and the stored procedure will return the results as a result set.

Time - 00:55
To call the stored procedure, we prepared columns before. Using a question mark as a parameter place holder. We set the first input parameter as the name of the department. Then we execute the stored procedure. Once the execution is complete, then we can retrieve the result set by calling, get result set.

Time - 01:15
Okay, so let's switch Eclipse and see this in action. I have a sample Java program called, Get Employees for Department. Let's walk through the code. The first thing we'll do is get a connection to the database. We'll also set up our variables for the department name. Then I'll prepare a call to the stored procedure. I'll make use of the question mark as a parameter place holder. Then I'll set the parameter value. I will actually call the stored procedure. Then I will actually retrieve the result set from the stored procedure. Now, I'll call a helper method that will actually display the result set.

Time - 01:53
Now, let's go ahead and run the application. As we can see we make a call to the stored procedure, we pass in the department for engineering, we get the results, we call our helper method and it displays the results. Which looks pretty good. Let's go ahead and verify this in our SQL tool.

Time - 02:17
I'll move over to the tool, I have this query, select * from employees where department equals engineering. I will execute and I get four results. This matches with the information that we have from the Java program. This looks very good, so we're successful.

Time - 02:35

[jojojojola]

This man is one of the best java professors I have ever seen, congrats Chad your videos are really easy to understand and cover the most important stuff !

[sinamehrad]

Hey Chad. I am from Tehran from Iran. you're the best instructor in the world. I love luv2code <3

[bmagwebu]

Great tutorial, easy to follow, easy to implement

[rk-rfhy]

Hi luv2code your videos are very helpful and very easy to understand, thank you very much:)

[CaptainRinoXD]

The download has failed, can I get the code for the display method. thanks a lot, hope you can reply soon.

[puneetgarg]

Sir while coding i am getting this error while giving the value to myRs :
incompatible types : java.sql.ResultSet cannot be convertes to ResultSet
please help me to get out of this

[ahmedalmahmoud]

its realy nice and easy to understand, thank you very much:)

[TheRollsRover]

Hello chad,
While I run the application, it runs .Bt the problem is why it is always showing this message at the console window in the first five row...

Sun May 15 11:13:11 NPT 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Calling stored procedure. call
Finished calling stored procedure...

Mary Public Engineering 75000.00
Lisa Johnson Engineering 50000.00
Bill Brown Engineering 50000.00
Mary Fowler Engineering 65000.00

I hope u will clear it soon...