SQL ERROR java.lang.ArrayIndexOutOfBoundsException: 0

Question

private void jselectedhousecomboPopupMenuWillBecomeInvisible(javax.swing.event.PopupMenuEvent evt) {                                                                 

    String tmp = (String)jselectedhousecombo.getSelectedItem();
    String sql = "SELECT H.HOUSE, H.TOTALROOMS, H.BEDSPERROOM, H.ROOMSOCCUPIED, 
                         H.ROOMSAVAILABLE, HM.HM_ID, HM.HM_NAME, HM.HM_DEPARTMENT, 
                         HM.HOUSE, HM.HM_PHONENUMBER, HM.HM_DATEIN, HM.HM_DATEOUT, 
                         HM.HM_RESIDENCE, CA.C_ID, CA.C_NAME, CA.HOUSE, CA.C_PHONENUMBER,
                         CA.C_DATEIN, CA.C_DATEOUT, CA.C_RESIDENCE, HP.HP_REGNO,HP.HP_NAME, HP.HP_FORM,
                         HP.HOUSE,HP.HP_ROOMNO,HP.HP_DATE, HP.HP_DATEOUT    
                  FROM HOUSES H     
                  JOIN Housemaster HM 
                  ON H.HOUSE = HM.HOUSE JOIN Caretaker CA 
                  ON H.HOUSE = CA.HOUSE JOIN Houseprefect HP 
                  ON H.HOUSE = HP.HOUSE"; 

            try 
            {

                pst=conn.prepareStatement(sql);

                pst.setString(1, tmp);

                rs=pst.executeQuery();

            if(rs.next())
            {

                String add=rs.getString("HOUSE");
                jselectedhousename.setText(add);

                String add2=rs.getString("C_NAME");
                jselectedhousecaretaker.setText(add2);

                String add3=rs.getString("HM_NAME");
                jselectedhousemaster.setText(add3);

                String add4=rs.getString("HP_NAME");
                jselectedhouseprefect.setText(add4);

                String add5=rs.getString("TOTALROOMS");
                jselectedhousetr.setText(add5);

                String add6=rs.getString("BEDSPERROOM");
                jselectedhousebpr.setText(add6);

                String add7=rs.getString("ROOMSOCCUPIED");
                jselectedhousero.setText(add7);

                String add8=rs.getString("ROOMSAVAILABLE");
                jselectedhousera.setText(add8);

                String add9=rs.getString("HM_ID");
                jselectedhmid.setText(add9);

                String add10=rs.getString("HM_NAME");
                jselectedhmname.setText(add10);

                String add11=rs.getString("HM_DEPARTMENT");
                jselectedhmdept.setText(add11);

                String add12=rs.getString("HOUSE");
                jselectedhmhouse.setText(add12);

                String add13=rs.getString("HM_PHONENUMBER");
                jselectedhmphone.setText(add13);

                String add14=rs.getString("HM_DATEIN");
                jselectedhmdatein.setText(add14);

                String add15=rs.getString("HM_DATEOUT");
                jselectedhmdateout.setText(add15);

                String add16=rs.getString("HM_RESIDENCE");
                jselectedhmresidence.setText(add16);

                String add17=rs.getString("HP_REGNO");
                jselectedhpregno.setText(add17);

                String add18=rs.getString("HP_NAME");
                jselectedhpname.setText(add18);

                String add19=rs.getString("HP_FORM");
                jselectedhpform.setText(add19);

                String add20=rs.getString("HOUSE");
                jselectedhphouse.setText(add20);

                String add21=rs.getString("HP_ROOMNO");
                jselectedhproom.setText(add21);

                String add22=rs.getString("HP_DATEIN");
                jselectedhpdatein.setText(add22);

                String add23=rs.getString("HP_DATEOUT");
                jselectedhpdateout.setText(add23);

                String add24=rs.getString("C_ID");
                jselectedcid.setText(add24);

                String add25=rs.getString("C_NAME");
                jselectedcnames.setText(add25);

                String add26=rs.getString("HOUSE");
                jselectedchouse.setText(add26);

                String add27=rs.getString("C_PHONENUMBER");
                jselectedcphone.setText(add27);

                String add28=rs.getString("C_DATEIN");
                jselectedcdatein.setText(add28);

                String add29=rs.getString("C_DATEOUT");
                jselectedcdateout.setText(add29);

                String add30=rs.getString("C_RESIDENCE");
                jselectedcresidence.setText(add30);


            }
    } catch (Exception e) 
    {
        System.out.println(e);
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, e);
    }
            finally
    {
        try {
            rs.close();
            pst.close();
        } catch (Exception e) {
        }
    }

    // TODO add your handling code here:
}

select a, b, (select c from d) as e from f is allowed, you are trying to do select a, b, (select c, d, e from f) as g from h which is not allowed. — luk2302
– luk2302, Commented Jul 11, 2016 at 19:26
Looks like what you are actually trying to do is a Join which has a different syntax, look it up. — luk2302
– luk2302, Commented Jul 11, 2016 at 19:28
Thank you....could you please correct for me, complete syntax — Rick
– Rick, Commented Jul 11, 2016 at 19:36
What is your desired result? Please explain the structure and relationships/join keys between Houses, Housemaster, Caretaker, and Houseprefect tables? — Parfait
– Parfait, Commented Jul 11, 2016 at 19:37
Im trying to join 4 tables HOUSES, HOUSEPREFECT,HOUSEMASTER,CARETAKER all containing same relationship HOUSE......... — Rick
– Rick, Commented Jul 11, 2016 at 19:38

nicholas79171 · Accepted Answer · 2016-07-11 19:50:18Z

1

You are trying to return too many rows in your SELECT statements. If you have a SELECT statement inside a SELECT statement, it can only return 1 row.

You need to join multiple tables together. I don't know the exact schema of your tables, but if I had to take a guess, I'd say this is what you're after:

SELECT H.HOUSE,H.TOTALROOMS,H.BEDSPERROOM,H.ROOMSOCCUPIED,H.ROOMSAVAILABLE,
       HM.ID,HM.NAME,HM.DEPARTMENT,HM.HOUSE,HM.PHONENUMBER,HM.DATEIN,HM.DATEOUT,HM.RESIDENCE,
       CA.ID,CA.NAME,CA.HOUSE,CA.PHONENUMBER,CA.DATEIN,CA.DATEOUT,CA.RESIDENCE,
       HP.REGNO,HP.NAME,HP.FORM,HP.HOUSE,HP.ROOMNO,HP.DATEIN,HP.DATEOUT 
FROM HOUSES H JOIN HOUSEMASTER HM  ON H.HOUSE = HM.ROWID 
              JOIN CARETAKER CA ON H.HOUSE = CA.ROWID 
              JOIN HOUSEPERFECT HP ON H.HOUSE = HP.ROWID;

Read this guide on SQL JOIN statements for more info.

edited Jul 11, 2016 at 19:50

answered Jul 11, 2016 at 19:40

nicholas79171

1,2832 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rick Over a year ago

Yes Im trying to display all those entries in a textfield.............Thanks,Lemme try that one and see....Ill get back to you

Rick Over a year ago

Now Im getting this message java.lang.ArrayIndexOutOfBoundsException: 0........ Done some corrections...This is the code "SELECT H.HOUSE,H.TOTALROOMS,H.BEDSPERROOM,H.ROOMSOCCUPIED,H.ROOMSAVAILABLE,HM.ID,HM.NAME,HM.DEPARTMENT,HM.HOUSE,HM.PHONENUMBER,HM.DATEIN,HM.DATEOUT,HM.RESIDENCE,CA.ID,CA.NAME,CA.HOUSE,CA.PHONENUMBER,CA.DATEIN,CA.DATEOUT,CA.RESIDENCE,HP.REGNO,HP.NAME,HP.FORM,HP.HOUSE,HP.ROOMNO,HP.DATEIN,HP.DATEOUT FROM HOUSES H JOIN Housemaster HM ON H.HOUSE = HM.HOUSE JOIN Caretaker CA ON H.HOUSE = CA.HOUSE JOIN Houseprefect HP ON H.HOUSE = HP.HOUSE";

nicholas79171 Over a year ago

Could you update your original post with more information such as the code that's calling this query?

nicholas79171 Over a year ago

Also, try executing this code in an SQL editor. Which editor depends on which type of DB you're using. That will let you know if your SQL is at fault or your Java code is.

luk2302 Over a year ago

Please note @nicholas79171 that you have no obligation to further fix OP's Code. You fixed the first issue, the One the question was about. The new issue can easily ne resolved by OP himself via just a little bit of debugging

|

Collectives™ on Stack Overflow

SQL ERROR java.lang.ArrayIndexOutOfBoundsException: 0

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related