Advanced selection binding

Assume the following query as a starting point:

Town town = query.from(Town.class);
TownProperty dateProp = query.join(town.getProperties(), JoinType.Left);
query.getJoin(dateProp).with(dateProp.getPropertyKey()).eq("LastUfoSpottingDate");
        
TypeSafeSubQuery<Long> cntInhabitantsSQ = query.subquery(Long.class);
Person inhabitant = cntInhabitantsSQ.from(Person.class);
cntInhabitantsSQ.where(inhabitant.getTown().getId()).eq(town.getId());
cntInhabitantsSQ.select(cntInhabitantsSQ.function().count());

TownDetailsDto dto = query.select(TownDetailsDto.class);

Selecting values into the dto with various methods:

Select a subselected value into a dto property

dto.setInhabitants(cntInhabitantsSQ.select())

This works because a subquery is a TypeSafeValue, and the select() method registers the value into the query so that when the setter is called, the selected value is bound to it.

Select an embeddable property value into a nested dto property

dto.getNestedDto().setLattitude(town.getGeographicCoordinate().getLattitude());

The geographic coordinate has an @Embeddable annotation, this means hibernate will add its properties directly to the table in which it is embedded. Using the getters in a chain will work as expected though. TSQB knows no extra join is needed because it asks hibernate if a type is a composite type.

The nested dto may group some fields and calling the getter of the dto will return a proxy and it can be used like any other selection dto.

Select the upper case name into the dto

dto.setName(query.function().upper(town.getName()).select())

Some standard functions are available in the TSQB through query.function(), a function will return a TypeSafeValue and calling select() on this will return a dummy object of the wrapped type while registering the value into the query which is then used when setName(...) is invoked.

The upper(...) will be found in the constructed hql query as it is a dialect function, applied in the query string.

select a value of a different type using a converter to convert the selected value

dto.setLastUfoSpottingDate(query.select(Date.class, dateProp, new StringToDateTransformer(DateFormat.getDateInstance()))

When the type of the column doesn't match the type on the selection dto, it is possible to use a SelectionValueTransformer to convert the value. When using this, no extra fields need to be added to the selection dto to capture the 'wrong' type first. It also saves you from having to write an extra for loop after the data has been selected and transformed into dtos.

When no function is available and the value can't be retrieved another way it's still possible to just inject hql

dto.setCustomString(new CustomTypeSafeValue<>(query, String.class, "'SomeCustomHql'", Arrays.asList()).select())

It doesn't look pretty, and it isn't supposed to. This may be useful to provide a helper class containing some functions which have been added in your dialect. Then they can be used in a more elegant way.

The final resulting query would look like this:

select 
  (select count(*) from Person hobj3 where hobj3.town.id = hobj1.id) as inhabitants,
  hobj1.geographicCoordinate.lattitude as nestedDto_lattitude, 
  upper(hobj1.name) as name, 
  hobj2.propertyValue as lastUfoSpottingDate, 
  'SomeCustomHql' as customString 
from Town hobj1 left join hobj1.properties hobj2 with hobj2.propertyKey = ?
// with params: [LastUfoSpottingDate]