Hi,
I am trying to achieve two related things at once:
First I would like to be able to cast something to numeric/decimal but without any precision in HQL. Let’s say I am manipulating a parameter of type string in my query and I want to cast it as a number, but without involving any precision. If I use cast(:param as big_decimal) for example, I get the default precision of 38,2 (which I would prefer not to change, because other things are relying on it).
Second I would like to be able to use this HQL also as SQL with PostgreSQL, so I was hoping to manage to configure my Dialect by adding any jdbctype, javatype and/or customtype/usertype that would allow me to always write: cast(xxx as numeric) and have it be understood by Hibernate as the bare numeric type.
With Hibernate 5, I was able to achieve this by hacking around by basically adding my own custom jdbc type, mapped to numeric (without the ($p, $s) that we can find for the definition of Types.NUMERIC) and adding a StandardBasicTypeTemplate for BigDecimal and NumericJdbcType (where I would override the sql type to my custom type):
private void registerNumericWithoutPrecision(TypeContributions typeContributions) {
// we define our own custom jdbc type to bridge between HQL and SQL
var customTypeNumericNoPrecision = 12345;
try {
// this is just to ensure we are not overriding an existing type:
// if this happens, then we can just choose a different value for the custom type.
throw new IllegalStateException(getTypeName(customTypeNumericNoPrecision));
} catch (MappingException e) {
// this is expected: the mapping does not exist
}
// we map it to PostgreSQL's numeric without any precision nor scale:
// in the parent class, Types.NUMERIC is mapped to numeric($p, $s)
// and this is not something we want when using bare "numeric" as a type in HQL
registerColumnType(customTypeNumericNoPrecision, "numeric");
// we map it to the name "numeric" in HQL and the type BigDecimal in Java
typeContributions.contributeType(
new StandardBasicTypeTemplate<>(
new NumericTypeDescriptor() {
@Override
public int getSqlType() {
return customTypeNumericNoPrecision;
}
},
BigDecimalTypeDescriptor.INSTANCE,
"numeric"
)
);
}
Now with version 6.4, this does not work and I am struggling to find an alternative way of achieving this. Even achieving the first objective seems to not be possible but I feel like I’m missing something obvious maybe.
Thanks for any help
