Solved

Java / Jdbc / Oracle -> Getting Primary keys...

Posted on 2007-04-08
6
576 Views
Last Modified: 2008-02-01
Hi everyone,

I'm accessing an Oracle database using JDBC... I need to get the primary keys from each table.

I think the problem is in the if statement.... it never detects the column Name as being a Primary Key...


 ResultSet primaryKeys = Data_Extraction.meta.getPrimaryKeys ( null, null, tableName ) ;
              if( primaryKeys.equals(columnName)){
              tableNode.add ( new DefaultMutableTreeNode ( columnName + " [PK] ") ) ;      
              }else
                    tableNode.add ( new DefaultMutableTreeNode ( columnName ) ) ;      
            }
0
Comment
Question by:RicardoVGomes
  • 3
  • 3
6 Comments
 
LVL 27

Accepted Solution

by:
mrcoffee365 earned 500 total points
ID: 18873755
Are you sure that primaryKeys.equals(columnName is doing what you want?  This example might be closer:

   rs = meta.getPrimaryKeys(null, null, "survey");

    while (rs.next()) {
      String columnName = rs.getString("COLUMN_NAME");
       if( columnName.equalsIgnoreCase(columnName) {
         // your code here
      }
    }
0
 

Author Comment

by:RicardoVGomes
ID: 18873785
Worked.
Thanks!

One more thing... can you tell me how to do the same sort of thing.... but for the foreign keys?
0
 
LVL 27

Expert Comment

by:mrcoffee365
ID: 18873865
Glad to help.

These methods are from the DatabaseMetaData class in Java.  The API is here:
http://java.sun.com/j2se/1.5.0/docs/api/
page down the index to DatabaseMetaData, and you'll get all of the methods available.  The ones for foreign keys are called getExportedKeys and getImportedKeys (for foreign keys that work both ways on the primary keys).
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:RicardoVGomes
ID: 18878162
I tried this:
                  ResultSet primaryKeys = Data_Extraction.meta.getPrimaryKeys ( null, null, tableName ) ;
              ResultSet foreignKeys1 = Data_Extraction.meta.getImportedKeys ( null, null, tableName ) ;
              ResultSet foreignKeys2 = Data_Extraction.meta.getExportedKeys ( null, null, tableName ) ;

              while (primaryKeys.next() && foreignKeys1.next() && foreignKeys2.next()){
                    String bla = primaryKeys.getString("COLUMN_NAME");
                    String blu = foreignKeys1.getString("COLUMN_NAME");
                    String bli = foreignKeys2.getString("COLUMN_NAME");
              if( bla.equals(columnName)){
              tableNode.add ( new DefaultMutableTreeNode ( columnName + " [Primary Key] ") ) ;      

              }else if (blu.equals(columnName)){
                    tableNode.add ( new DefaultMutableTreeNode ( columnName + " [Foreign Key] " ) ) ;      

              }else if (bli.equals(columnName)){
                    tableNode.add ( new DefaultMutableTreeNode ( columnName + " [Foreign Key] " ) ) ;      

              }else
                    tableNode.add ( new DefaultMutableTreeNode ( columnName ) ) ;
              }}
            }

To get "Primary Key" printed in front of the primary key columns.... and "Foreign Key" printed in front of the foreign key columns..... but when I ran it.... only the tables were printed... no columns were printed.

I think it has something to do with the while loop.... and the .next()

please help
0
 
LVL 27

Assisted Solution

by:mrcoffee365
mrcoffee365 earned 500 total points
ID: 18878871
Your while statement says that you have to have a next for primaryKeys, foreignKeys1 and foreignkeys2.  I think the number of values in each of those lists would be different.  For your if statements, I think it would be good to use equalsIgnoreCase (as in my example) just in case the upper/lower case values of your input column name and the db labels don't match.

I would have thought that you would want all columns, then loop through them to check if they are a primary key or foreign key.  Then print all columns, with the extra labeling for primary key or foreign key.

That would mean
loop on all columns {
  loop to check column[i] against all primary keys
  loop to check column[i] against all foreign keys
}

Have you tried putting some debugging statements in your loop, to see what you're getting for metadata, and what you're matching it against?
0
 

Author Comment

by:RicardoVGomes
ID: 18879491
Could I get some code with that?

as you have probably seen, my java is extremely bad
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to free up undo space? 3 41
MySqlDump not dumping triggers 1 31
Oracle Listener Not Starting 11 44
oracle- set role and grant privileges 6 28
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Via a live example, show how to take different types of Oracle backups using RMAN.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question