How to use PreparedStatement in JDBC Java? An Example

PreparedStatement is used to execute specific queries which 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 than database assist in query preparation, which is faster and more secure. Such kind of queries are compiled, and their query plans are cached at database side to 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 issue 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 query 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 course 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() method 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 SQL query. Iterate through ResultSet and print data or create an object out of those data.


Here are code sample and detail explanation of each step

1) Create Database Connection
You need JDBC driver for the database you want to connect, e.g. if you want to connect Oracle, you need ojdbc6.jar, if you want to connect MySQL then you need mysql-connector-java-5.1.23-bin.jar.

And, if you want to connect Microsoft SQL Server then you need sqljdbc4.jar. It also depends upon which version of the database you are connecting. You also need 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 Java Platform: Working with Databases Using JDBC course on Pluarlsight to learn more about how to use these objects.

How to use PreparedStatement in JDBC Java? An Example



4) Set the query parameter by calling various setXXX() method 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 than you need to call the setString() method, if the placeholder is for a column of INT type than 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 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 placeholder, you can run the PreparedStatement by calling executeQuery() method as shown below:

ResultSet rs = ps.executeQuery();

This will return the ResultSet which contains all rows returned by the SQL query.


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 it out or you can create 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));
}

Here is the complete program:

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 query. 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 strategy can result in different performance.

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


Further Learning
The Complete Java Masterclass
Complete JDBC Programming Part 1 and 2
Java Platform: Working with Databases Using JDBC


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 tutorial for beginners then please share with your friends and colleagues. If you have any questions or feedback then please drop a note.

No comments:

Post a Comment