org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Function "WITHIN" not found; SQL statement

Question

Here I am trying to find all those entities who are within the range I provide. I mean if I give a circle of certain radius, it will have to show all entities having location coordinates positioned inside the given circle.

I am using hibernate-spatial to achieve this. But getting the mentioned error in JPA Repository interface.

Here is the pom.xml,

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-entitymanager</artifactId>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-spatial</artifactId>
    <version>5.2.12.Final</version>
</dependency>
<dependency>
    <groupId>org.opengeo</groupId>
    <artifactId>geodb</artifactId>
    <version>${project.version}</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

Jpa Repository,

public interface ResourceRepository extends ExtendedJpaRepository<Resource, String> {   
    @Query(value = "select resource from Resource resource where within(resource.address.location, :circle) = true")
    List<Resource> test(@Param("circle") Geometry circle);
}

Resource.java,

@Entity
@NoArgsConstructor
public class Resource extends UUIDEntity2 implements IsResource {

    @Type(type = "org.hibernate.spatial.GeometryType")
    @OneToOne
    private Address address;

    /*getters setters*/
}

Address.java ,

@Entity
public class Address extends UUIDEntity2 implements HasEmailAddress, HasLocation {

    @Embedded
    @Column(columnDefinition = "point")
    private Location location;
    /*getters setters*/
}

location.java,

@Embeddable
@Value(staticConstructor = "of")
@RequiredArgsConstructor(staticName = "of")
public class Location implements Serializable {

    @Column(nullable = true)
    private Double lat;

    @Column(nullable = true)
    private Double lon;
}

test,

    @Inject
    private ResourceRepository resourceRepository;

    public Geometry createCircle(double x, double y, double radius) {
        GeometricShapeFactory shapeFactory = new GeometricShapeFactory();
        shapeFactory.setNumPoints(32);
        shapeFactory.setCentre(new Coordinate(x, y));
        shapeFactory.setSize(radius * 2);
        return shapeFactory.createCircle();
    }

    @Test
    public void geometry(){
        Geometry m = createCircle(0.0, 0.0, 5);
        List<Resource> resources = resourceRepository.test(m);
    }

application.properties,

hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect

NB: Entities' all properties are not shown here. Reference I am following: Hibernate-Spatial

Answer 1

Sounds like you don't have the SpatialDialect for MySQL configured. Can you verify if you have the line

hibernate.dialect=org.hibernate.spatial.dialect.mysql.MySQL56SpatialDialect

in the hibernate.properties file.

You could also check the logs to see what dialect is actually used by Hibernate. It should have 'Spatial' in the name for the Spatial functions to be available to hibernate.