欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

通过JDBC获得表结构信息

程序员文章站 2022-04-06 17:44:06
...

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