Please have a look the following code
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;
}
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.
This program works fine with MSSQL Server. When the SQL server throws Violation of PRIMARY KEY exception, program starts updating the currect data.
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
Following is the SQL table
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.
