How to Sync Entity Bean with Database After Trigger Update

Question

PostgreSQL 9.1
Glassfish 3.1.2.2
Firefox 10.0.0.7
Linux

I am using JPA to persist entities in PostgreSQL. However, there is one table (named Position) which is populated by triggers and I use an entity of that table which acts as a read only. The entity never persist data into this table as it's loaded and manipulated from the triggers. I can load the data into my managed bean and view the Position table data fine.

The problem I have is that once the database (Position table) has been modified by the triggers, when the Position table is queried again, the values are still the same. The Position entity still contains the old values and have not reloaded.

Here is the bean which handles the Position entity. I added em.flush() which didn't help and wasn't sure how to use em.refresh() in this way. And actually, how would syncing help anyways since it doesn't know what I want to sync to without a query.

Any help much appreciated.

The EJB ...

@Stateless
public class PositionBean implements IPosition {
    @PersistenceContext(unitName="positionbean-pu")
    private EntityManager em;

    public Position getPositionById(Integer posId) {
        Position pos = null;

        try {
            pos = (Position) em.createNamedQuery("findPosition")
                .setParameter("posId", posId).getSingleResult();
        }
        catch (Exception e) {
            throw new EJBException(e.getMessage());
        }

        return pos;
   }

The entity bean ...

@Entity
@SequenceGenerator(name="posIdGen", initialValue=10000,
    sequenceName="pos_seq", allocationSize=1)
@NamedQuery(name="findPosition",
    query="SELECT p FROM Position p WHERE p.posId = :posId")
@Table(name="Position")
public class Position implements Serializable {
    // ...

persistence.xml

<persistence-unit name="positionbean-pu" transaction-type="JTA">
    <jta-data-source>jdbc/postgreSQLPool</jta-data-source>
</persistence-unit>

Primp

Answer 1

In case anyone runs into this, I learned how to use refresh() and this fixed my problem.

        pos = (Position) em.createNamedQuery("findPosition")
            .setParameter("posId", posId).getSingleResult();

        em.refresh(pos);

Primp

I assume you are using Resource-local transaction type. You should not need to call refresh() if you are using JTA transaction type. Within JTA transactions the container takes care of refreshing the persistence context. In a container managed JTA transaction refresh() would be used to discard your current transaction updates and overwrite the persistence context with a fresh copy of the DB, which is a completely different usage. – Chalaza

That confuses me the because I am using JTA and had to add the refresh to get it working. I added a snippet of my persistence.xml to the OP to show what it looks like. – Primp

The refresh method discards the effect of the persist/merge/remove operations of your current non-finished transaction and resorts to the DB latest state. In other words, refresh() copies the DB afresh onto the EntityManager persistence context and discards any updates done within your current transaction till the point of the call. This scenario comes to play within a transaction which takes a long time and you want to give priority to the latest state of the DB which gets updated by other applications than yours. – Chalaza

Can you post the whole method which you needed refresh() for? – Chalaza

Other than being within a try/catch and returning the position, that literally is the entire method. There is no persisting/merging or removing here as stated in the OP, this table and its entries are added by a database trigger function and the only thing I do is read this table w/ that named query. – Primp

Answer 2

In my opinion the correct way to create a read only entity that is updated "externally" is as follows:

1. Do use field based annotation for the entity class by setting annotation @Access to AccessType.FIELD (or remove the annotation because it is the default access type) as stated out in this answer. Also propose public getters only as stated out in this answer. This prevents the entity from beeing modified within the application.
2. Enable selective shared cache mode in persistence.xml file and add annotation @Cacheable(false) to the entity class as stated out in this answer. This forces JPA to always load the entity from the persistence layer when you call EntityManager#find(...).

Bethsaida