1、业务场景
要求获取不同数据库的表信息和表结构信息,数据库类型包括oracle,MySQL,SqlServer。
2、实现思路
step1 新增数据库连接信息(主机,端口,数据库类型,实例名,用户名,密码,状态)
step2 测试连接,成功状态正常,失败不正常
step3 创建JdbcTemplate对象,保存到map中(key=唯一标识符,value=JdbcTemplate对象),避免重复创建销毁
step4 通过SQL语句获取表信息和表结构
step5定时检查map,销毁不常用的JdbcTemplate对象
3、代码
工厂类生成JdbcTemplate对象。
package com.aadata.etl.cache;
import com.aadata.etl.model.DbConnInfo;
import com.aadata.etl.util.DateUtil;
import com.aadata.etl.util.StringUtil;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.EnableScheduling;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.text.ParseException;
import java.util.*;
@Component
@EnableScheduling
public class DbConnectCache {
//还是用线程安全的集合比较好哈
public static Map<String, JdbcTemplate> jdbcMap= new HashMap<String, JdbcTemplate>();
public static Map<String, Date> jdbcUseTimeMap= Collections
.synchronizedMap(new HashMap<String, Date>());
private static DataSource primaryDataSource(DbConnInfo info) {
DataSourceBuilder<?> one = DataSourceBuilder.create();
one.driverClassName(info.getDbType());
one.password(info.getDbPassword());
one.url(info.getDbUrl());
one.username(info.getDbUserName());
return one.build();
}
public static JdbcTemplate getJdbcTemplate(DbConnInfo info){
if(StringUtil.isNotEmpty(info.getDbType()) && StringUtil.isNotEmpty(info.getDbUserName())
&& StringUtil.isNotEmpty(info.getDbPassword()) && StringUtil.isNotEmpty(info.getDbUrl())){
if("oracle".equals(info.getDbType())){
info.setDbType("oracle.jdbc.driver.OracleDriver");
}else if("SQLServer".equals(info.getDbType())){
info.setDbType("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}else{
return null;
}
}
DataSource dbs = primaryDataSource(info);
if(dbs != null){
JdbcTemplate jdbc = new JdbcTemplate (primaryDataSource(info));
jdbcMap.put(info.getDbConnInfoSn(), jdbc);
return jdbc;
}else{
return null;
}
}
/**
* 定时检查jdbc对象数
* @throws ParseException
*/
@Scheduled(cron = "0/5 * * * * ?")
private void configureTasks() throws ParseException {
int count = jdbcMap.size();
if(count > 5){
Date now = new Date();
for(Iterator<String> iterator = jdbcUseTimeMap.keySet().iterator(); iterator.hasNext(); ) {
String key = iterator.next();
int hours = DateUtil.compare2DateGetHour(jdbcUseTimeMap.get(key),now);
if(hours>1){
jdbcMap.remove(key);
iterator.remove();
}
}
}
System.gc();
}
}
获取表名和表结构
/**
* 获取指定数据库用户下的表名
* @param info
* @return
*/
public static List<Map<String, Object>> getTableNameList(DbConnInfo info){
JdbcTemplate jdbc = null;
if(DbConnectCache.jdbcMap.containsKey(info.getDbConnInfoSn())){
jdbc = DbConnectCache.jdbcMap.get(info.getDbConnInfoSn());
}else{
jdbc = DbConnectCache.getJdbcTemplate(info);
}
if(jdbc != null){
DbConnectCache.jdbcUseTimeMap.put(info.getDbConnInfoSn(),new Date());
String sql = "select TABLE_NAME ,COMMENTS from user_tab_comments where table_type='TABLE'";
List<Map<String, Object>> list = jdbc.queryForList(sql);
return list;
}else{
return null;
}
}
/**
* 获取指定数据库的指定表的字段和字段注释
* @param info
* @param table
* @return
*/
public static List<Map<String, Object>> getTableField(DbConnInfo info, String table) {
JdbcTemplate jdbc = null;
if(DbConnectCache.jdbcMap.containsKey(info.getDbConnInfoSn())){
jdbc = DbConnectCache.jdbcMap.get(info.getDbConnInfoSn());
}else{
jdbc = DbConnectCache.getJdbcTemplate(info);
}
if(jdbc != null){
DbConnectCache.jdbcUseTimeMap.put(info.getDbConnInfoSn(),new Date());
String sql = "select tmp.*,DATA_TYPE from (select column_name,comments from user_col_comments where table_name=upper('"+table+"')) tmp"
+" left join (select column_name,DATA_TYPE from user_tab_columns " +
" where table_name = upper('"+table+"')) tmp0 on tmp.column_name=tmp0.column_name";
List<Map<String, Object>> list = jdbc.queryForList(sql);
return list;
}else{
return null;
}
}
4、补充说明
如果数据库的连接信息固定不变,可以考虑用mybatis的databaseId或者数据连接池。