Using PreparedStatement for variable number of parameters / handle SQL statements or queries with "IN" Clause

Following article discusses on using PreparedStatement to work on SQL queries involving clauses of type "IN"

Basic flow behind executing a SQL Statement:

* First, DBMS or RDBMS will compile the SQL statement
* Secondly, Execute the SQL statements

PreparedStatement in Java is an object representation of pre-compiled SQL statement unlike normal Statement in Java. Usually PreparedStatement are considered faster upon comparison with Statement but only when there are few iterations completed before they can actually catch-up with Statement otherwise only Statement gives better performance for fewer execution cycles.

Effective usage of PreparedStatement:

* Use them for SQL statements which will be executed multiple times.
* Use them for SQL statements that takes parameters though it can be used for statements without any parameters. This way you can use the same statement with different values to execute it.
* Use them for SQL statements that has parameters that are likely to have special characters so that we dont have to worry about handling parameters having quotes or any special characters.

Problem of SQL statement with variable number of parameters:

When we have a SQL statement which takes parameters with special characters and has variable number of parameters, there arises a problem on how to supply values for the PreparedStatement parameters (question mark placeholders) which are dynamic or variant in nature. Statement object might be a better choice to handle such cases as SQL statement can be dynamically constructed but problem would be to handle special characters.

Solution:

Solution to the above problem is to dynamically construct the PreparedStatement parameters using plain java programing and then supply values the same way in which variable parameters are constructed. Just remember though doing this way might not be having significant performance improvement as compared to its actual features, this helps in handling special characters and for scenarios where SQL statement has same set of parameters. That is, the pre-compiled SQL statement feature is used to some extent only when it has the same set of parameters passed as for variable parameters are unchanged. In case of parameters changing, anyway it will try to pre-compile for each change preventing it to effectively use its own feature.

Example: Implementation for the above solution:

SQL statement: SELECT * FROM EMPLOYEE WHERE COMP_NAME IN ("Jame's Consulting", "D'Souze Inc.", "Build-factory (P) Ltd")

Above SQL statement uses "IN" clause which might have variable parameters of company name (COMP_NAME). Based on the user input, the parameters might be one or more. Though we can use Statement here, we need to handle special characters like single quotes or any other.

Approach 1: Construct PreparedStatement for the above scenario assuming single parameter

....
String compNames[]={"Jame's Consulting", "D'Souze Inc.", "Build-factory (P) Ltd"};
String sqlQuery="SELECT * FROM EMPLOYEE WHERE COMP_NAME IN (?)";
PreparedStatement pstmt = con.prepareStatement(sqlQuery);
for(int i=0;i<compNames.length;i++)
{
pstmt.setString(i, compNames[i]);
ResultSet rs=pstmt.executeQuery();
}
....

This approach will have to execute the PreparedStatement for each and every company name which does not actually fit our requirement of using SQL statement with "IN" clause effectively. Also there are multiple network calls to execute multiple queries.

Approach 2 (Recommended): Construct PreparedStatement for the above scenario with dynamically constructing the number of parameters

....
String compParams="(";
String compNames[]={"Jame's Consulting", "D'Souze Inc.", "Build-factory (P) Ltd"};
String sqlQuery="";
PreparedStatement pstmt = con.prepareStatement(sqlQuery);
for(int i=0;i<compNames.length;i++)
{
compParams=compParams+"?,";
}
compParams=compParams.substring(0, compParams.lastIndexOf(","))+")";
sqlQuery="SELECT * FROM EMPLOYEE WHERE COMP_NAME IN "+compParams;
PreparedStatement pstmt = con.prepareStatement(sqlQuery);
int j=1;
for(int i=0;i<compNames.length;i++)
{
pstmt.setString(j, compNames[i]);
j=j+1;
}
ResultSet rs=pstmt.executeQuery();
....

This will effectively use the SQL statement with "IN" clause by executing the query only once. However as discussed earlier, this approach will help only in using PreparedStatement to handle special characters if the parameters are variant in nature for different calls other than other typical advantages of PreparedStatement.