通过JDBC获得表结构信息

通过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();   
                }   
            }   
        }   
    }   
  
}