RE: Duplicate Key in Db

[Akins, Greg]

You can do that now.  Maybe I'm missing something?

The Ibatis insert() statement throws a SQLException.. When you perform
your insert, catch the SQLException make sure it's a unique constraint
violation and move on...

-----Original Message-----
From: Tim Christopher [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 23, 2005 1:54 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Duplicate Key in Db


So what happens if you have a SQL statement that inserts data into
serveral tables.  Every change to the database would require you to
reevaluate what checks (select statements) need to be done to ensure a
situtation where it is possible for duplicate primary keys to occur in
each table.  I do not consider this to be a maintainable solution, even
though iBATIS does seem to be the best database framework I've seen. :o)

Would it not be possible to override one of the iBATIS files introducing
a try / catch loop to catch the database error at runtime and set it to
achieve the following behaviour:

  #### ModuleService #### OLD
  public void insertModule (Module module)
  {
    moduleDAO.insertModule(module);
  }


  #### ModuleDispatchAction #### OLD
  ModuleService moduleService = ModuleService.getInstance();
  DynaActionForm moduleMaintenanceForm = (DynaActionForm) form;
  Module module = new Module();
  BeanUtils.copyProperties(module, moduleMaintenanceForm);
  moduleService.insertModule(module);
  ...

-----------------------------------------

  #### ModuleService #### NEW
  public boolean insertModule (Module module)
  {
    return (moduleDAO.insertModule(module) != null);
  }


  #### ModuleDispatchAction #### NEW
  ModuleService moduleService = ModuleService.getInstance();
  DynaActionForm moduleMaintenanceForm = (DynaActionForm) form;
  Module module = new Module();
  BeanUtils.copyProperties(module, moduleMaintenanceForm);
  if (moduleService.insertModule(module)) {
      // Update went ok
  }
  else {
      // Update failed for unknown reason
    ActionMessages msgs = new ActionMessages();
    ActionMessage message = new ActionMessage("insert.fail");
    msgs.add(ActionMessages.GLOBAL_MESSAGE, message);
    saveMessages(request, msgs);
  }

  #### App.properties #### NEW
  insert.fail=The update failed failed.  This may be because the result
would have caused a duplicate field where a unique value is required.
Please check your data, and report this to the site admin.

Ok, so that's not perfect but would save running select queries on the
db before inserting data.  It would also mean that you wouldn't have to
look through what checks are made when the db is modified...  I'm sure I
must be missing something though as the above approach seems obvious to
me :oS

Tim Christopher