Java JDBC Tutorial – Part 5: Prepared Statements

///Java JDBC Tutorial – Part 5: Prepared Statements

Java JDBC Tutorial – Part 5: Prepared Statements

FavoriteLoadingAdd to favorites

NEED TO LEARN JAVA – 5 FREE JAVA VIDEO COURSES – CLICK HERE –

Java468x60

View more videos on my “Java JDBC Tutorial” Playlist:

Download Java Source Code:

Closed-Captioning and English subtitles available.

Please SUBSCRIBE to this channel:

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:

Website:
YouTube:
Twitter:
Facebook:

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
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.

[snip] … See Transcripts tab

source

By |2020-04-12T02:51:49+00:00April 12th, 2020|Java Video Tutorials|27 Comments

27 Comments

  1. Fid Zaccheus April 12, 2020 at 2:51 am - Reply

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

  2. Hai April 12, 2020 at 2:51 am - Reply

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

  3. SKD April 12, 2020 at 2:51 am - Reply

    Thank you sir

  4. Ekoate Onungwe Nwaforlor April 12, 2020 at 2:51 am - Reply

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

  5. oussama Boumhaout April 12, 2020 at 2:51 am - Reply

    many thanks to you brother.

  6. ratias0 April 12, 2020 at 2:51 am - Reply

    Thank you for this video, it's awesome

  7. christian lopes April 12, 2020 at 2:51 am - Reply

    Can someone explain the need/use for the `finally` statement?

  8. Giorgi Tsiklauri April 12, 2020 at 2:51 am - Reply

    Well, .prepareStatement(..) is not a constructor. You're rather invoking this method on myConn object reference.

  9. Hamza Usmani April 12, 2020 at 2:51 am - Reply

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

  10. HuzzyBoii April 12, 2020 at 2:51 am - Reply

    you're an amazing human being, thanks!!

  11. Arthur MacDonald April 12, 2020 at 2:51 am - Reply

    Very helpful. Thank you!

  12. Nioh April 12, 2020 at 2:51 am - Reply

    God of tutorials… thanks!

  13. Mavis cruz April 12, 2020 at 2:51 am - Reply

    Abbie sent me. Great Job sir

  14. raj bhosale April 12, 2020 at 2:51 am - Reply

    getting error as" Type mismatch: cannot convert from java.sql.PreparedStatement to com.mysql.jdbc.PreparedStatement"
    what should be done next?

  15. Adarsha Bn April 12, 2020 at 2:51 am - Reply

    Thnk you

  16. Eddie Garcia April 12, 2020 at 2:51 am - Reply

    Thank you so much for your videos!!!

  17. Corey Kuehl April 12, 2020 at 2:51 am - Reply

    why not show the display helper method? My problem is displaying the information from the result set. Nothing I have found has been helpful. I either get a print out of the .com.mysql nonsense, or it says it can't find the column name I mention.

  18. GameCorp April 12, 2020 at 2:51 am - Reply

    Helped a lot, thanks 😀

  19. Aditya Sinha April 12, 2020 at 2:51 am - Reply

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

  20. shiva Rebel April 12, 2020 at 2:51 am - Reply

    i got it . its all about space in the ("select * from employees" + " where salary > ? and department = ? ");

  21. shiva Rebel April 12, 2020 at 2:51 am - Reply

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '> 80000.0 and department='Legal'' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
    at jdemo.Predemo.main(Predemo.java:25)

  22. shiva Rebel April 12, 2020 at 2:51 am - Reply

    Error: Could not find or load main class com.mysql.jdbc.DocsConnectionPropsHelper

  23. Dediu Daniel April 12, 2020 at 2:51 am - Reply

    Thank you for this tutorial!

  24. TimeForRevenge96 April 12, 2020 at 2:51 am - Reply

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

  25. Catchy CO April 12, 2020 at 2:51 am - Reply

    thank you sir !!

  26. Chas Singh April 12, 2020 at 2:51 am - Reply

    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?

  27. Michael D. April 12, 2020 at 2:51 am - Reply

    I wonder if main can be taken out of Driver.java and have the functions called from a servlet?

Leave A Comment

*