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.
- Execute Single Queries
- Using Stored Procedure
- Creating PreparedStatement Query dynamically
- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#mysql DB properties DB_DRIVER_CLASS=com.mysql.jdbc.Driver DB_URL=jdbc:mysql://localhost:3306/UserDB DB_USERNAME=pankaj DB_PASSWORD=pankaj123 #Oracle DB Properties #DB_DRIVER_CLASS=oracle.jdbc.driver.OracleDriver #DB_URL=jdbc:oracle:thin:@localhost:1521:orcl #DB_USERNAME=hr #DB_PASSWORD=oracle |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
package com.journaldev.jdbc.preparedstatement.in; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Properties; public class DBConnection { public static Connection getConnection() { Properties props = new Properties(); FileInputStream fis = null; Connection con = null; try { fis = new FileInputStream("db.properties"); props.load(fis); // load the Driver Class Class.forName(props.getProperty("DB_DRIVER_CLASS")); // create the connection now con = DriverManager.getConnection(props.getProperty("DB_URL"), props.getProperty("DB_USERNAME"), props.getProperty("DB_PASSWORD")); } catch (SQLException e) { System.out.println("Check database is UP and configs are correct"); e.printStackTrace(); } catch (IOException e) { System.out.println("Looks like db.property file has some issues"); e.printStackTrace(); } catch (ClassNotFoundException e) { System.out.println("Please include JDBC API jar in classpath"); e.printStackTrace(); }finally{ try { fis.close(); } catch (IOException e) { System.out.println("File Close issue, lets ignore it."); } } return con; } } |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<strong><code> package com.journaldev.jdbc.preparedstatement.in; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCPreparedStatementSingle { private static final String QUERY = "select empid, name from Employee where empid = ?"; public static void printData(int[] ids){ Connection con = DBConnection.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(QUERY); for(int empid : ids){ ps.setInt(1, empid); rs = ps.executeQuery(); while(rs.next()){ System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name")); } //close the resultset here try{ rs.close(); } catch(SQLException e){} } } catch (SQLException e) { e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } </code></strong> |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
package com.journaldev.jdbc.preparedstatement.in; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCPreparedStatementDynamic { public static void printData(int[] ids){ String query = createQuery(ids.length); System.out.println("Query="+query); Connection con = DBConnection.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(query); for(int i = 1; i <=ids.length; i++){ ps.setInt(i, ids[i-1]); } rs = ps.executeQuery(); while(rs.next()){ System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name")); } //close the resultset here try{ rs.close(); } catch(SQLException e){} } catch (SQLException e) { e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } private static String createQuery(int length) { String query = "select empid, name from Employee where empid in ("; StringBuilder queryBuilder = new StringBuilder(query); for( int i = 0; i< length; i++){ queryBuilder.append(" ?"); if(i != length -1) queryBuilder.append(","); } queryBuilder.append(")"); return queryBuilder.toString(); } } |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
package com.journaldev.jdbc.preparedstatement.in; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCPreparedStatementNULL { private static final String QUERY = "select empid, name from Employee where empid in ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; private static final int PARAM_SIZE = 10; public static void printData(int[] ids){ if(ids.length > PARAM_SIZE){ System.out.println("Maximum input size supported is "+PARAM_SIZE); //in real life, we can write logic to execute in batches, for simplicity I am returning return; } Connection con = DBConnection.getConnection(); PreparedStatement ps = null; ResultSet rs = null; try { ps = con.prepareStatement(QUERY); int i = 1; for(; i <=ids.length; i++){ ps.setInt(i, ids[i-1]); } //set null for remaining ones for(; i<=PARAM_SIZE;i++){ ps.setNull(i, java.sql.Types.INTEGER); } rs = ps.executeQuery(); while(rs.next()){ System.out.println("Employee ID="+rs.getInt("empid")+", Name="+rs.getString("name")); } //close the resultset here try{ rs.close(); } catch(SQLException e){} } catch (SQLException e) { e.printStackTrace(); }finally{ try { ps.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } } } |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
package com.journaldev.jdbc.preparedstatement.in; public class JDBCPreparedStatementINTest { private static int[] ids = {1,2,3,4,5,6,7,8,9,10}; public static void main(String[] args) { JDBCPreparedStatementSingle.printData(ids); System.out.println("*********"); JDBCPreparedStatementDynamic.printData(ids); System.out.println("*********"); JDBCPreparedStatementNULL.printData(new int[]{1,2,3,4,5}); } } |
When we execute it with some test data in Employee table, we get below output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
<span style="color: #008000;"><strong><code> Employee ID=1, Name=Pankaj Employee ID=2, Name=David Employee ID=3, Name=Ram Employee ID=4, Name=Leela Employee ID=5, Name=Lisa Employee ID=6, Name=Saurabh Employee ID=7, Name=Mani Employee ID=8, Name=Avinash Employee ID=9, Name=Vijay ********* Query=select empid, name from Employee where empid in ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) Employee ID=1, Name=Pankaj Employee ID=2, Name=David Employee ID=3, Name=Ram Employee ID=4, Name=Leela Employee ID=5, Name=Lisa Employee ID=6, Name=Saurabh Employee ID=7, Name=Mani Employee ID=8, Name=Avinash Employee ID=9, Name=Vijay ********* Employee ID=1, Name=Pankaj Employee ID=2, Name=David Employee ID=3, Name=Ram Employee ID=4, Name=Leela Employee ID=5, Name=Lisa </code></strong></span> |
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.