# Form Builder and foreign keys

paoloa
24 1 2

I created a project to test the Form Builder.
I have two tables in a mySQL db: artist and datafile.
Datafile contains data of file resources related to an artist (pictures, audio, video); it has an artistId field wich is a foreign key for the artist table (defined in mySQL database).
I runned the Form Builder for the two tables and created the java, zeta and zul files.

The problem is with the foreign key.

The first question is: how to display a description of the record (e.g. full name) instead of the unuseful artistId in view mode?

The edit mode case is a little more complex.
I choose a listbox component and the code generator writed a code like this:

datafile.zul:

<row>
Artistid :
<listbox id="artistid"
model="@{textModel.artistidModel}"
selectedItem="@{datafileModel.selected.artistid,save-when=none}"
mold="select">
<listitem self="@{each=zx2}">
<listcell label="@{zx2}" />
</listitem>
</listbox>
</row>

datafileController.java:

@AfterCompose
public void afterCompose() {
super.afterCompose();

// TODO: MUST IMPLEMENT YOUR OWN.
List artistidModel = new ArrayList();
datafileModel.setArtistidModel(artistidModel);

}

Apparently, the list of choice values of the listbox (tha is the list the artist ids) should be created by java code in the afterCompose method.
But, if I run the generated sample code, when I press the "New" button, enter the field values, then press the "Save" button, i find the first problem: if I don't change the default selection in the list box, the selectedItem is null, because it is initialized with the artistId of the current datafile record (wich is a new record, with no assigned values yet).
The second problem is: how can I build an artistidModel list with real values? The task is trivial, but I cannot find any documentation.
I took a look at the generated java code, then I tried with this code:

ArtistModel artistModel = new ArtistModel();
ArtistDAO artistDAO = new ArtistDAO();
BasicDAO basicDAO = new BasicDAO();
artistDAO.setBasicDao(basicDAO);
artistModel.setDAO(artistDAO);
List<Artist> artistList = artistModel.getAll();
datafileModel.setArtistidModel(artistList);

It did not work because the BasicDAO class requires an EntityManager object. At this point I stopped and decided to ask for help.

The second question is: how can I obtain a list of all artists from the datafileController class?

Third question: how to set the initial value of the listbox to avoid the null value problem mentioned above?

Fourth question: is there a simpler way to manage such a trivial foreign key case?
I tried to set the artistModel as model property of the listbox, but it does not work, because when I press the "Save" button, the binder tries to assign the listbox value to the artist record, instead of the datafile record.
Really there is no way to define a so common case in ZUL? If not, I think it should be added with first priority. In my opinion, it makes no sense that a DB tool like this Form Builder has no direct support for foreign keys (I mean that list components should have properties to set the model of the lookup table, other than the model of the editing table)

Paolo

delete retag edit

## 25 Replies

iantsai
2755 1

You can add a getFullName() method to your bean, and change artistId to fullName like this:

<listcell label="@{zx1.fullname}" />

Yes! it's definitely our first priority to implement it.

Currently Zeta is in it's experimental stage, and we are still trying and thinking about what solutions
are qualified to implement the fundamental parts of ZK Application.

It should be human readable, developer modifiable and IDE controllable.
It should be as efficient as possible in both CPU and Memory wherever Server-side or Client-side.

iantsai
2755 1

why not use zeta to create "artistModel" first then attach it to the artistid listbox?
It's all about traditional ZK skill, you can learn it by imitating what zeta generated
for you according to your "datafile" schema.

paoloa
24 1 2

Sorry, I had already posted it in the help forum when I realized there was a specialized forum.

>You can add a getFullName() method to your bean, and change artistId to fullName like this:
><listcell label="@{zx1.fullname}" />
the problem is that the view is of datafile table, not artist; I need a simple way to display a property of the associated table, that is artist table, in the datafile table view

>why not use zeta to create "artistModel" first then attach it to the artistid listbox?
>It's all about traditional ZK skill, you can learn it by imitating what zeta generated
>for you according to your "datafile" schema.
yes, I tried to do it; but ZETA currently support only a single table per ZUL file.
Actually, if I write, in datafile.zul:

<listbox id="artistid"
model="@{artistModel.all}"
mold="select">
<listitem self="@{each=artist}" value="@{artist.id}">
<listcell label="@{artist.description}" />
</listitem>
</listbox>


i get an exception when the engine tries to load DB data:
java.lang.ClassCastException: artsite.datafile.base.Datafile cannot be cast to artsite.artist.base.Artist
artsite.artist.base.Artist.equals(Artist.java:21)
java.util.HashMap.get(Unknown Source)
org.zkoss.zkplus.databind.DataBinder.getBeanSameNodes(DataBinder.java:875)
org.zkoss.zkplus.databind.DataBinder.registerBeanNode(DataBinder.java:1072)
org.zkoss.zkplus.databind.DataBinder.myGetBeanWithExpression(DataBinder.java:906)
org.zkoss.zkplus.databind.DataBinder.getBeanAndRegisterBeanSameNodes(DataBinder.java:886)
org.zkoss.zkplus.databind.BindingListitemRenderer.render(BindingListitemRenderer.java:99)
org.zkoss.zul.Listbox\$Renderer.render(Listbox.java:2299)
org.zkoss.zul.Listbox.onInitRender(Listbox.java:2189)


apparently because data beans are stored in a Map whose entries are supposed to be all of the same type.

the problem is not only with the DataBinder, ZUL components would need some enhancements too;
we should be able to write a code like this:

<listbox id="artistid"
persistentModel="@{datafileModel.all}"
persistentProperty="artistid"
lookupModel="@{artistModel.all}"
mold="select">
<listitem self="@{each=artist}" value="@{artist.id}">
<listcell label="@{artist.description}" />
</listitem>
</listbox>


the artist with id equal to the artistid of the selected datafile record should be automatically selected (I see no need to specify it explicitly by setting the selectedItem property)

or, for the view mode:

<label
persistentModel="@{datafileModel.all}"
persistentProperty="artistid"
lookupModel="@{artistModel.all}"
lookupProperty="description"
/>


While waiting for similar features in a future version, do you know any not too complicated workaround to display meaningful lookup information in view and edit mode?

Thanks
Paolo

robertpic71
1275 1

>>While waiting for similar features in a future version, do you know any not too complicated workaround to >>display meaningful lookup information in view and edit mode?

I'm not sure, that i catch you problem. However, a 1:1/n:1 lookup is quite simple:

Domainbean - before:

@Entity
@Table(name = "PLANUNGEN")

public class Plan {

@Id @GeneratedValue
@Column(name = "PLAN_ID")
int id;
@Column(name = "PLAN_NR")
int nummer;
@Column(name = "CUSTOM_ID")
int customId;


CUSTOM_ID is a foreign key and for the table CUSTOMER

change it to:

@Column(name = "PLAN_NR")
int nummer;
// int customId; -->
@ManyToOne
@JoinColumn(name = "CUSTOM_ID")
Customer customer;


Now you have easy access inside the databinding, i.e for a list:

<listbox id="plan" model="@{Model.planService.all}">
<listitem self="@{each=plan}" value="@{plan}">
<listcell label="@{plan.nummer}" />
<listcell label="@{plan.customer.name}" />
</listitem>
</listbox>

Because customer (in the file plan) in now the object customer - and not only the customerId, you don't need do translate the id to the object.

Here is i.e. the edit form for a plan, the customer will be prompted:

<listbox id="customerid"
model="@{Model.customerService.all}"
selectItem="@{Model.plan.customer}"
mold="select">
<listitem self="@{each=customer}">
<listcell label="@{customer.number}" />
<listcell label="@{customer.name}" />
</listitem>
</listbox>


Of course, customer is a bad example for a listboxprompter....

BTW: An object to id switch and vice-a-versa could done by (databinding) Converter.

/Robert

paoloa
24 1 2

Robert,
thanks for your suggestion. The ManyToOne annotation may be used to display lookup values in view mode, but it does not solve the problem of displaying the list of all artist records to allow the user to choice the referenced artist in edit mode.
Anyway, I tried to use the annotation like this:

@ManyToOne
@JoinColumn(name="artistId")
protected Artist artist;


but the result was an exception whose cause I cannot figure out:
SEVERE: Servlet.service() for servlet zkLoader threw exception
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'datafileController': Injection of resource fields failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'artistid': Invocation of init method failed; nested exception is org.springframework.beans.factory.BeanCurrentlyInCreationException: Error creating bean with name 'artistid': Requested bean is currently in creation: Is there an unresolvable circular reference?
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessAfterInstantiation(CommonAnnotationBeanPostProcessor.java:293)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:957)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:470)

it looks like there is some circular reference, but the class Artist does not reference any Datafile object.
Apparently, some library code is written in a way that cause this exception to be thrown when you try to use an association annotation.

dermaik81
60 1

Hi Folks,
I got the same problems like paoloa. I have got the tables A and B where A got B's primary key as a foreign key.
I built the CRUD Pages for the table A and the table B. Now I want to modify the files of A to catch the information about the corresponding instance of b in a dropdownlist/listbox/combobox instead of fill in the PK of B in an intbox.

The hint of robertpic71 doesn't work. I am sure that I forgot some quite simple modifications in my sources. It would be very helpful for me if one of your core developers could list up the steps which must be performed to solve this problem. Let's get more conrete:

Table 1: employee (em_id, em_name,....,ro_id)
Table 2: role (ro_id, ro_rights, ro_desc,...,)

The files generated by your excellent form builder wizard:

Package vas.employee
EmployeeController.java
EmployeeDAO.java
EmployeeModel.java

Package vas.employee.base
Employee.java
EmployeeControllerBase.java
EmployeeModellBase.java

The same for "role" and two *.zul files
But these generated files don't allow any interaction between the Objects by nature. Moreover they are small stand-alone CRUD applications.

My first step was to modify the Employee.java and add the @ManyToOne Annotation for @column(name="ro_id"). After that any next step fails. Well, which of the listed files above have to be modified and in which way do I have to modify them. I hope you can catch my problem.

Every hint or link to another forum topic is welcome.
Best wishes an lots of thanks,
Maik

robertpic71
1275 1

Some questions:
Is artistId the fieldname inside the datafile?
is the the Artist Domainbean not "marked" as @Entity?
Is there a @Column(name = "artistId") inside Artist?

Check out the startuplog (server/console inside eclipse)! Mapping errors are show inside the statup!

Post your classes Artist and Datafile (this class, where you reference to artists).

>> but it does not solve the problem of displaying the list of all artist records to allow the user to choice the referenced artist in edit mode.
The persistence will do the switch for you - there is no need for a own id/object switch!

Here is the selector for you example:
.. other editfields for datafile...

<listbox id="artistid"
model="@{datafileController.artistModel.all}"
selectedItem="@{datafileModel.selected.artist}"
mold="select">
<listitem self="@{each=artist}" >
<listcell label="@{artist.description}" />
</listitem>
</listbox>

Check the line: selectedItem="@{datafileModel.selected.artist}"
The prompter moves the selectedEntry (artist) to the selected/edit selected.artist. Because the persistance is mapped to artist. it knows the right artistid for the database-action. After the mapping you don't need the artistId.

/Robert

robertpic71
1275 1

>> The hint of robertpic71 doesn't work. I am sure that I forgot some quite simple modifications in my sources.
You need some modifcations, because i.e. field is changing from label/intbox to listbox.

I try here a step-by-step example (sorry for the tablenames, but this is not my schema...)

My tables:
dcl_projects = projecthead fields, including a field status (int)
statuses = possible statuses (Open, Close, Deferred...)

change the domainbean (here dcl_projects, paola: DataFile, derMaik81: Employee)

change from:

@Column(name = "status", nullable=false)
protected Integer status;
public void setStatus(Integer value) {
this.status = value;
}
public Integer getStatus(){
return this.status;
}

-->
@ManyToOne
@JoinColumn(name="status", nullable=false)
Statuses status;

public void setStatus(Statuses value) {
this.status = value;
}
public Statuses getStatus(){
return this.status;
}

Change in the xxxxControllerBase (xxxx here: dcl_projects, paola: DataFile, derMaik81: Employee)

protected Intbox status; ---> protected Listbox status;     // that's the binded Input-GUI-Element
..
void validate() { ..
status.getValue();   // should fires contraint
// because Listbox do not support getValue you have to remove or change this statement


Change in the xxxxController (xxxx here: dcl_projects, paola: DataFile, derMaik81: Employee)
Add the other model (here: Statuses, paolo: Artists, derMaik: Roles)

@Resource
protected StatusesModel statusesModel;

public StatusesModel getStatusesModel() {
return statusesModel;
}

Change the zul-File:
1.) Change the listbox (mainlist)
<listcell label="@{zx1.status}" />
to:
<listcell label="@{zx1.status.name}" />
You will see the description instead the idvalue. In a normal DB-Schema it should be statusId --> status.name or status.description

Change the detail-view:
<row>Status :<label value="@{dcl_projectsModel.selected.status}" /></row>
to:
<row>Status :<label value="@{dcl_projectsModel.selected.status.name}" /></row>

Change the edit-field:

<row>Status :<intbox id="status"  constraint="no empty" value="@{dcl_projectsModel.selected.status,save-when=none}"  /></row>
to:
<row>Status :
<listbox id="status" mold="select" rows="1" model="@{dcl_projectsController.StatusesModel.all}" selectedItem="@{dcl_projectsModel.selected.status,save-when=none}">
<listitem self="@{each=status}">
<listcell label="@{status.name}" />
</listitem>
</listbox>
</row>

Note: If there any converters in the databinding, remove the converter or use the targetconverter.

This works for me. However, newer versions of ZK Studio will support this feature via wizard.

/Robert

robertpic71
1275 1

There seems to be a bug in the created equals. For correct databinding with more than one kind of object, you need to correct this.

change line:
if (!(this instanceof xxxx)) return false;
to:
if (!(obj instanceof xxxx)) return false;

/Robert

dermaik81
60 1

Hi Robert,
Thanks very much for your posting. I will test it as soon as possible. I think your answer is what I am searching for.
Best wishes and kind regards,
Maik

[hide preview]