When developing enterprise applications, we often encounter the problem of accessing multiple different databases at the same time. Sometimes the data must be archived in some kind of data warehouse, and sometimes data changes must be pushed to a third-party database. When using SpringFramework, it is very easy to use a single database, but if you want to access multiple databases at the same time, events become much more complicated.
This article takes developing a SpringMVC program under the Spring framework as an example to demonstrate a method of accessing multiple databases at the same time and simplifying configuration changes as much as possible.
It is recommended that you also set up two databases at the same time to follow our example. In this article we used PostgreSQL and MySQL.
The following script content is the command to create tables and insert data in two databases.
PostgreSQL
CREATE TABLE usermaster ( id integer, name character varying, emailid character varying, phoneno character varying(10), location character varying ) INSERT INTO usermaster(id, name, emailid, phoneno, location) VALUES (1, 'name_postgres', 'email@email.com', '1234567890', 'IN');
MySQL
CREATE TABLE `usermaster` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `emailid` varchar(20) DEFAULT NULL, `phoneno` varchar(20) DEFAULT NULL, `location` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) INSERT INTO `kode12`.`usermaster` (`id`, `name`, `emailid`, `phoneno`, `location`) VALUES ('1', 'name_mysql', 'test@tset.com', '9876543210', 'IN');
We use Spring Tool Suite (STS) To build this example:
Click File -> New -> Spring Starter Project.
Enter the project name, Maven coordinates, description and package information in the dialog box, and click Next.
Select Web in the boot dependency and click Next.
Click Finish. STS will automatically download the required content from the Spring warehouse according to project dependencies.
The created project is as shown below:
Next we will carefully study each related item in the project document content.
pom.xml
pom contains all required dependencies and plug-in mappings. Code:<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.aegis</groupId> <artifactId>MultipleDBConnect</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>MultipleDB</name> <description>MultipleDB with Spring Boot</description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.3.5.RELEASE</version> <relativePath /> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Explanation: The details of various dependencies are explained in detail below:
MySQL database.
application.properties
Contains all configuration information required by the program. In the old version of Spring, we had to provide this configuration information through multiple XML files.server.port=6060 spring.ds_post.url =jdbc:postgresql://localhost:5432/kode12 spring.ds_post.username =postgres spring.ds_post.password =root spring.ds_post.driverClassName=org.postgresql.Driver spring.ds_mysql.url = jdbc:mysql://localhost:3306/kode12 spring.ds_mysql.username = root spring.ds_mysql.password = root spring.ds_mysql.driverClassName=com.mysql.jdbc.DriverExplanation: "server.port=6060" declares that your embedded server will use port 6060 after startup (port.server.port is the default standard port of Boot). Other
Attributes:
MultipleDbApplication.java
package com.aegis; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public MultipleDbApplication { public static void main(String[] args) { SpringApplication.run(MultipleDbApplication.class, args); } }This file contains the main
function that starts our Boot program. The annotation "@SpringBootApplication" is a combination of all other Spring annotations and Java annotations, including:
@Configuration @EnableAutoConfiguration @ComponentScan @Target(value={TYPE}) @Retention(value=RUNTIME) @Documented @InheritedOther annotations:
@Configuration @EnableAutoConfiguration @ComponentScanThe above annotations will cause the container to pass this class Load our configuration.
MultipleDBConfig.java
package com.aegis.config; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.jdbc.core.JdbcTemplate; @Configuration public class MultipleDBConfig { @Bean(name = "mysqlDb") @ConfigurationProperties(prefix = "spring.ds_mysql") public DataSource mysqlDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "mysqlJdbcTemplate") public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) { return new JdbcTemplate(dsMySQL); } @Bean(name = "postgresDb") @ConfigurationProperties(prefix = "spring.ds_post") public DataSource postgresDataSource() { return DataSourceBuilder.create().build(); } @Bean(name = "postgresJdbcTemplate") public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb") DataSource dsPostgres) { return new JdbcTemplate(dsPostgres); } }Explanation: This is an annotated configuration class that contains functions for loading our PostgreSQL and MySQL database configurations and annotations. This will also be responsible for creating JDBC template classes for each database. Let’s take a look at these four functions:
@Bean(name = "mysqlDb") @ConfigurationProperties(prefix = "spring.ds_mysql") public DataSource mysqlDataSource() { return DataSourceBuilder.create().build(); }The first line of the above code creates the mysqlDb bean.
The second line helps @Bean load all properties with the prefix spring.ds_mysql.
The fourth line creates and initializes the DataSource class and creates the mysqlDb DataSource object.
@Bean(name = "mysqlJdbcTemplate") public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) { return new JdbcTemplate(dsMySQL); }The first line creates a new Bean of type JdbcTemplate named mysqlJdbcTemplate.
The second line passes the new parameter of the DataSource type created in the first line into the function, and uses mysqlDB as the qualifier.
The third line initializes the JdbcTemplate instance with the DataSource object.
@Bean(name = "postgresDb") @ConfigurationProperties(prefix = "spring.ds_post") public DataSource postgresDataSource() { return DataSourceBuilder.create().build(); }The first line creates the DataSource instance postgresDb.
The second line helps @Bean load all configurations prefixed with spring.ds_post.
The fourth line creates and initializes the DataSource instance postgresDb.
@Bean(name = "postgresJdbcTemplate") public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb") DataSource dsPostgres) { return new JdbcTemplate(dsPostgres); }The first line creates a new bean of type JdbcTemplate with the name postgresJdbcTemplate.
The second line accepts parameters of DataSource type and uses postgresDb as the qualifier.
The third line initializes the JdbcTemplate instance with the DataSource object.
DemoController.java
package com.aegis.controller; import java.util.HashMap; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController public class DemoController { @Autowired @Qualifier("postgresJdbcTemplate") private JdbcTemplate postgresTemplate; @Autowired @Qualifier("mysqlJdbcTemplate") private JdbcTemplate mysqlTemplate; @RequestMapping(value = "/getPGUser") public String getPGUser() { Map<String, Object> map = new HashMap<String, Object>(); String query = " select * from usermaster"; try { map = postgresTemplate.queryForMap(query); } catch (Exception e) { e.printStackTrace(); } return "PostgreSQL Data: " + map.toString(); } @RequestMapping(value = "/getMYUser") public String getMYUser() { Map<String, Object> map = new HashMap<String, Object>(); String query = " select * from usermaster"; try { map = mysqlTemplate.queryForMap(query); } catch (Exception e) { e.printStackTrace(); } return "MySQL Data: " + map.toString(); } }Explanation: The @RestController class annotation indicates that all functions defined in this class are bound to the response by default .
上面代码段创建了一个JdbcTemplate实例。@Qualifier用于生成一个对应类型的模板。代码中提供的是postgresJdbcTemplate作为Qualifier参数,所以它会加载MultipleDBConfig实例的jdbcTemplate(…)函数创建的Bean。
这样Spring就会根据你的要求来调用合适的JDBC模板。在调用URL “/getPGUser”时Spring会用PostgreSQL模板,调用URL “/getMYUser”时Spring会用MySQL模板。
@Autowired @Qualifier("postgresJdbcTemplate") private JdbcTemplate postgresTemplate;
这里我们用queryForMap(String query)函数来使用JDBC模板从数据库中获取数据,queryForMap(…)返回一个map,以字段名为Key,Value为实际字段值。
执行类MultipleDbApplication中的main (…)函数就可以看到演示效果。在你常用的浏览器中点击下面URL:
URL: http://localhost:6060/getMYUser
Url: http://localhost:6060/getPGUser
上面的URL会查询PostgreSQL数据库并以字符串形式返回数据。
The above is the detailed content of Code example sharing for accessing multiple different databases at the same time in Java Spring. For more information, please follow other related articles on the PHP Chinese website!