/**
* 取得一张表的结构信息
* 使用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;
}
}
