How to define entity that only exists sometimes

I have a table of customers and another table of comments. The comments links to the customer table by the customer ID (the primary key of the comments record is the same primary key as the customer record). We only create a comments record for customers that need them.

It’s an association of one to one, but the comments side could be missing. And, there is no unique UUID or sequential ID set up for the comments table – it IS the customer ID.

How would I define this?

That’s interesting. It will create a new ID even with @MapsId annotation? I thought @MapsId means that the ID is copied from another Entity?

And I found what was wrong with showing no customers. Nothing in the app! It turns out that Cuba Platform had created a new customers table in the schema I was using, which overrode the link to the real table. So, the customers table truly was empty. :-/ Sometimes the database update scripts seem to get in the way. I will have to watch them more closely. :slight_smile:

So, now that the application executes and comments are displayed, I can’t update them. I defined a customer view that contains the comments field. I based the customer edit screen on that view.

I think the problem may be that the customer edit screen has to save changes to two different entities? When an edit is made to the comments, how do I get the platform to update the database appropriately?

Would I be better off removing the comments from the custom view and creating a nested data source?

You should be able to do it by inheriting your Comments entity directly from BaseGenericIdEntity - then you have full control over the type and values of the primary key. Use BaseIntegerIdEntity as an example (but do not inherit from it to avoid automatic assigning of IDs).

If you have any troubles with this approach, send us a test project with a minimal data model and we’ll try to help.

I created a class:


package com.paslists.prm.entity;

import javax.persistence.*;

import com.haulmont.cuba.core.entity.BaseLongIdEntity;
import com.haulmont.chile.core.annotations.NamePattern;

@NamePattern("%s|comments")
@AttributeOverrides({
        @AttributeOverride(name="id", column=@Column(name="ID"))
})

@Table(name = "GWCOMMENTS")
@Entity(name = "prm$GWComment")
public class GWComment extends BaseLongIdEntity  {
    private static final long serialVersionUID = 1130005077378204782L;

    @Column(name = "COMMENTS", length = 256)
    protected String comments;

    @MapsId
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "comments")
    @JoinColumn(name="id")
    protected Gateway customer;

    public void setCustomer(Gateway customer) {
        this.customer = customer;
    }

    public Gateway getCustomer() {
        return customer;
    }


    public void setComments(String comments) {
        this.comments = comments;
    }

    public String getComments() {
        return comments;
    }

}

I then went into my customer record and set up a one-to-one mapping with the comments record, like this:


    @OneToOne(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
    @PrimaryKeyJoinColumn
    protected GWComment comments;

This seems to get past the build, but CUBA still wants to update the database with a script to create a foreign key. How do I stop that from happening?

And, strangely, the SQL fails - but when I extract if from the app.log and run it by hand the SQL works fine. The only edits I make are to fill in the “?” parameters. The link to comments actually looks correct, too. :-/


Query: ReadAllQuery(referenceClass=Gateway sql="SELECT * FROM (SELECT a.*, ROWNUM rnum  FROM
 (SELECT t1.ID AS a1, t1.BATCHNUM AS a2, t1.BIRTHDAY AS a3, t1.CITY AS a4, t1.COMPANY AS a5, t1.CPURCH AS a6, 
t1.DEL AS a7, t1.EMAIL AS a8, t1.FIRST AS a9, t1.FPURCH AS a10, t1.GNDEL AS a11, t1.HADEL AS a12, t1.HDEL 
AS a13, t1.HOTLINE AS a14, t1.JOBTITLE AS a15, t1.LAST AS a16, t1.LPURCH AS a17, t1.MI AS a18,
 t1.NAME AS a19, t1.NCOADATE AS a20, t1.NHPDEL AS a21, t1.NHZDEL AS a22, t1.NOBDEL AS a23, 
t1.NUMPURCH AS a24, t1.ORIGDATE AS a25, t1.PERMDEL AS a26, t1.PHONE AS a27, t1.PRIMADDR AS a28, 
t1.RTYPE AS a29, t1.SECADDR AS a30, t1.SEXCODE AS a31, t1.STATE AS a32, t1.ZIP AS a33, t1.ZIP4 AS a34, 
t0.ID AS a35, t0.COMMENTS AS a36 
FROM GATEWAY t1 LEFT OUTER JOIN GWCOMMENTS t0 ON (t0.ID = t1.ID), gwprods t3, gwtrans t2
WHERE (((t3.CORPCODE = ?) OR (? = ?)) AND ((t2.GATEWAY_ID = t1.ID) AND (t3.PROD_ID = t2.GWPRODS_PROD_ID))) ORDER BY t1.ZIP, t1.LAST) a 
WHERE ROWNUM <= ?) 
WHERE rnum > ?")
FetchGroup(){birthday, city, hotline, batchnum, del, zip4, ncoadate, rtype, hdel, hadel, nobdel, permdel, company, gndel, state, id, m
i, sexcode, email, origdate, zip, comments => {class java.lang.Object=FetchGroup(comments){comments, id}}, fpurch, jobtitle, last, sec
addr, lpurch, nhzdel, transactions => {class java.lang.Object=FetchGroup(transactions){amount, quantity, gwprodsProdId => {class java.
lang.Object=FetchGroup(gwprodsProdId){prodCode, active, id, prodDesc, corpcode}}, transnum, batchnum, purchDate, credCard, source, prm
ordnum, refdate, refamt, paymethod, retcheck, ccexpDate, id, gatewayId => {class java.lang.Object=FetchGroup(gatewayId){birthday, city
, hotline, batchnum, del, zip4, ncoadate, rtype, hdel, hadel, nobdel, permdel, company, gndel, state, id, mi, sexcode, email, origdate
, zip, fpurch, jobtitle, last, secaddr, lpurch, nhzdel, primaddr, phone, nhpdel, numpurch, name, first, cpurch}}, key, shipdate}}, primaddr, phone, nhpdel, numpurch, name, first, cpurch}
---
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Sorry to answer my own post, but I found my error. It turns out that the user I was connecting to the database with did not have permissions on the new table “gwcomments”. Silly mistake. :-/

So, now that the error message is gone, my query is coming back empty! When I go into the Entity Inspector for the customers table, I get no rows. I was getting rows before I added this new entity.

The JPQL it executes according to the app.log is “select e from prm$Gateway, max=50”.

Any clue where to look for this part of the problem?

Look at what SQL is actually generated as a result of your mapping: set DEBUG level to the eclipselink.sql logger in tomcat/conf/logback.xml or at runtime in Administration > Server Log screen.

Also, keep in mind that if you inherit GWComment from BaseLongIdEntity, the platform will automatically assign IDs to newly created instances. This is not related to the current issue of course.

You definitely need a separate datasource for Comments entity to save it.

As for ID generation, the platform does not recognize @MapsId annotation, and it just looks at the entity base class. That’s why a suggested you to use BaseGenericIdEntity as a base class.

I just can’t seem to make this work. Even though I have a separate data source for the comments, and my text field on the screen is linked to that data source, I can’t get the insert entity listener to fire.

The interface doesn’t seem to know that when I type into the text field on the screen, I need a new instance of the GWComments entity even though that data source is selected as a property in the text field.

Do I manually have to code the entire thing - include a listener on the text field to create a new entity? And then do I have to add it to the CommitContext somehow so that it gets written?

It’s really not worth all this effort. I think I will just create “instead of” triggers inside my Oracle database and handle it there! :-/

Otherwise, is there a simple example of how to handle a one-to-one mapping where the IDs are the same?

Thanks. I will change it. Does this mean that I have to manually set my ID to my customer’s ID when creating a new entity or is the typical getter/setter (like this) OK?


@Table(name = "GWCOMMENTS")
@Entity(name = "prm$GWComment")
public class GWComment extends BaseGenericIdEntity<Long> {
    private static final long serialVersionUID = 1130005077378204782L;

    @Id
    @Column(name="ID")
    private Long id;

    @Column(name = "COMMENTS", length = 256)
    private String comments;

    @MapsId
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "gwcomments")
    private Gateway customer;

    public Long getId() { return id; }

    public void setId(Long id) { this.id = id; }

    public void setCustomer(Gateway customer) { this.customer = customer;   }

    public Gateway getCustomer() { return customer;  }

    public void setComments(String comments) {  this.comments = comments;  }

    public String getComments() { return comments;  }

}

Yes, you should assign the ID manually right after creating an instance in memory and before adding it to the datasource.

Here it is: https://github.com/knstvk/cuba-sample-same-id-entity

You are right, the platform creates a new instance automatically only for Embedded entities, which is not your case. But an instance is required to correctly initialize the nested datasource. So the idea is to always create the Details entity, but on commit to clean up the CommitContext to remove empty Details:


public class CustomerEdit extends AbstractEditor<Customer> {

    @Inject
    private Metadata metadata;

    @Override
    protected void postInit() {
        Customer customer = getItem();
        // always create Details to be able to enter into fields
        if (customer.getDetails() == null) {
            Details details = metadata.create(Details.class);
            details.setId(customer.getId());
            customer.setDetails(details);
        }
    }

    @Override
    public void init(Map<String, Object> params) {
        getDsContext().addBeforeCommitListener(context -> {
            Customer customer = getItem();
            // iterate through committed instances
            for (Iterator<Entity> it = context.getCommitInstances().iterator(); it.hasNext(); ) {
                Entity entity = it.next();
                if (entity instanceof Details) {
                    Details details = (Details) entity;
                    if (Strings.isNullOrEmpty(details.getAddress())
                            && Strings.isNullOrEmpty(details.getDescription())) {
                        // if Details instance is empty, remove it from the collection so it won't be committed
                        it.remove();
                        // if Details instance is not new, remove it from the database
                        if (!PersistenceHelper.isNew(details)) {
                            context.addInstanceToRemove(details);
                        }
                        // update Customer if we removed Details
                        customer.setDetails(null);
                        context.addInstanceToCommit(customer);
                    }
                }
            }
        });
    }
}

Thank you so much! I will work through this an integrate it into the app to see what happens.

So, I’m clearly missing something because my comments are not saving. I did this:


package com.paslists.prm.web.gateway;

import com.google.common.base.Strings;
import com.haulmont.cuba.core.entity.Entity;
import com.haulmont.cuba.core.global.Metadata;
import com.haulmont.cuba.core.global.PersistenceHelper;
import com.haulmont.cuba.gui.components.AbstractEditor;
import com.paslists.prm.entity.GWComment;
import com.paslists.prm.entity.Gateway;

import javax.inject.Inject;
import java.util.Iterator;
import java.util.Map;

public class GatewayEdit extends AbstractEditor<Gateway> {

    @Inject
    private Metadata metadata;

    @Override
    protected void postInit() {
        Gateway gateway = getItem();
        // always create a GWCOmments to be able to enter into fields
        if (gateway.getGwcomments() == null) {
            GWComment gwComment = metadata.create(GWComment.class);
            gwComment.setId(gateway.getId());
            gateway.setGwcomments(gwComment);
        }
    }


    @Override
    public void init(Map<String, Object> params) {

        getDsContext().addBeforeCommitListener(context -> {
            Gateway gateway = getItem();
            // iterate through committed instances
            for (Iterator<Entity> it = context.getCommitInstances().iterator(); it.hasNext(); ) {
                Entity entity = it.next();
                if( entity instanceof GWComment) {
                    GWComment gwcomment = (GWComment) entity;
                    if(Strings.isNullOrEmpty(gwcomment.getComments())) {
                        // If no comments - remove the entity
                        it.remove();
                        // If was already in the database, delete it
                        if (!PersistenceHelper.isNew(gwcomment)) {
                            context.addInstanceToRemove(gwcomment);
                        }
                        // update gateway if we removed gwcomment
                        gateway.setGwcomments(null);
                        context.addInstanceToCommit(gateway);
                    }
                }
            }
        });
    }
}

I think is is almost identical to your example. My edit view that I base my screen on contains the gwcomment linked to it. I stepped through the code. I put a breakpoint in postInit(). The object inside postInit() gets an empty gwcomment. However, as soon as I step OUT of postInit(), the entity in the AbstractEditor::setItem() function that called postInit() does NOT contain a gwcomment. It’s like the postInit() is operating on a different entity. As a matter of fact, when I look in the variables window at the item in the setItem() call is named “{Gateway@13494} com.paslists.prm.entity.Gateway-281 [detached]”. Inside postInit(), the getItem() returns “{Gateway@13642} com.paslists.prm.entity.Gateway-281 [detached]”. The postInit() adds a GWComment entity to the Gateway@13642 entity, but then postInit() returns. The object left - Gateway@13494 - HAS gwcomments=null again! It’s like the postInit() didn’t operate on the correct entity. :-/

Clearly I’m missing something here, because your example seems to work. What could it be?

It’s like the postInit() is operating on a different entity.

This is normal, because setItem() reloads the passed entity before calling postInit(). So you should just always operate on the instance returned by getItem() and not on the one passed to setItem().

You are saying that comments are not saved. Are you able to enter them in fields? If yes, could you set a breakpoint into the commit listener body and inspect what is inside CommitContext collections?

I set a breakpoint in my addBeforeCommitListener(). I loaded a record and entered a comment. Then, when I hit the breakpoint, I noticed that the comment on the screen was missing. That made me look closely at your example again, and I found that the Address/Description fields in your fieldGroup were attached to the nested datasource, which I had missed. I modified my text field (not using fieldGroup) to use my nested datasource and tried again.

IT NOW WORKS! Thanks very much for your help on this!

So, to recap the steps I had to follow - for anyone else trying to get this done. Entities are Customers and Comments. There is a one-to-one mapping between them and their ID is the same. We only store a comment row when there is a comment to record.

  1. Create an entity extending BaseGenericIDEntity to hold the Comment. Create a property that links back to the customer with the annotation @MapsId and @OneToOne.

  2. Create a one-to-one property in the Customer entity that links to the Comment with the annotation @PrimaryKeyJoinColumn (otherwise I received errors about multiple IDs) and @OneToOne.

  3. Create a nested datasource on the screen that contains my one-to-one Comment entity

  4. Override the postInit() method in the screen controller to create an empty Comment entity if the Customer doesn’t already link to one. Initialize the ID to the ID of the Customer entity. Save that newly created Comment entity to the Customer entity.

  5. Override the init method in the screen contoller to add a BeforeCommitListener. This listener passes through all entities that will be committed looking for Comment entities. When found, it looks to see if they are empty. If so, it marks them as a delete if they have been persisted before or it just removes them from the CommitContext if they have not. It also updates the Customer entity to remove the Comment link.

I think that covers it.

Sorry - but one MINOR issue left. When I open up a Customer that does not currently have a Comment, then I close the edit dialog for that customer with the Cancel button, I am asked whether or not to save changes. I assume this is because we have created an empty Comment entity.

We really should just remove the empty Comment entity without asking.

Any clues on how to make that happen? What do I @Override?

You are right, the reason for this notification is that we programmatically modified entities and the datasources switched to “modified” state. So we should switch them back:


@Override
protected void postInit() {
    Customer customer = getItem();
    // always create Details to be able to enter into fields
    if (customer.getDetails() == null) {
        Details details = metadata.create(Details.class);
        details.setId(customer.getId());
        customer.setDetails(details);
        // reset "modified" flags on datasources to avoid "Unsaved changes" notification on Cancel
        ((DatasourceImplementation) customerDs).setModified(false);
        ((DatasourceImplementation) detailsDs).setModified(false);
    }
}

In theory, you could also override the isModified() method of the screen controller and analyze what’s going on there, but it would be even more cumbersome.

Besides, I found one more issue with this sample: when you create a new Customer and do not enter Details, it throws exception on commit because the Customer instance has a reference to an empty Details instance which is not in the commit context. So I had to clean it up in the commit listener:


@Override
public void init(Map<String, Object> params) {
    getDsContext().addBeforeCommitListener(context -> {
        Customer customer = getItem();
        // in a new Customer, clean reference to Details if the Details instance is not going to be committed
        if (PersistenceHelper.isNew(customer) && !context.getCommitInstances().contains(customer.getDetails())) {
            customer.setDetails(null);
        }
        // iterate through committed instances
        for (Iterator<Entity> it = context.getCommitInstances().iterator(); it.hasNext(); ) {
// ...

We’ve finally got it, I think! :slight_smile: Thanks very much for all of your help.