出现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了  测试一下就好了。