java hibernate postgresql shardingshpere shardingshere-proxy分库分表使用时，主键设置不生效为null的报错处理

文章目录

场景报错一：分库的表报错代码程序报错sharding中间件报错解决

报错二：不分库的表报错代码程序报错sharding中间件报错解决

场景

SaaS平台项目，需要根据租户分库，使用ShardingSphere-proxy 版本5.1项目采用Spring Cloud微服务架构，全部是Spring Boot项目，持久层使用hibernate，版本 5.4编程语言Java，程序运行，使用jdk 11数据库PostgresQL 13.5普通的JPA保存方法，代码没有变动，原本可以正常运行没有报错，使用sharding分库分表后，报错

报错一：分库的表

报错代码

报错的表，是需要分库的表，已经在配置文件里设置好了分库规则和主键规则使用的就是hibernate普通的保存方法

Long id = this.saveEntity(cmsContentEO);

程序报错

下面是我的应用程序运行报错的部分日志

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259) ~[spring-orm-5.3.3.jar:5.3.3]

at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-5.3.3.jar:5.3.3]

at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551) ~[spring-orm-5.3.3.jar:5.3.3]

at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.3.3.jar:5.3.3]

at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) ~[spring-tx-5.3.3.jar:5.3.3]

at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174) ~[spring-data-jpa-2.4.3.jar:2.4.3]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215) ~[spring-aop-5.3.3.jar:5.3.3]

at com.sun.proxy.$Proxy178.save(Unknown Source) ~[na:na]

at cn.lonsun.core.base.service.impl.BaseService.saveEntity(BaseService.java:113) ~[ls-framework-core-4.0.5-20220125.094401-2.jar:na]

at cn.lonsun.digitalvillage.content.content.internal.service.impl.CmsContentServiceImpl.addContent(CmsContentServiceImpl.java:143) ~[classes/:na]

at cn.lonsun.digitalvillage.content.content.internal.service.impl.CmsContentServiceImpl$$FastClassBySpringCGLIB$$8fb2b787.invoke() ~[classes/:na]

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.3.jar:5.3.3]

at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.3.jar:5.3.3]

......

Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.id.enhanced.SequenceStructure$1.getNextValue(SequenceStructure.java:95) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.id.enhanced.NoopOptimizer.generate(NoopOptimizer.java:40) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.id.enhanced.SequenceStyleGenerator.generate(SequenceStyleGenerator.java:523) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:115) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:185) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:128) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:55) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:720) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:706) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

......

Caused by: org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist

位置：17

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.23.jar:42.2.23]

at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.2.23.jar:42.2.23]

at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na]

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]

at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57) ~[hibernate-core-5.4.27.Final.jar:5.4.27.Final]

... 135 common frames omitted

sharding中间件报错

PostgreSQLComParseExecutor :: sql = select nextval ('hibernate_sequence')

[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select nextval ('hibernate_sequence')

[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)

[INFO ] 2022-03-03 17:48:42.200 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select nextval ('hibernate_sequence')

[ERROR] 2022-03-03 17:48:42.216 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:

org.postgresql.util.PSQLException: ERROR: relation "hibernate_sequence" does not exist

Position: 17

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)

at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:132)

at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)

at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyPreparedStatementExecutorCallback.execute(ProxyPreparedStatementExecutorCallback.java:41)

at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:73)

at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:66)

at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:44)

at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:85)

at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)

at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:101)

at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:87)

at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:81)

at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)

at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:71)

at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:185)

at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:138)

at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:127)

at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.doExecute(ProxyLockEngine.java:103)

at org.apache.shardingsphere.proxy.backend.communication.ProxyLockEngine.execute(ProxyLockEngine.java:81)

at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:126)

at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.PostgreSQLPortal.execute(PostgreSQLPortal.java:99)

at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.bind.PostgreSQLComBindExecutor.execute(PostgreSQLComBindExecutor.java:49)

at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.binary.execute.PostgreSQLComExecuteExecutor.execute(PostgreSQLComExecuteExecutor.java:56)

at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:99)

at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:72)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

at java.lang.Thread.run(Thread.java:748)

解决

关键报错是ERROR: relation "hibernate_sequence" does not exist，说明程序没有使用我的主键策略检查了下主键注解，实体类的底层抽象类，主键策略配置的事AUTO将主键注解改为 IDENTIFIFY 后解决，不报这个错误了对于一个项目，建议将公共字段作为抽象类或接口，保持统一性，主键全部使用ID字段，这样也方便以后的修改，像这次，只需要修改抽象类即可，不需要改动所有实体类

/**

* 主键

@Id

@GeneratedValue(strategy = GenerationType.IDENTITY)

@Column(name = "id")

private Long id;

报错二：不分库的表

报错代码

部分表不需要分库分表，单独在一个库里存储已经在sharding的配置文件，设置了公共的主键生成策略考虑到这些表不需要分库分表，就没有在sharding配置文件里对这些表配置还是普通的hibernate的保存方法

this.saveEntity(customerInfoEO);

程序报错

insert的是测试数据，没有实际意义会对部分字段（身份证、手机号）进行加密，使用的也是sharding，这里没有配置，没体现出来

2022-03-07 15:20:52.773 WARN 37532 --- [io-10010-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState: 23502

2022-03-07 15:20:52.773 ERROR 37532 --- [io-10010-exec-6] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: null value in column "id" of relation "customer_info" violates not-null constraint

详细：Failing row contains (null, 镜湖社区, 张三二, 17689789097, 2022-03-07 00:00:00, 2022-04-07 00:00:00, 1,M, 0, 108340, 2022-03-07 15:20:52.643, 108340, 2022-03-07 15:20:52.643, null, 0, null, null, null, null, 340000,340200,340202).

2022-03-07 15:20:52.947 ERROR 37532 --- [io-10010-exec-6] c.l.c.b.controller.LSControllerAdvice : 程序已知异常捕获：could not execute statement; SQL [n/a]; constraint [id" of relation "customer_info]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

cn.lonsun.core.exception.FormatedException: cn.lonsun.core.exception.BaseRunTimeException : null

at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController.saveCustomerInfo(CustomerInfoController.java:110) ~[classes/:na]

at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController$$FastClassBySpringCGLIB$$4942ea3a.invoke() ~[classes/:na]

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.3.jar:5.3.3]

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.3.jar:5.3.3]

at cn.lonsun.core.base.logger.LogAspect.logAround(LogAspect.java:138) ~[ls-framework-core-4.0.5-SNAPSHOT.jar:na]

at cn.lonsun.digitalvillage.customer.controller.CustomerInfoController$$EnhancerBySpringCGLIB$$abb9989b.saveCustomerInfo() ~[classes/:na]

at cn.lonsun.core.base.filter.BaseFilter.doFilter(BaseFilter.java:100) ~[ls-framework-core-4.0.5-SNAPSHOT.jar:na]

sharding中间件报错

[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: insert into customer_info (create_date, create_organ_id, create_user_id, update_date, update_user_id, record_status, collect_date_limit, customer_contacts, customer_end_time, customer_name, customer_phone, customer_start_time, customer_term, customer_type, org_code, org_id, recurrent_collect_limit, remark) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

RETURNING *

[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLInsertStatement(withSegment=Optional.empty)

[INFO ] 2022-03-07 15:41:11.114 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds ::: insert into customer_info (create_date, create_organ_id, create_user_id, update_date, update_user_id, record_status, collect_date_limit, customer_contacts, customer_end_time, customer_name, customer_phone, customer_start_time, customer_term, customer_type, org_code, org_id, recurrent_collect_limit, remark) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

RETURNING * ::: [2022-03-07 15:41:11.156+08, null, 108340, 2022-03-07 15:41:11.156+08, 108340, 0, null, 张三二, 2022-04-07 00:00:00+08, 镜湖社区, 17689789097, 2022-03-07 00:00:00+08, 1,M, 0, 340000,340200,340202, null, null, null]

[ERROR] 2022-03-07 15:41:11.124 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:

org.postgresql.util.PSQLException: ERROR: null value in column "id" of relation "customer_info" violates not-null constraint

Detail: Failing row contains (null, 镜湖社区, 张三二, 17689789097, 2022-03-07 00:00:00, 2022-04-07 00:00:00, 1,M, 0, 108340, 2022-03-07 15:41:11.156, 108340, 2022-03-07 15:41:11.156, null, 0, null, null, null, null, 340000,340200,340202).

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)

at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)

at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)

at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)

at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)