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

悠悠 2022-12-30 07:59 194阅读 0赞

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

关键的Maven依赖：

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

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

application.properties的代码如下：

#Oracle DB Config
    db.dialect=org.hibernate.dialect.Oracle10gDialect
    db.driver=oracle.jdbc.driver.OracleDriver
    db.url=jdbc:oracle:thin:@localhost:1521:xe
    db.user=pavans
    db.password=******
    
    #MySQL DB Config
    mysql.db.dialect=org.hibernate.dialect.MySQLDialect
    mysql.db.driver=com.mysql.jdbc.Driver
    mysql.db.url=jdbc:mysql://localhost:3306/localdb?useSSL=false
    mysql.db.user=lessroot
    mysql.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";
    	
    	@Autowired
    	private Environment env;
    
    	
    	@Bean
    	public AnnotationMBeanExporter annotationMBeanExporter() {
    	    AnnotationMBeanExporter annotationMBeanExporter = new AnnotationMBeanExporter();
    	    annotationMBeanExporter.addExcludedBean("dataSource");
    	    annotationMBeanExporter.setRegistrationPolicy(RegistrationPolicy.IGNORE_EXISTING);
    	    return annotationMBeanExporter;
    	}
    	
    	@Bean(destroyMethod = "close")
    	@Primary
    	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 = "transactionManager")
    	@Primary
    	public JpaTransactionManager jpaTransactionManager() {
    		JpaTransactionManager transactionManager = new JpaTransactionManager();
    		transactionManager.setEntityManagerFactory(entityManagerFactoryBean().getObject());
    		return transactionManager;
    	}
    
    	@Bean(name = "entityManagerFactory")
    	@Primary
    	public 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
    	@Primary
    	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;
    	}
    }

这里有几个关键地方：

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";
    
    	@Autowired
    	private Environment env;
    
    	@Bean
    	public 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="http://java.sun.com/xml/ns/persistence"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/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。

仓库声明如下：

@Autowired
    private OracleMessageRepo oracleMessageRepo;
    
    @Autowired
    private 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();
    }

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxNzg0NDI3NjE_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxNzg0NDI3NjE_size_16_color_FFFFFF_t_70 1][]

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

创库代码如下：

[https://github.com/pavansolapure/opencodez-samples/tree/master/multi-db][https_github.com_pavansolapure_opencodez-samples_tree_master_multi-db]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxNzg0NDI3NjE_size_16_color_FFFFFF_t_70]: /images/20221120/5c6adb36f25c4b89a2900166416b5e33.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxNzg0NDI3NjE_size_16_color_FFFFFF_t_70 1]: /images/20221120/eed554417e2d42f7970ec2fa3b5c9966.png
[https_github.com_pavansolapure_opencodez-samples_tree_master_multi-db]: https://github.com/pavansolapure/opencodez-samples/tree/master/multi-db