Announcement Announcement Module
Collapse
No announcement yet.
Generating user friendly error messages / constraint violati Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generating user friendly error messages / constraint violati

    I have 2 unique constraints on a table, as well as not null and foreign key constraints. When I get a sql exception:

    org.springframework.dao.DataIntegrityViolationExce ption: (HibernateTemplate): data integrity violated by SQL ''; nested exception is java.sql.BatchUpdateException: ORA-00001: unique constraint (SRT2.SYS_C0039349) violated

    Spring is nice enough to wrap it into a DataIntegrityViolationException, vs some of the other things a SqlException could represent, but itís nasty to have to parse the cause string, and SYS_C0039349 doesnít do much for me. In the DDL, the foreign key constraints are the only ones we seem to have control over naming.

    Is there a slick way to map all of my possible constraint violations, so the spring exception will give me an error code I can lookup?

    It's possible that an insert/update could violate several constraints, although it looks like the db will throw the exception on the first violation found, so it may not be possible to report all of the errors the user has made.

  • #2
    You can name your constraints to give a more meaningful indication as to what went wrong. In general, I think that your data validation should check for anything that is enforced via a database constraint. The database constraint should only be there as a safeguard. IMO any constraint violations should be considered to be programming errors.

    Here is an example of how to name the constraint in Oracle:
    Code:
    ALTER TABLE ORDERS ADD (
      CONSTRAINT FK_ORDERS_TO_CUSTOMER FOREIGN KEY (CUSTOMER_ID) 
        REFERENCES CUSTOMER (ID));
    That would give me this error message:
    Code:
    ORA-02291: integrity constraint (TRISBERG.FK_ORDERS_TO_CUSTOMER) violated - parent key not found

    Comment


    • #3
      I have good names for my foreign key constraints, but not for unique constraints.

      Code:
      select * from user_cons_columns;
      Is a useful query, that could be used to generate a mapping. It gives constraint name, table name, column name and a position (that I am not familiar with).

      Doing data validation in the app to check for uniqueness, forces a few extra queries, and is not failsafe as the state of the database can change between doing the query to check for uniqueness and executing the insert/update.

      I wouldn't want to do these checks in my DAO, and I certainally wouldn't do table level locking, so while I can make the constraint violation error rare, I cannot eliminate it.

      Comment


      • #4
        Originally posted by trisberg
        In general, I think that your data validation should check for anything that is enforced via a database constraint. The database constraint should only be there as a safeguard. IMO any constraint violations should be considered to be programming errors.
        The question is, where?
        Taking the case of unique constraints on a name. You'd like to place this in the domain layer, but that requires a reference from domain to DAO, which requires wiring your whole domain using Spring - discussed http://forum.springframework.org/showthread.php?t=9846.

        I settled on placing the call in the service layer, just prior to actually adding/modifying, as such.
        Service layer code:

        Code:
            public void addDictionary(Client client, Dictionary dico) 
            	throws MyException
            {
                checkDictionaryName(client, dico);
                client.addDictionary(dico);
             }
        
            private void checkDictionaryName(Client client, Dictionary dico) throws MyException {
                 if (dictionaryDao.checkIfNameTaken(client, dico.getName(), dico.getId()))
        		    throw new MyException ("Name taken, try another.");
            }
        DAO code:
        Code:
        	public boolean checkIfNameTaken(Client client, String name, Long idDico)  {
        	    Iterator dicos = null;
        	    if (idDico==null) {
        	        dicos = getHibernateTemplate().iterate("FROM DictionaryImpl dico WHERE dico.client=? AND dico.name= ?",
        			        new Object[] {client.getId(), name},
        			        new Type[] {Hibernate.LONG, Hibernate.STRING});
        	    } else {
        	        dicos = getHibernateTemplate().iterate("FROM DictionaryImpl dico WHERE dico.client=? AND dico.name= ? AND dico != ?",
        			        new Object[] {client.getId(), name, idDico},
        			        new Type[] {Hibernate.LONG, Hibernate.STRING, Hibernate.LONG});
        	    }
        	    return dicos.hasNext();
        	}
        What think ye? Is this a reasonable approach?
        Best regards,
        Assaf
        Last edited by robyn; May 14th, 2006, 11:00 AM.

        Comment


        • #5
          Originally posted by tedberg
          I have good names for my foreign key constraints, but not for unique constraints.
          You can, of course, name those constraints as well.

          I'm at the tail end of porting an EJB application (both session and CMP entity beans) to Spring and Hibernate and am about to tackle this piece. In the previous version, the session beans parsed the exception message for the constraint name and mapped it to field code constants. The code was short but specific to each bean -- not generic code in the superclasses.

          However, as 95% of the DAO code is in my generic framework -- subclassed only to add specialized queries -- I'd like the constraint mapping to be generic, too. My initial idea is to have the subclasses create a map from constraint name to field codes for the framework DAO to put into the exception thrown to the domain layer.

          I really dislike the idea of performing a query from the domain layer before attempting the insert/update for the same reasons already mentioned.
          • It's not a 100% solution, so you have to handle the constraint violation anyway.
          • It results in an extra query for every operation.
          • Worse, the extra query in 999 out of 1,000 cases isn't needed (no duplicate), so it's wasteful.
          I'll gladly post up what I end up with if anyone's interested. It will be pretty quick-and-dirty, though.

          Comment


          • #6
            David, I'd be interested to see what you came up with. This type of problem could really use a solid design pattern.

            Comment


            • #7
              You might also want to think about the "try don't catch" principle (I think that is what it is called).

              Essentially you do all your validation before the operation, so the operation will never fail. In this case it would involve checking the DB for unique constraints *before* the insert.

              Of course; it still leaves a small window open for someone else to sneak in (transactions permitting), and may be too expensive to perform, but it works very well for a lot of problems.

              Comment

              Working...
              X