Take care when using SELECT SQL Statement with IN Clause

Consider the following code snippet

	public static String getMySqlStatement(ArrayList<String> dataValues) {
		if ((dataValues == null) || (dataValues.size() == 0)) {
			throw new IllegalArgumentException();
		}
		StringBuilder sql = new StringBuilder ("SELECT * FROM MY_TABLE WHERE MY_COLUMN IN (");
		StringBuilder colValues = new StringBuilder();
		for (String data: dataValues) {
			colValues.append("'").append(data).append("',");
		}
		//Remove the trailing comma(,) and add the final braces
		sql.append(colValues.substring(0, (colValues.length()-1))).append(")");
		return sql.toString();
	}

The method builds an Select query using the elements in the arraylist dataValues in the IN clause.
The SQL returned by the method executes normally when the size of dataValues is less then or equal to 1000. But, when the size of dataValues is greater than 1000, the SQL throws an exception.

This is because there is a limit on the number of parameters in the IN Clause. For Oracle 9i, the limit is 1000. The limit can vary based on database.

Hence, the above method does not work in all cases. It is required to build SQL queries based on the size of the parameters in the IN clause like breaking up into multiple SQL statements and then combining the results. The above method is modified to return multiple SQL statements if the number of parameters is greater than the limit(in this case 1000).

    public static ArrayList<String> getMySqlStatements(ArrayList<String> dataValues) {
        if ((dataValues == null) || (dataValues.size() == 0)) {
            throw new IllegalArgumentException();
        }
        final int IN_PARAMETER_LIMIT = 1000;
        ArrayList<String> sqlStatements = new ArrayList<String>();
        StringBuilder sql = new StringBuilder ("SELECT * FROM MY_TABLE WHERE MY_COLUMN IN (");
        StringBuilder colValues = null;
        for (int i=0, j=0, length= dataValues.size(); i < length; i++) {
            if (colValues == null) {
                colValues = new StringBuilder();
            }
            j++;
            String data = dataValues.get(i);
            colValues.append("'").append(data).append("',");
            if (j == IN_PARAMETER_LIMIT) {
                sqlStatements.add((sql.append(colValues.substring(0, (colValues.length()-1))).append(")")).toString());
                j=0;
                colValues = null;
            }
        }
        return sqlStatements;
    }

This error may not show up in normal circumstances unless the number of parameters is greater than 1000. To find bugs due to software limits, it is always required to unit test under maximum load conditions.