Help us understand the problem. What is going on with this article?

Spring Data JPAでバージョンが異なるMySQLにそれぞれアクセスする

バージョンの違う旧DBと新DB両方に接続したいときがあったので、Spring Data JPAでの実装方法のメモ

環境の準備

dockerを使ってバージョンの違うMysql、sample01とsample02を立てる

docker-compose.yml
version: "3"
services:
  mysql_sample01:
    image: mysql:5.5          // 旧バージョン
    container_name: sample01
    ports:
      - 3355:3306
    environment:
      - MYSQL_DATABASE=sample
      - MYSQL_USER=sample_user
      - MYSQL_PASSWORD=password
      - MYSQL_ROOT_PASSWORD=root
      - TZ=UTC
    volumes:
      - ./sample01/initdb.d:/docker-entrypoint-initdb.d
  mysql_sample02:
    image: mysql:latest          // 新バージョン
    container_name: sample02
    ports:
      - 3356:3306
    environment:
      - MYSQL_DATABASE=sample
      - MYSQL_USER=sample_user
      - MYSQL_PASSWORD=password
      - MYSQL_ROOT_PASSWORD=root
      - TZ=UTC
    volumes:
      - ./sample02/initdb.d:/docker-entrypoint-initdb.d

user_tableを作ってデータを入れとく

// sample01
mysql> select * from user_table;
+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+
| 001 | Taro       | Tanaka    |
+-----+------------+-----------+
1 row in set (0.00 sec)

// sample02
mysql> select * from user_table;
+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+
| 002 | Jiro       | Suzuki    |
+-----+------------+-----------+
1 row in set (0.00 sec)

準備完了!

実装

ポイントは、

  • 接続先のDBごとにパッケージを別にしてrepositoryを作ること
  • repositoryごとのconfigファイルを作ること

↓ こんな感じ

demo/src/main/java/com/example/demo
 ├ controller/
 └ domein
     ├ entity/
     └ repository
           ├ sample01
           |    └ UserRepository01.java
           |
           ├ sample02
           |    └ UserRepository02.java
           |
           └ config
                ├ Sample01MysqlConfig.java
                └ Sample02MysqlConfig.java
controller

 sample01とsample02のselect結果を返すだけ

SampleController.java
package com.example.demo.controller;
// import省略

@RestController
@RequestMapping("/demo")
public class SampleController {
    private final UserRepository01 userRepository01;
    private final UserRepository02 userRepository02;

    public SampleController(UserRepository01 userRepository01, UserRepository02 userRepository02) {
        this.userRepository01 = userRepository01;
        this.userRepository02 = userRepository02;
    }

    @GetMapping
    public UserResponse getUser() {
        return new UserResponse(userRepository01.findAll(), userRepository02.findAll());
    }
}
Entity
UserEntity.java
package com.example.demo.domain.entity;
// import省略

@Entity
@Table(name="user_table")
@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserEntity {

    @Id
    @Column(name = "id")
    private String id;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;
}

sample01用のrepositoryの設定ファイル

 sample01に接続するためのもろもろの設定
 各メソッドに@primaryをつけてやる必要がある

Sample01MysqlConfig.java
package com.example.demo.domain.config;
// import省略

@Configuration
@EnableJpaRepositories(basePackages = {
        "com.example.demo.domain.repository.sample01" }, entityManagerFactoryRef = "sample01EntityManager")
public class Sample01MysqlConfig {
    @Value("${spring.datasource.sample01.driverClassName}")
    private String driverClassName;
    @Value("${spring.datasource.sample01.url}")
    private String url;
    @Value("${spring.datasource.sample01.username}")
    private String username;
    @Value("${spring.datasource.sample01.password}")
    private String password;

    @Bean("sample01")
    @Primary
    public DataSource createDataSource() {
        return DataSourceBuilder
                .create()
                .driverClassName(driverClassName)
                .url(url)
                .username(username)
                .password(password)
                .build();
    }

    @Bean(name = "sample01EntityManager")
    @Primary
    public EntityManagerFactory mySqlEntityManagerFactory(@Qualifier("sample01") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource);
        factory.setPackagesToScan("com.example.demo.domain.entity");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.afterPropertiesSet();

        return factory.getObject();
    }
}
sample01用のrepository

 Sample01MysqlConfig.javaのbasePackagesで指定した場所に配置

UserRepository01.java
package com.example.demo.domain.repository.sample01;
// import省略

@Repository
public interface UserRepository01 extends JpaRepository<UserEntity, String>{
}
sample02用のrepositoryの設定ファイル

 だいたいSample01MysqlConfig.javaと同じ
 primaryは外す
 BeanNameはつけないとダメ

Sample01MysqlConfig.java
package com.example.demo.domain.config;
// import省略

@Configuration
@EnableJpaRepositories(basePackages = {
        "com.example.demo.domain.repository.sample02" }, entityManagerFactoryRef = "sample02EntityManager")
public class Sample02MysqlConfig {
    @Value("${spring.datasource.sample02.driverClassName}")
    private String driverClassName;
    @Value("${spring.datasource.sample02.url}")
    private String url;
    @Value("${spring.datasource.sample02.username}")
    private String username;
    @Value("${spring.datasource.sample02.password}")
    private String password;

    @Bean("sample02")
    public DataSource createDataSource() {
        return DataSourceBuilder
                .create()
                .driverClassName(driverClassName)
                .url(url)
                .username(username)
                .password(password)
                .build();
    }

    @Bean(name = "sample02EntityManager")
    public EntityManagerFactory mySqlEntityManagerFactory(@Qualifier("sample02") DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setDataSource(dataSource);
        factory.setPackagesToScan("com.example.demo.domain.entity");

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.afterPropertiesSet();

        return factory.getObject();
    }
}
sample02用のrepository

 Sample02MysqlConfig.javaのbasePackagesで指定した場所に配置

UserRepository02.java
package com.example.demo.domain.repository.sample02;
// import省略

@Repository
public interface UserRepository02 extends JpaRepository<UserEntity, String>{
}

実行結果

{
  "sample01":[
    {"id":"001","firstName":"Taro","lastName":"Tanaka"}
  ],
  "sample02":[
    {"id":"002","firstName":"Jiro","lastName":"Suzuki"}
  ]
}

それぞれのDBのデータが取得できました!

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away