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, ',') &&
<foreach item="phenom" index="," collection="phenoms"
open="'{"" separator="","" close=""}'">
#{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.
#{}
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.
STRING_TO_ARRAY
on the right sideARRAY[...]::TEXT[]
syntaxSTRING_TO_ARRAY
on the right sideThere 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} ',')
ARRAY[...]::TEXT[]
syntaxThe 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>
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}