[java] Spring Boot Multiple Datasource



Answers

幾天前我遇到同樣的問題,我按照下面提到的鏈接,我能夠克服這個問題

http://www.baeldung.com/spring-data-jpa-multiple-databases

Question

我對Spring啟動很新,我想為我的項目創建一個多數據源。 這是我目前的情況。 我為多個數據庫提供了兩個實體包。 比方說吧

com.test.entity.db.mysql ; for entities that belong to MySql
com.test.entity.db.h2 ; for entities that belong to H2 Databases

所以,目前我有兩個實體類

UserMySql.java

@Entity
@Table(name="usermysql")
public class UserMysql{

    @Id
    @GeneratedValue
    public int id;

    public String name;

}

UserH2.java

@Entity
@Table(name="userh2")
public class Userh2 {

    @Id
    @GeneratedValue
    public int id;

    public String name;
}

我想實現一個配置,如果我從UserMySql創建用戶,它將被保存到MySql數據庫,如果我從Userh2創建用戶,它將被保存到H2數據庫。 所以,我也有兩個DBConfig,比方說MySqlDbConfig和H2DbConfig。

(com.test.model是我將放置我的Repositories類的包。它將在下面定義)

MySqlDbConfig.java

@Configuration
@EnableJpaRepositories(
    basePackages="com.test.model",
    entityManagerFactoryRef = "mysqlEntityManager")
public class MySqlDBConfig {

@Bean
@Primary
@ConfigurationProperties(prefix="datasource.test.mysql")
public DataSource mysqlDataSource(){
    return DataSourceBuilder
            .create()
            .build();
}

@Bean(name="mysqlEntityManager")
public LocalContainerEntityManagerFactoryBean mySqlEntityManagerFactory(
        EntityManagerFactoryBuilder builder){       
    return builder.dataSource(mysqlDataSource())                
            .packages("com.test.entity.db.mysql")
            .build();
}   

}

H2DbConfig.java

@Configuration
@EnableJpaRepositories(
    entityManagerFactoryRef = "h2EntityManager")
public class H2DbConfig {

@Bean
@ConfigurationProperties(prefix="datasource.test.h2")
public DataSource h2DataSource(){
    return DataSourceBuilder
            .create()
            .driverClassName("org.h2.Driver")
            .build();
}

@Bean(name="h2EntityManager")
public LocalContainerEntityManagerFactoryBean h2EntityManagerFactory(
        EntityManagerFactoryBuilder builder){
    return builder.dataSource(h2DataSource())
            .packages("com.test.entity.db.h2")
            .build();
}
}

我的application.properties文件

#DataSource settings for mysql
datasource.test.mysql.jdbcUrl = jdbc:mysql://127.0.0.1:3306/test
datasource.test.mysql.username = root
datasource.test.mysql.password = root
datasource.test.mysql.driverClassName = com.mysql.jdbc.Driver

#DataSource settings for H2
datasource.test.h2.jdbcUrl = jdbc:h2:~/test
datasource.test.h2.username = sa

# DataSource settings: set here configurations for the database connection
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.validation-query=SELECT 1


# Specify the DBMS
spring.jpa.database = MYSQL

# Show or not log for each sql query
spring.jpa.show-sql = true

# Hibernate settings are prefixed with spring.jpa.hibernate.*
spring.jpa.hibernate.ddl-auto = update
spring.jpa.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.hibernate.show_sql = true
spring.jpa.hibernate.format_sql = true

server.port=8080
endpoints.shutdown.enabled=false

然後為了crud我有UserMySqlDao和UserH2Dao

UserMySqlDao.java

@Transactional 
@Repository
public interface UserMysqlDao extends CrudRepository<UserMysql, Integer>{

    public UserMysql findByName(String name);
}

UserH2Dao.java

@Transactional
@Repositories
public interface UserH2Dao extends CrudRepository<Userh2, Integer>{

    public Userh2 findByName(String name);
}

最後,我有一個UserController作為端點來訪問我的服務

UserController.java

@Controller 
@RequestMapping("/user")
public class UserController {


@Autowired
private UserMysqlDao userMysqlDao;

@Autowired
private UserH2Dao userH2Dao;

@RequestMapping("/createM")
@ResponseBody
public String createUserMySql(String name){
    UserMysql user = new UserMysql();
    try{            
        user.name = name;
        userMysqlDao.save(user);
        return "Success creating user with Id: "+user.id;
    }catch(Exception ex){
        return "Error creating the user: " + ex.toString();
    }
}

@RequestMapping("/createH")
@ResponseBody
public String createUserH2(String name){
    Userh2 user = new Userh2();
    try{
        user.name = name;
        userH2Dao.save(user);
        return "Success creating user with Id: "+user.id;
    }catch(Exception ex){
        return "Error creating the user: " + ex.toString();
    }
}   
}

Application.java

@Configuration
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@EntityScan(basePackages="com.test.entity.db")
@ComponentScan
public class Application {

public static void main(String[] args) {
    System.out.println("Entering spring boot");
    ApplicationContext ctx = SpringApplication.run(Application.class, args);

    System.out.println("Let's inspect the beans provided by Spring Boot:");
    String[] beanNames = ctx.getBeanDefinitionNames();
    Arrays.sort(beanNames);
    for (String beanName : beanNames) {
        System.out.print(beanName);
        System.out.print(" ");
    }

    System.out.println("");
}

}

使用這種配置,我的Spring啟動運行良好,但是當我訪問時

http://localhost/user/createM?name=myname it writes an exception

Error creating the user: org.springframework.dao.InvalidDataAccessResourceUsageException:   could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

我谷歌搜索,還沒有解決方案。 有什麼想法發生這種異常? 這是實現多個數據源以實現上述情況的最佳方法嗎? 如果需要,我願意接受全部重構。

謝謝







一旦你開始使用jpa和一些驅動程序在你的類路徑彈簧啟動馬上把它作為你的數據源(例如h2)放入使用defult數據源,因此你只需要定義

spring.datasource.url= jdbc:mysql://localhost:3306/
spring.datasource.username=test
spring.datasource.password=test

如果我們更進一步,你想使用兩個,我會建議使用兩個數據源,如下所述: Spring Boot配置和使用兩個DataSources




使用兩個數據源需要自己的事務管理器。

@Configuration
public class MySqlDBConfig {
    @Bean
    @Primary
    @ConfigurationProperties(prefix="datasource.test.mysql")
    public DataSource mysqlDataSource(){
         return DataSourceBuilder
                 .create()
                 .build();
    }

    @Bean("mysqlTx")
    public DataSourceTransactionManager mysqlTx() {
        return new DataSourceTransactionManager(mysqlDataSource());
    }

    // same for another DS
}

然後在@Transaction中相應地使用它

@Transactional("mysqlTx")
@Repository
public interface UserMysqlDao extends CrudRepository<UserMysql, Integer>{
    public UserMysql findByName(String name);
}



Links