Best way to deal with a legacy code description table?

Support
#1

I have a legacy table in an application that looks like this:


create table code_control (
   rec_type          char(1),      /* used to group codes for each field - codes for a field have the same rec_type */
   code              char(1),      /* code within the field */
   seq               number,       /* order to display the codes in a lookup field */
   descr             varchar2(20), /* text to display */
   status            char(1),      /* A = Active (display it), I = Inactive (ignore it) */
   constraint  codecontrol$pk primary key on (rec_type, code)
);

I am looking for the best way to populate my lookup fields from these codes. I have set up a CodeControl Entity with a Composite Key. I have also set up a MANY_TO_ONE association inside an Entity that contains a code field. Unfortunately, this isn’t enough to get things working. The missing factors are:

  1. I have to only select the particular rec_types for the lookup field I’m associating with in each case.
  2. I have to only include code records with status = ‘A’

Will I have to create Eclipselink DescriptionCustomizer classes for each and every code lookup? Ref: EclipseLink/Examples/JPA/MappingSelectionCriteria - Eclipsepedia

If I select the data I need in a CollectionDatasource and set it as the optionsDatasource on my lookup field, I still have to somehow extract the chosen code out of the lookup to save to the item I’m editing.

And, to make matters more interesting, I’m doing this as an inline edit in a table. I’m using code like this to set up the lookup field:


    @Inject
    private CollectionDatasource<CodeControl, CodeControlCompKey> assignmentDs;

    @Override
    public void init(Map<String, Object> params) {
        offersTable.addGeneratedColumn("assignment", new Table.ColumnGenerator() {
            @Override
            public Component generateCell(Entity entity) {
                LookupField field = (LookupField) componentsFactory.createComponent(LookupField.NAME);
                field.setDatasource(offersTable.getItemDatasource(entity), "assignment");
                field.setOptionsDatasource(assignmentDs);
                field.addValueChangeListener(e -> {
                    Offer offer = getItem();
                    offer.setAssignment(((CodeControl) e.getValue()).getId().getCode());
                }); 
                return field;
            }
        });
    }

My ValueChangeListener on the embedded LookupField is not firing when I choose a value. Instead, I’m getting this error:

ConversionException: Unable to convert value of type com.paslists.rade.entity.CodeControl to model type class java.lang.String. No converter is set and the types are not compatible.

It appears the framework is trying to store my CodeControl entity directly to my entity, rather than the code field I would like to save.

In addition, when loading an entity that already exists, I’m 99% sure that the CodeControl value won’t be matched up, since I don’t have a true key to it in my entity and only have the code field. Something has to look up the description to go with the code to display it as the current value of the field.

Does anybody have a better way to do all of this?

#2

Well, I got something to work, although I don’t know if there is a better way. Posting here for anybody else that might need it…

My entities are Offer and CodeControl. CodeControl has a composite key made up of recType and code. My Offer contains a field named assignment. The values of the description for the assignment codes are found in CodeControl with recType = ‘O’. My LookupField will show the descriptions with recType = ‘O’ and set the Offer.assignment with the corresponding CodeControl.code value. Hopefully this makes sense! :wink:

As part of the problem, I’m doing an in-place edit on a table, so I have to make sure that my table column attached to the Offer.assignment field is created as a LookupField. The code is:


public class OfferBrowse extends AbstractEditor<Offer> {

    @Inject
    private CollectionDatasource<Offer, Long> offersDs;

    @Inject
    private CollectionDatasource<CodeControl, CodeControlCompKey> assignmentDs;

    @Inject
    private Metadata metadata;

    @Inject
    private Table offersTable;

    @Inject
    private DataManager dataManager;

    @Inject
    private ComponentsFactory componentsFactory;

    @Override
    public void init(Map<String, Object> params) {
        offersTable.addGeneratedColumn("assignment", new Table.ColumnGenerator() {
            @Override
            public Component generateCell(Entity entity) {
                LookupField field = (LookupField) componentsFactory.createComponent(LookupField.NAME);
                field.setOptionsDatasource(assignmentDs);
                Offer currentOffer = (Offer) ((Datasource)offersTable.getItemDatasource(entity)).getItem();
                if (currentOffer.getAssignment() != null) {
                    LoadContext<CodeControl> loadContext = LoadContext.create(CodeControl.class)
                                   .setQuery(LoadContext.createQuery("select c from rade$CodeControl c where c.id.recType = 'O' and c.id.code = :code")
                                                        .setParameter("code", currentOffer.getAssignment()))
                                   .setView(View.LOCAL);
                    List<CodeControl> codes = dataManager.loadList(loadContext);
                    if (codes.size() == 1) {
                        field.setValue(codes.get(0));
                    }
                }
                field.addValueChangeListener(e -> {
                    currentOffer.setAssignment(((CodeControl) e.getValue()).getId().getCode());
                });
                return field;
            }
        });
    }
}

Some notes:

  1. offersTable is the table of Offer entities on my screen where I’m doing in-place editing
  2. I use offersTable.addGeneratedColumn to attach a LookupField to my assignment column.
  3. the generateCell() creates a LookupField and sets the optionsDatasource to be a datasource that specifically lists all CodeControl records with recType = ‘O’. That datasource is created declaratively in the XML, like this:

        <collectionDatasource id="assignmentDs"
                              class="com.paslists.rade.entity.CodeControl"
                              view="_local">
            <query>
                <![CDATA[select e from rade$CodeControl e
where e.id.recType = "O" and e.status = "A"]]>
            </query>
        </collectionDatasource>
  1. In order to display the current value of the description attached to the assignment code, I have to get the current Offer and use the DataManager to look up the description in the CodeControl table. I then set that entity to the value of my LookupField.
  2. I set up a ValueChangeListener on the LookupField to pull the CodeControl.code value from the selected CodeControl record and save that in the Offer.assignment column

The setup works, but is there a better way?

And, if not, is it possible to create an extended LookupField specifically for this type of Lookup? I would call it CodeLookupField. It would have a property that contains the recType. It would automatically return the CodeControl.code as its value and automatically look up the correct CodeControl entity when loaded.

How would I create this and integrate it as a Studio GUI Component so I could drag and drop it on a screen?

#3

Maybe I missed something, but do you really need an entity for such codes in your data model? If not, you can use codes from your code_control as follows:

  • Create a service that loads codes by a given rec_type

  • Invoke this service when creating LookupField instances and use LookupField’s setOptionsList() or setOptionsMap() methods to populate the list of options

As for creating a specific implementation of LookupField for these code, take a look at the following example: GitHub - knstvk/cuba-sample-web-component. It shows how to completely substitute the implementation, so your problem is a bit different. See also Extend Studio link in Studio - it allows you to register a new component in the palette. Let us know if you need help with this.

#4

Thanks for the suggestion. I will try to implement it. It sounds much simpler in the long run. I have re-implemented like this:


@Service(CodeService.NAME)
public class CodeServiceBean implements CodeService {
    @Inject
    private DataManager dataManager;

    public Map getCodes(String recType) {
        LoadContext<CodeControl> loadContext = LoadContext.create(CodeControl.class)
                .setQuery(LoadContext.createQuery("select c from rade$CodeControl c where c.id.recType = :recType and c.status = 'A' order by c.seq")
                                     .setParameter("recType", recType)
                          );
        Map<String, String> codeMap = new LinkedHashMap();
        for (CodeControl row: dataManager.loadList(loadContext)) {
            codeMap.put(row.getDescr(), row.getId().getCode());
        }
        return codeMap;
    }
}


@Inject
CodeService codeService;
   .
   .
   .
offersTable.addGeneratedColumn("assignment", new Table.ColumnGenerator() {
  @Override
  public Component generateCell(Entity entity) {
    LookupField field = (LookupField) componentsFactory.createComponent(LookupField.NAME);
    field.setOptionsMap(codeService.getCodes("O"));
    field.setDatasource(offersDs, "assignment");
    return field;
  }
});

When I do this, an existing row in the table doesn’t show a value for the assignment field until I change the focus to any field in that row. How do I make the generated fields show the correct value when they are first displayed and before anybody clicks anything?

#5

You’ve set incorrect datasource for the field. It should be a special datasource of the table row, as shown in the docs for the addGeneratedColumn() method:


field.setDatasource(offersTable.getItemDatasource(entity), "assignment");
#6

Yes, that was my error. Thank you!