如何使用MyBatis将数组值插入到PostgreSQL

huangapple go评论85阅读模式
英文:

How to insert an array value to postgreSQL with mybatis

问题

My class

class PgFolder {
    long id;
    int[] pgFeature;
}
getter...
setter...

My table

CREATE TABLE folder {
    ID BIGSERIAL PRIMARY KEY,
    feature INT[]
}

My xml

<insert id="insertManyFolder" parameterType="java.util.List">
    INSERT INTO
        folder
       (feature)
        VALUES
        <foreach collection="list" item="item" index="" separator=",">
            (#{item.pgFeature})
        </foreach>
</insert>

I want to insert a List<PgFolder> list. There are many PgFolder instances in the list. When I run the program, I got an error:

>org.apache.ibatis.exceptions.PersistenceException: <br>
Error updating the database. Cause: java.lang.IllegalStateException: The type handler was null on the parameter mapping for property '__frch_item_0.pgFeature'. It was either not specified and/or could not be found for the javaType (java.util.List) : jdbcType (null) combination.<br>
> Cause: java.lang.IllegalStateException: The type handler was null on the parameter mapping for property '__frch_item_0.pgFeature'. It was either not specified and/or could not be found for the javaType (java.util.List) : jdbcType (null) combination.**

Why did I get this error message? How can I insert the array value correctly?
I can insert an array value if I use the SQL in the PostgreSQL client. So, the version of PostgreSQL I installed can support the array value.

英文:

My class

class PgFolder {
    long id;
    int[] pgFeature;
}
getter...
setter...

My table

CREATE TABLE folder {
    ID BIGSERIAL PRIMARY KEY,
    feature INT[]
}

My xml

&lt;insert id=&quot;insertManyFolder&quot; parameterType=&quot;java.util.List&quot;&gt;
    INSERT INTO
        folder
       (feature)
        VALUES
        &lt;foreach collection=&quot;list&quot; item=&quot;item&quot; index=&quot;&quot; separator=&quot;,&quot;&gt;
            (#{item.pgFeature})
        &lt;/foreach&gt;
&lt;/insert&gt;

I want to insert a List&lt;PgFolder&gt; list. There are many PgFolder instance in the list. When I run the program, I got an error:

>org.apache.ibatis.exceptions.PersistenceException: <br>
Error updating database. Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property '__frch_item_0.pgFeature'. It was either not specified and/or could not be found for the javaType (java.util.List) : jdbcType (null) combination.<br>
> Cause: java.lang.IllegalStateException: Type handler was null on parameter mapping for property '__frch_item_0.pgFeature'. It was either not specified and/or could not be found for the javaType (java.util.List) : jdbcType (null) combination.**

Why I got this error message? How to insert the array value in the right way?
I can insert an array value if I use the sql in postgres client. so the postgres of the version I installed can support the array value.

答案1

得分: 4

Mapping Integer[]

class PgFolder {
  long id;
  Integer[] pgFeature;
  // getter / setter
}

在这种情况下,您可以使用 MyBatis 的内置 ArrayTypeHandler。 在参数引用中指定 typeHandler 即可。

&lt;foreach ...&gt;
  (#{item.pgFeature,typeHandler=org.apache.ibatis.type.ArrayTypeHandler})
&lt;/foreach&gt;

映射 int[]

由于内置的 ArrayTypeHandler 可能无法处理原始数组,您可能需要编写一个自定义类型处理器。例如:

package pkg;

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 org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class PgIntArrayTypeHandler extends BaseTypeHandler&lt;int[]&gt;{
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, int[] parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setObject(i, parameter);
  }

  @Override
  public int[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
    return extractArray(rs.getArray(columnName));
  }

  @Override
  public int[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    return extractArray(rs.getArray(columnIndex));
  }

  @Override
  public int[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    return extractArray(cs.getArray(columnIndex));
  }

  protected int[] extractArray(Array array) throws SQLException {
    if (array == null) {
      return null;
    }
    // pgjdbc returns Integer[] for the INT[] column
    Integer[] wrapperArray = (Integer[]) array.getArray();
    int[] result = Arrays.stream(wrapperArray).mapToInt(Integer::intValue).toArray();
    array.free();
    return result;
  }
}

然后在参数引用中指定类型处理器。

&lt;foreach ...&gt;
  (#{item.pgFeature,typeHandler=pkg.PgIntArrayTypeHandler})
&lt;/foreach&gt;

已在 MyBatis 3.5.5 和 pgjdbc 42.2.15 上进行测试。

英文:

I'll explain both solutions i.e. mapping Integer[] property and mapping int[] property.

Mapping Integer[]

class PgFolder {
  long id;
  Integer[] pgFeature;
  // getter / setter
}

In this case, you can use MyBatis' built-in ArrayTypeHandler.
Specifying typeHandler in the parameter reference is sufficient.

&lt;foreach ...&gt;
  (#{item.pgFeature,typeHandler=org.apache.ibatis.type.ArrayTypeHandler})
&lt;/foreach&gt;

Mapping int[]

As the built-in ArrayTypeHandler may not work with primitive arrays, you may have to write a custom type handler. e.g.

package pkg;

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 org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class PgIntArrayTypeHandler extends BaseTypeHandler&lt;int[]&gt;{
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, int[] parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setObject(i, parameter);
  }

  @Override
  public int[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
    return extractArray(rs.getArray(columnName));
  }

  @Override
  public int[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    return extractArray(rs.getArray(columnIndex));
  }

  @Override
  public int[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    return extractArray(cs.getArray(columnIndex));
  }

  protected int[] extractArray(Array array) throws SQLException {
    if (array == null) {
      return null;
    }
    // pgjdbc returns Integer[] for the INT[] column
    Integer[] wrapperArray = (Integer[]) array.getArray();
    int[] result = Arrays.stream(wrapperArray).mapToInt(Integer::intValue).toArray();
    array.free();
    return result;
  }
}

Then specify the type handler in the parameter reference.

&lt;foreach ...&gt;
  (#{item.pgFeature,typeHandler=pkg.PgIntArrayTypeHandler})
&lt;/foreach&gt;

Tested with MyBatis 3.5.5 and pgjdbc 42.2.15.

huangapple
  • 本文由 发表于 2020年8月20日 20:16:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/63504881.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定