mysql多数据源切换_Springboot 2.0 动态切换数据库，多数据源的实现

要制作快速开发框架需要动态切换数据库做增删改查操作，本文介绍在 Springboot 2.0 中制作的工具类

package com.cc.dynamicdb.utils;

import com.cc.dynamicdb.model.DBConfig;

import com.zaxxer.hikari.HikariDataSource;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import org.springframework.stereotype.Service;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

* @ClassName:DynamicDBUtils

* @Description:依赖类：DBConfig

* @Author:chanchaw

* @Date:2019/11/13 15:45

* @Version:1.0

**/

@Service

public class DynamicDBUtils {

/**

* 查询返回记录集

* @param dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }

* @param sql 要执行的SQL语句字串，已经将参数拼接在内了，不用传查询参数进来

* @return

*/

public List> queryForList(DBConfig dbConfig,String sql){

HikariDataSource dataSource = customDataSource(dbConfig);

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

List> list = jdbcTemplate.queryForList(sql,new HashMap<>());

dataSource.close();

return list;

}

/**

* 查询返回记录集

* @param dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }

* @param sql 查询SQL语句，带有占位符，例如：SELECT * FROM projects WHERE 1=1 AND iid>:iid; 最后的 :iid 是参数占位符

* @param params Map 类型的参数，其中包含1 - N 个参数，键名称要和第二个参数的占位符保持一致

* @return

*/

public List> queryForList(DBConfig dbConfig,String sql,Map params){

HikariDataSource dataSource = customDataSource(dbConfig);

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

List> list = jdbcTemplate.queryForList(sql,new MapSqlParameterSource(params));

dataSource.close();

return list;

}

/**

* 增/删/改 数据

* @param dbConfig

* @param sql

* @return

*/

public int update(DBConfig dbConfig,String sql){

HikariDataSource dataSource = customDataSource(dbConfig);

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

int ret = jdbcTemplate.update(sql,new HashMap<>());

dataSource.close();

return ret;

}

/**

* 增/删/改 数据

* @param dbConfig dbConfig 链接数据库用到的5个参数构成的对象，例如：{ server:49.234.90.38,port:3306,dbName:atools,userName:root,userPwd:!Xdf123456 }

* @param sql 查询SQL语句，带有占位符，例如：SELECT * FROM projects WHERE 1=1 AND iid>:iid; 最后的 :iid 是参数占位符

* @param params Map 类型的参数，其中包含1 - N 个参数，键名称要和第二个参数的占位符保持一致

* @return

*/

public int update(DBConfig dbConfig,String sql,Map params){

HikariDataSource dataSource = customDataSource(dbConfig);

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

int ret = jdbcTemplate.update(sql,new MapSqlParameterSource(params));

dataSource.close();

return ret;

}

public HikariDataSource customDataSource(DBConfig dbConfig) {

final HikariDataSource customDataConfig = new HikariDataSource();

customDataConfig.setJdbcUrl(dbConfig.getUrl());

// 不设置驱动 - 采用默认驱动

if( dbConfig.getDriver() == null||dbConfig.getDriver().length()<=0 ){

customDataConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");

}else{

customDataConfig.setDriverClassName(dbConfig.getDriver());

}

customDataConfig.setUsername(dbConfig.getDbUserName());

customDataConfig.setPassword(dbConfig.getDbUserPwd());

customDataConfig.setMaximumPoolSize(2);

customDataConfig.setMinimumIdle(1);

customDataConfig.setAutoCommit(true);

//customDataConfig.setConnectionTestQuery("SELECT 1 FROM DUAL");

customDataConfig.addDataSourceProperty("cachePrepStmts", "true");

customDataConfig.addDataSourceProperty("prepStmtCacheSize", "250");

customDataConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

return customDataConfig;

}

}

还有个依赖类

package com.cc.dynamicdb.model;

/**

* @ClassName:DBConfig

* @Description:TODO

* @Author:chanchaw

* @Date:2019/11/13 8:16

* @Version:1.0

**/

public class DBConfig {

private String server;

private int port;

private String dbName;

private String driver;

private String dbUserName;

private String dbUserPwd;

public String getServer() {

return server;

}

public void setServer(String server) {

this.server = server;

}

public int getPort() {

return port;

}

public void setPort(int port) {

this.port = port;

}

public String getDbName() {

return dbName;

}

public void setDbName(String dbName) {

this.dbName = dbName;

}

public String getDriver() {

return driver;

}

public void setDriver(String driver) {

this.driver = driver;

}

public String getDbUserName() {

return dbUserName;

}

public void setDbUserName(String dbUserName) {

this.dbUserName = dbUserName;

}

public String getDbUserPwd() {

return dbUserPwd;

}

public void setDbUserPwd(String dbUserPwd) {

this.dbUserPwd = dbUserPwd;

}

@Override

public String toString() {

return "DBConfig{" +

"server='" + server + '\'' +

", port=" + port +

", dbName='" + dbName + '\'' +

", driver='" + driver + '\'' +

", dbUserName='" + dbUserName + '\'' +

", dbUserPwd='" + dbUserPwd + '\'' +

'}';

}

/**

* 构建数据库链接字串

* @return

*/

public String getUrl(){

String ret = "jdbc:mysql://" + server + ":" + port + "/" + dbName + "?userUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useLegacyDatetimeCode=false";

return ret;

}

}