Solved

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

Posted on 2007-04-08
6
590 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
[X]
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
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn how to implement Singleton Design Pattern in Java.

689 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