Spring Boot文档阅读笔记-使用Spring Data JPA连接多源数据库（MySQL和Oracle）

下面这个小项目展示了如何连接2个数据库，一个是Oracle，一个是MySQL。

关键的Maven依赖：

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!-- /artifact/oracle/ojdbc6 --><dependency><groupId>com.oracle</groupId><artifactId>ojdbc6</artifactId><version>11.2.0</version></dependency><!-- Database and Pooling --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency>

这个Oracle的jar包是不再maven仓库里面的，需要自己手动导入到maven仓库。

application.properties的代码如下：

#Oracle DB Configdb.dialect=org.hibernate.dialect.Oracle10gDialectdb.driver=oracle.jdbc.driver.OracleDriverdb.url=jdbc:oracle:thin:@localhost:1521:xedb.user=pavansdb.password=******#MySQL DB Configmysql.db.dialect=org.hibernate.dialect.MySQLDialectmysql.db.driver=com.mysql.jdbc.Drivermysql.db.url=jdbc:mysql://localhost:3306/localdb?useSSL=falsemysql.db.user=lessrootmysql.db.password=******

所以，要定义2个dataSource的Bean，Spring需要知道那个dataSource是主要的，哪个是次要的。如果不去定义主次，那么Spring程序将会启动失败。如果不定义主次，那么相同的bean，Spring是不能将其注册的。

使用@Primary注解定义主要的dataSource的Bean。

首要的DtaSource配置：

@Configuration@EnableTransactionManagement@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory", transactionManagerRef = "transactionManager", basePackages = "com.opencodez.dao.oracle.repo")public class PrimaryDbConfig {public static final String PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE = "hibernate.jdbc.batch_size";public static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";public static final String PROPERTY_NAME_HIBERNATE_FMT_SQL = "hibernate.format_sql";public static final String[] ENTITYMANAGER_PACKAGES_TO_SCAN = { "com.opencodez.dao.oracle.domain" };public static final String DB_URL = "db.url";public static final String DB_USER = "db.user";public static final String DB_PASSWORD = "db.password";public static final String DB_DRIVER = "db.driver";public static final String DB_DIALECT = "db.dialect";@Autowiredprivate Environment env;@Beanpublic AnnotationMBeanExporter annotationMBeanExporter() {AnnotationMBeanExporter annotationMBeanExporter = new AnnotationMBeanExporter();annotationMBeanExporter.addExcludedBean("dataSource");annotationMBeanExporter.setRegistrationPolicy(RegistrationPolicy.IGNORE_EXISTING);return annotationMBeanExporter;}@Bean(destroyMethod = "close")@Primarypublic DataSource dataSource() {ComboPooledDataSource dataSource = new ComboPooledDataSource();try {dataSource.setDriverClass(env.getProperty(DB_DRIVER));} catch (PropertyVetoException e) {e.printStackTrace();}dataSource.setJdbcUrl(env.getProperty(DB_URL));dataSource.setUser(env.getProperty(DB_USER));dataSource.setPassword(env.getProperty(DB_PASSWORD));dataSource.setAcquireIncrement(5);dataSource.setMaxStatementsPerConnection(20);dataSource.setMaxStatements(100);dataSource.setMaxPoolSize(500);dataSource.setMinPoolSize(5);return dataSource;}@Bean(name = "transactionManager")@Primarypublic JpaTransactionManager jpaTransactionManager() {JpaTransactionManager transactionManager = new JpaTransactionManager();transactionManager.setEntityManagerFactory(entityManagerFactoryBean().getObject());return transactionManager;}@Bean(name = "entityManagerFactory")@Primarypublic LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();entityManagerFactoryBean.setJpaVendorAdapter(vendorAdaptor());entityManagerFactoryBean.setDataSource(dataSource());entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);entityManagerFactoryBean.setPersistenceUnitManager(persistenceUnitManager());entityManagerFactoryBean.setPersistenceUnitName("orcl");entityManagerFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);entityManagerFactoryBean.setJpaProperties(jpaHibernateProperties());return entityManagerFactoryBean;}@Bean@Primarypublic DefaultPersistenceUnitManager persistenceUnitManager() {DefaultPersistenceUnitManager persistenceUnitManager = new DefaultPersistenceUnitManager();persistenceUnitManager.setDefaultDataSource(dataSource());return persistenceUnitManager;}private HibernateJpaVendorAdapter vendorAdaptor() {HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();vendorAdapter.setDatabasePlatform(env.getProperty(DB_DIALECT));vendorAdapter.setShowSql(false);return vendorAdapter;}private Properties jpaHibernateProperties() {Properties properties = new Properties();properties.put(PROPERTY_NAME_HIBERNATE_FMT_SQL, env.getProperty(PROPERTY_NAME_HIBERNATE_FMT_SQL));properties.put(PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE, env.getProperty(PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE));properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL, env.getProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));return properties;}}

这里有几个关键地方：

1. dataSource要加上@Primary注解；

2. entityMananger也要加上@Primary注解；

3. persistenceUnitManager同样也要加上@Primary

次要DataSource源配置：

@Configuration@EnableTransactionManagement@EnableJpaRepositories(entityManagerFactoryRef = "mysqlEntityManager", transactionManagerRef = "mysqlTransactionManager", basePackages = "com.opencodez.dao.mysql.repo")public class SecondaryDbConfig {public static final String PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE = "hibernate.jdbc.batch_size";public static final String PROPERTY_NAME_HIBERNATE_SHOW_SQL = "hibernate.show_sql";public static final String PROPERTY_NAME_HIBERNATE_FMT_SQL = "hibernate.format_sql";public static final String[] ENTITYMANAGER_PACKAGES_TO_SCAN = { "com.opencodez.dao.mysql.domain" };public static final String DB_URL = "mysql.db.url";public static final String DB_USER = "mysql.db.user";public static final String DB_PASSWORD = "mysql.db.password";public static final String DB_DRIVER = "mysql.db.driver";public static final String DB_DIALECT = "mysql.db.dialect";@Autowiredprivate Environment env;@Beanpublic AnnotationMBeanExporter annotationMBeanExporter() {AnnotationMBeanExporter annotationMBeanExporter = new AnnotationMBeanExporter();annotationMBeanExporter.addExcludedBean("dataSource");annotationMBeanExporter.setRegistrationPolicy(RegistrationPolicy.IGNORE_EXISTING);return annotationMBeanExporter;}@Bean(name = "mysqlDataSource", destroyMethod = "close")public DataSource dataSource() {ComboPooledDataSource dataSource = new ComboPooledDataSource();try {dataSource.setDriverClass(env.getProperty(DB_DRIVER));} catch (PropertyVetoException e) {e.printStackTrace();}dataSource.setJdbcUrl(env.getProperty(DB_URL));dataSource.setUser(env.getProperty(DB_USER));dataSource.setPassword(env.getProperty(DB_PASSWORD));dataSource.setAcquireIncrement(5);dataSource.setMaxStatementsPerConnection(20);dataSource.setMaxStatements(100);dataSource.setMaxPoolSize(500);dataSource.setMinPoolSize(5);return dataSource;}@Bean(name = "mysqlTransactionManager")public JpaTransactionManager jpaTransactionManager() {JpaTransactionManager transactionManager = new JpaTransactionManager();transactionManager.setEntityManagerFactory(entityManagerFactoryBean().getObject());return transactionManager;}@Bean(name = "mysqlEntityManager")public LocalContainerEntityManagerFactoryBean entityManagerFactoryBean() {LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();entityManagerFactoryBean.setJpaVendorAdapter(vendorAdaptor());entityManagerFactoryBean.setDataSource(dataSource());entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class);entityManagerFactoryBean.setPersistenceUnitManager(persistenceUnitManager());entityManagerFactoryBean.setPersistenceUnitName("mysql");entityManagerFactoryBean.setPackagesToScan(ENTITYMANAGER_PACKAGES_TO_SCAN);entityManagerFactoryBean.setJpaProperties(jpaHibernateProperties());return entityManagerFactoryBean;}@Bean(name = "mysqlpersistenceUnitManager")public DefaultPersistenceUnitManager persistenceUnitManager() {DefaultPersistenceUnitManager persistenceUnitManager = new DefaultPersistenceUnitManager();persistenceUnitManager.setDefaultDataSource(dataSource());return persistenceUnitManager;}private HibernateJpaVendorAdapter vendorAdaptor() {HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();vendorAdapter.setDatabasePlatform(env.getProperty(DB_DIALECT));vendorAdapter.setShowSql(false);return vendorAdapter;}private Properties jpaHibernateProperties() {Properties properties = new Properties();properties.put(PROPERTY_NAME_HIBERNATE_FMT_SQL, env.getProperty(PROPERTY_NAME_HIBERNATE_FMT_SQL));properties.put(PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE,env.getProperty(PROPERTY_NAME_HIBERNATE_JDBC_BATCH_SIZE));properties.put(PROPERTY_NAME_HIBERNATE_SHOW_SQL, env.getProperty(PROPERTY_NAME_HIBERNATE_SHOW_SQL));return properties;}}

这里配置类，无需@Primary注解，但是@Bean中的name需要唯一。

这样就完成了数据库的配置，下面定义2个Java类，用于映射数据库表，一个是TblOracle，一个是TblMysql。

TblOracle

@Entity@Table(name = "TBL_ORCL")public class TblOracle {@Id@GeneratedValue@Column(name = "MESSAGE_ID")private Long id;@Column(name = "MESSAGE")private String message;@Column(name = "CREATED_DATE")private Date created;//Getters and Setters}

TblMysql

@Entity@Table(name = "tbl_mysql")public class TblMysql {@Id@GeneratedValue@Column(name = "MESSAGE_ID")private Long id;@Column(name = "MESSAGE")private String message;@Column(name = "CREATED_DATE")private Date created;//Getters and Setters}

对应的persistence.xml如下：

<?xml version="1.0" encoding="UTF-8"?><persistence xmlns="/xml/ns/persistence"xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/xml/ns/persistence /xml/ns/persistence/persistence_1_0.xsd"version="1.0"><persistence-unit name="orcl" transaction-type="RESOURCE_LOCAL"><class>com.opencodez.dao.oracle.domain.TblOracle</class><exclude-unlisted-classes>true</exclude-unlisted-classes></persistence-unit><persistence-unit name="mysql" transaction-type="RESOURCE_LOCAL"><class>com.opencodez.dao.mysql.domain.TblMysql</class><exclude-unlisted-classes>true</exclude-unlisted-classes></persistence-unit></persistence>

在此事例中，定义了相同的controller，这些controller调用了Spring Data JPA中的Repositories。

仓库声明如下：

@Autowiredprivate OracleMessageRepo oracleMessageRepo;@Autowiredprivate MysqlMessageRepo mysqlMessageRepo;

调用如下：

List<TblOracle> messages = oracleMessageRepo.findAll();List<TblMysql> messages = mysqlMessageRepo.findAll();

上面是使用JPA的方式存储，如果要使用传统的实体管理，需要这样：

@Autowired@Qualifier("entityManagerFactory")private EntityManager oracleEM;@Autowired@Qualifier("mysqlEntityManager")private EntityManager mysqlEM;

这里需要自己提供检索条件，如下事例：

try {String sql = "select t from TblOracle t";Query query = oracleEM.createQuery(sql);List<TblOracle> list =(List<TblOracle>)query.getResultList( );} catch (Exception e) {e.printStackTrace();}try {String sql = "select t from TblMysql t";Query query = mysqlEM.createQuery(sql);List<TblMysql> list=(List<TblMysql>)query.getResultList( );} catch (Exception e) {e.printStackTrace();}

下面是调用了，运行截图如下：

总结：本次实例展示了如何使用Spring Data JPA轻松配置多源数据库。

创库代码如下：

/pavansolapure/opencodez-samples/tree/master/multi-db