Java JDBC Tutorial - Part 5: Prepared Statements

---

Closed-Captioning and English subtitles available.

---

In this video tutorial we will learn how to use Prepared Statements.
- What are Prepared Statements
- Create a Prepared Statement
- Setting Parameter Values
- Executing a Prepared Statement
- Reusing a Prepared Statement

----

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:25
What exactly is a Prepared Statement? A Prepared Statement is simply a precompiled SQL statement. Prepared Statements provide the following benefits. They make it easier to set SQL parameters. They prevent against SQL dependency injection attacks, and they also may improve application performance since the SQL statement is precompiled.

Time - 00:50

Time - 01:29
Now let's look at adding Java code. You'll create a Prepared Statement by calling the constructor and passing it into SQL. Then when you're ready to execute the statement, you'll set the Parameter Values. The Parameter Values are set based on their data type and position. The parameter positions are 1 based, starting left going to right. Here we set the Parameter Value for salary and department. Then we execute the query by calling executeQuery. That's it. It'll return a result set, and we can process that result set in its normal fashion. Notice there's no need to provide the SQL since it was already provided earlier when we created the statement.

Time - 02:08
Now let's switch over to clips and see this in action. I have a simple driver program in place. Let's walk through the actual code. First we get a connection to the database. Then I make use of that Prepared Statement. Then from there I set the parameters. I set the first parameter for the salary of 80,000; the second parameter for the department of Legal. Then I'll execute the statement. This will give me a result set. Then I have a helper method that will display the result set. Now let's run this application to see the output. As you can see, this gave us the output for 3 employees, and they all meet the parameters. The salary is greater than 80,000; and they work in the Legal department.

Time - 03:02
Another advantage of Prepared Statements is that you can reuse the statement. If you need to run the same query again but with different Parameter Values, then all you have to do is set the new Parameter Values and execute the statement. Here what I'll do is I will add in a new section of code for our application to reuse the values from before. I'll just move down here, drop in another little snippet. We have this reuse statement. We're going to reuse our Prepared Statement. This time we're simply going to set the salary to 25,000 and set the department to HR. We set those parameters accordingly in our code. Then we go through and we execute the query.

Time - 03:41
Then we go and we display the result set. Let me save this one a bit, pull my window up, and run it one more time. Now I'll see some new output. Here's the reuse section. Note we have 4 employees here. Their salary is greater than 25,000; and they work for the HR department.

[snip] ... See Transcripts tab

[Mksandman]

I want to like this video a million times!!! I've been stuck on this for two days and just like that you explained it perfectly and it works! Thank you sir

[inadaizz]

String exactlyWhatIWanted = "You're the best! Thank you! Literally the 1st 2 min was more helpful than my previous half hour.";

[marcvashcane]

Noice! Fast and easy to understand video. Thank you sir! you have earned my sub.

[igorkosandyak]

Your videos are awesome. Thank you very much! Just a pleasure to get information in this way.

[israahs]

Clear explanation for the general idea, thank you.

[BestFilmproducer]

This was very informative :) I´ve been studying SQL all day, because I thought my teacher was going through it too quickly for me, but this helped me to grasp the basics a lot better. Only had a little trouble with the display helper like Cecep Gorbacev, since I can´t really find anything in your video that is related to that display() method. When I code it, eclipse just gives me this error: "The method display(ResultSet) is undefined for the type NummerDataAccess" and only solution it gives, is to create the method in my class. Other than that, great video! :)

[IINEXUSGAMERII]

you're an amazing human being, thanks!!

[TimeForRevenge]

Awesome explanations! You definitely deserve more subs and views! Always check this page when I need something with JDBC :P

[CecepGorbacev]

i didnt know how to activate the display helper (2:36).. i used the script before.. with while loop..

[ViaVanille]

Also thank you so much for explaining the Prepared Statements!! I was so confused on the parameters where 1, 2 and so forth was used. Now I have a better understanding on the material. The numbers represent the column numbers to the database correct? because im getting an error when I run my code:
This is the messege:
Exception in thread "AWT-EventQueue-0" 2
at Source)
at Source)
at Source)
at
at
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Method)
at Source)
at Source)
at Source)
at Source)
at Method)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)
at Source)

[sinanprof]

Good day Sir. I am so much enjoying your class. Thank you. Please how can i download your Table Set Up SQL script from your website? I navigate through but couldnt get. Especially the fact that there is no search option to ease the work. Thank you

[ekoatenwaforlor]

hello, thank you for this. How do I create more than one prepared statement using different objects ?

[chassingh]

Hi There,
I've been trying to get grips with prepared statements and I understand it all except this one issue in my project. I am performing a login check so it will try to perform a query using a user login in the database. If the query fails the login fails if the query returns a row the login is successful. However, even if the login details are correct it still fails. I am just wondering if it has something to do with the fact I am setting more than one String in my prepared statement? Can I only set one data type each time?

[dubskiski]

Great tutorials, thank you so much for the help!

[hamzausmani]

What if there is a float type variable im entering do i still use
like this?
myStmt.setDouble(8.9);

[HaiChu]

How can I do multiple statements? And thanks for this great tutorial!

[baburaobean]

i would like to ask if we can do this in MySQL say we have the employees table @0:52 every time i insert a new value in salary col the previous value is overwritten can we add new inserted value to the previous salary value before it is overwritten

[ratias]

Thank you for this video, it's awesome

[gilbertslam]

Hello Sir, When I imported your eclipse project, I changed the build path configuration to match with mine. All "x" marks were removed then but when I ran the project, it would show this exception message: 

Exception in thread "main" Driver : Unsupported major.minor version 52.0

Did I miss something in the build path configuration?

[adityasinha]

what should i do ?
The method display(ResultSet) is undefined for the type Driver