Updating Join Views

[RogerV]

Hi

Finally got my view working in EclipseLink (Thanks) but now when I try to update one of the base tables through the view, EclipseLink throws java.sql.SQLException: Can not modify more than one base table through a join view. I only want to update one of the base tables via the view. The underlying database (MySQL) allows this through native SQL. Is the clue in the words "more than one table" or does EclipseLink prevent any update through views?

Which brings me to another newbie question. All the CRUD examples I've seen, work with single entities in their entirety (i.e all fields are retrieved, displayed (some may be hidden fields)  in the web-app and all are returned. In a web-app, how do you deal with the situation where you may be displaying/updating only a subset of the fields in an entity - so when you try to merge() later, when only a subset of the fields are populated because some haven't been returned by the webapp . I guess what I'm trying to ask, is how to implement UPDATE TABLE SET Field7 = "xxx", Field24 = "yyy" using EclipseLink

Hope that this makes sense

Regards

[Andreas Schmidt]

Hello Roger,

if you use faces, you can put a <t:saveState value="#{Bean or entity}"/> tag in you page.
This will restore all fields of the entity (or the entire bean).
If your framework does not support such a tag and the bean is in request mode, then you can update the fields as shown below:


        public void save() {


                ApplicationBean applicationBean = (ApplicationBean) getBean("applicationBean");
                EntityManager em = applicationBean.getEMF().createEntityManager();

                try {
                        em.getTransaction().begin();

                        Entity e = em.find(Entity.class, this.entity.getId());

                        e.setField1(this.entity.getField1());
                        e.setSprache(this.entity.getField2());

                        em.getTransaction().commit();

                       
                } catch (Exception e) {
                        this.error(e.toString());

                } finally {
                        em.close();
                }

        }

RogerV wrote:

Hi

Finally got my view working in EclipseLink (Thanks) but now when I try to update one of the base tables through the view, EclipseLink throws java.sql.SQLException: Can not modify more than one base table through a join view. I only want to update one of the base tables via the view. The underlying database (MySQL) allows this through native SQL. Is the clue in the words "more than one table" or does EclipseLink prevent any update through views?

Which brings me to another newbie question. All the CRUD examples I've seen, work with single entities in their entirety (i.e all fields are retrieved, displayed (some may be hidden fields)  in the web-app and all are returned. In a web-app, how do you deal with the situation where you may be displaying/updating only a subset of the fields in an entity - so when you try to merge() later, when only a subset of the fields are populated because some haven't been returned by the webapp . I guess what I'm trying to ask, is how to implement UPDATE TABLE SET Field7 = "xxx", Field24 = "yyy" using EclipseLink

Hope that this makes sense

Regards

[RogerV]

Andreas Schmidt wrote:

Hello Roger,

if you use faces, you can put a <t:saveState value="#{Bean or entity}"/> tag in you page.
This will restore all fields of the entity (or the entire bean).
If your framework does not support such a tag and the bean is in request mode, then you can update the fields as shown below:


        public void save() {


                ApplicationBean applicationBean = (ApplicationBean) getBean("applicationBean");
                EntityManager em = applicationBean.getEMF().createEntityManager();

                try {
                        em.getTransaction().begin();

                        Entity e = em.find(Entity.class, this.entity.getId());

                        e.setField1(this.entity.getField1());
                        e.setSprache(this.entity.getField2());

                        em.getTransaction().commit();

                       
                } catch (Exception e) {
                        this.error(e.toString());

                } finally {
                        em.close();
                }

        }

Hi Andreas

I've tried your approach which works fine, but has led me to a fresh problem. What I'm doing now is reading the data via EclipseLink using the view and displaying it to a web page. When the data is posted back I find the entity in the base table I want to update and update the fields necassary as you describe. Checking in the database shows that the data field and the version field (optimistic locking) have been correctly updated. However, when I re-load the data from the view I see the pre-updated data, EclipseLink is reading from a cache, not the database.

I've tried @Cache(disableHits = true) and @Cache(refreshOnlyIfNewer = true) on my view.java but neither seem to make any difference. What do I need to do to force EclipseLink to reload the view data from the database.

Regards

[Andreas Schmidt]

RogerV wrote:

Andreas Schmidt wrote:

Hello Roger,

if you use faces, you can put a <t:saveState value="#{Bean or entity}"/> tag in you page.
This will restore all fields of the entity (or the entire bean).
If your framework does not support such a tag and the bean is in request mode, then you can update the fields as shown below:


        public void save() {


                ApplicationBean applicationBean = (ApplicationBean) getBean("applicationBean");
                EntityManager em = applicationBean.getEMF().createEntityManager();

                try {
                        em.getTransaction().begin();

                        Entity e = em.find(Entity.class, this.entity.getId());

                        e.setField1(this.entity.getField1());
                        e.setSprache(this.entity.getField2());

                        em.getTransaction().commit();

                       
                } catch (Exception e) {
                        this.error(e.toString());

                } finally {
                        em.close();
                }

        }

Hi Andreas

I've tried your approach which works fine, but has led me to a fresh problem. What I'm doing now is reading the data via EclipseLink using the view and displaying it to a web page. When the data is posted back I find the entity in the base table I want to update and update the fields necassary as you describe. Checking in the database shows that the data field and the version field (optimistic locking) have been correctly updated. However, when I re-load the data from the view I see the pre-updated data, EclipseLink is reading from a cache, not the database.

I've tried @Cache(disableHits = true) and @Cache(refreshOnlyIfNewer = true) on my view.java but neither seem to make any difference. What do I need to do to force EclipseLink to reload the view data from the database.

Regards

Hello Roger,  
as you said, the view will be cached and not updated with the fresh values from the database.
I read somewhere, that you have to "invalidate" the cache of the view (can' remeber how this can be achieved).

I my case I use the following annotation in the "view entity", since my resultset is small and performance is not an issue:

@Cache(expiry = 1000 * 60)
//force cache refresh every minute (View !)

You can increase this values if required or disbale caching for this entity in your persistance.xml