AW: stringtype=unspecified is null check problem - Mailing list pgsql-jdbc

Whole Source is too much, as there is a lot of other things inside:

Null Replacement went into general DBAccess, not only in special PostgreSQLDBAccess like executeFunction or executeProcedure.

https://sourceforge.net/p/jvx/code/HEAD/tree/trunk/java/library/src/com/sibvisions/rad/persist/jdbc/DBAccess.java

Relevant Source with example:

String originalStatement = "select 1 where ? is null";

Object[] originalParams = new Object[] {null};

StringBuilder statementToBeChanged = new StringBuilder(originalStatement);

Object[] newParams = replaceNullParameter(statementToBeChanged, originalParams);

String newStatement = statementToBeChanged.toString();

Real usage would be:

List<Bean> result = dba.executeQuery("select 1 where ? is null or 1 = ?", null, null);

/**

* Replaces null parameters (?) directly with null, to avoid cast exceptions.

* 

 * @param pStatement the original statement

* @param pParameters the parameters

* @return the new reduced parameters

*/

protected Object[] replaceNullParameter(StringBuilder pStatement, Object[] pParameters)

{

        int[] paramIndexes = getParameterIndexes(pStatement.toString());

        for (int i = paramIndexes.length - 1; i >= 0; i--)

        {

            Object param = pParameters[i];

            if (AbstractParam.getValue(param) == null)

            {

                int index = paramIndexes[i];

                if (index < pStatement.length() - 1 && !Character.isWhitespace(pStatement.charAt(index + 1)))

                {

                    pStatement.insert(index + 1, " ");

                }

                pStatement.replace(index, index + 1, "null");

                if (index > 0 && !Character.isWhitespace(pStatement.charAt(index - 1)))

                {

                    pStatement.insert(index, " ");

                }

                pParameters = ArrayUtil.remove(pParameters, i);

            }

        }

        return pParameters;

  }

    /**

     * Gets the indexes of parameters (?).

     * 

     * @param pStatement the statement

     * @return the indexes of parameters (?)

     */

    protected int[] getParameterIndexes(String pStatement)

    {

        int[] result = new int[0];

        String sOpenQu = getOpenQuoteCharacter();

        String sCloseQu = getCloseQuoteCharacter();

        if (StringUtil.isEmpty(sOpenQu) || StringUtil.isEmpty(sCloseQu))

        {

            sOpenQu = null;

            sCloseQu = null;

        }

        String sCloseMark = null;

        for (int idx = 0, cnt = pStatement.length(); idx < cnt; idx++)

        {

            if (sCloseMark != null)

            {

                if (pStatement.startsWith(sCloseMark, idx))

                {

                    idx += sCloseMark.length() - 1;

                    sCloseMark = null;

                }

            }

            else

            {

                if (pStatement.startsWith("\'", idx))

                {

                    sCloseMark = "\'";

                }

                else if (pStatement.startsWith("\"", idx))

                {

                    sCloseMark = "\"";

                }

                else if (sOpenQu != null && pStatement.startsWith(sOpenQu, idx))

                {

                    idx += sOpenQu.length() - 1;

                    sCloseMark = sCloseQu;

                }

                else if (pStatement.startsWith("--", idx))

                {

                    idx++;

                    sCloseMark = "\n";

                }

                else if (pStatement.startsWith("/*", idx))

                {

                    idx++;

                    sCloseMark = "*/";

                }

                else if (pStatement.startsWith("?", idx))

                {

                    result = ArrayUtil.add(result, idx);

                }

            }

        }

        return result;

}