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

Please SUBSCRIBE to this channel

Download Java Source Code

Download Java Source Code

—
Transcript

Time – 00:00
Hi, this is Chad (Shod) with luv2code.com. Welcome back to another tutorial on Java and JDBC. In this video we will learn how to use Prepared Statements. We’ll first define Prepared Statements. Next we’ll learn how to create Prepared Statements. Then we’ll set Parameter Values, execute a Prepared Statement, and finally reuse a Prepared Statement.

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
For this tutorial we’ll use the following table, employees. This table already has some sample data in it for testing. I have a SQL script that creates the table and also adds a sample data. You can download it from a link below. Inside of the download there’s a file called sql/table-setup.sql. You can run this in your SQL tool to create the table and set up the sample data. Instead of hardcoding your SQL values in your statement, you can make use of parameter placeholders. You will use a question mark for the placeholder symbol. As you can see in this example, we replaced the hardcoded values with question mark placeholders.

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.

Time – 04:03
We can also use Prepared Statements to insert, update, and delete. On this example I’m going to perform a delete where a given salary and department is given. I’ll set the salary to 80,000. I’ll set the department to Legal. Then I’ll run the method called executeUpdate. This method will perform the SQL operation, and it’ll also return the number of rows affected. In this scenario it’ll be the number of rows that were deleted.

Time – 04:33
That wraps up our discussion on Prepared Statements. Please subscribe to our channel to view more videos on Java, click the thumbs up to like our video, also visit our website, https://luv2code.com to download the Java source code used in this video.