精华内容
下载资源
问答
  • import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.PreparedStatement; import java.sql.ResultSetMetaData; import java.sql.SQLException; public class ...
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.PreparedStatement;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    
    public class TestDemo {
    	public static Connection getConnection() {
    		Connection conn = null;
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    			String url = "jdbc:mysql://数据库IP地址:3306/数据库名称";
    			String user = "数据库用户名";
    			String pass = "数据库用户密码";
    			conn = DriverManager.getConnection(url, user, pass);
    		} catch (ClassNotFoundException e) {
    			e.printStackTrace();
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		return conn;
    	}
    
    	public static void main(String[] args) {
    		Connection conn = getConnection();
    		String sql = "select * from AccessType";
    		PreparedStatement stmt;
    		try {
    			stmt = conn.prepareStatement(sql);
    			ResultSet rs = stmt.executeQuery(sql);
    			ResultSetMetaData data = rs.getMetaData();
    			for (int i = 1; i <= data.getColumnCount(); i++) {
    				// 获得所有列的数目及实际列数
    				int columnCount = data.getColumnCount();
    				// 获得指定列的列名
    				String columnName = data.getColumnName(i);
    				// 获得指定列的列值
    				int columnType = data.getColumnType(i);
    				// 获得指定列的数据类型名
    				String columnTypeName = data.getColumnTypeName(i);
    				// 所在的Catalog名字
    				String catalogName = data.getCatalogName(i);
    				// 对应数据类型的类
    				String columnClassName = data.getColumnClassName(i);
    				// 在数据库中类型的最大字符个数
    				int columnDisplaySize = data.getColumnDisplaySize(i);
    				// 默认的列的标题
    				String columnLabel = data.getColumnLabel(i);
    				// 获得列的模式
    				String schemaName = data.getSchemaName(i);
    				// 某列类型的精确度(类型的长度)
    				int precision = data.getPrecision(i);
    				// 小数点后的位数
    				int scale = data.getScale(i);
    				// 获取某列对应的表名
    				String tableName = data.getTableName(i);
    				// 是否自动递增
    				boolean isAutoInctement = data.isAutoIncrement(i);
    				// 在数据库中是否为货币型
    				boolean isCurrency = data.isCurrency(i);
    				// 是否为空
    				int isNullable = data.isNullable(i);
    				// 是否为只读
    				boolean isReadOnly = data.isReadOnly(i);
    				// 能否出现在where中
    				boolean isSearchable = data.isSearchable(i);
    				System.out.println(columnCount);
    				System.out.println("获得列" + i + "的字段名称:" + columnName);
    				System.out.println("获得列" + i + "的类型,返回SqlType中的编号:" + columnType);
    				System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
    				System.out.println("获得列" + i + "所在的Catalog名字:" + catalogName);
    				System.out.println("获得列" + i + "对应数据类型的类:" + columnClassName);
    				System.out.println("获得列" + i + "在数据库中类型的最大字符个数:" + columnDisplaySize);
    				System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
    				System.out.println("获得列" + i + "的模式:" + schemaName);
    				System.out.println("获得列" + i + "类型的精确度(类型的长度):" + precision);
    				System.out.println("获得列" + i + "小数点后的位数:" + scale);
    				System.out.println("获得列" + i + "对应的表名:" + tableName);
    				System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
    				System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
    				System.out.println("获得列" + i + "是否为空:" + isNullable);
    				System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
    				System.out.println("获得列" + i + "能否出现在where中:" + isSearchable);
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    	}
    }
    

     

    展开全文
  • 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.HashM....
    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
     * 
     */
    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");
             
             
        }
         
    }

     

    展开全文
  • 本节主要介绍了Oracle 查询表信息获取表字段及字段注释,需要的朋友可以参考下
  • SELECT DISTINCT * FROM ( SELECT comments AS "Name", a.column_name "Code", a.DATA_TYPE AS "DataType", b.comments AS "Comment", DECODE( c.column_name, NULL, 'FALSE', 'TRUE' ...

    SELECT DISTINCT
        * 
    FROM
        (
    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" 
    FROM
        all_tab_columns a,
        all_col_comments b,
        (
    SELECT
        a.constraint_name,
        a.column_name 
    FROM
        user_cons_columns a,
        user_constraints 
    WHERE
        a.constraint_name = b.constraint_name 
        AND b.constraint_type = 'P' 
        AND a.table_name = upper( 'sys_config' ) 
        ) c 
    WHERE
        a.Table_Name = b.table_Name 
        AND a.column_name = b.column_name 
        AND a.Table_Name = upper( 'sys_config' ) 
        AND a.COLUMN_NAME = c.column_name ( + ) 
    ORDER BY
        a.COLUMN_ID 
        )

    展开全文
  • Oracle 查询表字段和注释声明:Oracle查询字段注释和字段时,表名都必须大写---查询表的名称,字段信息以及字段注释select ut.COLUMN_NAME,--字段名称 uc.comments,--字段注释 ut.DATA_TYPE,--字典类型 ut....

    Oracle 查询表字段和注释

    声明:Oracle 中查询字段注释和字段时,表名都必须大写

    ---查询表的名称,字段信息以及字段注释
    
    select 
          ut.COLUMN_NAME,--字段名称
          uc.comments,--字段注释
          ut.DATA_TYPE,--字典类型
          ut.DATA_LENGTH,--字典长度
          ut.NULLABLE--是否为空
    from user_tab_columns  ut
    inner JOIN user_col_comments uc
    on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
    where ut.Table_Name='RC_METADATA' 
    order by ut.column_name
    -------------执行结果
    ![这里写图片描述](https://img-blog.csdn.net/20170713170154882?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQveWFsaTE5OTA1MTU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
    
    --获取表字段:
    
    select * 
    from user_tab_columns 
    where Table_Name='RC_METADATA' 
    order by column_name
    
    --获取表注释:
    
    select * 
    from user_tab_comments 
    where Table_Name='RC_METADATA'
    order by Table_Name
    
    --获取字段注释:
    
    select * 
    from user_col_comments 
    where Table_Name='RC_METADATA'
    order by column_name
    
    
    /* 获取表:*/
    
    select table_name from user_tables; --当前用户的表      
    
    select table_name from all_tables; --所有用户的表  
    
    select table_name from dba_tables;--包括系统表
    
    select table_name from dba_tables where owner='SS_MONITOR'
    
    /* 
    user_tables:
    
    table_name,tablespace_name,last_analyzed等
    
    dba_tables:
    
    ower,table_name,tablespace_name,last_analyzed等
    
    all_tables:
    
    ower,table_name,tablespace_name,last_analyzed等
    
    all_objects:
    
    ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等 
    */
    
    /*  获取表字段:*/
    
    select * from user_tab_columns where Table_Name='RC_METADATA';
    
    select * from all_tab_columns where Table_Name='RC_METADATA';
    
    select * from dba_tab_columns where Table_Name='RC_METADATA';
    
    /* user_tab_columns:
    
    table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
    
    all_tab_columns :
    
    ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
    
    dba_tab_columns:
    
    ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等 
    */
    
    /*  获取表注释:*/
    
    select * from user_tab_comments
    
    /* 
    user_tab_comments:table_name,table_type,comments
    
    相应的还有dba_tab_comments,all_tab_comments,这两个比user_tab_comments多了ower列。 
    */
    
    /* 获取字段注释:*/
    
    select * from user_col_comments
    
    /*
    
    user_col_comments:table_name,column_name,comments
    
    相应的还有dba_col_comments,all_col_comments,这两个比user_col_comments多了ower列。 
    */
    
    展开全文
  • 根据数据库表字段自动生成javaBean工具(支持Mysql,Oracle) , 能够智能开启驼峰标识 ,操作非常简单, 包含使用教程
  • 今天在研究如何将oracle表字段的信息存到列表数组中,做了一点实验,是在java1.5下,用myeclips做的,代码如下: public static List> gocilist(Connection conn,String table) {  ResultSet rs = null;  ...
  • oracle数据库查看表和表字段的语句

    千次阅读 2018-10-31 14:33:05
    删除表字段 alter table 表名称 drop column 表字段; 11.添加/修改表注释 comment on table 表名称is '注释'; 12.查看索引被哪个表使用 select t.* from user_ind_columns t where t.index_name='索引名称';
  • package com.sb.test; import org.junit.Before; import org.junit.Test; import java.sql.*; public class OracleTest { //jdbc:oracle:thin:@localhost:1521:zhyl private String url="jdbc:oracle:t...
  • Oracle表字段类型问题

    2017-09-17 01:29:06
    # oracle数据类型int转varchar 是这样的,由于某种原因,本人想将表中int类型的字段变成varchar, 但是需要保留原来的数据,比如原来的表中int类型的2改成string类型的2, 谁知道 如何做到,谢谢。
  • 添加Oracle表字段 alter table <table_name> add <column_name> <数据类型和约束条件>; 删除Oracle表字段 alter table <table_name> drop column <column_name>; -------------欢迎...
  • java讀取oracle表字段,生成XSSFWorkbook xlsx,維護對應數據並上傳xlsx ,按照表結構插入數據到oracle數據庫代碼環境根據表字段生成對應XLSX1三级目录 代碼環境 Markd12 Markdown将文本转换为 HTML。 根據表字段生成對...
  • 报错 org.springframework.... Cause: java.sql.SQLException: An identifier or literal is invalid ### The error may exist in file [/app/domain/user_projects/domains/base_domain/servers/VC/stage/visionl..
  • select A.COLUMN_NAME,A.DATA_TYPE from user_tab_columns A where TABLE_NAME = '表名'; 表名一定要大写 select A.COLUMN_NAME,A.DATA_TYPE from user_tab_columns A where TABLE_NAME =...
  • Java更新Oracle的clob类型字段

    千次阅读 2019-04-28 11:46:10
    Java更新Oracle的clob类型字段1.查询该clob字段2.处理该clob字段查询结果3.更新该clob字段查询结果 1.查询该clob字段 <select id="selectItsmTask" resultType="java.util.HashMap" parameterType="java.util....
  • PG 查询所有表信息、表字段信息

    千次阅读 2020-04-18 15:02:13
    查询所有业务表信息 select * from pg_tables where tablename not like 'pg%' and tablename not like 'sql_%' order by tablename; 查询所有业务表名称及表描述 select tablename,obj_description...
  • SELECT b.comments as 注释, a.column_name as 列名, a.data_type || '(' || a.data_length || ')' as 数据类型, a.nullable as 是否为空 FROM user_tab_columns a, user_col_comments b WHERE a....
  • Oracle 表字段定义CLOB类型导致系统文件占用大,导致表空间被占满,需要清理。 如下表中定义CLOB类型字段。 select table_name, column_name, segment_name, a.bytes from dba_segments a join dba_lobs b ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,541
精华内容 2,616
关键字:

java查询oracle表字段

java 订阅