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

最新推荐文章于 2025-05-16 01:48:59 发布

IT1995

最新推荐文章于 2025-05-16 01:48:59 发布

阅读量1.3k

点赞数

分类专栏：文档阅读笔记 Java Spring Boot 文章标签： java spring boot hibernate spring mysql

本文链接：https://blog.csdn.net/qq78442761/article/details/111628471

版权

Java 同时被 3 个专栏收录

370 篇文章

订阅专栏

文档阅读笔记

201 篇文章

订阅专栏

Spring Boot

140 篇文章

订阅专栏

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

关键的Maven依赖：

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
 
<!-- https://mvnrepository.com/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 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();
}

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