Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-04-08
6
579 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

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

856 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