使用jdbc获取数据库全部表、表包含字段

1.  创建数据源Vo, 封装数据源相关信息；

//数据源信息
public class DataSource implements Serializable {    
    //数据库类型
    private JdbcType          jdbcType;
	//url
	private String            jdbcUrl;
	//user
	private String            jdbcUser;
	//pwd
	private String            jdbcPassword;
	//表名 | 视图名
	private String            tvname;
   
    //getter 、setter...
}

2.  抽象出接口

public interface JdbcService {
	/**
	 * 测试数据库的连通性
	 * @return true表示成功
	 */
	public boolean test();
	
	/**
	 * 列出所有的表和视图
	 * @return 所有的表和视图
	 */
	public List<String> listAllTables();
	
	/**
	 * 列出所有包含某些字段的表和视图
	 * @param fields 字段
	 * @return 所有包含某些字段的表和视图
	 */
	public List<String> listAllTablesFields(String... fields);
	
	/**
	 * 列出所有的字段
	 * @param tableName表名或视图名
	 * @return 所有的字段
	 */
	public List<String> listAllFields(String tableName);
	
}

3.  接口抽象实现类

public abstract class AbstractJdbcService implements JdbcService {
	private final DataSource dataSource;
	
	public AbstractJdbcService(DataSource dataSource) {
		this.dataSource = dataSource;
		checkDataSource(dataSource);
	}
	
	/**
	 * 校验数据源信息是否有效
	 * @param dataSource 数据源
	 */
	private void checkDataSource(DataSource dataSource){
		if(dataSource == null){
			throw new IllegalArgumentException("dataSource is null. ");
		}
		if(StringUtils.isBlank(dataSource.getJdbcUrl())){
			throw new IllegalStateException("jdbc url is null. ");
		}
		if(StringUtils.isAnyBlank(dataSource.getJdbcUser(), dataSource.getJdbcPassword())){
			throw new IllegalStateException("jdbc user or password is null. ");
		}
	}
	
	protected DataSource getDataSource() {
		return dataSource;
	}
	
	/**
	 * 关闭(释放)资源
	 * @param conn Connection
	 */
	protected void close(Connection conn){
		close(conn, null, null);
	}
	
	/**
	 * 关闭(释放)资源
	 * @param conn Connection
	 * @param ps PreparedStatement
	 * @param rs ResultSet
	 */
	protected void close(Connection conn, Statement ps, ResultSet rs){
		//关闭ResultSet
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				rs = null;
			}
		}
		//关闭PreparedStatement
		if(ps != null){
			try {
				ps.close();
			} catch (SQLException e) {
				ps = null;
			}
		}
		//关闭Connection
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				conn = null;
			}
		}
	}
	
	@Override
	public boolean test() {
		Connection conn = null;
		try {
			conn = getConnection();
			if(conn == null){
				return false;
			}
			
			DatabaseMetaData meta =  conn.getMetaData();
			if(meta == null){
				return false;
			}
		} catch (SQLException e) {
			
		} finally {
			close(conn);
		}
		return true;
	}
	
	/**
	 * 获取连接
	 * @return Connection
	 */
	protected Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName(loadDriverClass());
			conn = DriverManager.getConnection(dataSource.getJdbcUrl(), dataSource.getJdbcUser(), dataSource.getJdbcPassword());
		} catch (Exception e) {
			
		}
		return conn;
	}
	
	@Override
	public List<String> listAllTables() {
		Connection conn = getConnection();
		if(conn == null){
			return null;
		}
		
		List<String> result = new ArrayList<>();
		ResultSet rs = null;
		try{
		    //参数1 int resultSetType
		    //ResultSet.TYPE_FORWORD_ONLY 结果集的游标只能向下滚动。
		    //ResultSet.TYPE_SCROLL_INSENSITIVE 结果集的游标可以上下移动，当数据库变化时，当前结果集不变。
		    //ResultSet.TYPE_SCROLL_SENSITIVE 返回可滚动的结果集，当数据库变化时，当前结果集同步改变。

		    //参数2 int resultSetConcurrency
		    //ResultSet.CONCUR_READ_ONLY 不能用结果集更新数据库中的表。
		    //ResultSet.CONCUR_UPDATETABLE 能用结果集更新数据库中的表
		    
		    conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			DatabaseMetaData meta = conn.getMetaData();
			//目录名称; 数据库名; 表名称; 表类型;
			rs = meta.getTables(catalog(), schemaPattern(), tableNamePattern(), types());
			while(rs.next()){
				result.add(rs.getString("TABLE_NAME"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			close(conn, null, rs);
		}
		
		return result;
	}
	
	@Override
	public List<String> listAllFields(String tableName) {
		Connection conn = getConnection();
		if(conn == null){
			return null;
		}
		List<String> result = new ArrayList<>();
        ResultSet rs = null;
        try{
            DatabaseMetaData meta = conn.getMetaData();
            rs = meta.getColumns(catalog(), schemaPattern(), tableOrViewName.trim(), null);
            while(rs.next()){
                result.add(rs.getString("COLUMN_NAME"));
            }
        }catch(Exception e){
            
        }finally{
            close(conn, null, rs);
        }
		return result;
	}
	
	@Override
	public List<String> listAllTablesFields(String... fields) {
		//如果字段为空, 则返回null
		if(fields == null || fields.length == 0){
			return null;
		}
		//如果表结构(视图)为空, 则返回null
		List<String> tvs = listAllTables();
		if(tvs == null || tvs.size() == 0){
			return null;
		}
		
		List<String> result = new CopyOnWriteArrayList<>();
		//并发筛选包含特定字段的表结构
		tvs.parallelStream().forEach(tv -> {
			List<String> fieldsList = listAllFields(tv);
			if(fieldsList != null && fieldsList.size() > 0){
				if(fieldsList.containsAll(Arrays.asList(fields))){
					result.add(tv);
				}
			}
		});
				
		return result;
	}
	
	/**
	 * a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
	 */
	protected String catalog(){
		return null;
	}
	
	/**
	 * a table name pattern; must match the table name as it is stored in the database
	 */
	protected String tableNamePattern(){
		return "%";
	}
	
	/**
	 * a list of table types, which must be from the list of table types returned from {@link DatabaseMetaData.getTableTypes},to include; null returns all types
	 */
	protected String[] types(){
		
		return new String[]{"TABLE", "VIEW"};
	}
	
	/**
	 * 加载驱动class
	 * @return class
	 */
	protected abstract String loadDriverClass();
	
	/**
	 * a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
	 */
	protected abstract String schemaPattern();
}

4.  工厂类

public class JdbcServiceFactory {
	
	/**
	 * 获取jdbc service
	 * @param dataSource 数据源
	 * @return jdbc service
	 */
	public static JdbcService getJdbcService(DataSource dataSource){
		if(dataSource.getJdbcType() == JdbcType.MYSQL){
			return new MySQLJdbcService(dataSource);
		}else if(dataSource.getJdbcType() == JdbcType.ORACLE){
			return new OracleJdbcService(dataSource);
		}
		return null;
	}
	
}

5. mysql对应jdbc实现

public class MySQLJdbcService extends AbstractJdbcService {
    
	public MySQLJdbcService(DataSource dataSource) {
		super(dataSource);
	}
	
	@Override
	protected String loadDriverClass() {
		return "com.mysql.jdbc.Driver";
	}
	
	@Override
	protected String schemaPattern() {
		//mysql的schemaPattern为数据库名称
		String url = getDataSource().getJdbcUrl();
		//jdbc:mysql://localhost:3306/iso_db?useUnicode=true&characterEncoding=UTF-8
		String database = url.substring(url.indexOf("/", 13) + 1);
		int index = database.indexOf("?");
		if(index > 0){
			database = database.substring(0, index);
		}
		return database;
	}
}

6. oracle对应jdbc实现

public class OracleJdbcService extends AbstractJdbcService {
	
	public OracleJdbcService(DataSource dataSource) {
		super(dataSource);
	}
	
	@Override
	protected String loadDriverClass() {
		
		return "oracle.jdbc.driver.OracleDriver";
	}
	
	/**
	 * 注意：oracle用户名称须大写
	 */
	@Override
	protected String schemaPattern() {
		//oracle的schemaPattern为用户名
		return getDataSource().getJdbcUser().toUpperCase();
	}
}