不支持左操作数类型为“VARCHAR”和右操作数类型为“VARCHAR”的“”运算符

Question

请看下面的代码。我使用Apache Derby作为嵌入式数据库

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;
    }

此代码出现以下错误

java.sql.SQLSyntaxErrorException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at normal.DatabaseHandler.getDetails(DatabaseHandler.java:150)
    at normal.UpdateDeleteForm$DetailsLoader.itemStateChanged(UpdateDeleteForm.java:417)
    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)
Caused by: java.sql.SQLException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 56 more
Caused by: ERROR 42Y95: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.BaseTypeCompiler.resolveArithmeticOperation(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
    at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 50 more
Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 6, 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:435)
    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)
BUILD SUCCESSFUL (total time: 7 seconds)

在这里，我试图做的是，获取全名，它作为FirstName、MiddleName和LastName存储在数据库中。

示例：名字-彼得，中间名-梅塔，姓氏-约翰逊

然后我试着得到“彼得·梅塔·约翰逊”。这就是为什么我使用运算符将值合并到3列中。

为什么我得到这个错误？这在MSSQL Server中有效，但在Derby中失败。请帮助！

Answer 1

根据德比文件（http://db.apache.org/derby/docs/10.4/ref/rrefsqlj40899.html），Derby中的连接运算符类似于Oracle，因此将“”s替换为“||”。

Answer 2

在Derby中，字符串连接运算符不是but||

http://db.apache.org/derby/docs/10.2/ref/rrefsqlj40899.html