Java JDBC Tutorial – Part 5: Prepared Statements

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, http://www.luv2code.com to download the Java source code used in this video.

Did you like my blog post?

Signup now and receive an email when I publish new luv2code content.

I agree to have my personal information transfered to AWeber ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

21 Comments

  1. Comments  Mohammed Manna   |  Sunday, 15 March 2015 at 9:42 am

    Hi Chad,

    Is it possible to use the sql file for my PostGreSQL database?

  2. Comments  Mohammed Manna   |  Sunday, 15 March 2015 at 10:02 am

    I figured that it wouldn’t….thanks!

  3. Comments  Chad Darby   |  Sunday, 15 March 2015 at 11:13 am

    Hi,

    Yeah, the SQL scripts only work for MySQL. However, if you’d like I can convert them to PostGreSQL for you. Let me know and I can knock it out this afternoon 🙂

  4. Comments  Vel Dhanish   |  Tuesday, 14 July 2015 at 10:33 am

    Chad. Your tutorials helped me a lot. Thank you

  5. Comments  Chad Darby   |  Wednesday, 15 July 2015 at 4:25 am

    awesome! glad to hear 🙂

  6. Comments  ristour   |  Saturday, 25 July 2015 at 8:02 pm

    I Prof. I have a problem with display(myRs) . Its not working. This is my code .

    package jdbcdemo;

    import java.sql.*;

    public class Driver {

    public static void main(String[] args) throws SQLException{
    Connection myConn = null;
    PreparedStatement myStmt = null;
    ResultSet myRs = null;

    try {
    //1. Get a connection to database
    myConn = DriverManager.getConnection(“jdbc:mysql://localhost:3306/demo”,”student”,”student”);

    //2. Create a statement
    myStmt = myConn.prepareStatement(“select * from employees where salary > ? and department=?”);

    //3. Execute SQL query
    myStmt.setDouble(1, 80000);
    myStmt.setString(2, “Legal”);

    //4. Execute SQl query
    myRs = myStmt.executeQuery();

    //5. Display the result set
    display(myRs);

    }
    catch(Exception exc){
    exc.printStackTrace();

    }

    }
    }

  7. Comments  Chad Darby   |  Sunday, 26 July 2015 at 10:55 am

    Hi,

    The method “display(myRs)” is defined in the source code download. You can download the source code here: http://www.luv2code.com/wp-content/uploads/2014/09/jdbc-prepared-statements-tutorial.zip

    Let me know if that resolves your issue. Cheers!

  8. Comments  Earnest   |  Monday, 14 September 2015 at 12:36 pm

    Hi ,
    Chad
    I really enjoy you tutorials.About JDBC. I was blank but now i got an idea how to connect a Java application to a database of any type andhosted either locally or remotely on the network or in the cloud. Thanks alot.

  9. Comments  Chad Darby   |  Monday, 14 September 2015 at 1:06 pm

    Wow! that’s awesome … I’m glad you enjoyed the video 🙂

  10. Comments  Chelariu Rares   |  Saturday, 12 December 2015 at 9:57 am

    Great tutorial. I love the fact that JDBC is so much easier than PHP, and I can implement it in my Android Apps. Thank you, man !

  11. Comments  emmy   |  Friday, 26 February 2016 at 8:36 pm

    hi i did this program but for display method in the last i did the other way . but im getting error after i run the program

    package jdbcdemo;
    import java.sql.*;

    public class jdbcPreparedStmts {

    public static void main(String[] args) throws SQLException{
    //A prepared statement is simply a pre-compiled SQL statement
    //http://www.luv2code.com/2014/09/01/java-jdbc-tutorial-part-5-prepared-statements/
    String url = “jdbc:mysql://localhost:3306/demo”;
    String username = “root”;
    String password = “chaibiscuit”;
    Connection myConn = null;
    PreparedStatement myStmt = null;
    // 1) Get connection to database
    try{
    myConn = DriverManager.getConnection(url,username,password);

    //2) create the statement
    myStmt = myConn.prepareStatement(“select * from employee” + “,” + “where salary > ? and department = ?”);

    //3)Set the parameters
    myStmt.setDouble(1, 8000);
    myStmt.setString(2,”Legal”);

    // 4)Execute SQL Query
    ResultSet myRs = myStmt.executeQuery();

    //print or display myRs
    //System.out.println(myRs);
    display(myRs);
    }catch(Exception exe){
    exe.printStackTrace();
    }
    }

    private static void display(ResultSet myRs) throws SQLException {
    while(myRs.next()){
    System.out.println(myRs.getString(“last_name”)+”,”+ myRs.getString(“fist_name”)+”,”+ myRs.getDouble(“salary”)+”,”+ myRs.getString(“email”));
    }

    }

    }

    and the error is like this

    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 ‘where salary > 8000.0 and department = ‘Legal” at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)

    please reply me as soon as possible thanks

  12. Comments  Chad Darby   |  Friday, 26 February 2016 at 11:14 pm

    Replace step 2 with this

    myStmt = myConn.prepareStatement(“select * from employee where salary > ? and department = ?”);

    Let me know if this resolves your problem.

  13. Comments  yaash   |  Wednesday, 30 March 2016 at 10:35 pm

    Prof, what is wrong with this code? i cant figure it out.. help is needed

  14. Comments  Chad Darby   |  Thursday, 31 March 2016 at 7:09 am

    is this code from my video?

  15. Comments  theos   |  Friday, 18 November 2016 at 7:17 am

    thanks a lot !

    you are the best

  16. Comments  Chad Darby   |  Wednesday, 30 November 2016 at 8:41 pm

    You are welcome!

  17. Comments  Adirya Nandagiri   |  Friday, 17 November 2017 at 4:41 pm

    Hi Chad ,
    thankyou very much for the tutorials
    you made my life easy

  18. Comments  Chad Darby   |  Sunday, 26 November 2017 at 6:15 pm

    you are welcome Adirya. thanks for the kind words 🙂

  19. Comments  George   |  Sunday, 18 March 2018 at 9:07 pm

    Very good lession, thank you for this ! 🙂

  20. Comments  Ian   |  Sunday, 04 November 2018 at 10:23 pm

    Just curious. Do you have the SQL insert query for this employees table available for download?

    Thanks!

  21. Comments  Chad Darby   |  Monday, 19 November 2018 at 3:19 pm

    Hi Ian,

    Yes, the SQL script is available here. Let me know if you need anything else 🙂

Leave a Reply