无法更新pimary键所在的表 [英] Unable to update the table where pimary key is located
本文介绍了无法更新pimary键所在的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
请看下面的代码
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)，密码和用户名.这样一来，只能将一个电子邮件帐户配置为一种类型的电子邮件帐户(例如:只能存在一个"yahoo"电子邮件帐户，只能存在一个"gmail"帐户)
This is an email configuration program. This method is used to configure email accounts. Users enter email account type (yahoo.gmail), password and user name. This allows only one email account to be configured, to one type of email account (ex: Only one 'yahoo' email account can exists, only one 'gmail' account can exists)
在这里，如果数据已经存在(这意味着特定帐户类型已经存在一个帐户)，程序将对其进行更新，而不是直接插入数据.如果没有数据，程序将输入数据.
Here, if the data is already there(which means, the there is already an account for a particular account type), the program will update it rather than directly inserting data. If no data, program will enter data.
此程序可与MSSQL Server正常运行.当SQL Server抛出Violation of PRIMARY KEY异常时，程序开始更新当前数据.
This program works fine with MSSQL Server. When the SQL server throws Violation of PRIMARY KEY exception, program starts updating the currect data.
但是，这不适用于Derby.我正在使用Derby嵌入式版本.我a，当已经有数据并且没有更新任何数据时，出现以下错误.
However, this is not working with Derby. I am using Derby embedded version. I a, getting the following error, when there is already data, and it is not updating any.
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)
)
当数据已经存在时，请帮助我更新数据.
Please help me to update the data when the data are already there.
推荐答案
您正在使用此语法搜索字符串
You are searching for a string using this syntax
if(sql.getMessage().contains("Violation of PRIMARY KEY") || sql.getMessage().contains("SQLIntegrityConstraintViolationException"))
而derby返回此字符串
while derby returns this string
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语句无法执行的原因
Thats why your if statement does not execute
这篇关于无法更新pimary键所在的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
