?
Solved

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

Posted on 2007-04-08
6
Medium Priority
?
592 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 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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 2000 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month12 days, 13 hours left to enroll

777 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