springboot+shardingjdbc+mybatis+oracle与mysql坑

于 2021-10-20 13:45:25 发布
阅读量2.2k 收藏 5
点赞数 3
分类专栏： SpringBoot 文章标签： oracle mysql java spring boot
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lovezhaohaimig/article/details/120864329
版权

随着公司的业务增长，从一个工厂单表对应的数据量到达为2个亿数据量，现在引入4个工厂数据估计数量到达10个亿数据量，考虑后期数据量导致数据表崩溃。想引入现在比较流行的分库分表shardingjdbc技术，由于只分表不库的功能，按照厂site进行分成四张表。

步骤如下；

第一步 引入包

<!--Oracle驱动 11.2.0.3  -->
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.1.0.6.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

        <!-- sharding-sphere -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

第二步 配置yml文件

spring:
  #配置Sharding-jdbc
  shardingsphere:
    datasource:
      names: ds1,ds2
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: oracle.jdbc.driver.OracleDriver
        url: jdbc:oracle:thin:@172.127.17.249:1521:d1rptdb1
        username: root
        password: 123456
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: oracle.jdbc.driver.OracleDriver
        url: jdbc:oracle:thin:@172.127.17.249:1521:d1rptdb1
        username: root
        password: 123456
    sharding:
      props:
        sql.show: true
      tables:
        T_ORDER:  #t_user表
          actual-data-nodes: ds${1..2}.T_ORDER${1..2}    #数据节点,均匀分布
          table-strategy:  #分表策略
            inline: #行表达式
              sharding-column: ORDER_ID
              algorithm-expression: T_ORDER$->{ORDER_ID % 2+1}  #按模运算分配
      # 默认数据源，未分片的表默认执行库
      default-database-strategy:
        inline:
          sharding-column:  ORDER_ID
          algorithm-expression: ds$->{ORDER_ID % 2+1}
    props:
      sql:
        show: true

注意点：由于目前业务需求只考虑分表，不考虑分库。我第一反应的只要一个数据源就可以没有必要搞两个或者两个以数据源。但是问题来了：如果只在yml配置一个数据源启动报：

13:43:37.213 [main] INFO ShardingSphere-metadata - Loading 1 logic tables' meta data.
13:43:48.733 [main] INFO ShardingSphere-metadata - Loading 527 tables' meta data.
java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在
而且为什么出现Loading 527 tables' meta data  527表。 反反复复看官文文档:https://shardingsphere.apache.org/document/4.1.1/cn/manual/sharding-jdbc/configuration/config-spring-boot/配置也没有错,由于自己在本地电脑上重新安装mysql数据库，同样的代码

package com.shardingjdb.shardingjdbc.Controller;


import com.alibaba.druid.pool.DruidDataSource;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.Random;

public class Test {

    public static void main(String[] args) {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        // 配置第一个数据源
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //druidDataSource.setUrl("jdbc:mysql://10.108.243.87:3306/order");
        druidDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/order?useUnicode=yes&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        dataSourceMap.put("ds0",druidDataSource);


        //在EDU_LDA库中创建了三张表：T_ORDER,T_ORDER1,T_ORDER2
        // 配置orders表规则  ds0.t_user${0..1}orders_${1..2}
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("t_order","ds0.t_order${1..2}");

        // 配置分库+分表策略
        //orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id","ds${customer_id%2+1}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration
                                                         ("ORDER_ID","t_order$->{ORDER_ID % 2+1}"));

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
        shardingRuleConfiguration.getTableRuleConfigs().add(orderTableRuleConfig);

        // 获取数据源对象
        try {
            DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap,shardingRuleConfiguration,new Properties());
            Connection connection = dataSource.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO t_order(ORDER_ID, USER_ID, STATUS) values(?,?,?)");

            for (int i = 420; i <430 ; i++) {
                preparedStatement.setInt(1,i);
                preparedStatement.setInt(2,i);
                preparedStatement.setInt(3,i);
                preparedStatement.execute();
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }

    }
}

mysql是进行起到分表的作用，我就纳闷啊 为什么mysql一个数据源可以 而oralce一个数据源不可以总是报表或者视图不存在，

启动项目报错如下：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'artificialNodeHistoryController': Unsatisfied dependency expressed through field 'service'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'artificialNodeHistoryServiceImpl': Unsatisfied dependency expressed through field 'olFirstruneqflagMapper'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'olFirstruneqflagMapper' defined in file [D:\code\ims-switchline-service\ims-switchline-service-svc\target\classes\com\csot\ims\mapper\OlFirstruneqflagMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingDataSource' defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingDataSource' threw exception; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在

at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$AutowiredFieldElement.inject(AutowiredAnnotationBeanPostProcessor.java:643) at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:130) at org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor.postProcessProperties(AutowiredAnnotationBeanPostProcessor.java:399) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1420) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516) at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324) at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322) at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:897) at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:879) at org.springframework.context.support.AbstractApplicationContext.__refresh(AbstractApplicationContext.java:551) at org.springframework.context.support.AbstractApplicationContext.jrLockAndRefresh(AbstractApplicationContext.java:40002) at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:41008) at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758) at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) at com.csot.ims.Application.main(Application.java:30) Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'artificialNodeHistoryServiceImpl': Unsatisfied dependency expressed through field 'olFirstruneqflagMapper'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'olFirstruneqflagMapper' defined in file [D:\code\ims-switchline-service\ims-switchline-service-svc\target\classes\com\csot\ims\mapper\OlFirstruneqflagMapper.class]: Unsatisfied dependency expressed through bean property 'sqlSessionFactory'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'sqlSessionFactory' defined in class path resource [com/baomidou/mybatisplus/autoconfigure/MybatisPlusAutoConfiguration.class]: Unsatisfied dependency expressed through method 'sqlSessionFactory' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingDataSource' defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingDataSource' threw exception; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: 表或视图不存在

由于我在oracle中搞两个相同的数据源就可以启动项目，我不知道是我自己那里配置问题还是shardingjdbc支持oracle一个bug呢，总之解决自己困惑好几天的问题今天终于解决还是挺开心的。 本来想不能用shardingjdb分表，想用oracel的分区表来实现，但是领导对应这个做法不太满意，怕10个亿扛不住，由于我又不断的在测试环境测压数据量是否能扛住10亿的数据。

总结上面问题：如果你也出现上面问题是否考虑配置两个数据源，虽然只用到一个数据源那就配置两个相同的数据源。

第三步 创建数据库表:

 CREATE TABLE "EDU_LDA"."T_ORDER" 
   (	"ORDER_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"USER_ID" NUMBER(*,0) NOT NULL ENABLE, 
	"STATUS" NUMBER(*,0), 
	 PRIMARY KEY ("ORDER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BD_LDA_DAT"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "BD_LDA_DAT"

分别创建三个表：T_ORDER 、T_ORDER​​​​​​​1、T_ORDER2

 第四步：写sql 插入语句

  <!-- 保存order信息-->
    <insert id="insertOrder" parameterType="com.csot.ims.entity.Order">
    INSERT INTO T_ORDER (ORDER_ID, USER_ID, STATUS)
            VALUES(#{orderId}, #{userId},#{status})
    </insert>

代码：
 

  @ApiOperation("testshardingjdbc")
    @GetMapping("/testshardingjdbc")
    public RestResponse testshardingjdbc(@Valid @NotBlank @ApiParam("InstanceNo") String instanceNo) {
        try {
            Order order = new Order();
            order.setStatus(6);
            order.setUserId(6);
            order.setOrderId(24);
            orderService.insertOrder(order);
            //orderService.saveFactoryList();

            return RestResponse.ok("");
        } catch (Exception e) {
            log.error("=="+e);
            return RestResponse.failed(500, "testshardingjdbc信息发生异常" + e.getMessage());
        }
    }

调用接口后

 赶紧试试吧 祝你也成功！

 

lovezhaohaimig
关注
  • 3
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值