Can I use JPA relations in tree widget?

[larsho]

I have a JPA tree structure like this:

@Entity @Table(name = "section")
public class Section implements Serializable {

@ManyToOne @JoinColumn(name = "parent")
private Section parent;

@OneToMany(mappedBy = "parent")
private java.util.Set<Section> section = new java.util.HashSet<Section>();
}

I want to display this in a TreeWidget. Is this possible?

[Isomorphic]

Yes, add a declaration for a primaryKey, and declare the primaryKey fields and foreignKey field ("parent") in the .ds.xml file, specifying serverConstructor to use the JPA DataSource.

More information on Tree DataBinding is here, and there's a sample for JPA in the "samples" directory of the SDK.

[larsho]

Thanks, but I can't get it to work.

My DataSource looks like this:

<DataSource
ID="section"
serverConstructor="com.isomorphic.jpa.JPADataSource"
beanClassName="data.Section"
dropExtraFields="true"
>
<fields>
<field name="id" type="sequence" hidden="true" primaryKey="true" />
<field name="parent" type="integer" title="Parent" foreignKey="id" />
<field name="name" type="text" title="Name" required="true" />
</fields>
</DataSource>

The table has multiple root elements where parent=NULL. This is MySQL.

I have tried all kinds of alternatives: Changing field parent type to "section", setting a rootValue to a known value, changing the Java code from a relation to just a Long with the foreign key, but it doesn't help.

The DSRequest looks like this:
Request #1 (DSRequest) payload: {
criteria:{
parent:null
},
operationConfig:{
dataSource:"section",
operationType:"fetch",
textMatchStyle:"exact"
},
componentId:"isc_TreeGrid_0",
appID:"builtinApplication",
operation:"section_fetch",
oldValues:{
parent:null
}
}

This returns a DSResponse: List with 0 items.

I have no idea what JPA or SQL is executed in the server. It doesn't get logged even if I turn on all DEBUG.

I am considering writing my own operationBinding to handle this. The alternative would be to use SQL instead of JPA. The following SQL datasource works and builds a tree in the UI:

<DataSource
ID="sectionTree"
serverType="sql"
tableName="section"
>
<fields>
<field name="id" type="sequence" hidden="true" primaryKey="true" />
<field name="parent" type="integer" title="Parent" foreignKey="id" />
<field name="name" type="text" title="Sektion" required="true" />
</fields>
</DataSource>

Any help is appreciated.

[larsho]

I got this to work by adding the following operation binding. I think the problem is that the JPA query didn't handle null correctly. I had to have special handling for null values.

<operationBindings>
<operationBinding operationType="fetch">
<serverObject className="TreeRequestHandler" methodName="get"/>
</operationBinding>
</operationBindings>



public class TreeRequestHandler {
public DSResponse get(DSRequest request) throws Exception {
String dataSourceName = request.getDataSourceName();
Long parent = (Long) request.getFieldValue("parent");

DSResponse response = new DSResponse();

EntityManager em = EMF.getEntityManager();
String queryString = "from " + Section.class.getName() + " where ";
if(parent != null) {
queryString += "parent=:parent";
} else {
queryString += "parent is null";
}
Query query = em.createQuery(queryString);
if(parent != null) {
query.setParameter("parent", parent);
}

@SuppressWarnings("unchecked")
List<Section> result = query.getResultList();
Section[] resultArray = (Section[]) result.toArray(new Section[result.size()]);
response.setData(resultArray);
response.setSuccess();
return response;
}
}

Update: The above code worked if I changed "class Section { Section parent; }" to "class Section { Long parent; }". Without the change, the query should be:
if(parent != null) {
queryString += "parent.id=:parent";
} else {
queryString += "parent is null";
}

[alius]

Hi larsho,

Thanks for pointing out a problem.

I've just committed changes:
correct support for null values in criteria;
debug logging showing created query and parameters used in that query.

You will be able to pick up these changes with next nightly build.

Regards,
Alius.

[larsho]

Thanks for the quick response! Null parameters now work.

But if the parameter is not null, I get an exception:

java.lang.ClassCastException: Value '2' of type 'class java.lang.Long' can not be casted to type 'class data.Section'.
at com.isomorphic.jpa.JPADataSource.castValue(JPADataSource.java:895)
at com.isomorphic.jpa.JPADataSource.executeFetch(JPADataSource.java:251)
at com.isomorphic.datasource.DataSource.execute(DataSource.java:784)
at com.isomorphic.jpa.JPADataSource.execute(JPADataSource.java:98)
at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:721)
at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
at com.isomorphic.application.AppBase.execute(AppBase.java:491)
at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:1397)
at com.isomorphic.datasource.DataSource.fetch(DataSource.java:1605)
at com.isomorphic.datasource.DataSource.fetch(DataSource.java:1619)


Perhaps this could be fixed if you changed the query string when parent is not null to something like "x.id = :param" instead of "x = :param"?

Regards,
Lars

[alius]

Hi larsho,

It is expected exception.
I've tested your case: you have declared property "parent" as type of Section; in data source declaration you have specified same property as integer. As exception suggests, Long can not be casted to class Section.

To make it work you have to declare property which would represent actual foreign key value.
Here is your updated entity class (worked in my test case):

@Entity
@Table(name = "section")
public class Section implements Serializable {

    @Id
    private Long id;

// This column represents actual foreign key value
    @Column(name = "parent")
    private Long parentId;

// Parent object is not editable
// This property is not required for Tree operation
    @ManyToOne
    @JoinColumn(name = "parent", insertable=false, updatable=false)
    private Section parent;

// I've commented out this property:
// Due to automatic entity conversion to JS object it has to eagerly fetched
// This means that all sublevels will be fetched from DB
// (actually whole table will be loaded when root nodes are fetched)
//    @OneToMany(mappedBy = "parent", fetch=FetchType.EAGER)
//    private List<Section> sections;

    private String name;
}

And here is data source declaration (only one change: using 'parentId' instead of 'parent'):

<DataSource
    ID="section_DataSource"
    serverConstructor="com.isomorphic.jpa.JPADataSource"
    beanClassName="data.Section"
    dropExtraFields="true"
    >
    <fields>
        <field name="id"    type="sequence" hidden="true"   primaryKey="true" />
        <field name="parentId" type="integer" title="Parent" foreignKey="id" />
        <field name="name"  type="text"     title="Name"    required="true"   />
    </fields>
</DataSource>

Regards,
Alius

[larsho]

Ok, this works. But I would really like to be able to use JPA relations, not just id fields. So I have a feature request: With JPA/Hibernate datasource when an object is sent, generate a query with the object id like I described in my previous post.

Also, it would be great if the service did not return lazy relations, at least not if they are explicitly requested by the client.

/Lars

[alius]

Hi Lars,

You can still use JPA relations for your application.
On the other hand - transferring complete object over wire just to use it's single field (id in this case) is not feasible. It consumes bandwidth and browser resources (remember that your entities ends up as JS objects in browser).

Regards,
Alius

[larsho]

Aha, now I see. You use the "parent" column for 2 different variables in the Java class. I'll try that.

I didn't mean to transfer the whole object, just some smart logic that understood when to use the id instead of an object reference.

Anyway, thanks for a great tool and great support!

/Lars