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、补充说明

如果数据库在2个且连接信息固定不变，可以考虑用mybatis的databaseId或者数据连接池。