实现方案

  1. <dependency>
  2.       <groupId>mysql</groupId>
  3.       <artifactId>mysql-connector-java</artifactId>
  4.     </dependency>
  6.     <dependency>
  7.       <groupId>org.postgresql</groupId>
  8.       <artifactId>postgresql</artifactId>
  9.       <version>42.3.3</version>
  10.     </dependency>
  12.     <dependency>
  13.       <groupId>com.h2database</groupId>
  14.       <artifactId>h2</artifactId>
  15.       <scope>test</scope>
  16.     </dependency>
  1. spring:
  2.   datasource:
  3.     dynamic:
  4.       primary: h2
  5.       datasource:
  6.         mysql:
  7.           url: jdbc:mysql://${zlt.mysql.datasource.ip}:${zlt.mysql.datasource.port}/user-center?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
  8.           username: ${zlt.mysql.datasource.username}
  9.           password: ${zlt.mysql.datasource.password}
  10.           driver-class-name: com.mysql.cj.jdbc.Driver
  11.         postgresql:
  12.           url: jdbc:postgresql://${zlt.postgresql.datasource.ip}:5432/user-center
  13.           username: ${zlt.postgresql.datasource.username}
  14.           password: ${zlt.postgresql.datasource.password}
  15.           driver-class-name: org.postgresql.Driver
  16.         h2:
  17.           url: jdbc:h2:mem:user-center;DB_CLOSE_DELAY=-1;INIT=runscript from 'classpath:db/user-center-schema.sql'\;runscript from 'classpath:db/user-center-data.sql'
  18.           driver-class-name: org.h2.Driver
  19.           username: sa
  20.           password:
  21.   h2:
  22.     console:
  23.       enabled: true
  24.       path: /h2
  25.       settings:
  26.         web-allow-others: true

实现自定义注解
image.png

  1. @Target({ElementType.TYPE, ElementType.METHOD})
  2. @Retention(RetentionPolicy.RUNTIME)
  3. @Documented
  4. @DS("h2")
  5. public @interface H2 {
  7. }
  9. @Target({ElementType.TYPE, ElementType.METHOD})
  10. @Retention(RetentionPolicy.RUNTIME)
  11. @Documented
  12. @DS("mysql")
  13. public @interface MySQL {
  15. }

整合h2数据库

问题一：多数据源的注解只生效于service上标注
只作用Mapper层的调用的方法？在mapper的测试类增加注解，不能生效

问题二：读取不到h2数据库的表
使用mysql、pg数据源启动，测试没有问题
image-20220615151307039.png
问题是使用h2启动测试
image-20220615152156410.png
启动用户中心的服务，h2使用的是定义的数据库，访问http://localhost:7000/h2也没有问题，适用语句在h2可视化界面查找，确实没有表，于是添加了表和数据，再次启动测试还是不行，但是通过接口访问可以获取数据
image-20220615152244550.png
百度说是使用内存模式的h2当自动化测试停止后就断开了内存的h2
因此，尝试使用持久化的h2，测试成功

问题三：做不到h2数据库数据的初始化
当不使用多数据源的时候，指定schema和data，可以做到数据的初始化
但如果是通过多数据源切换到h2，这两个参数无效

spring:
  datasource:
    url: jdbc:h2:mem:user-center;DB_CLOSE_DELAY=-1
    schema: classpath:db/user-center-schema.sql
    data: classpath:db/user-center-data.sql
    driver-class-name: org.h2.Driver
    username: sa
    password:

  h2:
    console:
      enabled: true
      path: /h2
      settings:
        web-allow-others: true

总结：内存和持久化两种模式
其实问题都是出在表结构和数据的初始化问题，确实是找不到表或者数据
解决初始化问题：
虽然schema和data两个参数失效，也不能在全局的datasource配置这两个参数，这样会将mysql和pg数据库的数据也初始化
因此，只能在h2的url上加上参数

spring:
  datasource:
    dynamic:
      primary: h2
      datasource:
        mysql:
          url: jdbc:mysql://${zlt.mysql.datasource.ip}:${zlt.mysql.datasource.port}/user-center?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
          username: ${zlt.mysql.datasource.username}
          password: ${zlt.mysql.datasource.password}
          driver-class-name: com.mysql.cj.jdbc.Driver
        postgresql:
          url: jdbc:postgresql://${zlt.postgresql.datasource.ip}:5432/user-center
          username: ${zlt.postgresql.datasource.username}
          password: ${zlt.postgresql.datasource.password}
          driver-class-name: org.postgresql.Driver
        h2:
            url: jdbc:h2:mem:user-center;DB_CLOSE_DELAY=-1;INIT=runscript from 'classpath:db/user-center-schema.sql'\;runscript from 'classpath:db/user-center-data.sql'
            #          url: jdbc:h2:file:D:/data/user-center
            driver-class-name: org.h2.Driver
            username: sa
            password:
    # spring.datasource全局配置下生效
    #    schema: classpath:db/user-center-schema.sql
    #    data: classpath:db/user-center-data.sql
    initialization-mode: always
  h2:
    console:
      enabled: true
      path: /h2
      settings:
        web-allow-others: true

理论上来说，两种模式都可以加上参数，但是服务在运行的时候，会锁住本地的持久化数据文件，占用数据库。所以，最好使用内存模式
image-20220615183600575.png

数据库语法不兼容

mysql数据源更改为pg的问题

  • boolean字段与smallint ```sql CREATE OR REPLACE FUNCTION boolean_to_smallint(b boolean) RETURNS smallint AS $$ BEGIN RETURN (b::boolean)::bool::int; END; $$LANGUAGE plpgsql;

CREATE CAST (boolean AS smallint) WITH FUNCTION boolean_to_smallint(boolean) AS implicit;


- 主键自增
```sql
CREATE SEQUENCE oauth_client_details_id_seq START 5;

nextval('oauth_client_details_id_seq')

image-20220620120338377.png