出现ORA-01461:仅能绑定要插入LONG列的LONG值 mybatis+orcale

批量存储CLOB类型：

原sql语句：

<insert id="insertByList" parameterType="java.util.List">insert into Users(fid,fname)<foreach open="(" close=")" collection="list" item="item"index="index" separator="union">select#{item.fid,jdbcType=VARCHAR},#{fname,jdbcType=CLOB}from dual</foreach></insert> 

此时报错：出现ORA-01461:仅能绑定要插入LONG列的LONG值

原因在于sql语句中 select 后面的字段如果大于4000个字节时就会被转成Long型。

解决办法：使用拦截器

BatchExecutorAdaptor.java

package com.voucher.engine.utils.batch;import com.google.common.base.Throwables;import org.apache.ibatis.executor.BatchExecutor;import org.apache.ibatis.executor.BatchResult;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.session.Configuration;import org.apache.ibatis.transaction.Transaction;import java.sql.SQLException;import java.util.Arrays;import java.util.Collections;import java.util.List;import java.util.Map;final class BatchExecutorAdaptor extends BatchExecutor {  public BatchExecutorAdaptor(Configuration configuration, Transaction transaction) {    super(configuration, transaction);  }  @Override  public int update(MappedStatement ms, Object parameter) throws SQLException {    if (parameter == null) {      super.update(ms, parameter);    }    Map map = (Map)parameter;    map.get("list");    final Object params = map.get("list");    final Iterable<?> paramIterable = toIterable(params);    try {      for (Object obj : paramIterable) {        super.update(ms, obj);      }      List<BatchResult> batchResults = doFlushStatements(false);      if (batchResults == null || batchResults.size() == 0) {        return 0;      }      return resolveUpdateResult(batchResults);    } catch (Exception e) {      doFlushStatements(true);      Throwables.propagate(e);      return 0;    }  }  private Iterable<?> toIterable(final Object params) {    if (params == null) {      return Collections.emptyList();    }    Iterable<?> paramIterable;    if (params instanceof Iterable) {      paramIterable = (Iterable<?>) params;    } else if (params.getClass().isArray()) {      Object[] array = (Object[]) params;      paramIterable = Arrays.asList(array);    } else {      paramIterable = Collections.singletonList(params);    }    return paramIterable;  }  private int resolveUpdateResult(final List<BatchResult> batchResults) {    int result = 0;    for (BatchResult batchResult : batchResults) {      int[] updateCounts = batchResult.getUpdateCounts();      if (updateCounts == null || updateCounts.length == 0) {        continue;      }      for (int updateCount : updateCounts) {        result += updateCount;      }    }    return result;  }}

BatchExecutorInterceptor.java

package com.voucher.engine.utils.batch;import java.sql.SQLException;import java.util.Properties;import org.apache.ibatis.executor.BatchExecutor;import org.apache.ibatis.executor.Executor;import org.apache.ibatis.mapping.MappedStatement;import org.apache.ibatis.plugin.Interceptor;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.plugin.Signature;import org.apache.ibatis.session.Configuration;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.voucher.engine.utils.batch.utils.BatchUtils;import com.voucher.engine.utils.batch.utils.ExecutorUtils;import com.voucher.engine.utils.batch.utils.Reflections;/** * 方便使用的批量更新插件,只需要sql statement id以batch开头,参数为Iterable或者数组即可. * <p/> * 限制:最好是作为第一个拦截器使用,因为在它之前的拦截器不会被调用 * *  */@Intercepts({        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),})public class BatchExecutorInterceptor implements Interceptor {  private static final Logger LOGGER = LoggerFactory.getLogger(BatchExecutorInterceptor.class);  @Override  public Object intercept(final Invocation invocation) throws Throwable {    //check argument    if (invocation.getArgs()[1] == null) {      return invocation.proceed();    }    final MappedStatement ms = (MappedStatement) invocation.getArgs()[0];    //if it should use batch    if (!BatchUtils.shouldDoBatch(ms.getId())) {      return invocation.proceed();    }    //create batch executor    final Executor targetExecutor = ExecutorUtils.getTargetExecutor((Executor) invocation.getTarget());    if (targetExecutor instanceof BatchExecutor) {      return invocation.proceed();    }    final Configuration configuration = (Configuration) Reflections.getField("configuration", targetExecutor);    final BatchExecutor batchExecutor = new BatchExecutorAdaptor(configuration, targetExecutor.getTransaction());    try {      return batchExecutor.update(ms, invocation.getArgs()[1]);    } catch (SQLException e) {      batchExecutor.flushStatements(true);      throw e;    }  }  @Override  public Object plugin(final Object target) {    if (!(target instanceof Executor)) {      return target;    }    if (target instanceof BatchExecutor) {      return target;    }    return Plugin.wrap(target, this);  }  @Override  public void setProperties(final Properties properties) {  }}

BatchUtils.java

package com.voucher.engine.utils.batch.utils;public abstract class BatchUtils {  private BatchUtils() {  }  public static boolean shouldDoBatch(final String statementId) {    return statementId.startsWith("batch", statementId.lastIndexOf('.') + 1);  }}

package com.voucher.engine.utils.batch.utils;import org.apache.ibatis.executor.CachingExecutor;import org.apache.ibatis.executor.Executor;import java.lang.reflect.Proxy;public abstract class ExecutorUtils {  private ExecutorUtils() {  }  public static Executor getTargetExecutor(final Executor executor) {    Executor targetExecutor = executor;    while (targetExecutor instanceof Proxy) {      targetExecutor = (Executor) Reflections.getField("target",              Proxy.getInvocationHandler(targetExecutor));    }    //取真正的executor    if (targetExecutor instanceof CachingExecutor) {      targetExecutor = (Executor) Reflections.getField("delegate", targetExecutor);    }    return targetExecutor;  }}

package com.voucher.engine.utils.batch.utils;import java.lang.reflect.Field;public final class Reflections {  private Reflections() {  }  private static Field findField(Class<?> clazz, String name) {    return findField(clazz, name, null);  }  public static Field findField(Class<?> clazz, String name, Class<?> type) {    Class<?> searchType = clazz;    while (!Object.class.equals(searchType) && searchType != null) {      Field[] fields = searchType.getDeclaredFields();      for (Field field : fields) {        if ((name == null || name.equals(field.getName())) && (type == null || type                .equals(field.getType()))) {          return field;        }      }      searchType = searchType.getSuperclass();    }    return null;  }  public static Object getField(String fieldName, Object target) {    Field field = findField(target.getClass(), fieldName);    if (!field.isAccessible()) {      field.setAccessible(true);    }    try {      return field.get(target);    } catch (IllegalAccessException ex) {      throw new IllegalStateException("Unexpected reflection exception - " + ex.getClass()              .getName() + ": " + ex.getMessage(), ex);    }  }}

开始写配置文件：

mybatisDao.xml:(以batch开头)

<insert id="batchInsert" parameterType="java.util.List">insert into Users (fid,fname) values(#{fid,jdbcType=VARCHAR},#{fname,jdbcType=CLOB})}</insert> 

mybatis-configxml：

 <plugins>   <plugin interceptor="com.voucher.engine.utils.batch.BatchExecutorInterceptor"></plugin>    </plugins>

appliccationContext.xml

<bean id="SqlSessionFactoryOracle" class="org.mybatis.spring.SqlSessionFactoryBean"><property name="dataSource" ref="dataSourceOracle" /><property name="typeAliasesPackage" value="com.voucher.**.domain" /><property name="mapperLocations"><list><value>classpath*:mybatis/StorageDao.xml</value></list></property><property name="configLocation" value="classpath:mybatis/mybatis-config.xml"/></bean>

ok了  测试一下就好了。