获取指定表的表结构

最近公司项目中需要做一个使用excel员工导入的需求，在导入过程中，需要对数据核验，就需要获取相应的表结构的字段类型，及其类型精度。以下为代码：

 

    private Map<String, TableColumnInfo> getColumnInfo() {
        Map<String, String> condition = new HashMap<String, String>();
	// 参数值只能是小写
        condition.put("tableSchema", "public");
        condition.put("tableName", "***");
        List<Map<String, Object>> schemaList = sqlSession.selectList("com.*.*.*.getTableSchemaByCondition", condition);
        
        Map<String, TableColumnInfo> columnMap = new HashMap<String, TableColumnInfo>();
        for (Map<String, Object> map : schemaList) {
            TableColumnInfo column = new TableColumnInfo();
            column.setTableName(String.valueOf(map.get("table_name")));
            column.setColName(String.valueOf(map.get("column_name")));
            column.setColType(String.valueOf(map.get("data_type")));
            column.setNullable(String.valueOf(map.get("is_nullable")));
            if (map.get("character_maximum_length") == null) {
                column.setColLegth(0);
            } else {
                column.setColLegth(Integer.valueOf(String.valueOf(map.get("character_maximum_length"))));
            }
            if (map.get("numeric_precision") == null) {
                column.setNumericPrecision(0);
            } else {
                column.setNumericPrecision(Integer.valueOf(String.valueOf(map.get("numeric_precision"))));
            }
            if (map.get("numeric_scale") == null) {
                column.setNumericScale(0);
            } else {
                column.setNumericScale(Integer.valueOf(String.valueOf(map.get("numeric_scale"))));
            }
            
            columnMap.put(column.getColName(), column);
        }
        return columnMap;
    }

 

public class TableColumnInfo {
    private String  tableName;
    private String  colName;
    private String  colType;
    private int     colLegth;
    private boolean nullable;
    private int     numericPrecision;
    private int     numericScale;

    public String toString() {
        return String.format("%s %s(%s)", new Object[] { this.colName, this.colType, Integer.valueOf(this.colLegth) });
    }

    public String getColName() {
        return this.colName;
    }

    public void setColName(String colName) {
        this.colName = colName;
    }

    public String getColType() {
        return this.colType;
    }

    public void setColType(String colType) {
        this.colType = colType;
    }

    public int getColLegth() {
        return this.colLegth;
    }

    public void setColLegth(int colLegth) {
        this.colLegth = colLegth;
    }

    public String getTableName() {
        return this.tableName;
    }

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

    public boolean isNullable() {
        return this.nullable;
    }

    public boolean getNullable() {
        return this.nullable;
    }

    public void setNullable(String nullable) {
        if ("YES".equals(nullable))
            this.nullable = true;
        else
            this.nullable = false;
    }

    public int getNumericPrecision() {
        return numericPrecision;
    }

    public void setNumericPrecision(int numericPrecision) {
        this.numericPrecision = numericPrecision;
    }

    public int getNumericScale() {
        return numericScale;
    }

    public void setNumericScale(int numericScale) {
        this.numericScale = numericScale;
    }
}

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.*.*.*">

	<select id="getTableSchemaByCondition" parameterType="map" resultType="map">
		SELECT
		    col.table_schema ,
		    col.table_name ,
		    col.ordinal_position,
		    col.column_name ,
		    col.data_type ,
		    col.character_maximum_length,
		    col.numeric_precision,
		    col.numeric_scale,
		    col.is_nullable,
		    col.column_default ,
		    des.description
		FROM information_schema.columns col 
		LEFT JOIN pg_description des ON col.table_name::regclass = des.objoid AND col.ordinal_position = des.objsubid
		WHERE table_schema = #{tableSchema} AND table_name = #{tableName}
		ORDER BY ordinal_position
	</select>
</mapper>

 针对不同的数据库，语法不尽相同，请注意。