Solved

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

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

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 26

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 26

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now