我是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是一个包,我将在其中放置我的存储库类。它将在下面定义)
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
我已经在谷歌上搜索过了,还没有找到解决方案。你知道为什么会发生这种异常吗?这是实现多个数据源来实现我上面的案例的最佳方式吗?如果需要,我对完全重构持开放态度。
谢谢
发布于 2014-12-29 03:45:34
我想你会发现它很有用
http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-two-datasources
它展示了如何定义多个数据源并将其中一个数据源指定为主数据源。
这是一个相当完整的例子,如果你需要的话,它还包含了分布式事务。
您需要的是创建两个配置类,分离模型/存储库包等,以使配置变得简单。
此外,在上面的示例中,它手动创建数据源。您可以在spring doc上使用带有@ConfigurationProperties注释的方法来避免这种情况。下面是一个这样的例子:
http://xantorohara.blogspot.com.tr/2013/11/spring-boot-jdbc-with-multiple.html
希望这些能有所帮助。
发布于 2016-07-20 21:30:27
几天前我遇到了同样的问题,我点击了下面提到的链接,我可以克服这个问题。
发布于 2016-10-27 14:14:25
使用多个数据源或实现读写分离。你必须有支持动态数据源选择的AbstractRoutingDataSource
类的知识。
这是我的datasource.yaml
,我想出了如何解决这个问题。你可以参考这个项目的spring-boot + quartz。希望这能对你有所帮助。
dbServer:
default: localhost:3306
read: localhost:3306
write: localhost:3306
datasource:
default:
type: com.zaxxer.hikari.HikariDataSource
pool-name: default
continue-on-error: false
jdbc-url: jdbc:mysql://${dbServer.default}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8
username: root
password: lh1234
connection-timeout: 30000
connection-test-query: SELECT 1
maximum-pool-size: 5
minimum-idle: 2
idle-timeout: 600000
destroy-method: shutdown
auto-commit: false
read:
type: com.zaxxer.hikari.HikariDataSource
pool-name: read
continue-on-error: false
jdbc-url: jdbc:mysql://${dbServer.read}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8
username: root
password: lh1234
connection-timeout: 30000
connection-test-query: SELECT 1
maximum-pool-size: 5
minimum-idle: 2
idle-timeout: 600000
destroy-method: shutdown
auto-commit: false
write:
type: com.zaxxer.hikari.HikariDataSource
pool-name: write
continue-on-error: false
jdbc-url: jdbc:mysql://${dbServer.write}/schedule_job?useSSL=true&verifyServerCertificate=false&useUnicode=true&characterEncoding=utf8
username: root
password: lh1234
connection-timeout: 30000
connection-test-query: SELECT 1
maximum-pool-size: 5
minimum-idle: 2
idle-timeout: 600000
destroy-method: shutdown
auto-commit: false
https://stackoverflow.com/questions/27614301
复制相似问题