java - "IndexOutOfBoundsException"因为空值？

Question

请看下面的代码

DatabaseHandler.java

public List getDetails(String name)
    {
        List details = new ArrayList();

        name = name.replaceAll("\\s+", "").toUpperCase();
        try
        {
            createConnection();

            PreparedStatement ps = con.prepareStatement("select * from PhoneData where upper(rtrim(NULLIF(names,''))) || upper(rtrim(NULLIF(middleName,''))) ||  upper(rtrim(NULLIF(lastName,'')))=?");
            ps.setString(1, name);

            ResultSet rs = ps.executeQuery();

          // System.out.println("First Mobile Number: "+rs.getInt(3));
            while(rs.next())
            {
                details.add(rs.getInt(1));
                details.add(rs.getString(2));
                details.add(rs.getString(3));
                details.add(rs.getString(4));
                details.add(rs.getString(5));
                details.add(rs.getString(6));
                details.add(rs.getString(7));
                details.add(rs.getString(8));
                details.add(rs.getString(9));
                details.add(rs.getString(10));
                details.add(rs.getString(11));
                details.add(rs.getString(12));
                details.add(rs.getString(13));
                details.add(rs.getString(14));
            }

            for(int i=0;i<details.size();i++)
            {
                System.out.println(details.get(i));
            }
            System.out.println("------------Database handler Done------------");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            closeConnection();
        }

        return details;
    }

上面的代码由下面的代码通过接口(interface)调用

UpdateDeleteForm.java

private class DetailsLoader implements ItemListener
    {
        public void itemStateChanged(ItemEvent ie)
        {
            if(ie.getStateChange()==ItemEvent.SELECTED)
            {
                List details = new ArrayList();


                if(nameTxtCombo.getSelectedItem().toString() != "Select a Name")
                {
                details = dateBaseConnector.getDetails(nameTxtCombo.getSelectedItem().toString());

                idTxt.setText("");
                firstNameTxt.setText("");
                lastNameTxt.setText("");
                mobileNumber1Txt.setText("");
                mobileNumber2Txt.setText("");
                landNumber1Txt.setText("");
                landNumber2Txt.setText("");
                streetAddressTxt.setText("");
                cityTxt.setText("");
                countryTxt.setText("");
                middleNameTxt.setText("");
                emailTxt.setText("");
                nickNameTxt.setText("");






                String mobileNumber1 = (String)details.get(2)+" ";
                String mobileNumber2 = (String)details.get(3)+" ";
                String landNumber1 = (String)details.get(4)+" ";
                String landNumber2 = (String)details.get(5)+" ";
                String address = (String)details.get(6)+" ";

                idTxt.setText(String.valueOf(details.get(0)));
                firstNameTxt.setText((String)details.get(1));                    
                mobileNumber1Txt.setText(mobileNumber1.trim());
                mobileNumber2Txt.setText(mobileNumber2.trim());
                landNumber1Txt.setText(landNumber1.trim());
                landNumber2Txt.setText(landNumber2.trim());
                streetAddressTxt.setText(address.trim());
                //categoryCombo.setSelectedItem((String)details.get(7));
                nickNameTxt.setText((String)details.get(8));
                emailTxt.setText((String)details.get(9));
                middleNameTxt.setText((String)details.get(10));
                lastNameTxt.setText((String)details.get(11));
                cityTxt.setText((String)details.get(12));
                countryTxt.setText((String)details.get(13));

                System.out.println((String)details.get(2));
                System.out.println((String)details.get(3));
                System.out.println((String)details.get(4));
                System.out.println((String)details.get(5));
                System.out.println(address.trim());
                System.out.println((String)details.get(7));
                System.out.println((String)details.get(8));
                System.out.println((String)details.get(9));
                System.out.println();
                System.out.println();
                System.out.println("---------------New Line---------------------");
                }

            }
        }
    }

当我使用 MS SQLServer 时，此代码可以 100% 正常工作。但是，现在我将其转移到 Apache Derby - 嵌入式版本。

如果 getDetails() 方法返回的任何项目为 null，我将收到以下错误

Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 2, Size: 0
    at java.util.ArrayList.rangeCheck(ArrayList.java:604)
    at java.util.ArrayList.get(ArrayList.java:382)
    at normal.UpdateDeleteForm$DetailsLoader.itemStateChanged(UpdateDeleteForm.java:438)
    at javax.swing.JComboBox.fireItemStateChanged(JComboBox.java:1225)
    at javax.swing.JComboBox.selectedItemChanged(JComboBox.java:1282)
    at javax.swing.JComboBox.contentsChanged(JComboBox.java:1329)
    at javax.swing.AbstractListModel.fireContentsChanged(AbstractListModel.java:118)
    at javax.swing.DefaultComboBoxModel.setSelectedItem(DefaultComboBoxModel.java:94)
    at javax.swing.JComboBox.setSelectedItem(JComboBox.java:578)
    at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:624)
    at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.java:835)
    at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(BasicComboPopup.java:499)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

如果 non 为 null，则完全没有问题。我记得我在 MS SQL Server 中遇到了同样的问题，我设法用“isnull”解决了这个问题。我在这里做了同样的事情，但似乎 apache 版本的 isnull (nullif) 不起作用，因为如果它至少返回一个空格，我就没有这个问题!请帮忙!!

Answer 1

<强> COALESCE 在你的PreparedStatement 中有更多的用途

PreparedStatement ps = con.prepareStatement("
                                select * 
                                from PhoneData 
                                where upper(rtrim(COALESCE(names,''))) || 
                                      upper(rtrim(COALESCE(middleName,''))) ||  
                                      upper(rtrim(COALESCE(lastName,''))) = ?");