Java-org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection cannot be unwrap

2024年03月28日 10:58 · 阅读(493) ·

问题描述

  • 同样的代码，只是数据库地址不一样。
  • 本地正常
  • 开发环境正常
  • 测试环境，查询数据库报错
  1. [etms-payment-wallet-service,3f66d8b409c8de54,3f66d8b409c8de54,true] | ERROR | com.luoma.finance.service.impl.PaymentWalletDetailServiceImpl | http-nio-18800-exec-4 | getTransactionDiffReport() >> 调用异常 >> 入参:【dto = DiscrepancyCheck(period=202403, tenantId=1, isDelete=0)】,e:
  2. org.springframework.dao.InvalidDataAccessApiUsageException: 
  3. ### Error querying database.  Cause: java.sql.SQLFeatureNotSupportedException: `org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection` cannot be unwrapped as `com.mysql.jdbc.MySQLConnection`
  4. The error may exist in class path resource [mapper/PaymentWalletAccountMapper.xml]
  5. ### The error may involve defaultParameterMap
  6. ### The error occurred while setting parameters
  7. ### SQL: select recharge_order_number,                pay_order_number,                pay_currency_code,                org_name,                pay_status,                pay_month,                actual_pay_date,                sum(pay_amount) AS total_pay_amount         from payment_wallet_account         where is_delete = ?           and tenant_id = ?           and actual_pay_month = ?         group by recharge_order_number,               pay_order_number,               pay_currency_code,               org_name,               pay_status,               pay_month,               actual_pay_date
  8. ### Cause: java.sql.SQLFeatureNotSupportedException: `org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection` cannot be unwrapped as `com.mysql.jdbc.MySQLConnection`
  9. ; `org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection` cannot be unwrapped as `com.mysql.jdbc.MySQLConnection`; nested exception is java.sql.SQLFeatureNotSupportedException: `org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection` cannot be unwrapped as `com.mysql.jdbc.MySQLConnection`

shading 配置文件

  1. dataSources:
  2.   master_0:
  3.     dataSourceClassName: com.zaxxer.hikari.HikariDataSource
  4.     driverClassName: com.mysql.jdbc.Driver
  5.     jdbcUrl: jdbc:mysql://1.1.1.1:3306/test1111?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
  6.     username: aaaaaaaa
  7.     password: aaaaaaaa
  9. # 这里进行分库分表的表，需要在ShardingConfig中进行热启动，否则第一次查询会很慢
  10. rules:
  11.   - !SHARDING
  12.     tables:
  13.       payment_wallet_iface:
  14.         actualDataNodes: master_${0}.payment_wallet_iface_${2024}${(2..5).collect{it.toString().padLeft(2,'0')}}
  15.         tableStrategy:
  16.           standard:
  17.             shardingColumn: pay_date
  18.             shardingAlgorithmName: pay_date_algorithm
  19.         keyGenerateStrategy:
  20.           column: id
  21.           keyGeneratorName: snowflake
  22.       payment_wallet_detail:
  23.         actualDataNodes: master_${0}.payment_wallet_detail_${2024}${(2..5).collect{it.toString().padLeft(2,'0')}}
  24.         tableStrategy:
  25.           standard:
  26.             shardingColumn: pay_month
  27.             shardingAlgorithmName: pay_month_algorithm
  28.         keyGenerateStrategy:
  29.           column: id
  30.           keyGeneratorName: snowflake
  32.       payment_wallet_account:
  33.         actualDataNodes: master_${0}.payment_wallet_account_${2024}${(3..5).collect{it.toString().padLeft(2,'0')}}
  34.         tableStrategy:
  35.           standard:
  36.             shardingColumn: actual_pay_month
  37.             shardingAlgorithmName: pay_month_algorithm
  38.         keyGenerateStrategy:
  39.           column: id
  40.           keyGeneratorName: snowflake
  42.       payment_wallet_batch:
  43.         actualDataNodes: master_${0}.payment_wallet_batch_${2024}${(2..5).collect{it.toString().padLeft(2,'0')}}
  44.         tableStrategy:
  45.           standard:
  46.             shardingColumn: pay_month
  47.             shardingAlgorithmName: pay_month_algorithm
  48.         keyGenerateStrategy:
  49.           column: id
  50.           keyGeneratorName: snowflake
  51.       payment_wallet_kv:
  52.         actualDataNodes: master_${0}.payment_wallet_kv_${0..49}
  53.         tableStrategy:
  54.           complex:
  55.             shardingColumns: reference_id
  56.             shardingAlgorithmName: payment_wallet_kv_algorithm
  57.         keyGenerateStrategy:
  58.           column: id
  59.           keyGeneratorName: snowflake
  60.       payment_wallet_transaction:
  61.         actualDataNodes: master_${0}.payment_wallet_transaction_${0..49}
  62.         tableStrategy:
  63.           complex:
  64.             shardingColumns: pay_order_number
  65.             shardingAlgorithmName: payment_wallet_transaction_algorithm
  66.         keyGenerateStrategy:
  67.           column: id
  68.           keyGeneratorName: snowflake
  69.       payment_wallet_ap_check_batch:
  70.         actualDataNodes: master_${0}.payment_wallet_ap_check_batch_${2024}${(2..5).collect{it.toString().padLeft(2,'0')}}
  71.         tableStrategy:
  72.           standard:
  73.             shardingColumn: actual_pay_month
  74.             shardingAlgorithmName: pay_month_algorithm
  75.         keyGenerateStrategy:
  76.           column: id
  77.           keyGeneratorName: snowflake
  78.       payment_wallet_ap_check_detail:
  79.         actualDataNodes: master_${0}.payment_wallet_ap_check_detail_${2024}${(2..5).collect{it.toString().padLeft(2,'0')}}
  80.         tableStrategy:
  81.           standard:
  82.             shardingColumn: actual_pay_month
  83.             shardingAlgorithmName: pay_month_algorithm
  84.         keyGenerateStrategy:
  85.           column: id
  86.           keyGeneratorName: snowflake
  88.     shardingAlgorithms:
  89.       pay_date_algorithm:
  90.         type: CLASS_BASE
  91.         props:
  92.           strategy: standard
  93.           algorithmClassName: com.luoma.finance.sharingrule.PayDateStandardShardingAlgorithm
  95.       pay_month_algorithm:
  96.         type: CLASS_BASE_PAY_MONTH
  97.         props:
  98.           strategy: standard
  99.           algorithmClassName: com.luoma.finance.sharingrule.PayMonthAlgorithm
  101.       payment_wallet_kv_algorithm:
  102.         type: CLASS_WALLET_KV
  103.         props:
  104.           strategy: complex
  105.           algorithmClassName: com.luoma.finance.sharingrule.WalletKvShardingRule
  107.       payment_wallet_transaction_algorithm:
  108.         type: CLASS_WALLET_PAY_ORDER_NUMBER
  109.         props:
  110.           strategy: complex
  111.           algorithmClassName: com.luoma.finance.sharingrule.WalletPayOrderNumberShardingRule
  115.     keyGenerators:
  116.       snowflake:
  117.         type: SNOWFLAKE
  118.   - !SINGLE
  119.     tables:
  120.       - "*.*"
  122. props:
  123.   sql-show: true

问题分析

1.检查测试环境数据库连接，表是否创建

  • 检查测试环境下面的数据库，用户名，密码是否能正常使用

  • 检查报错查询的接口表是否创建

  1. dataSources:
  2.   master_0:
  3.     dataSourceClassName: com.zaxxer.hikari.HikariDataSource
  4.     driverClassName: com.mysql.jdbc.Driver
  5.     jdbcUrl: jdbc:mysql://a.a.a.a:3306/test1111?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
  6.     username: aaaaaaaa
  7.     password: aaaaaaaa

2.检查 sharding 配置是否正确

  • 拷贝测试环境报错的配置文件到本地，查询报错接口，看看是否正常

3.检查测试环境对应的 Jar 包是否正常

  • 检查本地和测试环境编译生成的 Jar 包，看看对应版本和名称是否正常

4.本地启动 test 分支是否正常

  • 正常

5.开发环境使用 test 分支发布是否正常

  • 正常

6.本地使用 test 环境数据库是否正常

  • 正常

问题解决

1.重新整理上传了 application.yml 文件

2.检查了表结构，发现缺失了一些字段

2.重新整理了 shading.yml 文件

  1. dataSources:
  2.   master_0:
  3.     dataSourceClassName: com.zaxxer.hikari.HikariDataSource
  4.     driverClassName: com.mysql.jdbc.Driver
  5.     jdbcUrl: jdbc:mysql://1.1.1.1:3306/test1111?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false
  6.     username: aaaaaaaa
  7.     password: aaaaaaaa
  9. # 这里进行分库分表的表，需要在ShardingConfig中进行热启动，否则第一次查询会很慢
  10. rules:
  11.   - !SHARDING
  12.     tables:
  13.       payment_wallet_iface:
  14.         actualDataNodes: master_${0}.payment_wallet_iface_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  15.         tableStrategy:
  16.           standard:
  17.             shardingColumn: pay_date
  18.             shardingAlgorithmName: pay_date_algorithm
  19.         keyGenerateStrategy:
  20.           column: id
  21.           keyGeneratorName: snowflake
  23.       payment_wallet_detail:
  24.         actualDataNodes: master_${0}.payment_wallet_detail_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  25.         tableStrategy:
  26.           standard:
  27.             shardingColumn: pay_month
  28.             shardingAlgorithmName: pay_month_algorithm
  29.         keyGenerateStrategy:
  30.           column: id
  31.           keyGeneratorName: snowflake
  33.       payment_wallet_account:
  34.         actualDataNodes: master_${0}.payment_wallet_account_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  35.         tableStrategy:
  36.           standard:
  37.             shardingColumn: actual_pay_month
  38.             shardingAlgorithmName: pay_month_algorithm
  39.         keyGenerateStrategy:
  40.           column: id
  41.           keyGeneratorName: snowflake
  43.       payment_wallet_batch:
  44.         actualDataNodes: master_${0}.payment_wallet_batch_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  45.         tableStrategy:
  46.           standard:
  47.             shardingColumn: pay_month
  48.             shardingAlgorithmName: pay_month_algorithm
  49.         keyGenerateStrategy:
  50.           column: id
  51.           keyGeneratorName: snowflake
  53.       payment_wallet_kv:
  54.         actualDataNodes: master_${0}.payment_wallet_kv_${0..49}
  55.         tableStrategy:
  56.           complex:
  57.             shardingColumns: reference_id
  58.             shardingAlgorithmName: payment_wallet_kv_algorithm
  59.         keyGenerateStrategy:
  60.           column: id
  61.           keyGeneratorName: snowflake
  63.       payment_wallet_transaction:
  64.         actualDataNodes: master_${0}.payment_wallet_transaction_${0..49}
  65.         tableStrategy:
  66.           complex:
  67.             shardingColumns: pay_order_number
  68.             shardingAlgorithmName: payment_wallet_transaction_algorithm
  69.         keyGenerateStrategy:
  70.           column: id
  71.           keyGeneratorName: snowflake
  73.       payment_wallet_ap_check_batch:
  74.         actualDataNodes: master_${0}.payment_wallet_ap_check_batch_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  75.         tableStrategy:
  76.           standard:
  77.             shardingColumn: actual_pay_month
  78.             shardingAlgorithmName: pay_month_algorithm
  79.         keyGenerateStrategy:
  80.           column: id
  81.           keyGeneratorName: snowflake
  83.       payment_wallet_ap_check_detail:
  84.         actualDataNodes: master_${0}.payment_wallet_ap_check_detail_202${4..6}${(1..12).collect{it.toString().padLeft(2,'0')}}
  85.         tableStrategy:
  86.           standard:
  87.             shardingColumn: actual_pay_month
  88.             shardingAlgorithmName: pay_month_algorithm
  89.         keyGenerateStrategy:
  90.           column: id
  91.           keyGeneratorName: snowflake
  93.     shardingAlgorithms:
  94.       pay_date_algorithm:
  95.         type: CLASS_BASE
  96.         props:
  97.           strategy: standard
  98.           algorithmClassName: com.luoma.finance.sharingrule.PayDateStandardShardingAlgorithm
  100.       pay_month_algorithm:
  101.         type: CLASS_BASE_PAY_MONTH
  102.         props:
  103.           strategy: standard
  104.           algorithmClassName: com.luoma.finance.sharingrule.PayMonthAlgorithm
  106.       payment_wallet_kv_algorithm:
  107.         type: CLASS_WALLET_KV
  108.         props:
  109.           strategy: complex
  110.           algorithmClassName: com.luoma.finance.sharingrule.WalletKvShardingRule
  112.       payment_wallet_transaction_algorithm:
  113.         type: CLASS_WALLET_PAY_ORDER_NUMBER
  114.         props:
  115.           strategy: complex
  116.           algorithmClassName: com.luoma.finance.sharingrule.WalletPayOrderNumberShardingRule
  118.     keyGenerators:
  119.       snowflake:
  120.         type: SNOWFLAKE
  121.   - !SINGLE
  122.     tables:
  123.       - "*.*"
  125. props:
  126.   sql-show: true