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, Jul 11 '16 at 19:26
Looks like what you are actually trying to do is a `Join` which has a different syntax, look it up. — luk2302, Jul 11 '16 at 19:28
Thank you....could you please correct for me, complete syntax — Rick, Jul 11 '16 at 19:36
What is your desired result? Please explain the structure and relationships/join keys between Houses, Housemaster, Caretaker, and Houseprefect tables? — Parfait, Jul 11 '16 at 19:37
Im trying to join 4 tables HOUSES, HOUSEPREFECT,HOUSEMASTER,CARETAKER all containing same relationship HOUSE......... — Rick, Jul 11 '16 at 19:38
How is this valid Java? Java does not support [multiline string](http://stackoverflow.com/questions/878573/java-multiline-string). — Parfait, Jul 11 '16 at 23:19
**Don't** change your entire question just because your code still does not work after fixing your sql. — luk2302, Jul 12 '16 at 05:18

nicholas79171 · Answer 1 · 2016-07-11T19:50:18.017

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 '16 at 19:50

answered Jul 11 '16 at 19:40

nicholas79171

1,203
2
15
28

Yes Im trying to display all those entries in a textfield.............Thanks,Lemme try that one and see....Ill get back to you – Rick Jul 11 '16 at 19:49
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"; – Rick Jul 11 '16 at 20:08
Could you update your original post with more information such as the code that's calling this query? – nicholas79171 Jul 11 '16 at 20:10
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. – nicholas79171 Jul 11 '16 at 20:11
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 – luk2302 Jul 12 '16 at 05:20
@RobertKibet as luk2302 said, since this problem is solved, please accept my answer (if it solved the issue you were first having) and try some simple debugging yourself. If you still cannot figure it out, please post a new question. – nicholas79171 Jul 12 '16 at 11:16
Oh okay, thanks though @nicholas79171 it fixed the sql issue, but now Im realizing that java doesnt support multiline string – Rick Jul 12 '16 at 18:09

SQL ERROR java.lang.ArrayIndexOutOfBoundsException: 0

1 Answers1