通过JDBC取得数据库的连接,然后关键性的代码如下:
DatabaseMetaData databaseMetaData = connection.getMetaData();
resultSet =databaseMetaData.getColumns(null,null,"tableName".toUpperCase(),"%");
注意:连接数据库的时候设置remarksReporting为true。
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.junit.Test;
/**
* @description:
* @author syq
* @2012-8-23
*/
public class ExprotDBInfo {
@Test
public void testExprotDBInfo() {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Properties props = new Properties();
props.put("user", "orcl");
props.put("password", "orcl");
props.put("remarksReporting", "true");
/*connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "zjpmsoa",
"zjpmsoa");
StringBuffer sql = new StringBuffer();
//通过查询的方法获得表结构信息,此种方法不够灵活而且不通用,只适用于oracle
sql.append("SELECT a.column_name,b.data_type||'('|| case b.data_type when 'NUMBER' then b.data_precision when 'VARCHAR2' then b.char_length end||','||b.data_scale||')' AS type,a.comments"
+ " FROM user_col_comments a , user_tab_columns b"
+ " WHERE a.table_name= b.table_name AND a.column_name =b.column_name AND a.table_name =upper(?)"
+ " ORDER BY a.column_name");
statement = connection.prepareStatement(sql.toString());
statement.setString(1, "kd_vehicle_data");
resultSet = statement.executeQuery();*/
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", props);
DatabaseMetaData databaseMetaData = connection.getMetaData();
//获得指定tableName对应的列
resultSet =databaseMetaData.getColumns(null,null,"tableName".toUpperCase(),"%");
/*ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
for(int i =0;i<resultSetMetaData.getColumnCount();i++){
System.out.println(resultSetMetaData.getColumnTypeName(i+1)+"\t"+resultSetMetaData.getColumnLabel(i+1));
}*/
StringBuffer result = new StringBuffer();
while(resultSet.next()){
result.append(resultSet.getString("COLUMN_NAME")).append("\t")
.append(resultSet.getString("TYPE_NAME")).append("\t")
.append(resultSet.getString("IS_NULLABLE")).append("\t")
.append(resultSet.getString("REMARKS")).append("\t")
.append("\n");
}
System.out.println(result.toString());
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (null != connection) {
try {
connection.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
}
}