java 读取所有指定数据库所有表名

最新推荐文章于 2024-08-29 03:45:18 发布

转载最新推荐文章于 2024-08-29 03:45:18 发布 · 2.7k 阅读

本文详细介绍了如何使用Java代码获取数据库中的表名、表字段信息及字段注释，包括利用DatabaseMetaData接口进行数据库查询的基本操作，以及通过SQL语句获取所有表名的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
/***
 *  2014-07-21日：
 *  oracle数据库， 表结构查询 ，字段信息查询，字段注释查询 
 *  表字段查询 all_tab_columns
 *  表字段注释查询 all_col_comments
 *  @author 王华荣
 * 
 */
public class OracleTable {
    private static final String DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
    private static final String DATABASE_URL = "jdbc:oracle:thin:@192.168.1.10:1521:orcl";
    private static final String DATABASE_USER = "dev";
    private static final String DATABASE_PASSWORD = "dev";
    private static Connection con = null;
     
    public static Connection getConnection() {
        try {
            Class.forName(DRIVER_CLASS);
            con=DriverManager.getConnection(DATABASE_URL,DATABASE_USER,DATABASE_PASSWORD);
            return con;
        } catch (Exception ex) {
            System.out.println("2:"+ex.getMessage());
        }
        return con;
    }
     
    /***
     * 打印test
     * @throws SQLException
     */
    public static void sysoutStrTablePdmCloumns(String Table,String Owner) throws SQLException{
        getConnection();
         
        List<HashMap<String,String>> columns = new ArrayList<HashMap<String,String>>();
         
        try{
            Statement stmt = con.createStatement();
             
            String sql=
             "select "+
             "         comments as \"Name\","+
             "         a.column_name \"Code\","+
             "         a.DATA_TYPE as \"DataType\","+
             "         b.comments as \"Comment\","+
             "         decode(c.column_name,null,'FALSE','TRUE') as \"Primary\","+
             "         decode(a.NULLABLE,'N','TRUE','Y','FALSE','') as \"Mandatory\","+
             "         '' \"sequence\""+
             "   from "+
             "       all_tab_columns a, "+
             "       all_col_comments b,"+
             "       ("+
             "        select a.constraint_name, a.column_name"+
             "          from user_cons_columns a, user_constraints b"+
             "         where a.constraint_name = b.constraint_name"+
             "               and b.constraint_type = 'P'"+
             "               and a.table_name = '"+Table+"'"+
             "       ) c"+
             "   where "+
             "     a.Table_Name=b.table_Name "+
             "     and a.column_name=b.column_name"+
             "     and a.Table_Name='"+Table+"'"+
             "     and a.owner=b.owner "+
             "     and a.owner='"+Owner+"'"+
             "     and a.COLUMN_NAME = c.column_name(+)" +
             "  order by a.COLUMN_ID";
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()){
                HashMap<String,String> map = new HashMap<String,String>();
                map.put("Name", rs.getString("Name"));
                map.put("Code", rs.getString("Code"));
                map.put("DataType", rs.getString("DataType"));
                map.put("Comment", rs.getString("Comment"));
                map.put("Primary", rs.getString("Primary"));
                map.put("Mandatory", rs.getString("Mandatory"));
                columns.add(map);
            }
        }
        catch (SQLException e){
            e.printStackTrace();
        }finally{
            con.close();
        }
    }
    public static void main(String[] args) throws SQLException{
         
        sysoutStrTablePdmCloumns("CT_INFO_CONTRACT_BORROW","DEV");
         
         
    }
     
}

test

java获取数据库里表的名字

博客分类： java
 
一.Java方法 
// 得到当前数据库下所有的表名 
Java代码  收藏代码
public void getTableNameByCon(Connection con) {  
   try {  
   DatabaseMetaData meta = con.getMetaData();  
   ResultSet rs = meta.getTables(null, null, null,  
     new String[] { "TABLE" });  
   while (rs.next()) {  
     System.out.println("表名：" + rs.getString(3));  
     System.out.println("表所属用户名：" + rs.getString(2));  
     System.out.println("------------------------------");  
   }  
   con.close();  
   } catch (Exception e) {  
   try {  
     con.close();  
   } catch (SQLException e1) {  
     // TODO Auto-generated catch block  
     e1.printStackTrace();  
   }  
   // TODO Auto-generated catch block  
   e.printStackTrace();  
   }  
}  
二.通过sql语句得到数据库里所有的表名 
--MySQL数据库 
Java代码  收藏代码
show tables;  
--Oracle数据库（当前登录用户下的表） 
Java代码  收藏代码
select uo.object_name from user_objects uo where uo.object_type = 'TABLE';  
--sql server 2000/2005数据库 
Java代码  收藏代码
select name from sysobjects  
--sybase数据库 
Java代码  收藏代码
select name from sysobjects where type='U';  
--DB2数据库  

Java代码  收藏代码
select tabname from syscat.tables where tabschema = current schema ;//   
获取当前模式下面的所有用户表 

转自:http://hi.baidu.com/rybwxn/blog/item/1a59502a75a7de25d52af1cc.html

test

首先是DatabaseMetaData接口:

public static void main(String[] args) {   
           
        //这里没有指定数据库   
        String url = "jdbc:mysql://127.0.0.1:3306/";   
        String user = "root";   
        String pass = "";   
        try {   
  
            Class.forName("com.mysql.jdbc.Driver").newInstance();   
            Connection conn = DriverManager.getConnection(url, user, pass);   
            DatabaseMetaData metadata = conn.getMetaData();   
            System.out.println("数据库已知的用户: "+ metadata.getUserName());   
            System.out.println("数据库的系统函数的逗号分隔列表: "+ metadata.getSystemFunctions());   
            System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ metadata.getTimeDateFunctions());   
            System.out.println("数据库的字符串函数的逗号分隔列表: "+ metadata.getStringFunctions());   
            System.out.println("数据库供应商用于 'schema' 的首选术语: "+ metadata.getSchemaTerm());   
            System.out.println("数据库URL: " + metadata.getURL());   
            System.out.println("是否允许只读:" + metadata.isReadOnly());   
            System.out.println("数据库的产品名称:" + metadata.getDatabaseProductName());   
            System.out.println("数据库的版本:" + metadata.getDatabaseProductVersion());   
            System.out.println("驱动程序的名称:" + metadata.getDriverName());   
            System.out.println("驱动程序的版本:" + metadata.getDriverVersion());   
               
            System.out.println();   
            System.out.println("数据库中使用的表类型");   
            ResultSet rs = metadata.getTableTypes();   
            while (rs.next()) {   
                System.out.println(rs.getString(1));   
            }   
            rs.close();   
               
            System.out.println();   
            /**  
             * 获取指定的数据库的所有表的类型,getTables()的第一个参数就是数据库名  
             * 因为与MySQL连接时没有指定,这里加上,剩下的参数就可以为null了  
             * 第二个参数是模式名称的模式,但是输出也是什么都没有。谁知道告诉我一声  
             */  
            System.out.println("获取指定的数据库的所有表的类型");   
            ResultSet rs1 = metadata.getTables("ssi2bbs", null, null, null);   
            while (rs1.next()) {   
                System.out.println();   
                System.out.println("数据库名:"+ rs1.getString(1));   
                System.out.println("表名: "+rs1.getString(3));   
                System.out.println("类型: "+rs1.getString(4));   
            }   
            rs1.close();   
               
            System.out.println();   
            System.out.println("获取指定的数据库的表的主键");   
            //获取指定的数据库的表的主键，第二个参数也是模式名称的模式,使用null了   
            ResultSet rs2 = metadata.getPrimaryKeys("mysql", null, "db");   
            while (rs2.next()) {   
                System.out.println("主键名称: "+ rs2.getString(4));   
            }   
            rs2.close();   
               
            System.out.println();   
            System.out.println("DatabaseMetaData.getIndexInfo()方法返回信息:");   
            ResultSet rs3 = metadata.getIndexInfo("ssi2bbs", null, "user", false, true);   
            while (rs3.next()) {   
                System.out.println("数据库名: "+ rs3.getString(1));   
                System.out.println("表模式: "+ rs3.getString(2));   
                System.out.println("表名称: "+ rs3.getString(3));   
                System.out.println("索引值是否可以不唯一: "+ rs3.getString(4));   
                System.out.println("索引类别: "+ rs3.getString(5));   
                System.out.println("索引名称: "+ rs3.getString(6));   
                System.out.println("索引类型: "+ rs3.getString(7));   
                System.out.println("索引中的列序列号: "+ rs3.getString(8));   
                System.out.println("列名称: "+ rs3.getString(9));   
                System.out.println("列排序序列: "+ rs3.getString(10));   
                System.out.println("TYPE为 tableIndexStatistic时它是表中的行数否则它是索引中唯一值的数量: "+ rs3.getString(11));   
                System.out.println("TYPE为 tableIndexStatisic时它是用于表的页数否则它是用于当前索引的页数: "+ rs3.getString(12));   
                System.out.println("过滤器条件: "+ rs3.getString(13));   
            }   
            rs3.close();   
               
        } catch (Exception e) {   
            e.printStackTrace();   
        }   
    }  
 ResultSetMetaData接口, 用于获取关于ResultSet 对象中列的类型和属性信息的对象。下面的代码是把一个表的列信息打印了出来。当然了，这只是一部分。详细的参考JAVA JDK。

public static void main(String[] args) {   
        try {   
            Class.forName("com.mysql.jdbc.Driver").newInstance();   
            Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/ssi2bbs","root","");   
            PreparedStatement ps = conn.prepareStatement("select * from bbs");   
            ResultSet rs = ps.executeQuery();   
            ResultSetMetaData rsme = rs.getMetaData();   
               
            int columnCount = rsme.getColumnCount();   
            System.out.println("ResultSet对象中的列数"+ columnCount);   
            for (int i = 1; i < columnCount ; i++) {   
                System.out.println();   
                System.out.println("列名称: "+ rsme.getColumnName(i));   
                System.out.println("列类型(DB): " + rsme.getColumnTypeName(i));   
                System.out.println("长度: "+ rsme.getPrecision(i) );   
                System.out.println("是否自动编号: "+ rsme.isAutoIncrement(i));   
                System.out.println("是否可以为空: "+ rsme.isNullable(i));   
                System.out.println("是否可以写入: "+ rsme.isReadOnly(i));   
            }   
               
        } catch (Exception e) {   
            e.printStackTrace();   
        }   
           
    }