Using GET_WKT() in Mybatis returns null value

Question

I was trying to get spatial data from Oracle database with Mybatis in Springboot, when I use the function GET_WKT() the returned value is null but they should not be null.

This is the code for query:

@Select("SELECT T.OBJECT_ID," +
        "T.STRUCTURE_CODE," +
        "T.TYPE," +
        "T.NAME," +
        "T.LENGTH," +
        "T.WIDTH," +
        "T.HEIGHT," +
        "T.WEIGHT," +
        "T.REMARK," +
        "dbms_lob.substr(T.GEOM.GET_WKT(),4000)  " +
        "FROM JZW_BRIDGE_POINT T WHERE T.STRUCTURE_CODE = #{structureCode}")
Bridge getBridgeByStructureCode(@Param("structureCode") String structureCode);

This is the object used to hold the result:

public class Bridge {
private String objectId;
    private String structureCode;
    private String type;
    private String name;
    private Double length;
    private Double width;
    private Double height;
    private Double weight;
    private String remark;
    private Integer isDelete;
    private String geom;

getters and setters...

When I use the same SQL in the PL/SQL, it can return a proper result with the WKT returned as a string. I'm using OJDBC 8 and I used OJDBC 6, both of them doesn't work as I intended. Any help is appreciated.

Answer 1

There is no name for the resulting column specified so mybatis does not know what value from the result set should be put to geom column.

By default mybatis maps the column to the property (optionally mapping underscore names to camel case like STRUCTURE_CODE -> structureCode). But you query does not specify the name for the value returned by dbms_lob.substr(T.GEOM.GET_WKT(),4000) expression so it gets some name generated by oracle or jdbc driver and mybatis can't know that it should be put to some column in the result object.

Modify the query and specify the name for the result:

@Select("SELECT T.OBJECT_ID," +
        "T.STRUCTURE_CODE," +
        "T.TYPE," +
        "T.NAME," +
        "T.LENGTH," +
        "T.WIDTH," +
        "T.HEIGHT," +
        "T.WEIGHT," +
        "T.REMARK," +
        "dbms_lob.substr(T.GEOM.GET_WKT(),4000) geom " +
        "FROM JZW_BRIDGE_POINT T WHERE T.STRUCTURE_CODE = #{structureCode}")
Bridge getBridgeByStructureCode(@Param("structureCode") String structureCode);