javaxmlpostgresqlpsqlmybatis

Postgres && statement Error in Mybatis Mapper?


I have a working postgres query that I am trying to setup in Mybatis but keep receiving syntax errors. The query that works in my PgAdmin that I would like to implement in Mybatis checks if there are any common items between 2 arrays. The working query in my pgAdmin goes like:

SELECT * FROM weather_schema.weather weather
WHERE STRING_TO_ARRAY(weather.phenoms, ',') && '{"TO", "WI"}'```

Now below is how I have this setup in Mybatis xml Mapper with the '{"TO", "WI"}' being replaced by injectable list of strings called "phenoms".

<select id="getFilteredWeather" resultMap="WeatherObj">
    SELECT
    *
    FROM weather_schema.weather weather
    WHERE
    string_to_array(weather.custom_phenoms, ',') &amp;&amp; 
    <foreach item="phenom" index="," collection="phenoms"
             open="&#39;{&quot;" separator="&quot;,&quot;" close="&quot;}&#39;">
        #{phenom}
    </foreach>
</select>

This is giving below result:


org.mybatis.spring.MyBatisSystemException\] with root cause
org.postgresql.util.PSQLException: The column index is out of range: 7, number of columns: 6.

Solution

  • #{} is a placeholder in prepared statement, so you cannot use it in a literal.

    Although it is technically possible to generate such literal using ${} instead of #{}, it is not recommended because it is difficult to prevent SQL injection that way (see this FAQ entry).

    I'll show you three alternative solutions.

    1. Join the string list and use STRING_TO_ARRAY on the right side
    2. Use ARRAY[...]::TEXT[] syntax
    3. Use a custom type handler

    1. Join the string list and use STRING_TO_ARRAY on the right side

    There maybe a few options for how to use the joined phenoms.
    One way is to add a new method to the WhetherObj.

    public String getPhenomsStr() {
      return String.join(",", phenoms);
    }
    

    Then the WHERE clause in the mapper would look like this.

    WHERE
    string_to_array(weather.custom_phenoms, ',') &amp;&amp;
    string_to_array(#{phenomsStr} ',')
    

    2. Use ARRAY[...]::TEXT[] syntax

    The idea is similar to your original solution.

    WHERE
    string_to_array(weather.custom_phenoms, ',') &amp;&amp;
    <foreach item="phenom" collection="phenoms"
        open="ARRAY[" separator="," close="]::TEXT[]">
      #{phenom}
    </foreach>
    

    3. Use a custom type handler

    You can write a custom type handler that calls java.sql.PreparedStatement#setArray().

    import java.sql.Array;
    import java.sql.CallableStatement;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Arrays;
    import java.util.List;
    
    import org.apache.ibatis.type.BaseTypeHandler;
    import org.apache.ibatis.type.JdbcType;
    
    public class StringListTypeHandler extends BaseTypeHandler<List<String>> {
    
      @Override
      public void setNonNullParameter(PreparedStatement ps, int i, List<String> parameter, JdbcType jdbcType)
          throws SQLException {
        Array array = ps.getConnection().createArrayOf("TEXT", parameter.toArray());
        ps.setArray(i, array);
        array.free();
      }
      // ...
    }
    

    Note: complete implementation of this type handler is in this executable demo.

    Then, you can specify the type handler in the parameter reference.

    WHERE
    string_to_array(weather.custom_phenoms, ',') &amp;&amp;
    #{phenoms,typeHandler=my.pkg.StringListTypeHandler}