Solved

accessing table view from a programme

Posted on 2004-09-01
13
328 Views
Last Modified: 2011-08-18
hi,

I have designed an application using Java with tree control on left and fields on right. I am able to retreive data from oracle db into tree and also display data on the fields.
Now I have made a view taking data from two tables as I need to display data from both the tables. View is created successfully and showing the right results, but when I am accessing from the prog, Im getting only the first row.
For eg:- Im retreiving emp_number into the first node of the tree control, when I am retreiving this from the normal(original) table, its showing me all the emp_numbers, but when I am using view, its showing me only the first row.

So my question is, is it problem with view or shld I change something in the code. As far as I read from books, one of the main advantages of view is joining tables and retreiving from the prog, so that user doesnt have to worry about joining tables.

All contributions will be greatly appreciatted.
0
Comment
Question by:vihar123
  • 4
  • 4
  • 2
  • +1
13 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 11950341
Please show your sql
0
 
LVL 92

Expert Comment

by:objects
ID: 11950344
our db developer avoids views, i think cause they are a maintenance nightmare.
0
 

Author Comment

by:vihar123
ID: 11950378
>>our db developer avoids views, i think cause they are a maintenance nightmare.

but what if I have to access many tables, for each one do i need to build a new connection?
whats the general method that is followed :-)
--------------------------------------------------------------------------------------------------------------------------------------------


CREATE OR REPLACE VIEW regdet (
  ang_nr1,
  proj_nr1,
  prod_bez1,
  kunde_nr1,
  faktor1,
  basisprojekt1,
  ver_nr1,
  ver_besh1,
  gesc_jahr1,
  curr1,
  ges_kalk1,
  kalk_mek1,
  kalk_ele1,
  ref_nr1
) AS
SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
/

SELECT * FROM regdet;

0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 35

Expert Comment

by:TimYates
ID: 11950402
> but what if I have to access many tables, for each one do i need to build a new connection?

Can't you just run the SQL:

SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr

instead of using the view?  I have had real problems with views and live updates before...  changing them at run-time causes some db's to get things wrong...

However, if they are static, they should work...
0
 
LVL 92

Expert Comment

by:objects
ID: 11950403
> but what if I have to access many tables, for each one do i need to build a new connection?


not at all, you can do your join in a single statement
or better still use a stored proc
0
 

Author Comment

by:vihar123
ID: 11950447
>>not at all, you can do your join in a single statement

can you give me an example of how to join them in a single statement?

an other problem is I dont have direct access to the tables, so I thought using views would be a better solution, anyway i think experience counts more, so any more suggestions, how to achieve this.

I think its clear what i want to do, i need to retreive data from two tables and display. tree control is important cause on its node selection i want to display related data.
0
 
LVL 92

Expert Comment

by:objects
ID: 11950477
you already have the sql :)

SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 11950488
>>tree control is important cause on its node selection i want to display related data.

You will need a custom TableModel, but there are plenty of examples of getting the results:

http://javaalmanac.com/egs/java.sql/CreateResultSet.html
0
 
LVL 92

Expert Comment

by:objects
ID: 11950526
dxepending on how much data you are using it mat be worth loading all the data up front, and store it in a Map keyed on emp number for easy lookup when user selects node.
0
 

Author Comment

by:vihar123
ID: 11951116
well guys, i want to try it out with view though its not advisory, and the present problem im facing is it is showing only one record when im accessing from the programme. how can i solve this problem.

my code looks like this

--------------
Connection conn1;
       
       Statement stmt1;
       
       ResultSet rset1 = null;
       
       
        try
        {
            DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
           
            conn1 =
            DriverManager.getConnection ("");
           
            stmt1 = conn1.createStatement ();
                rset1 = stmt1.executeQuery("select * from regdet");
                
                           while (rset1.next())
               {
                       
                DefaultMutableTreeNode Angnr = new DefaultMutableTreeNode(rset1.getString("ang_nr1"));
              topnode.add(Angnr);              
              DefaultMutableTreeNode Version = new DefaultMutableTreeNode(rset1.getString("ver_nr1"));
              Angnr.add(Version);
              DefaultMutableTreeNode UA = new DefaultMutableTreeNode(rset1.getString("faktor"));
              Version.add(UA);
           
                              }
                              conn1.close();
                              stmt1.close();                                                      
                              rset1.close();
                              
                        
                                
                               
                    jTree1.setRootVisible(true);
                    treeModel.reload();      
                   
                     
        }
                 
        catch (SQLException e)
        {
            System.out.println(e);
        }
       
 
          jTree1.addTreeSelectionListener(new TreeSelectionListener() {
              
                public void valueChanged(TreeSelectionEvent evt) {
                                                                                       
                                TreePath tp = evt.getNewLeadSelectionPath();
       
                                 Connection conn2;
       
                               Statement stmt2;
       
                               ResultSet rset2 = null;
                               DefaultMutableTreeNode node1 = (DefaultMutableTreeNode)          
                                                                tp.getLastPathComponent();                               
                               String empNo = node1.toString();
                            

                                                              
                               try
                                {
                              DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
                     
                        conn2 = DriverManager.getConnection ("");
           
                              stmt2 = conn2.createStatement ();
                      rset2 = stmt2.executeQuery("select * from regdet WHERE ang_nr1 = '" + empNo + "'");
                                  
                               
                               while (rset2.next())
                           {
                            jTextField1.setText(node1.toString());
                            jTextField2.setText(rset2.getString("ang_nr1"));
                            jTextField3.setText(rset2.getString("prod_bez1"));
                            jTextField4.setText(rset2.getString("kunde_nr1"));
                            jTextField5.setText(rset2.getString("faktor1"));
                            jTextField6.setText(rset2.getString("proj_nr1"));
                            jTextField7.setText(rset2.getString("basisprojekt1"));
                                    
                           }
                              
                                          conn2.close();
                                          stmt2.close();
                                          rset2.close();
                                          
                                    
                                  }
                 
                          catch (SQLException e)
                                {
                                    System.out.println(e);
                                }
                                  
                                                                                        
                                                                                        
                                                                                        }
               
                                                                                                    }
       
                                                          );      
---------------------------------------

and the view looks like this

CREATE OR REPLACE VIEW regdet (
  ang_nr1,
  proj_nr1,
  prod_bez1,
  kunde_nr1,
  faktor1,
  basisprojekt1,
  ver_nr1,
  ver_besh1,
  gesc_jahr1,
  curr1,
  ges_kalk1,
  kalk_mek1,
  kalk_ele1,
  ref_nr1
) AS
SELECT r.ang_nr, r.proj_nr, r.prod_bez, r.kunde_nr, r.faktor, r.basisprojekt,
          d.ver_nr, d.ver_besh, d.gesc_jahr, d.curr, d.ges_kalk, d.kalk_mek, d.kalk_ele, d.ref_nr
FROM offreg1 r, offrdet1 d
WHERE r.ang_nr = d.ang_nr
/
-------------------------------------------

and the results from the view look like this.........

17.001      test      38      1,08      1234      2      Test Zdenko      2004      EUR      test1

test2      test3      17

........................................................


and the problem tree is showing only one record, ie the first column 17.001, its not showing other numbers, can anybody tell me where the problem is
0
 

Accepted Solution

by:
vihar123 earned 0 total points
ID: 11951283
ok guys, i solved the problem by myself, its working now. problem was with the view, i did not commit and didnt use column name properly, anyway thanks for the contributions.
--------------------------------------------------------------------------------------------------------------------------------------------
Hello Venabili,

Could you please delete the question, and refund the points.

Thanks.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
numbers ascending pyramid 101 191
eclipse formatting 6 87
javap bin 2 34
hibernate insert example 13 28
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

813 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

8 Experts available now in Live!

Get 1:1 Help Now