[Spring] ST_intersects method

Issue finding - Information gathering

Caused by: java.sql.SQLSyntaxErrorException: FUNCTION moyamo_db.intersects does not exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
        ... 179 common frames omitted

expected intersects function does not exists.

 

Details collision point tracking

public interface TagRepository extends JpaRepository<Tag, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select c from Comment c where c.id = :id")
    Optional<Tag> findLockOnly(@Param("id") long id);

    Optional<Tag> findByName(String name);

    @Query(value = "select t from Tag t where intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
    List<Tag> findByLocation(@Param("area") Geometry area);

    @Query(value = "select t.id from Tag t where intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
    List<Long> findIdByLocation(@Param("area") Geometry area);

	List<Tag> findByNameLike(String name, Pageable pageable);

    List<Tag> findByPlantId(Long plantId);

    @Modifying
    @Query(value = "delete from Tag t where t.plantId = :plantId")
    void deleteByPlantId(@Param("plantId") Long plantId);
}

it could be caused that There is no Funtion, called interesect after updating Aurora 5.7 to 8.0. Let's make function 

 

DELIMITER //

CREATE FUNCTION intersects(geom1 GEOMETRY, geom2 GEOMETRY) 
RETURNS BOOLEAN
BEGIN
    RETURN ST_Intersects(geom1, geom2);
END //

DELIMITER ;

 

And we will take Geometry spatial data.

 

Fix Query like below

@Query(value = "select t from Tag t where ST_Intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
List<Tag> findByLocation(@Param("area") Geometry area);

@Query(value = "select t.id from Tag t where ST_Intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
List<Long> findIdByLocation(@Param("area") Geometry area);