handling database constraint hibernate

Question

My project is using hibernate with spring transaction manager and my database is postgres (might be irrelevant).

I'm trying to read big xml files and construct objects out of those (objects are not big but the amount is) and insert them into database.

If by some chance one of my objects violates database constraint the whole process stops. How can I skip the ones which violate the database constraint? alternatively log their id or whatever to a log file?

Question update:

I've been browsing trough SO and found that for batch inserts it's best recommended to use Stateless session but I still get the same issue and insert stops :

May 26, 2012 4:45:47 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: duplicate key value violates unique constraint "UN_FK"
  Detail: Key (fid)=(H1) already exists.

Here are the relevant parts of my code for parsing xml and inserting into db, for simplicity let's assume I'm inserting movies :

//class field
@Autowired
private SessionFactory sessionFactory;

@Override
public void startDocument() throws SAXException {
    session = sessionFactory.getCurrentSession();
}

@Override
public void endElement(String uri, String localName, String qName) throws SAXException  {
if (qName.equalsIgnoreCase("FILM")) {
        movie.setCategory(category);
        movie.setAdded(new Date());
        session.insert(movie);
    }
}

I and have this property set in the app-ctx hibernate.jdbc.batch_size to 100. Is it really necessary to do select before insert in order to avoid this?

Update 2:

If I use StatelessSession instead of session, I get arround 20 inserts and than the processing stops indefinitely without any exception or anything.

I assume the number 20 is because I'm pooling connections with tomcat and have maxActive="20".

Bounty Update :

I'd really love to see someone offer solution (without defensive select if possible). Using statelessSession or just session.

Answer 1

Most types of constraints, such as if a column is nullable or has a max width, you can check using Hibernate Validator. Just manually execute the validation on the object before attempting to persist it.

For some things, particularly unique constraints, you need to either execute a 'defensive' select to see if a collision exists, or maintain an in memory set of values already inserted.

Answer 2

I think it is impossible to validate something so completely that you can guarantee successful insert. In some cases, no matter what you do, someone else can insert something into the DB between validation and insert causing a constraint violation.

In most cases I would just recommend to handle the exception like any other.

Answer 3

To insert a large amount of objects from an xml file, you should consider using spring batch. The parameter skip-limit permits you to tell how many erroneous lines you can have in your xml before stopping the batch process. Check also skip-policy and skippable-exception; seee Configuring a Step in the Spring documentation.

If you don't want to use spring batch, just use a simple try catch that will permit your process to continue until the end.

Answer 4

Why do you think this all has to be one big transaction? Everything you describe in fact implies to me that you in fact have numerous transactions here. For objects that "error out", just evict that entity and rollback the transaction. It gets a little more complex if the "FILM" element defines a graph of objects, but the idea is the same.