Spring Boot多个数据源

Z时代
2024-01-10
分类：问答

我对Spring Boot" title="Spring Boot">Spring Boot还是很陌生，我想为我的项目创建一个多数据源。这是我目前的情况。我有两个用于多个数据库的实体包。比方说

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

然后我有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();
    }
}   
}

应用程序

@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

我已经四处搜寻，还没有解决方案。任何想法为什么会发生此异常？这是实现多个数据源以实现上述情况的最佳方法吗？如果需要，我愿意进行全面重构。