HibernateDataSource best practices

[sunnyl]

SmartGWT Version: 3.0p Pro

I'm running through some use cases that my previous projects using LGPL had and trying to re-implement them in the Pro edition making use of the server side data integration features.

Example I'm trying achieve: two ListGrids, 1 showing a list of countries. When a country is selected, the database is interrogated for a list of country's cities to show in the second grid

Caveats:
1) Using HibernateDataSource purely as a learning experience, and I may have ties to Hibernate still in the future to avoid writing straight SQL in exceptional cases
2) The city grid I'm trying to request from the server rather than have the client side load the whole set and filter because I may be dealing with much larger datasets.

Here is a snippet from the GUI:

// tell the other grid to go load something
gridCountry.addRecordClickHandler(new RecordClickHandler() {
	@Override
	public void onRecordClick(RecordClickEvent event) {
		Criteria criteria = new Criteria();
		criteria.addCriteria("countryId", event.getRecord().getAttribute("id"));
		gridCity.fetchData(criteria);
	}
});

Unfortunately because I'm on Pro, I don't have customHQL to simply do this within the datasource (or am I wrong?). The approach I've taken is to:
1) The GUI passes in the countryId as criteria to gridCity's fetchData
2) DMI is used on the City datasource, which executes some handwritten DAO code to retrieve the relevant cities based on the countryId passed.

City DS:

<DataSource 
	ID="cityHibernate" 
	serverType="hibernate"
	autoDeriveSchema="true"
	schemaBean="test.shared.models.City" >
	
	<fields>
		<field name="country" type="countryHibernate" foreignKey="country.id"/>
	</fields>

	<!-- override fetch to select cities for a given country -->
	<operationBindings>
		<binding operationType="fetch" serverMethod="fetch">
			<serverObject lookupStyle="new" className="test.server.dmi.CityDMI" />
		</binding>
	</operationBindings>
	
</DataSource>

City DMI:

public DSResponse fetch(DSRequest dsRequest) throws Exception {
		
	Integer countryId = Integer.parseInt(((String)dsRequest.getCriteriaValue("countryId")));
	List<City> cities = CityDAO.getCitiesByCountryId(countryId);
		
	DSResponse response = new DSResponse();
	response.setStartRow(0);
	response.setEndRow(cities.size()-1);
	response.setTotalRows(cities.size());
	response.setStatus(DSResponse.STATUS_SUCCESS);
	response.setData(cities);
	return response;
}

City DAO:

public static List<City> getCitiesByCountryId(int countryId) throws Exception {
	Session session = getSession();
	String hql = "FROM City c WHERE c.country.id=:countryId ORDER BY c.name ASC";
	Query query = session.createQuery(hql);
	query = query.setInteger("countryId", countryId);
	return (List<City>)query.list();		
}

Country DS:

<DataSource 
	ID="countryHibernate" 
	serverType="hibernate"
	autoDeriveSchema="true" 
	schemaBean="test.shared.models.Country">

	<fields>
		<field name="cities" multiple="true" type="cityHibernate" foreignKey="city.id"/>
	</fields>

</DataSource>

This works and the city grid loads up with the correct cities when a country is selected.

Question:
In the logs I'm seeing:

=== 2011-12-29 15:24:39,934 [l0-2] WARN  BasicDataSource - Can not load related data source 'test.shared.models.Country' for field 'country'. Treating as simple field.
=== 2011-12-29 15:24:39,935 [l0-2] WARN  BasicDataSource - Can not load related data source 'country' for field 'country'. Treating as simple field.
=== 2011-12-29 15:24:39,935 [l0-2] WARN  BasicDataSource - Can not load related data source 'country' for field 'country'. Treating as simple field.
=== 2011-12-29 15:24:39,942 [l0-2] DEBUG XML - Parsed XML from C:\countryHibernate.ds.xml: 1ms
=== 2011-12-29 15:24:39,948 [l0-2] WARN  BasicDataSource - Can not load related data source 'java.util.Set' for field 'cities'. Treating as simple field.
=== 2011-12-29 15:24:39,948 [l0-2] WARN  BasicDataSource - Can not load related data source 'city' for field 'cities'. Treating as simple field.
=== 2011-12-29 15:24:39,949 [l0-2] WARN  BasicDataSource - Can not load related data source 'city' for field 'cities'. Treating as simple field.

...

=== 2011-12-29 15:24:45,068 [l0-2] WARN  DataSource - test.shared.models.Country contains a (potentially indirect) looping reference to itself.  Returning null for recursed value.

Are these valid concerns? I would've expected by explicitly defining the relationship in the ds.xml files there wouldn't be any problems.

I believe the last one is about the country having the cities which then have a country in it again, and can be avoided by removing "type" from either the city or country DS (http://www.smartclient.com/smartgwte...tegration.html)

Question:
Given the restriction of needing to use Hibernate and Pro edition, is this already the most code-less approach? How different might this be if it was a plain SQLDataSource?

[Isomorphic]

In 3.0, this can be done completely declaratively using DataSourceField.includeFrom. This also supports search and sort on the included field. See the samples in the Showcase. The same property works for SQL, Hibernate and JPA DataSources.

[sunnyl]

Thanks, I've gone through and looked at the functionality of includeFrom and was able to include a country name field into the city grid, very cool :)

I'm more curious about the mechanism that I've used to populate the city grid though (by using DMI and invoking manually written DAO code to retrieve the list of cities through a manually specified country ID in the criteria). Is there a declarative way of doing something like this in the Pro edition?

[Isomorphic]

As far as we can tell, that's what includeFrom does, but maybe you had something additional in mind?

You can of course always use a DMI to do operations on other DataSources, and combine them with the records from the default DSResponse in whatever way you want. It's just not necessary for the types of joins that are covered by includeFrom.

[sunnyl]

Hmm...I think we might not still be in sync or there's something else to includeFrom I haven't quite understood.

Attached is a screenshot of the test app.

The top grid is driven by country DS. The lower grid is driven by city DS. Using includeFrom:

<DataSource 
	ID="cityHibernate" 
	serverType="hibernate"
	autoDeriveSchema="true"
	schemaBean="test.shared.models.City" >
	
	<fields>
		<field name="countryName" includeFrom="countrySQL.name"/>
		<field name="country" foreignKey="countrySQL.id"/>
	</fields>

	<!-- override fetch to select cities for a given country -->
	<operationBindings>
		<binding operationType="fetch" serverMethod="fetch">
			<serverObject lookupStyle="new" className="test.server.dmi.CityDMI" />
		</binding>
	</operationBindings>
</DataSource>

<DataSource 
	ID="countryHibernate" 
	serverType="hibernate"
	autoDeriveSchema="true" 
	schemaBean="test.shared.models.Country">
	
</DataSource>

Using the includeFrom field in the city DS allows us to do a join to retrieve the country name from the country table and have it displayed in the city grid.

What I was referring to in my last post was actually the loading of the cities themselves - once the ID of a country is available after being clicked on in the country grid, is there a way (of using includeFrom?) to load all the country's cities into the city grid in a declarative way, while making the city DS make an explicit trip to the database to retrieve them, as opposed to using DMI and manually written DAOs to retrieve the cities from the database?

[Isomorphic]

Much much simpler than any of that: given a "countryHibernate" Record you have the country ID and can pass this as criteria to fetchData() to cause the related cities to be loaded.

This just relies on the existing behavior of a "fetch" operation for cities. No new server code is required.

[sunnyl]

Much much simpler than any of that: given a "countryHibernate" Record you have the country ID and can pass this as criteria to fetchData() to cause the related cities to be loaded.

This just relies on the existing behavior of a "fetch" operation for cities. No new server code is required.

hmm this approach worked when I was using SQLDataSource, but I haven't managed to get this working using HibernateDataSource yet. It appears as though I'm having trouble lining up the name of the criteria and what the datasource knows as the country ID field.

FYI, the city table has a field called country_id, and the City.hbm.xml file has the correct mapping set.

The id's I've tried include:

criteria.addCriteria("country_id", event.getRecord().getAttribute("id"));    // SQL table field name
criteria.addCriteria("countryid", event.getRecord().getAttribute("id"));
criteria.addCriteria("countryHibernate.id", event.getRecord().getAttribute("id"));
criteria.addCriteria("Country.id", event.getRecord().getAttribute("id"));

I'm seeing in the logs:

=== 2012-01-01 15:34:31,564 [0-11] WARN  DSRequest - In criteria, definition 'countryHibernate.id' refers to a field name ('id') that is already declared on this dataSource ('cityHibernate').  Ignoring this criteria entry.
=== 2012-01-01 15:34:31,565 [0-11] WARN  DSRequest - In criteria, definition 'Country.id' refers to a field name ('id') that is already declared on this dataSource ('cityHibernate').  Ignoring this criteria entry.
...
=== 2012-01-01 15:34:31,566 [0-11] WARN  HibernateDataSource - [builtinApplication.cityHibernate_fetch] Field name: 'country_id' specified in criteria is not defined in data source. Skipping.
=== 2012-01-01 15:34:31,566 [0-11] WARN  HibernateDataSource - [builtinApplication.cityHibernate_fetch] Field name: 'countryid' specified in criteria is not defined in data source. Skipping.

The end result here is as expected - the criteria contains no fields that the HiberateDataSource recognises and all cities are always returned for any country selected.

[Isomorphic]

The name of the field is simply whatever you named it in your Java bean. If you have eg getCountryId then it's countryId. Capitalization must match.

[sunnyl]

The name of the field is simply whatever you named it in your Java bean. If you have eg getCountryId then it's countryId. Capitalization must match.

I see, so the HibernateDataSource behaviour matches that of SQLDataSource.

I was hoping to see that datasources supported the POJOs that would've been created by Hibernate's codegen tools - specifically the beans that get created detect mappings in the database (fields of the generated bean below):

private Integer id;
private Country country;
private String name;

Is there any way for HibernateDataSource to take advantage of the Country field?

[Isomorphic]

Not sure what's not getting through..

In general, if you are creating Criteria, use the same field names as are declared in the DataSource (obviously).

So if you were filtering on the "countries" DataSource and you want to filter by the "id" field then the fieldName to use is "id" (obviously).

If you were filtering on the "cities" DataSource and you've used includeFrom to incorporate the "id" field from the "countries" DataSource, use the name of the field when you have "includeFrom". If you had a field "countryId" with includeFrom, then use "countryId" in the Criteria.