根据数据库配置获取数据库、表、字段的信息

最新推荐文章于 2024-01-19 11:36:11 发布

但-嘎嘎嘎

最新推荐文章于 2024-01-19 11:36:11 发布

阅读量403

点赞数

分类专栏： database 文章标签： database 数据库表字段

本文链接：https://blog.csdn.net/m0_37598340/article/details/98200329

版权

database 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考文档：https://blog.csdn.net/qq_21187515/article/details/91495237
https://www.cnblogs.com/zuidongfeng/p/7895090.html
https://201212262922.iteye.com/blog/2096785
https://my.oschina.net/sprouting/blog/1580961
https://blog.csdn.net/zs520ct/article/details/78326204
https://www.xuebuyuan.com/3246611.html
http://blog.sina.com.cn/s/blog_c90ce4e001032ai0.html
https://www.cnblogs.com/taojietx/p/5189229.html
https://www.cnblogs.com/cunkouzh/p/5504052.html

数据库类型转java类型对应

mysql
	INT::::java.lang.Integer
	VARCHAR::::java.lang.String
	TINYINT::::java.lang.Integer
	SMALLINT::::java.lang.Integer
	MEDIUMINT::::java.lang.Integer
	INT::::java.lang.Integer
	INT::::java.lang.Integer
	BIGINT::::java.lang.Long
	BIT::::java.lang.Boolean
	DOUBLE::::java.lang.Double
	DOUBLE::::java.lang.Double
	FLOAT::::java.lang.Float
	DECIMAL::::java.math.BigDecimal
	DECIMAL::::java.math.BigDecimal
	CHAR::::java.lang.String
	VARCHAR::::java.lang.String
	DATE::::java.sql.Date
	TIME::::java.sql.Time
	YEAR::::java.sql.Date
	TIMESTAMP::::java.sql.Timestamp
	DATETIME::::java.sql.Timestamp
	TINYBLOB::::[B
	BLOB::::[B
	MEDIUMBLOB::::[B
	LONGBLOB::::[B
	VARCHAR::::java.lang.String
	VARCHAR::::java.lang.String
	VARCHAR::::java.lang.String
	VARCHAR::::java.lang.String
	CHAR::::java.lang.String
	CHAR::::java.lang.String
	BINARY::::[B
	VARBINARY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	GEOMETRY::::[B
	JSON::::java.lang.String]
		 
		 
		 
postgreSql
	int4::::java.lang.Integer
	text::::java.lang.String
	int4::::java.lang.Integer
	bpchar::::java.lang.String
	float4::::java.lang.Float
	bit::::java.lang.Boolean
	bool::::java.lang.Boolean
	box::::org.postgresql.geometric.PGbox
	bytea::::[B
	bpchar::::java.lang.String
	cidr::::java.lang.Object
	circle::::org.postgresql.geometric.PGcircle
	date::::java.sql.Date
	numeric::::java.math.BigDecimal
	float4::::java.lang.Float
	float8::::java.lang.Double
	inet::::java.lang.Object
	int2::::java.lang.Integer
	int8::::java.lang.Long
	interval::::org.postgresql.util.PGInterval
	json::::java.lang.Object
	line::::org.postgresql.geometric.PGline
	lseg::::org.postgresql.geometric.PGlseg
	macaddr::::java.lang.Object
	money::::org.postgresql.util.PGmoney
	path::::org.postgresql.geometric.PGpath
	point::::org.postgresql.geometric.PGpoint
	polygon::::org.postgresql.geometric.PGpolygon
	int2::::java.lang.Integer
	int4::::java.lang.Integer
	int8::::java.lang.Long
	text::::java.lang.String
	time::::java.sql.Time
	timetz::::java.sql.Time
	timestamp::::java.sql.Timestamp
	timestamptz::::java.sql.Timestamp
	tsquery::::java.lang.Object
	tsvector::::java.lang.Object
	txid_snapshot::::java.lang.Object
	uuid::::java.lang.Object
	varbit::::java.lang.Object
	varchar::::java.lang.String
	xml::::java.lang.Object
	 
	 
	 
oracle 
	VARCHAR2::::java.lang.String
	VARCHAR2::::java.lang.String
	NUMBER::::java.math.BigDecimal
	TIMESTAMP::::oracle.sql.TIMESTAMP
	VARCHAR2::::java.lang.String
	CHAR::::java.lang.String
	NCHAR::::java.lang.String
	VARCHAR2::::java.lang.String
	NVARCHAR2::::java.lang.String
	CLOB::::oracle.jdbc.OracleClob
	NCLOB::::oracle.jdbc.OracleNClob
	LONG::::java.lang.String
	NUMBER::::java.math.BigDecimal
	BINARY_FLOAT::::oracle.sql.BINARY_FLOAT
	BINARY_DOUBLE::::oracle.sql.BINARY_DOUBLE
	DATE::::java.sql.Timestamp
	INTERVALDS::::oracle.sql.INTERVALDS
	INTERVALYM::::oracle.sql.INTERVALYM
	TIMESTAMP::::oracle.sql.TIMESTAMP
	TIMESTAMP WITH TIME ZONE::::oracle.sql.TIMESTAMPTZ
	TIMESTAMP WITH LOCAL TIME ZONE::::oracle.sql.TIMESTAMPLTZ
	BLOB::::oracle.jdbc.OracleBlob
	BFILE::::oracle.jdbc.OracleBfile
	ROWID::::oracle.sql.ROWID
	CHAR::::java.lang.String
	VARCHAR2::::java.lang.String
	VARCHAR2::::java.lang.String
	NUMBER::::java.math.BigDecimal
	NUMBER::::java.math.BigDecimal
	NUMBER::::java.math.BigDecimal
	NUMBER::::java.math.BigDecimal
	NUMBER::::java.math.BigDecimal
	NUMBER::::java.lang.Double
	NUMBER::::java.lang.Double
	NUMBER::::java.lang.Double
	NUMBER::::java.lang.Double
	NUMBER::::java.lang.Double
	NCHAR::::java.lang.String
	
	
	
sqlserver 
	varchar::::java.lang.String
	binary::::[B
	bit::::java.lang.Boolean
	char::::java.lang.String
	datetime::::java.sql.Timestamp
	date::::java.sql.Date
	datetime2::::java.sql.Timestamp
	datetimeoffset::::microsoft.sql.DateTimeOffset
	decimal::::java.math.BigDecimal
	float::::java.lang.Double
	hierarchyid::::[B
	image::::[B
	int::::java.lang.Integer
	money::::java.math.BigDecimal
	nchar::::java.lang.String
	ntext::::java.lang.String
	numeric::::java.math.BigDecimal
	nvarchar::::java.lang.String
	real::::java.lang.Float
	smalldatetime::::java.sql.Timestamp
	smallint::::java.lang.Short
	smallmoney::::java.math.BigDecimal
	nvarchar::::java.lang.String
	text::::java.lang.String
	time::::java.sql.Time
	timestamp::::[B
	tinyint::::java.lang.Short
	uniqueidentifier::::java.lang.String
	varbinary::::[B
	varchar::::java.lang.String
	xml::::java.lang.String
	nvarchar::::java.lang.String
	varbinary::::[B
	varchar::::java.lang.String

引入相应的数据库依赖

		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
		<dependency>
		  <groupId>com.microsoft.sqlserver</groupId>
		  <artifactId>sqljdbc4</artifactId>
		  <version>4.0</version>
		</dependency>
		<dependency>
		    <groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.1</version>
		</dependency>
		<dependency>
		    <groupId>org.lucee</groupId>
		    <artifactId>postgresql</artifactId>
		    <version>8.3-606.jdbc4</version>
		</dependency>

抽象公共类

public abstract class DataBaseUtil {
    private final static Logger LOGGER = LoggerFactory.getLogger(DataBaseUtil.class);
    
    private String ip;
    private Integer port;
    private String userName;
    private String password;
    
    //表名
    private String tableName;
    
    public String getIp() {
		return ip;
	}

	public void setIp(String ip) {
		this.ip = ip;
	}

	public Integer getPort() {
		return port;
	}

	public void setPort(Integer port) {
		this.port = port;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	/**
     * 获取驱动
     * @return
     */
    abstract String getDriver();
    
    /**
     * 获取没有数据数据库的url
     * @return
     */
    abstract String getNoDataBaseUrl();
    
    
    /**
     * 获取带数据库的url
     * @return
     */
    abstract String getUrl(String dataBaseName);
    
    /**
     * 获取查询所有数据库的sql
     * @return
     */
    abstract String getDataBaseSql();
    
    /**
     * 获取表中所有字段信息
     * @param tableName 表名
     * @return
     */
    public abstract List<SrcField> getColumnParams(String dataBaseName, String tableName);
    
    public DataBaseUtil(String ip, Integer port, String userName, String password) {
		super();
		this.ip = ip;
		this.port = port;
		this.userName = userName;
		this.password = password;
		
		try {
			Class.forName(getDriver());
		} catch (ClassNotFoundException e) {
			LOGGER.error("can not load jdbc driver", e);
		}
	}
    
    /**
     * 创建不带数据库的Connection
     * @return
     */
    public Connection getNoDataBaseConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(this.getNoDataBaseUrl(), this.userName, this.password);
        } catch (SQLException e) {
            LOGGER.error("get connection failure", e);
        }
        return conn;
    }
    
    /**
     * 获取数据库连接
     * @return
     */
    public Connection getConnection(String dataBaseName) {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(this.getUrl(dataBaseName), this.userName, this.password);
        } catch (SQLException e) {
            LOGGER.error("get connection failure", e);
        }
        return conn;
    }
    
    /**
     * 关闭数据库连接
     * @param conn
     */
    public void closeConnection(Connection conn) {
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOGGER.error("close connection failure", e);
            }
        }
    }
    
    /**
     * 查询数据库的信息
     */
    public List<String> getDatabaseNames(){
    	List<String> databaseNames = new ArrayList<>();
    	//与数据库的连接
        Connection conn = this.getNoDataBaseConnection();
        String tableSql = this.getDataBaseSql();
        Statement stmt = null;
        try {
        	stmt=conn.createStatement(); 
            ResultSet rs=stmt.executeQuery(tableSql);
            while(rs.next())
            {
                String tableName = rs.getString(1);
                databaseNames.add(tableName);
            }
        } catch (SQLException e) {
            LOGGER.error("getColumnNames failure", e);
        } finally {
            if (stmt != null) {
                try {
                	stmt.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnNames close pstem and connection failure", e);
                }
            }
        }
        
        return databaseNames;  
    }
    
    /**
     * 获取数据库下的所有表名
     */
    public List<String> getTableNames(String dataBaseName) {
        List<String> tableNames = new ArrayList<>();
        Connection conn = this.getConnection(dataBaseName);
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[] { "TABLE" });
            while(rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            LOGGER.error("getTableNames failure", e);
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }
}

mysql

/**
 * mysql查询数据源信息工具类
 *
 */
public class DataBaseMysqlUtil extends DataBaseUtil{
	private final static Logger LOGGER = LoggerFactory.getLogger(DataBaseMysqlUtil.class);
	
    public DataBaseMysqlUtil(String ip, Integer port, String userName, String password) {
		super(ip, port, userName, password);
	}

	@Override
	String getDriver() {
		return "com.mysql.jdbc.Driver";
	}

	@Override
	String getNoDataBaseUrl() {
		return "jdbc:mysql://" + this.getIp() + ":" + this.getPort()+ "?useUnicode=true&characterEncoding=utf8";
	}

	@Override
	String getUrl(String dataBaseName) {
		return "jdbc:mysql://" + this.getIp() + ":" + this.getPort()+ "/" + dataBaseName
				+ "?useUnicode=true&characterEncoding=utf8";
	}

	@Override
	String getDataBaseSql() {
		return "show databases";
	}

	@Override
	public List<SrcField> getColumnParams(String dataBaseName, String tableName) {
		 //与数据库的连接
        Connection conn = this.getConnection(dataBaseName);
        
        List<SrcField> srcFieldList = new ArrayList<>();//列名信息集合
        
        Statement stmt = null;
        ResultSet rs = null;
        try {
        	stmt=conn.createStatement(); 
            rs = stmt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
            	SrcField srcField = new SrcField();
            	srcField.setComment(rs.getString("Comment"));
            	srcField.setName(rs.getString("Field"));
            	
            	String type = rs.getString("Type");
            	if(!StringUtils.isEmpty(type)) type = type.substring(0, type.indexOf("(") == -1 ? type.length() : type.indexOf("(") );
            	srcField.setDataType(type);
            	
            	String key = rs.getString("Key");
            	Integer isPrimaryKey = key != null && key.equals("PRI") ? 1 : 0;
            	srcField.setIsPrimaryKey(isPrimaryKey);
            	
            	srcFieldList.add(srcField);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return srcFieldList;
	}
	public DbColumnType processTypeConvert(String fieldType) {
        String t = fieldType.toLowerCase();
        if (!t.contains("char") && !t.contains("text")) {
            if (t.contains("bigint")) {
                return DbColumnType.BIGINT;
            } else if (t.contains("int")) {
                return DbColumnType.INT;
            } else if (!t.contains("date") && !t.contains("time") && !t.contains("year")) {
                if (t.contains("bit")) {
                    return DbColumnType.STRING;
                } else if (t.contains("decimal")) {
                    return DbColumnType.DECIMAL;
                } else if (t.contains("clob")) {
                    return DbColumnType.STRING;
                } else if (t.contains("blob")) {
                    return DbColumnType.BINARY;
                } else if (t.contains("binary")) {
                    return DbColumnType.BINARY;
                } else if (t.contains("float")) {
                    return DbColumnType.FLOAT;
                } else if (t.contains("double")) {
                    return DbColumnType.DOUBLE;
                } else {
                    return !t.contains("json") && !t.contains("enum") ? DbColumnType.STRING : DbColumnType.STRING;
                }
            } else {
            	if(t.contains("time")){
            		return DbColumnType.TIMESTAMP;
            	}else{
            		return DbColumnType.DATE;
            	}
            }
        } else {
            return DbColumnType.STRING;
        }
    }
}

oracle

/**
 * oracle查询数据源信息工具类
 */
public class DataBaseOracleUtil extends DataBaseUtil{
	private final static Logger LOGGER = LoggerFactory.getLogger(DataBaseOracleUtil.class);
	
    public DataBaseOracleUtil(String ip, Integer port, String userName, String password) {
		super(ip, port, userName, password);
	}

	@Override
	String getDriver() {
		return "oracle.jdbc.driver.OracleDriver";
	}

	@Override
	String getNoDataBaseUrl() {
		return "jdbc:oracle:thin:@" + this.getIp() + ":" + this.getPort()+ ":orcl";
	}

	@Override
	String getUrl(String dataBaseName) {
		return this.getNoDataBaseUrl();
	}

	@Override
	String getDataBaseSql() {
		return "select name from v$database";
	}
	
	public List<String> getDatabaseNames(){
		List<String> databaseNames = new ArrayList<>();
		databaseNames.add(this.getUserName());
		
		return databaseNames;
	}

	/**
     * 获取数据库下的所有表名
     */
    public List<String> getTableNames(String dataBaseName) {
        List<String> tableNames = new ArrayList<>();
        Connection conn = this.getConnection(dataBaseName);
        
        String tableSql = "select table_name from user_tables";
        Statement stmt = null;
        try {
        	stmt=conn.createStatement(); 
            ResultSet rs=stmt.executeQuery(tableSql);
            while(rs.next())
            {
                String tableName = rs.getString(1);
                tableNames.add(tableName);
            }
        } catch (SQLException e) {
        	 LOGGER.error("getTableNames failure", e);
        } finally {
            try {
            	stmt.close();
                conn.close();
            } catch (SQLException e) {
            	 LOGGER.error("close ResultSet failure", e);
            }
        }
        return tableNames;
    }
	
	@Override
	public List<SrcField> getColumnParams(String Owner, String tableName) {
		 //与数据库的连接
        Connection conn = this.getConnection(Owner);
        
        List<SrcField> srcFieldList = new ArrayList<>();//列名信息集合
        
        Statement stmt = null;
        ResultSet rs = null;
        try {
        	String sql=  
        			"select "+  
                    "         comments as \"Name\","+  
                    "         a.column_name \"Field\","+  
                    "         a.DATA_TYPE as \"Type\","+  
                    "         b.comments as \"Comment\","+  
                    "         decode(c.column_name,null,'FALSE','TRUE') as \"Primary\","+  
                    "         decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as \"Mandatory\","+  
                    "         '' \"sequence\""+  
                    "   from "+  
                    "       all_tab_columns a, "+  
                    "       all_col_comments b,"+  
                    "       ("+  
                    "        select a.constraint_name, a.column_name"+  
                    "          from user_cons_columns a, user_constraints b"+  
                    "         where a.constraint_name = b.constraint_name"+  
                    "               and b.constraint_type = 'P'"+  
                    "               and a.table_name = '"+tableName+"'"+  
                    "       ) c"+  
                    "   where "+  
                    "     a.Table_Name=b.table_Name "+  
                    "     and a.column_name=b.column_name"+  
                    "     and a.Table_Name='"+tableName+"'"+  
                    "     and a.owner=b.owner "+  
                    "     and a.owner='"+Owner+"'"+  
                    "     and a.COLUMN_NAME = c.column_name(+)" +  
                    "  order by a.COLUMN_ID";
        	
        	stmt=conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
            	SrcField srcField = new SrcField();
            	srcField.setComment(rs.getString("Comment"));
            	srcField.setName(rs.getString("Field"));
            	
            	String type = rs.getString("Type");
            	if(!StringUtils.isEmpty(type)) type = type.substring(0, type.indexOf("(") == -1 ? type.length() : type.indexOf("(") );
            	srcField.setDataType(type);
            	
            	String key = rs.getString("Primary");
            	Integer isPrimaryKey = key != null && key.equals("TRUE") ? 1 : 0;
            	srcField.setIsPrimaryKey(isPrimaryKey);
            	
            	srcFieldList.add(srcField);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return srcFieldList;
	}
	public DbColumnType processTypeConvert(String fieldType) {
        String t = fieldType.toUpperCase();
        if (t.contains("CHAR")) {
            return DbColumnType.STRING;
        } else if (!t.contains("DATE") && !t.contains("TIMESTAMP")) {
            if (t.contains("NUMBER")) {
                if (t.matches("NUMBER\\(+\\d\\)")) {
                    return DbColumnType.INT;
                } else {
                    return t.matches("NUMBER\\(+\\d{2}+\\)") ? DbColumnType.BIGINT : DbColumnType.DOUBLE;
                }
            } else if (t.contains("FLOAT")) {
                return DbColumnType.FLOAT;
            } else if (t.contains("DOUBLE")) {
            	return DbColumnType.DOUBLE;
            } else if (t.contains("clob")) {
                return DbColumnType.STRING;
            } else if (t.contains("BLOB")) {
                return DbColumnType.BINARY;
            } else if (t.contains("binary") || t.contains("BFILE")) {
                return DbColumnType.BINARY;
            } else {
                return t.contains("RAW") ? DbColumnType.BINARY : DbColumnType.STRING;
            }
        } else {
        	if(t.contains("TIMESTAMP")){
        		return DbColumnType.TIMESTAMP;
        	}else{
        		return DbColumnType.DATE;
        	}
        }
    }
}

selserver

/**
 * sqlserver查询数据源信息工具类

 *
 */
public class DataBaseSqlServerUtil extends DataBaseUtil{
	private final static Logger LOGGER = LoggerFactory.getLogger(DataBaseSqlServerUtil.class);
	
	private static final String TABLE_NAME = "TABLE_NAME";
	
	//查询表字段的信息的sql
	private static final String DESCRIPTION_PRIMARY_KEY = 
    		"SELECT TB.NAME AS TABLE_NAME, COL.NAME AS FIELD, COL.MAX_LENGTH AS LENGTH, COL.IS_NULLABLE AS ISNULLABLE, T.NAME AS TYPE, "
    		+ "		( 	SELECT TOP 1 IND.IS_PRIMARY_KEY "
    		+ "			FROM SYS.INDEX_COLUMNS IC "
    		+ "			LEFT JOIN SYS.INDEXES IND ON IC.OBJECT_ID = IND.OBJECT_ID AND IC.INDEX_ID= IND.INDEX_ID AND IND.NAME LIKE 'PK_%' "
    		+ "			WHERE IC.OBJECT_ID = TB.OBJECT_ID AND IC.COLUMN_ID= COL.COLUMN_ID "
    		+ "		) AS IS_PRIMARY_KEY, "
    		+ "		CONVERT(VARCHAR(300), COM.VALUE) AS COMMENT "
    		+ "FROM SYS.TABLES TB "
    		+ "INNER JOIN SYS.COLUMNS COL ON COL.OBJECT_ID = TB.OBJECT_ID "
    		+ "LEFT JOIN SYS.TYPES T ON T.USER_TYPE_ID = COL.USER_TYPE_ID "
    		+ "LEFT JOIN SYS.EXTENDED_PROPERTIES COM ON COM.MAJOR_ID = COL.OBJECT_ID AND COM.MINOR_ID = COL.COLUMN_ID "
    		+ "WHERE TB.NAME = '" + TABLE_NAME + "'" ;
	
	
    public DataBaseSqlServerUtil(String ip, Integer port, String userName, String password) {
		super(ip, port, userName, password);
	}

	@Override
	String getDriver() {
		return "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	}

	@Override
	String getNoDataBaseUrl() {
		return "jdbc:sqlserver://" + this.getIp() + ":" + this.getPort()+ ";";
	}

	@Override
	String getUrl(String dataBaseName) {
		return "jdbc:sqlserver://" + this.getIp() + ":" + this.getPort()+ ";databasename=" + dataBaseName;
	}

	@Override
	String getDataBaseSql() {
		return "SELECT name FROM  master..sysdatabases WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'northwind','pubs' )";
	}

	@Override
	public List<SrcField> getColumnParams(String dataBaseName, String tableName) {
		 //与数据库的连接
        Connection conn = this.getConnection(dataBaseName);
        
        List<SrcField> srcFieldList = new ArrayList<>();//列名信息集合
        
        Statement stmt = null;
        ResultSet rs = null;
        try {
        	stmt=conn.createStatement(); 
            rs = stmt.executeQuery(DESCRIPTION_PRIMARY_KEY.replace(TABLE_NAME, tableName));
            while (rs.next()) {
            	SrcField srcField = new SrcField();
            	srcField.setComment(rs.getString("COMMENT"));
            	srcField.setName(rs.getString("FIELD"));
            	
            	String type = rs.getString("TYPE");
            	if(!StringUtils.isEmpty(type)) type = type.substring(0, type.indexOf("(") == -1 ? type.length() : type.indexOf("(") );
            	srcField.setDataType(type);
            	
            	String key = rs.getString("IS_PRIMARY_KEY");
            	Integer isPrimaryKey = StringUtils.isEmpty(key) ? 0 : new Integer(key);
            	srcField.setIsPrimaryKey(isPrimaryKey);
            	
            	srcFieldList.add(srcField);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return srcFieldList;
	}
	public DbColumnType processTypeConvert(String fieldType) {
        String t = fieldType.toLowerCase();
        if (!t.contains("char") && !t.contains("text")) {
        	if (t.contains("smallint") || t.contains("tinyint")) {
        		 return DbColumnType.SMALLINT;
        	} else if (t.contains("bigint")) {
                return DbColumnType.BIGINT;
            } else if (t.contains("int")) {
                return DbColumnType.INT;
            } else if (!t.contains("date") && !t.contains("time") && !t.contains("year")) {
            	if (t.contains("bit") || t.contains("xml") || t.contains("uniqueidentifier")) {
                    return DbColumnType.STRING;
                } else if (t.contains("decimal") || t.contains("money") || t.contains("numeric")) {
                    return DbColumnType.DECIMAL;
                } else if (t.contains("binary") || t.contains("image")) {
                    return DbColumnType.BINARY;
                } else if (t.contains("float")) {
                    return DbColumnType.DOUBLE;
                } else if (t.contains("real")) {
                	return DbColumnType.FLOAT;
                } else {
                    return DbColumnType.STRING;
                }
            } else {
            	if(t.contains("timestamp")){
            		return DbColumnType.BINARY;
            	}else if(t.contains("time")){
            		return DbColumnType.TIMESTAMP;
            	}else{
            		return DbColumnType.DATE;
            	}
            }
        } else {
            return DbColumnType.STRING;
        }
    }
}

postgreSQL

/**
 * postgre查询数据源信息工具类
 *
 */
public class DataBasePostgreUtil extends DataBaseUtil{
	private final static Logger LOGGER = LoggerFactory.getLogger(DataBasePostgreUtil.class);
	
	private static final String TABLE_NAME = "TABLE_NAME";
	
	//查询表字段的信息的sql
	private static final String DESCRIPTION_PRIMARY_KEY = 
    			"SELECT "
    		+ "		COL_DESCRIPTION(A.ATTRELID,A.ATTNUM) AS COMMENT,"
    		+ "		FORMAT_TYPE(A.ATTTYPID,A.ATTTYPMOD) AS TYPE,"
    		+ "		A.ATTNAME AS FIELD, B.NAME KEYNAME "
    		+ "	FROM   PG_ATTRIBUTE AS A "
    		+ "	LEFT JOIN PG_CLASS AS C ON A.ATTRELID = C.OID "
    		+ "	LEFT JOIN ( "
    		+ "			SELECT PG_CONSTRAINT.CONNAME AS PK_NAME,PG_ATTRIBUTE.ATTNAME AS NAME "
    		+ "			FROM PG_CONSTRAINT "
    		+ "			INNER JOIN PG_CLASS ON PG_CONSTRAINT.CONRELID = PG_CLASS.OID "
    		+ "			INNER JOIN PG_ATTRIBUTE ON PG_ATTRIBUTE.ATTRELID = PG_CLASS.OID AND  PG_ATTRIBUTE.ATTNUM = PG_CONSTRAINT.CONKEY[1] "
    		+ "			WHERE PG_CLASS.RELNAME = '" + TABLE_NAME + "' AND PG_CONSTRAINT.CONTYPE='p' "
    		+ "	) B ON A.ATTNAME = B.NAME "
    		+ "	WHERE C.RELNAME = '" + TABLE_NAME + "' AND  A.ATTNUM>0" ;
	
	
    public DataBasePostgreUtil(String ip, Integer port, String userName, String password) {
		super(ip, port, userName, password);
	}

	@Override
	String getDriver() {
		return "org.postgresql.Driver";
	}

	@Override
	String getNoDataBaseUrl() {
		return "jdbc:postgresql://" + this.getIp() + ":" + this.getPort();
	}

	@Override
	String getUrl(String dataBaseName) {
		return "jdbc:postgresql://" + this.getIp() + ":" + this.getPort()+ "/" + dataBaseName;
	}

	@Override
	String getDataBaseSql() {
		return "SELECT DATNAME FROM PG_DATABASE";
	}

	@Override
	public List<SrcField> getColumnParams(String dataBaseName, String tableName) {
		 //与数据库的连接
        Connection conn = this.getConnection(dataBaseName);
        
        List<SrcField> srcFieldList = new ArrayList<>();//列名信息集合
        
        Statement stmt = null;
        ResultSet rs = null;
        try {
        	stmt=conn.createStatement(); 
            rs = stmt.executeQuery(DESCRIPTION_PRIMARY_KEY.replace(TABLE_NAME, tableName));
            while (rs.next()) {
            	SrcField srcField = new SrcField();
            	srcField.setComment(rs.getString("COMMENT"));
            	srcField.setName(rs.getString("FIELD"));
            	
            	String type = rs.getString("TYPE");
            	if(!StringUtils.isEmpty(type)) type = type.substring(0, type.indexOf("(") == -1 ? type.length() : type.indexOf("(") );
            	srcField.setDataType(type);
            	
            	String key = rs.getString("KEYNAME");
            	Integer isPrimaryKey = StringUtils.isEmpty(key) ? 0 : 1;
            	srcField.setIsPrimaryKey(isPrimaryKey);
            	
            	srcFieldList.add(srcField);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                    closeConnection(conn);
                } catch (SQLException e) {
                    LOGGER.error("getColumnComments close ResultSet and connection failure", e);
                }
            }
        }
        return srcFieldList;
	}
	public DbColumnType processTypeConvert(String fieldType) {
        String t = fieldType.toLowerCase();
        if (!t.contains("char") && !t.contains("text")) {
            if (t.contains("bigint")) {
                return DbColumnType.BIGINT;
            } else if (t.contains("int")) {
                return DbColumnType.INT;
            } else if (!t.contains("date") && !t.contains("time") && !t.contains("year")) {
                if (t.contains("bit")) {
                    return DbColumnType.STRING;
                } else if (t.contains("decimal") || t.contains("numeric")) {
                    return DbColumnType.DECIMAL;
                } else if (t.contains("clob")) {
                    return DbColumnType.STRING;
                } else if (t.contains("blob")) {
                    return DbColumnType.BINARY;
                } else if (t.contains("float")) {
                    return DbColumnType.FLOAT;
                } else if (t.contains("double")) {
                    return DbColumnType.DOUBLE;
                } else if (!t.contains("json") && !t.contains("enum")) {
                    return t.contains("boolean") ? DbColumnType.STRING : DbColumnType.STRING;
                } else {
                    return DbColumnType.STRING;
                }
            } else {
            	if(t.contains("time")){
            		return DbColumnType.TIMESTAMP;
            	}else{
            		return DbColumnType.DATE;
            	}
            }
        } else {
            return DbColumnType.STRING;
        }
    }
}