Java PreparedStatement IN clause alternatives With Examples

If you are using JDBC API to run queries on database, you should know that PreparedStatement is the better choice than Statement. However since JDBC API allows only one literal for one “?” parameter, PreparedStatement doesn’t work for IN clause queries.

PreparedStatement IN clause

So if we need to execute a database query with IN clause, we need to look for some alternative approach. The aim of this post is to analyze different approaches and you can choose the one that suits your requirements.

  1. Execute Single Queries
  2. Using Stored Procedure
  3. Creating PreparedStatement Query dynamically
  4. Using NULL in PreparedStatement Query

Let’s look at these approaches one by one. But before that let’s create a utility program for database connection reading configurations from property file.

db.properties

Make sure you have JDBC jars in the build path of the project.

Now let’s look at the different approaches and their analysis.

Execute Single Queries

This is the simplest approach. We can get the input and execute single PreparedStatement query multiple times. A sample program with this approach will look like below.

JDBCPreparedStatementSingle.java

The approach is simple but it’s very slow because if there are 100 parameters then it will make 100 database calls. This will result in 100 ResultSet objects that will overload the system and it will also cause performance hit. So this approach is not recommended.

Using Stored Procedure

We can write a stored procedure and send the input data to the stored procedure. Then we can execute queries one by one in the stored procedure and get the results. This approach gives fastest performance but as we all know that Stored Procedures are database specific. So if our application deals with multiple types of databases such as Oracle, MySQL then it will become hard to maintain. We should use this approach only when we are working on single type of database and there is no plan to change the database server. Since writing stored procedure is out of scope of this tutorial, I will not demonstrate how to use it.

Creating PreparedStatement Query dynamically

This approach involves writing logic to create the PreparedStatement query dynamically based on the size of the elements in IN clause. A simple example showing how to use it will look like below code.

JDBCPreparedStatementDynamic.java

Notice that the query is created dynamically and it will run perfectly. There will be only one database call and the performance will be good. However if the size of user input varies a lot, we won’t get the PreparedStatement benefit of caching and reusing the execution plan. If you are not worried about PreparedStatement caching and there are not many queries with IN clause, then it seems to be the way to go.

Using NULL in PreparedStatement Query

If you really want to utilize the PreparedStatement caching feature, then another approach is to use NULL in PreparedStatement parameters. Suppose that the maximum allowed parameters in the query is 10, then we can write our logic like below.

JDBCPreparedStatementNULL.java

Notice that above program is using same PreparedStatement query for executing IN clause statement and will get the benefit of query caching and executing plan. For simplicity, I am just returning if the number of input parameters is greater than the parameters size in the query but we can easily extend it to execute in batches to allow any number of inputs.

Now let’s write a simple test program to check the output. For my test program, I am using Employee table created in JDBC DataSource example.

Our test program code is;

JDBCPreparedStatementINTest.java

When we execute it with some test data in Employee table, we get below output.

That’s all for the different options we have to use PreparedStatement for IN clause in queries. You can use any one of these based on your project requirements.

By admin

Leave a Reply

%d bloggers like this: