Navigating JPA relations

[lujop]

Hi,

By now I have used smartgwt with custom server solution using JSON in two small apps.
But now I'm evaluating to change to SmartGWTPro and I started to do some testing.

My first problem is navigating JPA relations:

-1-
It's posible to navigate them transparently?
It's mandatory to modify JPA models to have a field for the foreign key?

-2-
I modified JPA sample to has only a single grid for cities and be able to edit cityName and cityCountry with a SelectItem. What I've done is:

city_DataSource:
- Change canEdit to true for countryId
- Add:

   <field name="countryName" type="string"  title="Country" hidden="true"
               valueXPath="country/countryName" />

And in client use that ListGrid:

        ListGridField country=new ListGridField("countryId","Country");
        country.setEditorType(new SelectItem());
        country.setDisplayField("countryName");                
        grid.setFields(new ListGridField("cityName","Name"),country);

But that doesn't work, because when I update I get an error complaining about countryName.

I'm doing my grid correctly?

A lot of thanks in advance,

[Isomorphic]

Yes you need an explicitly declared foreignKey. At that point optionDataSource can be used to load entities from a related JPA DataSource into a SelectItem or ComboBoxItem.

As an example, take these JPA entities:

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

    @Id
    @Column (nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long countryId;

    @Column (nullable = false)
    private String countryName;

....
}

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

    @Id
    @Column (nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long cityId;

    @Column (nullable = false)
    private String cityName;

    // This property has real foreign key value
    @Column (nullable = false)
    private Long countryId;

    // This property refers to related entity (read-only)
    @ManyToOne
    @JoinColumn(name="countryId", referencedColumnName="countryId", nullable=false, insertable=false, updatable=false)
    private Country country;
....
}

.. create DataSources "City" and "Country" for each. City should have these fields:

<field name="countryName" type="text" hidden="true"
        valueXPath="country/countryName"/>
<field name="countryId" type="integer" displayField="countryName"
        foreignKey="Country.id"></field>

Now:

1. every "City" Record that comes from the server has a countryName field populated. It's not shown, but it's value is displayed for the countryId field instead of displaying the raw countryId value (due to displayField setting)

2. when countryId is edited, a SelectItem will automatically be shown for picking the related Country. You can specify using a ComboBoxItem instead via editorType="ComboBoxItem"

3. what's sent to the server when the field is edited and saved is just the countryId. Note the "updateable=false" etc flags on the country property in the JPA annotations. This makes it so you can just update the ID and not worry about an additional fetch to get the related Country object to assign via setCountry().

[lujop]

Yes you need an explicitly declared foreignKey

That's a problem because we have all the models and logic without this fake fields.
What I do in our custom messaging is also pass only the Id but then in the model we put the model entity using getReference. That way you have no cost penalty and works transparently with models.

The biggest problem that I see on that is that also I've to put updatable to false to my model. That is very problematic because existing logic does'nt work on the new fake fields and works with the entity relationship.

It's possible to do that in a better way or we have to adapt our existing logic to use the fake fields?

. At that point optionDataSource can be used to load entities from a related JPA DataSource into a SelectItem or ComboBoxItem.

As an example, take these JPA entities:

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

    @Id
    @Column (nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long countryId;

    @Column (nullable = false)
    private String countryName;

....
}

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

    @Id
    @Column (nullable = false)
    @GeneratedValue (strategy = GenerationType.IDENTITY)
    private Long cityId;

    @Column (nullable = false)
    private String cityName;

    // This property has real foreign key value
    @Column (nullable = false)
    private Long countryId;

    // This property refers to related entity (read-only)
    @ManyToOne
    @JoinColumn(name="countryId", referencedColumnName="countryId", nullable=false, insertable=false, updatable=false)
    private Country country;
....
}

.. create DataSources "City" and "Country" for each. City should have these fields:

<field name="countryName" type="text" hidden="true"
        valueXPath="country/countryName"/>
<field name="countryId" type="integer" displayField="countryName"
        foreignKey="Country.id"></field>

Now:

1. every "City" Record that comes from the server has a countryName field populated. It's not shown, but it's value is displayed for the countryId field instead of displaying the raw countryId value (due to displayField setting)

2. when countryId is edited, a SelectItem will automatically be shown for picking the related Country. You can specify using a ComboBoxItem instead via editorType="ComboBoxItem"

3. what's sent to the server when the field is edited and saved is just the countryId. Note the "updateable=false" etc flags on the country property in the JPA annotations. This makes it so you can just update the ID and not worry about an additional fetch to get the related Country object to assign via setCountry().

Thanks, now it works. Another need we have is that when loading a value with a combo or select item we need to load also some extra fields from the option datasource.
For example in the city/previous example we need to have also the countrycode as a field and load it (at the moment) when the user change the country. It is posible to do that easily?

A lot of thanks in advance,

[Isomorphic]

Interesting use of the phrase "fake fields" :) The fields we require to be declared are the real fields in the database. It's the abstraction of a "relation" that is fake, and very often you need to bypass this abstraction in order to understand how to write an efficiency query, write a query directly that can't be achieved in JQL, migrate or update a database in place, etc.

Even if you decide you want to keep your existing declarations, all you need is a getter and setter on the bean that returns the actual foreignKey id and allows it to be set (by, in this case, looking up the related Country object and calling setCountry()). There's no avoiding this, since clearly the right thing is to transmit only the ID value to the client, not the entire sub-bean.

You mentioned loading additional fields - you can add as many more fields from the Country sub-bean as you want via declaring additional fields that use valueXPath to extract values from the sub-bean.

[lujop]

Interesting use of the phrase "fake fields" :) The fields we require to be declared are the real fields in the database. It's the abstraction of a "relation" that is fake, and very often you need to bypass this abstraction in order to understand how to write an efficiency query, write a query directly that can't be achieved in JQL, migrate or update a database in place, etc.

But it's very innatural in JPA. If you want efficiency you can use the getReference that hasn't any cost penalty and if you want to use the key in the query you can do perfectly where city.country.id.
That way you have more natural 'JPA' model and the same efficiency.

Even if you decide you want to keep your existing declarations, all you need is a getter and setter on the bean that returns the actual foreignKey id and allows it to be set (by, in this case, looking up the related Country object and calling setCountry()).

Well, it's not a very bad workarround. Thank you.

You mentioned loading additional fields - you can add as many more fields from the Country sub-bean as you want via declaring additional fields that use valueXPath to extract values from the sub-bean.

Yeah, but I want to load them from the optiondatasource.
A very common case for us is for example that when you are editing a invoice line and you load a Item field you want to also load the price from the option datasource.
Or in the last example to be able to load the country code, but not only when loading the register, I need to load when the country change while loading it.

Cheers,

[Isomorphic]

But it's very innatural in JPA.

You're not using just JPA - you're building a web application. What is natural for JPA taken in isolation for simple samples is not necessarily natural for your overall goals.

And no, it is definitely not the same efficiency. There are many, many SQL queries that cannot be written with the same efficiency in JQL vs direct SQL (when they can be implemented at all), not to mention the overhead (on every query) of conversion from JDBC ResultSets to nested lists of beans with various proxy classes.

On your question related to additional fields - in the context of a SelectItem, pickListFields can be used to show multiple fields from the related DataSource, and getSelectedRecord() can be used to get access to the entire related records (when there is a selection).

[lujop]

You're not using just JPA - you're building a web application. What is natural for JPA taken in isolation for simple samples is not necessarily natural for your overall goals.

And no, it is definitely not the same efficiency. There are many, many SQL queries that cannot be written with the same efficiency in JQL vs direct SQL (when they can be implemented at all), not to mention the overhead (on every query) of conversion from JDBC ResultSets to nested lists of beans with various proxy classes.

Yes, offcourse but then you don't write JQLs you write direct SQL and then it doesn't matter what you use in the JPA entity.

On your question related to additional fields - in the context of a SelectItem, pickListFields can be used to show multiple fields from the related DataSource, and getSelectedRecord() can be used to get access to the entire related records (when there is a selection).

Ok I will test. Thanks.

[Isomorphic]

Yes, offcourse but then you don't write JQLs you write direct SQL and then it doesn't matter what you use in the JPA entity.

What may not be clear here is that we are talking about where real enterprise projects end up when they start with JPA - sooner or later, a need for direct SQL access is identified, whether it be for performance, simplicity, data migration, third party integration, access from another programming language, or whatever the issue is.

At that point it's very important that your JPA declarations map closely to the actual data you're storing and everything has clear names and appropriate types and that the developers have understood the SQL structure all along. If you have tried to let JPA "hide" the underlying details of the schema you're in a much worse position.

We've seen many enterprise teams go through this painful learning experience, and that's why we tell people to start with the much simpler, faster and more easily customized SQLDataSource and avoid the whole thing :) But if for whatever reason you *must* use JPA, the best practice we recommend is to know the underlying SQL and match your declarations to it closely.

[alius]

Hi,

Just committed transparent JPA relations support.

You can pick up it with next nightly build.

Check showcase for examples.

Best regards,
Alius