Postgres && statement Error in Mybatis Mapper?

Question

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.

Answer 1

#{} 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, ',') &&
string_to_array(#{phenomsStr} ',')

---

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

The idea is similar to your original solution.

WHERE
string_to_array(weather.custom_phenoms, ',') &&
<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, ',') &&
#{phenoms,typeHandler=my.pkg.StringListTypeHandler}