How to use PreparedStatement in Java - JDBC Example Tutorial

PreparedStatement is used to execute specific queries that are supposed to run repeatedly, for example, SELECT * from Employees WHERE EMP_ID=?. This query can be run multiple times to fetch details of different employees. If you use PreparedStatement like above then the database assists in query preparation, which is faster and more secure. Such kinds of queries are compiled, and their query plans are cached at the database side every time you execute it, you will get a faster response as opposed to using simple queries via Statement object, like SELECT * from Employees WHERE EMP_ID + emp_id.

The difference between the above two queries is place holder, I mean, the "?" character, one is using String concatenation to create dynamic query while PreparedStatement uses bind parameters to provide dynamic nature.

The string concatenation version also has security issues because it can be targeted with SQL injection, but the placeholder version, i.e. which uses PreparedStatement provides safety against SQL injection in Java.

In this article, you will learn how to use PreparedStatement to execute SQL queries in Java using JDBC API. You will also learn the pros and cons of using PreparedStatement in Java. But, if you want to learn more about JDBC API and other important APIs in Java then I suggest you take a look at The Complete Java Masterclass on Udemy, one of the most comprehensive and up-to-date courses on Java.

Steps to Create PreparedStatement

1) Create Database Connection

2) Create SQL query with Placeholder, like SELECT * from STOCKS WHERE TICKER=?

3) Create a PreparedStatement object by calling the Connection.prepareStatement() method and passing the SQL created in previous steps.

4) Set the query parameter by calling various setXXX() methods of PreparedStatement object depending upon data type.

5) Run the PreparedStatement by calling the executeQuery() method of PreparedStatement

6) The previous step returns a ResultSet containing all the rows returned by the SQL query. Iterate through ResultSet and print data or create an object out of those data.


Here are a code sample and detailed explanation of each step

1. Create Database Connection

You need a JDBC driver for the database you want to connect, like if you want to connect Oracle, you need ojdbc6.jar, if you want to connect MySQL then you need the mysql-connector-java-5.1.23-bin.jar.

And, if you want to connect to Microsoft SQL Server then you need sqljdbc4.jar. It also depends upon which version of the database you are connecting to. You also need the respective JDBC URL, username, and password to connect the database.

2. Create SQL query with Placeholder 

The "?" character is used for a placeholder, you can define as many placeholders you want, as shown below:

String SQL = SELECT * from STOCKS WHERE TICKER=?

3. Connection.prepareStatement()

Create PreparedStatement object by calling Connection.prepareStatement() method and passing the SQL created in previous steps.

PreparedStatement ps = con.prepareStatement(SQL);

If you are interested, you can further see the Java Platform: Working with Databases Using JDBC course on Pluralsight to learn more about how to use these objects.

4. Set the query parameter by calling various setXXX() methods of PreparedStatement object

You need to set the data for placeholder before executing the PreparedStatement. Depending upon the data type of placeholder, you need to call the respective setXXX() method.

For example, if the placeholder is for a column of VARCHAR type then you need to call the setString() method, if the placeholder is for a column of INT type then you need to call the setInt() method.

In our case, TICKER is VARCHAR, so we are calling the setString() method, as shown below:

ps.setString(1, "MSFT");

Remember, the first parameter is the index of placeholder, which starts at 1. For example, if you have two placeholders then for setting value for the second placeholder, you would have a need to call ps.setString(1, "GOOG");

If you call the setXXX() method with an invalid index then you will get the index out of range error as shown there.

5. Run the PreparedStatement by calling the executeQuery() method of PreparedStatement

Once you set the value for a placeholder, you can run the PreparedStatement by calling the executeQuery() method as shown below:

ResultSet rs = ps.executeQuery();

This will return the ResultSet which contains all rows returned by the SQL query. If you are not familiar with ResultSet and essential JDBC classes then you can also see The Complete JDBC Programming Part-1 course on Udemy. It's a great course to learn JDBC for Java developers. 

6. Iterate through ResultSet and print data or create an object out of those data.

Once you got the data in the client machine, you can do whatever you want to. You can just print them if you are trying them out or you can create a Java object by using those data. In our case, we are just printing it out on console, as shown below:

while(rs.next()){
  System.out.println(rs.getString(1));
}

JDBC PreparedStatement Example in Java

Here is the complete program:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcDemo {

  public static void main(String args[]) throws Exception{
    
    String dbURL = "jdbc:mysql://localhost:3306/test";
    String username ="root";
    String password = "root";
    
    Connection con = DriverManager.getConnection(dbURL)

    PreparedStatement ps = con
        .prepareStatement("SELECT * from Books WHERE ISBN=?");
    ps.setString(1, "978-0132778047");
    ps.setString(3, "1");

    ResultSet rs = ps.executeQuery();
    while (rs.next()) {
      System.out.println(rs.getString(1));
    }

  }
}

Pros and Cons of PreparedStatement

1) PreparedStatement query is pre-compiled and cached at the database server, so they are faster compared to normal SQL queries. If you want to learn more about performance improvement on JDBC or Persistence layer, then I suggest you go through Vlad Mihalcea's High-Performance Java Persistence course, where he has explained how different JDBC objects and strategies can result in different performances.

2) Use of query parameters prevents SQL injection.

3) PreparedStatement uses setXXX() method to set the data which also does type verification at compile-time like by calling setInt() you cannot pass a String.


That's all about how to use PreparedStatement in Java. You should use PreparedSatement to encapsulate repeatedly running SQL queries, it's faster and more secure. If your query depends upon any user data, like emp_id coming as a request parameter in Java Web application, you must use PreparedSatement to avoid SQL injection in Java.



Other JDBC Tutorials and  Resources you may like

• 10 JDBC Best Practices Every Java Programmer should follow (read)
• 6 Essential JDBC Performance tips for Java Programmers (tips)
• Difference between Type 1 and Type 4 JDBC drivers (answers)
• How to connect to MySQL database from Java Program? (tutorial)
• JDBC Batch Insert and Update example using PreparedStatement (tutorial)
• Top 10 JDBC Interview Question for Java programmers (read)
• Difference between java.sql.Date, java.sql.Timestamp, and java.util.Date in JDBC? (answer)
• 5 Free JDBC Courses for Java Developers (courses)
• 5 Free JDBC books for Java Programmers (books)
• My favorite free courses to learn Java in-depth (courses)
• Top 5 Courses to learn Spring Boot in Depth (courses)
• Difference between PreparedStatement and Statement in Java? (answer)

Thanks for reading this article so far. If you like this JDBC Prepared Statement Example for beginners then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.

P. S. - If you are new to JDBC and looking for a free online training course to learn JDBC then you can also check out Java Database Connection: JDBC and MySQL (FREE) on Udemy. It's completely free, all you need is to create an Udemy account to join this course.