Edited at

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のデータが取得できました!