导入依赖 <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.2.5</version> </dependency>
根据参数选择不同的数据源
import cn.hutool.db.DbUtil;
import cn.hutool.db.ds.simple.SimpleDataSource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;
/**
* author ss
* createTime 2020/4/21
* package ${com.dtroad.ieasweb.DynDataSource}
* DESC 根据参数获取不同数据源的数据
***/
public class DataSourceTools {
private final static Integer GET_ROW_NAME = 1;//获取列名
private final static Integer GET_DATA = 2;//获取数据
private static List<Map<String, Object>> connection(DataSource ds, String sql, Integer type) {
Connection conn = null;
List<Map<String, Object>> result = new ArrayList<>();
try {
conn = ds.getConnection();
/* 执行查询语句,返回实体列表,一个Entity对象表示一行的数据,Entity对象是一个继承自HashMap的对象,存储的key为字段名,value为字段值 */
PreparedStatement stm = conn.prepareStatement(sql);
ResultSet resultSet = stm.executeQuery(sql);
if (type == GET_DATA) {
Map<Integer, Object> rowName = buildRowName(resultSet);
result = buildResult(rowName, resultSet);
}
if (type == GET_ROW_NAME) {
result = new ArrayList<>();
result.add(buildColumnRowLabel(resultSet));
}
} catch (SQLException e) {
} finally {
DbUtil.close(conn);
}
return result;
}
/**
* 查询那数据的结果集列名称
*/
private static Map<String, Object> buildColumnRowLabel(ResultSet resultSet) throws SQLException {
Map<String, Object> map = new LinkedHashMap<>();
Integer size = resultSet.getMetaData().getColumnCount();
List<Map<String, Object>> result = new ArrayList<>();
for (Integer i = 0; i < size; i++) {
map.put(resultSet.getMetaData().getColumnLabel(size - i), resultSet.getMetaData().getColumnLabel(size - i));
}
//将返回的列名称倒叙显示
List<String> list = new ArrayList<>();
for (Map.Entry<String, Object> entry : map.entrySet()) {
String k = entry.getKey();
list.add(k);
}
Collections.reverse(list); // 倒序排列
Map<String, Object> m = new LinkedHashMap<>();
for (String k : list) {
m.put(k, k);
}
return m;
}
/**
* 查询那数据的结果集Map类型
*/
private static Map<Integer, Object> buildRowName(ResultSet resultSet) throws SQLException {
Map<Integer, Object> map = new LinkedHashMap<>();
Integer size = resultSet.getMetaData().getColumnCount();
for (Integer i = 0; i < size; i++) {
map.put(size - i, resultSet.getMetaData().getColumnLabel(size - i));
}
return map;
}
/**
* 构建返回结果集合List<Map<String, Object>>
*/
private static List<Map<String, Object>> buildResult(Map<Integer, Object> rowName, ResultSet resultSet) throws SQLException {
Integer size = resultSet.getMetaData().getColumnCount();
List<Map<String, Object>> result = new ArrayList<>();
while (resultSet.next()) {
Map<String, Object> map = new LinkedHashMap<>();
for (Integer i = 0; i < size; i++) {
//放入列名称和列值
map.put(rowName.get(size - i).toString(), resultSet.getString(size - i));
}
result.add(map);
}
return result;
}
//从properties获取数据源的配置信息
private static DataSource getDataSource(String ds) {
//可从数据库中获取以下值
String url = PropertiesUtil.getString("spring.datasource.dynamic.datasource." + ds + ".url");
String username = PropertiesUtil.getString("spring.datasource.dynamic.datasource." + ds + ".username");
String password = PropertiesUtil.getString("spring.datasource.dynamic.datasource." + ds + ".password");
return new SimpleDataSource(url, username, password);
}
/**
* 查询那数据的结果集
*/
public static List<Map<String, Object>> query(String ds, String sql) {
//可从数据库中获取以下值
DataSource dataSource = getDataSource(ds);
return connection(dataSource, sql, GET_DATA);
}
/**
* 获取列名称Map<String,Object> k值,value值相等
*/
public static List<Map<String, Object>> queryRowName(String ds, String sql) {
DataSource dataSource = getDataSource(ds);
Integer type = GET_ROW_NAME;
return connection(dataSource, sql, type);
}
}
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import java.io.IOException;
import java.util.MissingResourceException;
/**
* author ss
* createTime 2020/4/21
* package ${com.dtroad.ieasweb.DynDataSource}
* 获取系统参数 引用自其他网站
***/
@Component
public class PropertiesUtil {
public static final byte[] KEY = {9, -1, 0, 5, 39, 8, 6, 19};
private static Environment env;
@Autowired
protected void set(Environment env) throws IOException {
PropertiesUtil.env = env;
}
/**
* Get a value based on key , if key does not exist , null is returned
*
* @param key
* @return
*/
public static String getString(String key) {
try {
return env.getProperty(key);
} catch (MissingResourceException e) {
return null;
}
}
/**
* Get a value based on key , if key does not exist , null is returned
*
* @param key
* @return
*/
public static String getString(String key, String defaultValue) {
try {
String value = env.getProperty(key);
if (value == null) {
return defaultValue;
}
return value;
} catch (MissingResourceException e) {
return defaultValue;
}
}
/**
* 根据key获取值
*
* @param key
* @return
*/
public static int getInt(String key) {
return Integer.parseInt(env.getProperty(key));
}
/**
* 根据key获取值
*
* @param key
* @param defaultValue
* @return
*/
public static int getInt(String key, int defaultValue) {
String value = env.getProperty(key);
if (StringUtils.isBlank(value)) {
return defaultValue;
}
return Integer.parseInt(value);
}
/**
* 根据key获取值
*
* @param key
* @param defaultValue
* @return
*/
public static boolean getBoolean(String key, boolean defaultValue) {
String value = env.getProperty(key);
if (StringUtils.isBlank(value)) {
return defaultValue;
}
return new Boolean(value);
}
}