java - 无法更新主键所在的表

Question

请看下面的代码

public String setEmailAccount(String account,String userName, String password) 
    {
        createConnection();
        String result = "";

        try
        {
            //This part will set the new account
            con.setAutoCommit(false);
            PreparedStatement ps = con.prepareStatement("insert into Emails values (?,?,?)");
            ps.setString(1,account);
            ps.setString(2, userName);
            ps.setString(3, password);

            int resultInt = ps.executeUpdate();
            con.commit();
            if(resultInt>0)
            {
                result = "Account Created Successfully";
            }
            else
            {
                result = "Account creation falied. Error unknown";
            }
        }
        catch(SQLException sql)
        {
            result = sql.getMessage();
            sql.printStackTrace();

            //If the account exists, then this part will update the current account details
            if(sql.getMessage().contains("Violation of PRIMARY KEY") || sql.getMessage().contains("SQLIntegrityConstraintViolationException"))
            {
                try
                {
                    con.setAutoCommit(false);
                    PreparedStatement ps = con.prepareStatement("update Emails set userName=?,passwords=? where accountType=?");
                    ps.setString(1,userName);
                    ps.setString(2, password);
                    ps.setString(3, account);

                    int resultInt = ps.executeUpdate();
                    con.commit();

                    if(resultInt>0)
                    {
                        result = "Your "+account+" details has been updated succesfully";
                    }
                    else
                    {
                        result = "Error updating your "+account+" details. Rollback has not happened";
                    }
                }
                catch(SQLException updateSqlException)
                {
                    updateSqlException.printStackTrace();
                    result = "Error updating your " +account+ " details. Everything rollbacked succesfully";

                    try
                    {
                        con.rollback();
                    }
                    catch(Exception updateRollbakException)
                    {
                        updateRollbakException.printStackTrace();
                        result = "Error in updating your " + account + " details. Rollbak failed.";
                    }
                }
                catch(Exception updateE)
                {
                    result = "Error Occured. But your "+account+" data has been updated successfully";
                    updateE.printStackTrace();                    
                }
            }
            else
            {
                try
                {
                    con.rollback();
                }
                catch(Exception ee)
                 {
                        result = "Data insertion failed. RollBack failed. Error is below\n"+ee.getMessage();
                 }
            }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            closeConnection();
        }

        return result;
    }

这是一个电子邮件配置程序。此方法用于配置电子邮件帐户。用户输入电子邮件帐户类型(yahoo.gmail)、密码和用户名。这仅允许将一个电子邮件帐户配置为一种类型的电子邮件帐户(例如:只能存在一个“雅虎”电子邮件帐户，只能存在一个“gmail”帐户)

在这里，如果数据已经存在(这意味着已经有一个特定帐户类型的帐户)，程序将更新它而不是直接插入数据。如果没有数据，程序将输入数据。

该程序与 MSSQL Server 配合良好。当 SQL 服务器抛出 Violation of PRIMARY KEY异常，程序开始更新当前数据。

但是，这不适用于 Derby。我正在使用 Derby 嵌入式版本。我a，当已经有数据并且没有更新任何数据时，出现以下错误。

java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_USER' defined on 'EMAILS'.
    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.EmbedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
    at email.EmailDBHandler.setEmailAccount(EmailDBHandler.java:59)
    at email.ConfigureEmail$OKButton.actionPerformed(ConfigureEmail.java:62)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    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 statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_USER' defined on 'EMAILS'.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 47 more
Caused by: ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_USER' defined on 'EMAILS'.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown Source)
    at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(Unknown Source)
    at org.apache.derby.impl.sql.execute.IndexChanger.insert(Unknown Source)
    at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(Unknown Source)
    at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown Source)
    at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown Source)
    at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
    at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
    ... 41 more

以下是SQL表

create table Emails
(
accountType varchar(10) constraint pk_user primary key,
userName varchar(50) ,
passwords varchar(50)
)

当数据已经存在时，请帮助我更新数据。

Answer 1

您正在使用此语法搜索字符串

if(sql.getMessage().contains("Violation of PRIMARY KEY") || sql.getMessage().contains("SQLIntegrityConstraintViolationException"))

而 derby 返回此字符串

The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'PK_USER' defined on 'EMAILS'.

这就是为什么你的 if 语句不执行