StandardSQLFunction bindable parameter in hibernate 6.x

Hibernate ORM
1

Hi,
so in hibernate 5.x ve had following function.

private static class OracleFastSdoRelate extends StandardSQLFunction {
        private final String mask;

        OracleFastSdoRelate(final String mask) {
            super("SDO_RELATE", StandardBasicTypes.STRING);
            this.mask = mask;
        }

        @Override
        public String render(final Type firstArgumentType,
                             final List arguments,
                             final SessionFactoryImplementor sessionFactory) {
            return "SDO_RELATE(" + arguments.get(0) + ", " + arguments.get(1) + ", 'mask=" + mask + "')";
        }
    }

now in hibernate 6.x I rewrote it to following

public class OracleFastSdoRelate extends StandardSQLFunction {

    private final String mask;

    public OracleFastSdoRelate(final String mask) {
        super("SDO_RELATE", StandardBasicTypes.STRING);
        this.mask = mask;
    }

    @Override
    public void render(
        final SqlAppender sqlAppender,
        final List<? extends SqlAstNode> sqlAstArguments,
        final ReturnableType<?> returnType,
        final SqlAstTranslator<?> translator
    ) {
        sqlAppender.append("SDO_RELATE(");
        sqlAstArguments.get(0).accept(translator);
        sqlAppender.append(',');
        sqlAstArguments.get(1).accept(translator);
        sqlAppender.append(", 'mask=%s')".formatted(mask));
    }
}

However the problem is that it directly generates the second argument to the query which in some cases is too long. Is there any way how to tell function that it should be binded with ? (quotation mark)?

so something like SDO_RELATE(some_column, ?, 'mask=test')

THANKS

2

Theoretically, you could do that, but I would not recommend you that route. Simply change your HQL query to not use a literal, but a parameter instead.

3

@beikov the problem is that it was working without any issues in 5.x version, but now we are getting ORA-01704: string literal too long ] [n/a]; SQL [n/a]? Is there a better solution then ParameterExpression?

And regarding ParameterExpression can you please advice how should I change that?

this is original code

public static Predicate withinGeometry(
        final CriteriaBuilder cb,
        final Path<? extends Geometry> containedGeom,
        final Expression<? extends Geometry> containingGeom
    ) {
        return cb.equal(
            cb.function(
                CustomDatabaseFunctionNames.SPATIAL_WITHIN,
                String.class,
                containedGeom,
                containingGeom
            ),
            "TRUE"
        );
    }

I’ve tried this, but obviously it’s not correct.

    public static Predicate withinGeometry(
        final CriteriaBuilder cb,
        final Path<? extends Geometry> containedGeom,
        final Expression<? extends Geometry> containingGeom
    ) {
        final ParameterExpression<? extends Geometry> geoParam = cb.parameter(Geometry.class);

        return cb.and(
            cb.equal(
                cb.function(
                    CustomDatabaseFunctionNames.SPATIAL_WITHIN,
                    String.class,
                    containedGeom,
                    geoParam
                ),
                "TRUE"
            ),
            cb.equal(geoParam, containingGeom)
        );
    }

thanks

4

Just use the Hibernate ORM Spatial extension.

SpatialCriteriaBuilder cb = session.getCriteriaBuilder().unwrap(SpatialCriteriaBuilder.class);
cb.within(containedGeom, containingGeom);

I don’t know what this SQL error is that you’re referring to, but as long as you’re not using CriteriaBuilder.literal() to construct these Geometry expressions, you should be fine. Make sure you didn’t set the hibernate.criteria.value_handling_mode to INLINE.

5

@beikov
thanks for suggestion. The only thing which is not working is
SpatialCriteriaBuilder cb = session.getCriteriaBuilder().unwrap(SpatialCriteriaBuilder.class); since it returns null

Currently there are two interfaces

"interface org.hibernate.spatial.criteria.GeolatteSpatialCriteriaBuilder"
"interface org.hibernate.spatial.criteria.JTSSpatialCriteriaBuilder"

So I’ve tried to get JTSSpatialCriteriaBuilder instead…is that a correct approach?

I have to test also performance for oracle because I’ve seen some comment in code from my coworker which worries me:

on oracle we have to call a "within" method that is not set up as a boolean-return-function otherwise
hibernate will generate a case-when expression which is ~10x slower than the="TRUE" variant.

do you think this is still a case?

Thanks

6

Now I see what is the problem. Specifications doesn’t contain st_relate function for some reason (maybe because it doesn’t work for mysql?) and for oracle we are using SDO_RELATE.
So back to my question - how am I supposed to pass Geometry as a ParameterExpression instead of literal?

7

Looking into within implementation helped, seems like following is working

final Session session = entityManager.unwrap(Session.class);
final JTSSpatialCriteriaBuilder spatialCB = session
        .getCriteriaBuilder()
        .unwrap(JTSSpatialCriteriaBuilder.class);

//... few millions lines later

return cb.and(SpatialSpecifications.withinGeometry(
                        cb,
                        intelligenceCenterPath,
                        **spatialCB.value(differencePolygon)**
                    ));
8

Great that you figured it out. I indeed forgot that one has to specify the concrete spatial type to use i.e. JTSSpatialCriteriaBuilder vs. GeolatteSpatialCriteriaBuilder for JTS vs. Geolatte specific types.

Explicitly using value is not necessary with the builtin functions on SpatialCriteriaBuilder, but you seem to need special spatial functions. If you think something is missing, please create a Jira issue with a feature/enhancement request.