Use Spring Boot to configure dynamic data sources to access multiple databases.

Use Spring Boot to configure dynamic data sources to access multiple databases.

I wrote a blog titled Spring + Mybatis + Mysql to build a distributed database access framework. It describes how to configure dynamic data sources through Spring + Mybatis to access multiple databases. However, the previous solution has some limitations (as described in the previous blog): This is only applicable to scenarios where the number of databases is small and fixed. There is nothing to do with the dynamic increase of databases.

The solution described below supports dynamic addition, deletion, and unlimited quantity of databases.

Database environment preparation

The following Mysql is used as an example to create three local databases for testing. It must be noted that this solution does not limit the number of databases and supports different databases deployed on different servers. Db_project_001, db_project_002, and db_project_003.

Build a Java background microservice Project

Create a maven project for Spring Boot:

Config: data source configuration management class.

Datasource: self-implemented data source management logic.

Dbmgr: manages the ing between the project code and the database IP address and name (in the actual project, this part of data is saved in the redis cache and can be dynamically added or deleted ).

Mapper: database access interface.

Model: ing model.

Rest: The restful API released by microservices, which is used for testing.

Application. yml: JDBC parameters of the database are configured.

Detailed code implementation

1. Add data source configuration

Package com. elon. dds. config; import javax. SQL. dataSource; import org. apache. ibatis. session. sqlSessionFactory; import org. mybatis. spring. sqlSessionFactoryBean; import org. mybatis. spring. annotation. mapperScan; import org. springframework. beans. factory. annotation. qualifier; import org. springframework. boot. autoconfigure. jdbc. performancebuilder; import org. springframework. boot. context. properties. configurationPro Perties; import org. springframework. context. annotation. bean; import org. springframework. context. annotation. configuration; import com. elon. dds. datasource. dynamicDataSource;/*** data source configuration management. ** @ Author elon * @ version February 26, 2018 */@ Configuration @ MapperScan (basePackages = "com. elon. dds. mapper ", value =" sqlSessionFactory ") public class performanceconfig {/*** create a data source based on the configuration parameters. Use a derived subclass. ** @ Return data source */@ Bean (name = "dataSource") @ ConfigurationProperties (prefix = "spring. datasource ") public DataSource getDataSource () {performancebuilder builder = performancebuilder. create (); builder. type (DynamicDataSource. class); return builder. build ();}/*** create a session factory. ** @ Param dataSource data source * @ return session factory */@ Bean (name = "sqlSessionFactory") public SqlSessionFactory getSqlSessionFactory (@ Qualifier ("dataSource") DataSource) {SqlSessionFactoryBean bean = new SqlSessionFactoryBean (); bean. setDataSource (dataSource); try {return bean. getObject ();} catch (Exception e) {e. printStackTrace (); return null ;}}}

2. Define a dynamic data source

1) First, add a database identification class to distinguish different database accesses.

Because we have created a separate database for different projects, project encoding is used as the database index. Microservices support multi-thread concurrency and Use thread variables.

Package com. elon. dds. datasource;/*** database identity management class. Used to distinguish different databases connected by data sources. ** @ Author elon * @ version 2018-02-25 */public class DBIdentifier {/*** use different engineering codes to differentiate databases */private static ThreadLocal <String> projectCode = new ThreadLocal <string> (); public static String getProjectCode () {return projectCode. get ();} public static void setProjectCode (String code) {projectCode. set (code );}}

2) A DynamicDataSource is derived from DataSource, in which the database connection is dynamically switched.

Import java. lang. reflect. field; import java. SQL. connection; import java. SQL. SQLException; import org. apache. logging. log4j. logManager; import org. apache. logging. log4j. logger; import org. apache. tomcat. jdbc. pool. dataSource; import org. apache. tomcat. jdbc. pool. poolProperties; import com. elon. dds. dbmgr. projectDBMgr;/*** defines the derived class of the dynamic data source. Derived from the basic DataSource, dynamic implementation is self-implemented. ** @ Author elon * @ version 2018-02-25 */public class DynamicDataSource extends DataSource {private static Logger log = LogManager. getLogger (DynamicDataSource. class);/*** rewrite this method to connect to different databases when requesting data from different projects. * // @ Override public Connection getConnection () {String projectCode = DBIdentifier. getProjectCode (); // 1. Obtain the data source DataSource dds = DDSHolder. instance (). getDDS (projectCode); // 2. if the data source does not exist, create if (dds = null) {try {DataSource newDDS = initDDS (projectCode); DDSHolder. instance (). addDDS (projectCode, newDDS);} catch (IllegalArgumentException | IllegalAccessException e) {log. error ("Init data source fail. ProjectCode: "+ projectCode); return null ;}} dds = DDSHolder. instance (). getDDS (projectCode); try {return dds. getConnection ();} catch (SQLException e) {e. printStackTrace (); return null ;}/ *** copy the current data object as a template. ** @ Return dds * @ throws IllegalAccessException * @ throws syntax */private DataSource initDDS (String projectCode) throws IllegalArgumentException, IllegalAccessException {DataSource = new DataSource (); // 2. Copy the PoolProperties property OF PoolConfiguration = new PoolProperties (); Field [] pfields = PoolProperties. class. getDeclaredFields (); for (Field f: pfields) {f. setAccessible (true); Object value = f. get (this. getPoolProperties (); try {f. set (property, value);} catch (Exception e) {log.info ("Set value fail. attr name: "+ f. getName (); continue ;}} dds. setPoolProperties (property); // 3. Set the Database Name and IP address (generally, the port, user name, and password are all fixed) String urlFormat = this. getUrl (); String url = String. format (urlFormat, ProjectDBMgr. instance (). getDBIP (projectCode), ProjectDBMgr. instance (). getDBName (projectCode); dds. setUrl (url); return dds ;}}

3) control data connection release through DDSTimer (release of unused data sources beyond the specified time)

Package com. elon. dds. datasource; import org. apache. tomcat. jdbc. pool. DataSource;/*** Dynamic Data Source timer management. The database connection that has not been accessed for a long time is closed. ** @ Author elon * @ version February 25, 2018 */public class DDSTimer {/*** idle time period. The database connection that has not been accessed during this period will be released. The default value is 10 minutes. */Private static long idlePeriodTime = 10*60*1000;/* dynamic data source */private DataSource dds;/* last access time */private long lastUseTime; public DDSTimer (DataSource dds) {this. dds = dds; this. lastUseTime = System. currentTimeMillis ();}/*** update the last access time */public void refreshTime () {lastUseTime = System. currentTimeMillis ();}/*** checks whether the data connection times out and is closed. ** @ Return true-closed upon timeout; false-not timed out */public boolean checkAndClose () {if (System. currentTimeMillis ()-lastUseTime> idlePeriodTime) {dds. close (); return true;} return false;} public DataSource getDds () {return dds ;}}

4) added DDSHolder to manage different data sources and provided the data source addition and query functions.

Package com. elon. dds. datasource; import java. util. hashMap; import java. util. iterator; import java. util. map; import java. util. map. entry; import java. util. timer; import org. apache. tomcat. jdbc. pool. dataSource;/*** dynamic data source Manager. ** @ Author elon * @ version February 25, 2018 */public class DDSHolder {/*** manage the dynamic data source list. <Project code, data source> */private Map <String, DDSTimer> ddsMap = new HashMap <String, DDSTimer> (); /*** clear unused data sources cyclically through scheduled tasks */private static Timer clearIdleTask = new Timer (); static {clearIdleTask. schedule (new ClearIdleTimerTask (), 5000, 60*1000) ;}; private DDSHolder () {}/ ** get singleton object */public static DDSHolder instance () {return DDSHolderBuilder. instance;}/*** Add a dynamic data source. ** @ Param projectCode project code * @ param dds */public synchronized void addDDS (String projectCode, DataSource dds) {ddst1_ddst = new DDSTimer (dds); ddsMap. put (projectCode, ddst);}/*** query a dynamic data source ** @ param projectCode project code * @ return dds */public synchronized DataSource getDDS (String projectCode) {if (ddsMap. containsKey (projectCode) {ddst1_ddst = ddsMap. get (projectCode); ddst. refreshTim E (); return ddst. getDds ();} return null;}/*** clear the idle Data Source with time-out. */Public synchronized void clearIdleDDS () {Iterator <Entry <String, ddstator> iter = ddsMap. entrySet (). iterator (); for (; iter. hasNext ();) {Entry <String, DDSTimer> entry = iter. next (); if (entry. getValue (). checkAndClose () {iter. remove ();}}} /*** Singleton component class * @ author elon * @ version July 15/private static class DDSHolderBuilder {private static DDSHolder instance = new DDSHolder ();}}

5) The timer task ClearIdleTimerTask is used to regularly clear idle data sources.

Package com. elon. dds. datasource; import java. util. TimerTask;/*** clear idle connection tasks. ** @ Author elon * @ version February 26, 2018 */public class ClearIdleTimerTask extends TimerTask {@ Override public void run () {DDSHolder. instance (). clearIdleDDS ();}}

3. Manage the ing between project codes and database IP addresses and names

Package com. elon. dds. dbmgr; import java. util. HashMap; import java. util. Map;/*** project database management. Provides an interface for querying database names and IP addresses based on project codes. * @ Author elon * @ version February 25, 2018 */public class ProjectDBMgr {/*** saves the ing between the project code and the data name. This is hard-coded. In actual development, the relational data can be saved to the redis cache. * to add a project or delete a project, you only need to update the cache. Then the interface of this class only needs to be modified to get data from the cache. */Private Map <String, String> dbNameMap = new HashMap <String, String> ();/*** saves the ing between project encoding and database IP addresses. */Private Map <String, String> dbIPMap = new HashMap <String, String> (); private ProjectDBMgr () {dbNameMap. put ("project_001", "db_project_001"); dbNameMap. put ("project_002", "db_project_002"); dbNameMap. put ("project_003", "db_project_003"); dbIPMap. put ("project_001", "127.0.0.1"); dbIPMap. put ("project_002", "127.0.0.1"); dbIPMap. put ("project_003", "127.0.0.1");} public static ProjectDBMgr instance () {return ProjectDBMgrBuilder. instance;} // The public String getDBName (String projectCode) {if (dbNameMap. containsKey (projectCode) {return dbNameMap. get (projectCode);} return ";} // get public String getDBIP (String projectCode) {if (dbIPMap. containsKey (projectCode) {return dbIPMap. get (projectCode);} return "";} private static class ProjectDBMgrBuilder {private static ProjectDBMgr instance = new ProjectDBMgr ();}}

4. Define the mapper for database access

Package com. elon. dds. mapper; import java. util. list; import org. apache. ibatis. annotations. mapper; import org. apache. ibatis. annotations. result; import org. apache. ibatis. annotations. results; import org. apache. ibatis. annotations. select; import com. elon. dds. model. user;/*** Mybatis ing interface definition. ** @ Author elon * @ version February 26, 2018 */@ Mapperpublic interface UserMapper {/*** query all user data * @ return user data list */@ Results (value = {@ Result (property = "userId ", column = "id"), @ Result (property = "name", column = "name"), @ Result (property = "age", column = "age ")}) @ Select ("select id, name, age from tbl_user") List <User> getUsers ();}

5. Define the query object model

package com.elon.dds.model;public class User{ private int userId = -1; private String name = ""; private int age = -1; @Override public String toString() { return "name:" + name + "|age:" + age; } public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; }}

6. Define a restful interface for querying user data

Package com. elon. dds. rest; import java. util. list; import org. springframework. beans. factory. annotation. autowired; import org. springframework. web. bind. annotation. requestMapping; import org. springframework. web. bind. annotation. requestMethod; import org. springframework. web. bind. annotation. requestParam; import org. springframework. web. bind. annotation. restController; import com. elon. dds. datasource. DBIdentifier; im Port com. elon. dds. mapper. UserMapper; import com. elon. dds. model. User;/*** User data access interface. ** @ Author elon * @ version February 26, 2018 */@ RestController @ RequestMapping (value = "/user") public class WSUser {@ Autowired private UserMapper userMapper; /*** query all user information in the project ** @ param projectCode project code * @ return user list */@ RequestMapping (value = "/v1/users", method = RequestMethod. GET) public List <User> queryUser (@ RequestParam (value = "projectCode", required = true) String projectCode) {DBIdentifier. setProjectCode (projectCode); return userMapper. getUsers ();}}

The projectCode parameter is required for each query.

 7. Compile the startup code of the Spring Boot App

package com.elon.dds;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;/** * Hello world! * */@SpringBootApplicationpublic class App{ public static void main( String[] args ) { System.out.println( "Hello World!" ); SpringApplication.run(App.class, args); }}

8. Configure the data source in application. yml

The database IP address and database name are % s. Dynamically switches between Query user data.

spring: datasource: url: jdbc:mysql://%s:3306/%s?useUnicode=true&characterEncoding=utf-8 username: root password: driver-class-name: com.mysql.jdbc.Driverlogging: config: classpath:log4j2.xml

Test Plan

1. query project_001 data and return normally

2. query data of project_002 and return normally

Summary

The above is the implementation code for using Spring Boot to configure dynamic data sources to access multiple databases. I hope it will help you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!