Hibernate/JPA实体对象状态之Sharding-JDBC异常：Can not update sharding key

wsdhla

已于 2023-09-11 15:09:53 修改

阅读量1.9k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： # Spring 文章标签： hibernate jpa Sharding key ShardingSphere

于 2023-04-21 14:00:00 首次发布

本文链接：https://blog.csdn.net/wsdhla/article/details/130279652

Spring 专栏收录该内容

20 篇文章

订阅专栏

先上异常堆栈：

Hibernate: update Bbb set B1=?, TenantID=? where ID=?
2023-04-20 11:24:04.303 [http-nio-8090-exec-7] ERROR c.e.t.MyServiceImpl - 51******************出现异常：
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [Bbb], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@2b0aaffb].
	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:59)
	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:42)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.lambda$decorate$0(ShardingRouteDecorator.java:61)
	at java.util.Optional.ifPresent(Optional.java:159)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:61)
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53)

...
...
...
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at com.sun.proxy.$Proxy205.flush(Unknown Source)
	at com.etoak.tian.MyServiceImpl.saveAaa(MyServiceImpl.java:178)
...
...
...

从异常字面意思理解：不能更新分库字段。而且前一句Hibernate打印的sql上看确实如此。

关键问题是：整个业务代码就没有任何更新操作，涉及到事务的只有对Aaa对象的save和delete，那么如何触发的这个update呢？

架构&业务背景：

1、Spring JPA + Sharding-JDBC

2、根据字段TenantID对业务表（Aaa、Bbb等）进行分库

问题现象：

调用saveAndFlush(Aaa)对象时，提示不能更新Bbb表。

最终原因定位：

使用JPA查出Bbb数据之后，由于业务需要，又对Bbb进行了赋值，而正是这个赋值操作触发了update。这就涉及到Hibernate中的实体对象状态管理，以及缓存等相关内容，需要大家自行延伸扩展。

根本原因是因为在执行查询以后，查询结果对象在EntityManager上下文中进行了一级缓存，执行set方法以后缓存对象状态【持久态】，事务提交时会自动帮我们flush到数据库中，导致数据被更新。

*********20230911更新*************************************************************

后面又遇到一次这个异常，但是按照上面的原因排查了很多遍，确实不存在以上问题

org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [ConttInfo], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@1fa7d4ef].
	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:59) ~[sharding-core-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateStatementValidator.java:42) ~[sharding-core-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.lambda$decorate$0(ShardingRouteDecorator.java:61) ~[sharding-core-route-4.1.1.jar:4.1.1]
	at java.util.Optional.ifPresent(Optional.java:159) ~[na:1.8.0_232]
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:61) ~[sharding-core-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.decorate(ShardingRouteDecorator.java:53) ~[sharding-core-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:91) ~[shardingsphere-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76) ~[shardingsphere-route-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54) ~[shardingsphere-pluggable-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96) ~[shardingsphere-pluggable-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83) ~[shardingsphere-pluggable-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.addBatch(ShardingPreparedStatement.java:235) ~[sharding-jdbc-core-4.1.1.jar:4.1.1]
	at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.addToBatch(BatchingBatch.java:78) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3571) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3438) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3870) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:202) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at java.util.LinkedHashMap.forEach(LinkedHashMap.java:684) ~[na:1.8.0_232]
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:344) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:107) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1407) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1394) ~[hibernate-core-5.6.15.Final.jar:5.6.15.Final]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_232]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_232]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_232]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_232]
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:362) ~[spring-orm-5.3.29.jar:5.3.29]
	at com.sun.proxy.$Proxy110.flush(Unknown Source) ~[na:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_232]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_232]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_232]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_232]
	at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:315) ~[spring-orm-5.3.29.jar:5.3.29]
	at com.sun.proxy.$Proxy110.flush(Unknown Source) ~[na:na]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.flush(SimpleJpaRepository.java:727) ~[spring-data-jpa-2.7.15.jar:2.7.15]
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.saveAndFlush(SimpleJpaRepository.java:682) ~[spring-data-jpa-2.7.15.jar:2.7.15]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_232]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_232]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_232]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_232]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:289) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:530) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:286) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:640) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:76) ~[spring-data-commons-2.7.15.jar:2.7.15]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174) ~[spring-data-jpa-2.7.15.jar:2.7.15]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) ~[spring-aop-5.3.29.jar:5.3.29]
	at com.sun.proxy.$Proxy113.saveAndFlush(Unknown Source) ~[na:na]
	at com.etoak.testshardingsphere.controller.TestController.getAll(TestController.java:52) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_232]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_232]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_232]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_232]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1072) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:965) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.3.29.jar:5.3.29]

后来终于定位到了问题原因了。。。

因为用了分库，所以jpa提供的一些api需要重写，比如saveAndFlush，因为jap会先去根据主键查询记录判断存在不存在，这个时候必须带上分库key才可以。

另外saveAndFlush原本就是走的javax.persistence.EntityManager类的persist()或merge()，所以更新操作也需要重写。

// SimpleJpaRepository.class

    @Transactional
    public <S extends T> S save(S entity) {
        Assert.notNull(entity, "Entity must not be null.");
        if (this.entityInformation.isNew(entity)) {
            this.em.persist(entity);
            return entity;
        } else {
            return this.em.merge(entity);
        }
    }

    @Transactional
    public <S extends T> S saveAndFlush(S entity) {
        S result = this.save(entity);
        this.flush();
        return result;
    }

而这次遇到的问题是由于之前解决saveAllAndFlush效率问题更换为更底层的EntityManager的merge()方法，报错了，确实是因为找不到分库key。

经过多次测试：

1、查询如果没有给定分库key条件，会查询所有分库的数据集，取合集；

2、更新如果没有给定分库key，会报以上异常；

3、更新时，如果把分库key字段值更新为其他值，也会报以上异常。

附：

启动类需要增加注解：

@SpringBootApplication
// @EnableJpaRepositories("com.etoak.testshardingsphere.repository")
@EntityScan("com.etoak.testshardingsphere.entity")
@EnableJpaRepositories( repositoryBaseClass = JpaRepositoryExt.class,basePackages = "com.etoak.testshardingsphere.repository")
public class TestShardingsphereApplication {

    public static void main(String[] args) {
        SpringApplication.run(TestShardingsphereApplication.class, args);
    }
}

自定义扩展类JpaRepositoryExt

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.transaction.Transactional;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

@SuppressWarnings (value = "all")
@Slf4j
public class JpaRepositoryExt<T, ID> extends SimpleJpaRepository<T, ID> {

    private final JpaEntityInformation<T, ?> entityInformation;
    private final EntityManager em;

    @Autowired
    public JpaRepositoryExt(
            JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityInformation = entityInformation;
        this.em = entityManager;
    }

    
    @Override
    @Transactional
    public <S extends T> S saveAndFlush(S o) {
        ID entityId = (ID) this.entityInformation.getId(o);
        List<Object> obj = em.createQuery("SELECT id FROM " + o.getClass().getSimpleName() + " c WHERE c.id = :id and c.分库key= :分库key")
                .setParameter("id", entityId)
                .setParameter("分库key", getFieldValueByName("分库key", o))
                .getResultList();
        if (null == obj || obj.size() <= 0) {
            em.persist(o);
        } else {
            this.update(o);
        }
        this.flush();
        return o;
    }
    
    @Override
    @Transactional
    public <S extends T> List<S> saveAllAndFlush(Iterable<S> entities) {
        List<S> list = new ArrayList<>();
        for (S item:entities) {
            list.add(saveAndFlush(item));
        }
        return list;
    }

    public <S extends T> S update(S o) {
        String tableName = o.getClass().getSimpleName();
        StringBuffer sb = new StringBuffer();
        String sqlHeader = "update " + tableName + " set ";
        sb.append(sqlHeader);
        Field[] fields = o.getClass().getDeclaredFields();

        // 遍历拼接update语句
        for (Field field : fields) {
            String fieldName = field.getName();

            if ("分库key".equals(fieldName.toUpperCase())) {
                continue;
            }
            Object value = getFieldValueByName(fieldName, o);
            if (null != value) {

                String column = fieldName + "=:" + fieldName+",";
                sb.append(column);
            }
        }
        // 去除最后一个,
        int last=sb.lastIndexOf(",");
        sb.delete(last,last+1);
        // 拼接where 条件
        sb.append( " WHERE id = :id and 分库key= :分库key");

        Query query = em.createQuery(sb.toString());
        // 遍历拼接参数
        for (Field field : fields) {
            Object value = getFieldValueByName(field.getName(), o);
            if (null != value) {
                query.setParameter(field.getName(),value);
            }
        }

        ID entityId = (ID) this.entityInformation.getId(o);
        query.setParameter("id", entityId);
        query.setParameter("分库key", getFieldValueByName("分库key", o));
        query.executeUpdate();
        this.flush();
        return o;
    }

    
    private Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(o, new Object[]{});
            return value;
        } catch (Exception e) {
            return null;
        }
    }
}