编程知识 cdmana.com

Spring boot document reading notes - using spring data JPA to connect multi source databases (MySQL and Oracle)

The following small project shows how to connect 2 A database , One is Oracle, One is MySQL.

pivotal Maven rely on :

<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>

This Oracle Of jar Bag is no longer maven In the warehouse , You need to manually import it into maven Warehouse .

application.properties The code for is as follows :

#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=******

therefore , To define the 2 individual dataSource Of Bean,Spring Need to know that dataSource It's the main thing , Which is secondary . If you don't define primary and secondary , that Spring The program will fail to start . If you don't define primary and secondary , So the same bean,Spring It can't be registered .

Use @Primary Annotations define the main dataSource Of Bean.

 

primary DtaSource To configure :

@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;
	}
}

There are a few key points here :

1. dataSource To add @Primary annotation ;

2. entityMananger Plus @Primary annotation ;

3. persistenceUnitManager Add the same @Primary

 

 

secondary DataSource Source configuration :

@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;
	}
}

The class is configured here , There is no need to @Primary annotation , however @Bean Medium name Need to be the only .

This completes the configuration of the database , The following definition 2 individual Java class , Used to map database tables , One is TblOracle, One is 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

}

Corresponding persistence.xml as follows :

<?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>

In this case , It defines the same controller, these controller Called Spring Data JPA Medium Repositories.

The warehouse statement is as follows :

@Autowired
private OracleMessageRepo oracleMessageRepo;

@Autowired
private MysqlMessageRepo mysqlMessageRepo;

Call the following :

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

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

Above is the use of JPA How to store , If you want to use traditional entity management , It needs to be like this :

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

Here we need to provide our own search conditions , Here's an example :

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();
}

Here's the call to , The running screenshot is as follows :

summary : This example shows how to use Spring Data JPA Easily configure multi-source database .

The creation code is as follows :

https://github.com/pavansolapure/opencodez-samples/tree/master/multi-db

 

 

版权声明
本文为[IT1995]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224152601521h.html

Scroll to Top