JPA 自定义通用函数异常

由于业务场景需要，我需要一个根据主键列表删除主键的dao函数，JPA内置的函数不能满足此需求，所以我新增了一个通用函数

代码如下

import org.apache.ibatis.annotations.Param;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

import java.util.List;

@NoRepositoryBean
public interface MysqlGenericRepository<T> extends JpaRepository<T, Integer> {

    int deleteByIds(@Param("ids") List<Integer> ids);

}


import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import java.util.List;

public class MysqlGenericRepositoryImpl<T> extends SimpleJpaRepository<T, Integer>
        implements MysqlGenericRepository<T> {

    private final EntityManager entityManager;
    private final Class<T> clazz;

    public MysqlGenericRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
        this.clazz = entityInformation.getJavaType();
    }

    @Override
    public int deleteByIds(List<Integer> ids) {
        Entity entity = clazz.getAnnotation(Entity.class);
        StringBuilder sb = new StringBuilder("delete from ")
                .append(entity.name()).append(" where id in(");
        for (int i = 0, m = ids.size(); i < m; i++) {
            if (i == m - 1) {
                sb.append(ids.get(i));
            } else {
                sb.append(ids.get(i)).append(",");
            }
        }
        sb.append(")");
        return entityManager.createNativeQuery(sb.toString()).executeUpdate();
    }
}




import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;
import org.springframework.data.repository.core.RepositoryMetadata;
import org.springframework.data.repository.core.support.RepositoryFactorySupport;

import javax.persistence.EntityManager;
import java.io.Serializable;

@SuppressWarnings({"rawtypes", "unchecked"})
public class BaseRepositoryFactoryBean<R extends JpaRepository<T, I>, T,
        I extends Serializable> extends JpaRepositoryFactoryBean<R, T, I> {


    public BaseRepositoryFactoryBean(Class<? extends R> repositoryInterface) {
        super(repositoryInterface);
    }

    @Override
    protected RepositoryFactorySupport createRepositoryFactory(EntityManager em) {
        return new BaseRepositoryFactory(em);
    }

    private static class BaseRepositoryFactory<T, I extends Serializable>
            extends JpaRepositoryFactory {

        private final EntityManager em;

        public BaseRepositoryFactory(EntityManager em) {
            super(em);
            this.em = em;
        }

        @Override
        protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
            return MysqlGenericRepositoryImpl.class;
        }
    }
}

运行结果异常

org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:178)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy120.deleteByIds(Unknown Source)

仔细查看错误信息，找到最后一个异常提升

Caused by: java.sql.SQLException: Connection is read-only. Queries leading to data modification are not allowed

先说解决方案，在MysqlGenericRepositoryImpl类上加注解，@Transactional(readOnly = false)，问题解决

下面说解决过程：

百度了一下此异常，搜索到的东西都大同小异，最多的答案说是事务设置，需要将xml里对应函数名设置为非只读，实操了一把，然并卵，另有答案说在db配置的url后加readOnlyPropagatesToServer=false，实践后也没有什么x用

百度无果，开始debug源码

找到执行sql的函数executeUpdateInternal，此函数中JdbcConnection的readOnly属性为true，查看JdbcConnection的属性readOnly来源为其下面子类ConnectionImpl的setReadOnly函数，此函数在DataSourceUtils中调用，多次重置其属性，设置为true的代码为

/**
	 * Prepare the given Connection with the given transaction semantics.
	 * @param con the Connection to prepare
	 * @param definition the transaction definition to apply
	 * @return the previous isolation level, if any
	 * @throws SQLException if thrown by JDBC methods
	 * @see #resetConnectionAfterTransaction
	 * @see Connection#setTransactionIsolation
	 * @see Connection#setReadOnly
	 */
	@Nullable
	public static Integer prepareConnectionForTransaction(Connection con, @Nullable TransactionDefinition definition)
			throws SQLException {

		Assert.notNull(con, "No Connection specified");

		// Set read-only flag.
		if (definition != null && definition.isReadOnly()) {
			try {
				if (logger.isDebugEnabled()) {
					logger.debug("Setting JDBC Connection [" + con + "] read-only");
				}
				con.setReadOnly(true);
			}
			catch (SQLException | RuntimeException ex) {
				Throwable exToCheck = ex;
				while (exToCheck != null) {
					if (exToCheck.getClass().getSimpleName().contains("Timeout")) {
						// Assume it's a connection timeout that would otherwise get lost: e.g. from JDBC 4.0
						throw ex;
					}
					exToCheck = exToCheck.getCause();
				}
				// "read-only not supported" SQLException -> ignore, it's just a hint anyway
				logger.debug("Could not set JDBC Connection read-only", ex);
			}
		}

		// Apply specific isolation level, if any.
		Integer previousIsolationLevel = null;
		if (definition != null && definition.getIsolationLevel() != TransactionDefinition.ISOLATION_DEFAULT) {
			if (logger.isDebugEnabled()) {
				logger.debug("Changing isolation level of JDBC Connection [" + con + "] to " +
						definition.getIsolationLevel());
			}
			int currentIsolation = con.getTransactionIsolation();
			if (currentIsolation != definition.getIsolationLevel()) {
				previousIsolationLevel = currentIsolation;
				con.setTransactionIsolation(definition.getIsolationLevel());
			}
		}

		return previousIsolationLevel;
	}

往上找其调用，是在事务管理器中调用

protected abstract void doBegin(Object transaction, TransactionDefinition definition)
			throws TransactionException;

readOnly属性记录在TransactionDefinition中，追查TransactionDefinition来源，来自TransactionAspectSupport的invokeWithinTransaction函数，主要是下面这行代码

final TransactionAttribute txAttr = (tas != null ? tas.getTransactionAttribute(method, targetClass) : null);

深入函数，从TransactionalRepositoryProxyPostProcessor中找到computeTransactionAttribute函数，这个函数决定了事务的基础属性

private TransactionAttribute computeTransactionAttribute(Method method, Class<?> targetClass) {
			// Don't allow no-public methods as required.
			if (allowPublicMethodsOnly() && !Modifier.isPublic(method.getModifiers())) {
				return null;
			}

			// Ignore CGLIB subclasses - introspect the actual user class.
			Class<?> userClass = ProxyUtils.getUserClass(targetClass);
			// The method may be on an interface, but we need attributes from the target class.
			// If the target class is null, the method will be unchanged.
			Method specificMethod = ClassUtils.getMostSpecificMethod(method, userClass);
			// If we are dealing with method with generic parameters, find the original method.
			specificMethod = BridgeMethodResolver.findBridgedMethod(specificMethod);

			TransactionAttribute txAtt = null;

			if (specificMethod != method) {
				// Fallback is to look at the original method.
				txAtt = findTransactionAttribute(method);
				if (txAtt != null) {
					return txAtt;
				}
				// Last fallback is the class of the original method.
				txAtt = findTransactionAttribute(method.getDeclaringClass());

				if (txAtt != null || !enableDefaultTransactions) {
					return txAtt;
				}
			}

			// Start: Implementation class check block

			// First try is the method in the target class.
			txAtt = findTransactionAttribute(specificMethod);
			if (txAtt != null) {
				return txAtt;
			}

			// Second try is the transaction attribute on the target class.
			txAtt = findTransactionAttribute(specificMethod.getDeclaringClass());
			if (txAtt != null) {
				return txAtt;
			}

			if (!enableDefaultTransactions) {
				return null;
			}

			// Fallback to implementation class transaction settings of nothing found
			// return findTransactionAttribute(method);
			Method targetClassMethod = repositoryInformation.getTargetClassMethod(method);

			if (targetClassMethod.equals(method)) {
				return null;
			}

			txAtt = findTransactionAttribute(targetClassMethod);
			if (txAtt != null) {
				return txAtt;
			}

			txAtt = findTransactionAttribute(targetClassMethod.getDeclaringClass());
			if (txAtt != null) {
				return txAtt;
			}

			return null;
			// End: Implementation class check block
		}

属性的解析由下面方法执行

/**
		 * Determine the transaction attribute for the given method or class.
		 * <p>
		 * This implementation delegates to configured {@link TransactionAnnotationParser TransactionAnnotationParsers} for
		 * parsing known annotations into Spring's metadata attribute class. Returns {@code null} if it's not transactional.
		 * <p>
		 * Can be overridden to support custom annotations that carry transaction metadata.
		 *
		 * @param ae the annotated method or class
		 * @return TransactionAttribute the configured transaction attribute, or {@code null} if none was found
		 */
		protected TransactionAttribute determineTransactionAttribute(AnnotatedElement ae) {
			for (TransactionAnnotationParser annotationParser : this.annotationParsers) {
				TransactionAttribute attr = annotationParser.parseTransactionAnnotation(ae);
				if (attr != null) {
					return attr;
				}
			}
			return null;
		}

看到这儿已经很清楚了，事务属性是获取注解来的，我看了下事务注解，默认的readOnly属性是false，也就是说MysqlGenericRepositoryImpl继承或实现的类里面有事务注解并且readOnly=true，或者初始化的时候添加了此属性，为了证实这一点，我运行以下代码

public static void main(String[] args) {
        Transactional t = MysqlGenericRepositoryImpl.class.getAnnotation(Transactional.class);
        System.out.println(t.readOnly());
    }

输出结果为true，问题就出在这里，新增事务注解，设置readOnly=false，问题解决