How to make resilient EJB's which may throw System Exceptions like PSQLException

Question

I have an EJB running in GlassFish 4.1 which may have System Exceptions thrown in it like PSQLException now and then. In one particular case, duplicate key Exception is being thrown when trying to write a record to the db with an existing unique key value. I've tried catching all Exceptions as well as adding org.postgresql.util.PSQLException (and others) as an exception-class in ejb-jar.xml in application-exception, but the call always rolls back the transaction. I'd like my EJB to be resilient to these kinds of Exceptions. My application can recover from these issues if I can catch the Exceptions or even ignore them. How can I achieve this?

Here is an example. I have a Servlet which calls this EJB method when I hit it with a web browser:

public void initialize() {  
    try {  
        User user = new User(0, "John", "Doe");  
        em.persist(user);  
        em.flush();  
    } catch(Exception e) {  
        e.printStackTrace();  
    }

My ejb-jar.xml contains this:

<application-exception>
   <exception-class>org.postgresql.util.PSQLException</exception-class>
    <rollback>false</rollback>
    <inherited>true</inherited>
</application-exception>

The solution which works for me was provided by highstakes. "The solution is to simply mark 'initialize' as non-transactional and put this stuff into a separate method that starts a new transaction for each try."

Update: This solution doesn't work like I thought. I had an error in my application which made it seem like it works. After fixing the error, I'm back to the same issue. It seems that PSQLException is not catchable as it's a SystemException. I tried both container-managed and bean-managed methods. I do not agree that attempting to persist a record a second time should result in a SystemException. In a distributed environment, it's possible for this to happen. This should be a recoverable situation.

Update: I've solved my problem using the solution I posted below.

Answer 1

This is the solution which works for me an which I chose. Rather then injecting a PersistenceContext:

@PersistenceContext
private EntityManager em;

I'm injecting a DataSource Resource:

@Resource(lookup = "jdbc/sloocefw")
private DataSource dataSource;

This gives me the ability to create my own SQL statements which (and this is IMPORTANT) lets me catch SQLException on attempts to insert rows with duplicate keys.

public void initialize() {  
    try (Connection connection = dataSource.getConnection()) {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO user (id, firstName, lastName) VALUES (" + id + ", 'John', 'Doe')");
        ps.executeUpdate();
    } catch(SQLException e) {
        e.printStackTrace();
    }
}

My existing User entity bean manages the id (auto-increment). I need to change to either manage it manually (like in the example above) or use database auto-increment.

I would much rather use entity beans, but when I use entity beans attempting to insert a duplicate key causes an un-catchable SystemException.