java打印sql表_java程序获得SqlServer数据表的表结构

/**

* 取得一张表的结构信息

* 使用DatabaseMetaData与ResultSetMetaData结合的方式获取全部属性

* @param conn   数据连接

* @param tableName    表名

* @return  表结构中列的存储对象

* @throws SQLException

*/

public TableInfo getTableInfo(Connection conn,Statement st, String tableName) throws SQLException{

TableInfo result = new TableInfo();

/**设置表名*/

result.setTableName(tableName);

DatabaseMetaData dbmd = conn.getMetaData();

ResultSet rs = dbmd.getColumns(null, null, tableName.toUpperCase(), null);

/**判断字段是否自增*/

String sql = "select * from " + tableName + " where 1=2";

ResultSet rst = conn.prepareStatement(sql).executeQuery();

ResultSetMetaData rsmd = rst.getMetaData();

int i=1;

while(rs.next()){

//列名称

String columnName = rs.getString("COLUMN_NAME").toLowerCase();//列名

//数据类型

int dataType = rs.getInt("DATA_TYPE");//类型

//数据类型名称

String dataTypeName = rs.getString("TYPE_NAME").toLowerCase();

//精度,列的大小

int precision = rs.getInt("COLUMN_SIZE");//精度

//小数位数

int scale = rs.getInt("DECIMAL_DIGITS");// 小数的位数

//是否为空

int isNull = rs.getInt("NULLABLE");//是否为空

//字段默认值

String defaultValue = rs.getString("COLUMN_DEF");

//是否自增

boolean isAutoIncrement = rsmd.isAutoIncrement(i); //自增

ColumnInfo col = new ColumnInfo();

col.setName(columnName);

col.setDataType(dataType);

col.setDataTypeName(dataTypeName);

col.setPrecision(precision);

col.setScale(scale);

col.setIsNull(isNull);

col.setDefaultValue(defaultValue);

col.setAutoIncrement(isAutoIncrement);

result.setColInfo(columnName, col);

i++;

}

rs.close();

/**设置主键*/

rs = dbmd.getPrimaryKeys(null, null, tableName);

while(rs.next()){

result.setPrimaryKey(rs.getString("COLUMN_NAME").toLowerCase(), true);

System.out.println(rs.getString("COLUMN_NAME"));

}

rs.close();

return result;

}

/**

* 列信息存储对象

* @author

*/

public class ColumnInfo {

/** 主键标识 */

private boolean isKey;

/** 列名称 */

private String name;

/** 数据类型 */

private int dataType;

/** 数据类型名称 */

private String dataTypeName;

/** 自增标识 */

private boolean isAutoIncrement;

/** 精度 */

private int precision;

/** 是否为空*/

private int isNull;

/**小数位数 */

private int scale;

/**默认值 */

private String defaultValue;

public boolean isKey() {

return isKey;

}

public void setKey(boolean isKey) {

this.isKey = isKey;

}

public String getDefaultValue() {

return defaultValue;

}

public void setDefaultValue(String defaultValue) {

if(null==(defaultValue)){

}else{

this.defaultValue = "'"+defaultValue+"'";

}

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public int getDataType() {

return dataType;

}

public void setDataType(int dataType) {

this.dataType = dataType;

}

public boolean isAutoIncrement() {

return isAutoIncrement;

}

public void setAutoIncrement(boolean isAutoIncrement) {

this.isAutoIncrement = isAutoIncrement;

}

public String getDataTypeName() {

return dataTypeName;

}

public void setDataTypeName(String dataTypeName) {

this.dataTypeName = dataTypeName;

}

public String toString(){

StringBuffer buf = new StringBuffer();

buf.append("-------------\n");

buf.append("字段名称：" + getName() + "\n");

buf.append("数据类型：" + getDataType() + "\n");

buf.append("类型名称：" + getDataTypeName() + "\n");

buf.append("主键：" + isKey() + "\n");

buf.append("自增：" + isAutoIncrement + "\n");

buf.append("为空：" + isNull + "\n");

buf.append("小数位数：" + scale + "\n");

buf.append("精度:"+precision+"\n");

buf.append("初始值:"+defaultValue+"\n");

return buf.toString();

}

public int getPrecision() {

return precision;

}

public void setPrecision(int precision) {

this.precision = precision;

}

public int getIsNull() {

return isNull;

}

public void setIsNull(int isNull) {

this.isNull = isNull;

}

public int getScale() {

return scale;

}

public void setScale(int scale) {

this.scale = scale;

}

}