Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Many Database Management Systems let you create stored procedures that return more than one value, for example, in MySQL. You can create a stored procedure that returns both a result set and one or more of what are called Out Parameters. Here's the syntax in MySQL, I have provided this code in the file MySQLStoredProcs.txt, which is part of the free exercise files that accompany this course. As with any stored procedure in MySQL, you start by setting the DELIMITER to a double slash or some other String that won't be in conflict with SQL.
When you create the procedure, you define arguments. Here I am declaring an IN parameter, the same parameters as I used in a previous exercise, but I'm also defining an OUT parameter, a value that can be passed back from the stored procedure. In this stored procedure, I'm executing two separate select statements, the first one selects the count of rows from the tours table. It selects the count, but then instead of naming that return value as a part of the result set as you might do in conventional SQL, I'm taking the value of count, and I am passing it to the OUT parameter by its name, total.
Then I'm executing a second SELECT statement. This one will return a conventional result set, I am returning all columns, but I could just as easily put it on this column names that I want to return. It's essential that the select statement that returning the result set be executed last. That's how you'll get that data out of the StoredProcedure. The SELECT statements that are simply setting the value of OUT parameters such as my first select statement can be placed anywhere in the chain. So I'm going to take this code and import it into my MySQL database.
I'll select everything starting with a DROP command and ending with the DELIMITETR command, and I'll copy it to the clipboard. Then I'll go to phpMyAdmin, I'll go to my database, and to the SQL panel I'll paste in that code and click Go. I'll confirm and I'll check and make sure that everything was executed correctly. Now I'm ready to run this code for my Java application. I'll switch back to Eclipse where I am working with a project named StoredProcOut, this version of my project is using the CallableStatement interface already.
And right now it's calling the stored procedure GetToursByPrize, which receives a single argument. In this version of my application I'll change MySQL String from the original stored procedure GetToursByPrice to the new stored procedure GetToursWithCountByPrice. And I'll match the arguments in the original stored procedure in MySQL. I had two arguments, so I'll set two arguments here. I'm still calling a stored procedure, so I'll use the CallableStatement just like I did before. But now I have a couple more bits of code to add to deal with that OUT parameter.
I'll place the cursor after the code that's setting the IN parameter, which is the double value of the Max price, then I'll add another line here, and I'll call another method called registerOutParameter. Just as with the set methods, you can identify your parameter either by its ordinal position, that is 1, 2 and so on in the SQL String, or by its name. I'm going to use a version of registerOutParameter where I pass in the name of the parameter as a String, but again I could also pass the value of 2. Next, I indicate the data type of the value. I'm expecting that you set the data Type by using a constant that's the part of a class called Types, and I'm going to set this to Types.INTEGER.
So I have set the IN value with setDouble, and I have registered the OUT value with registerOutParameter. Now I execute the query. After I execute the query, the results will be returned automatically. It always returns the result set resulting from the last SQL statement in the stored procedure. But to get the OUT parameter, I have to do a little bit more work. I'll declare an integer variable that I'll name nRows, and I'll get its value by calling a method of this statement object not of the result set. It will look like this stmt.getint you'll find that there are other methods called getdouble, getstring, and so on.
And as before, I can reference the parameter either by its position in the argument list or by its name. I'll choose the version where I reference by Name, and I'll type in the name of the parameter, and now I have that value returned as the variable nRows. The code in my displayData method in the Tours class already has code to figure out how many rows there are, but I'm not going to need that anymore. That value is already being returned by the stored procedure. So I'll go into the displayData method and comment out those two lines of code, and I'll re-factor this so that nRows is passed in as an argument.
I'll save those changes, I'll go back to my main class, and I'll change the way that I'm calling displayData, and I'll pass in nRows. So I have moved some of the actual work from the client application back to the database. In fact, I no longer even need a scrollable result set because now I'm not moving the cursor forward and back, I'm only moving it forward looping through the returned rows. I'll test my application, I enter a value of 300, and there is a result the Number of tours is correct, and there is the data.
And I'll run it once again, and this time I'll pass in a value that gets back no rows, and there's the result. From the user's point of view it's all working exactly like it did before, but now I have moved as much of the logic as I can back to the database. This lightens the load on the client, reduces the amount of work you have to do and the amount of memory you have to use, and eliminates the need for a scrollable result sets, which do take a little bit more memory and resources than forward only result sets. You can do it either way moving the logic into your client or defining it and keeping it on the database.
As a JDBC developer, the more you know about what your database management system can do, the more choices you'll have.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99857 Viewers
56 Video lessons · 112982 Viewers
71 Video lessons · 81811 Viewers
131 Video lessons · 39261 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.