NamedParameterJdbcTemplate and Function Call

[Sebastian Janisch]

Hey guys,

I am facing a little problem, which I think is owing to the underlying prepared statement of the jdbc template.

here is what i want to do.

    private static final String UPDATE_QUERY = "UPDATE booking_credit_tours SET value = :value, currency = :currency, location = :location WHERE id_booking = :id";

    @Override
    public void update(BookingCreditTourDO bookingCreditTour)
    {
        assert (bookingCreditTour != null) : "Precondition violated: bookingCreditTour != null";
        assert (bookingCreditTour.getBookingId() != null) : "Precondition violated: bookingCreditTour.getBookingId() != null";

        EnhancedMapSqlParameterSource params = new EnhancedMapSqlParameterSource("id", bookingCreditTour.getBookingId());
        params.addValue("pin", bookingCreditTour.getPin());
        params.addValue("value", bookingCreditTour.getValue());
        params.addValue("currency", bookingCreditTour.getCurrency());
        params.addValue("location", bookingCreditTour.getLocation());
        
        int modifiedRows = getNamedParameterJdbcTemplate().update(UPDATE_QUERY, params);

        assert (modifiedRows == 1) : "Postcondition violated: modifiedRows == 1";
    }

The tricky part is the location, which is a longitude latitude pair which must be interpreted as a postgis geometry.

what the underlying implementation of params.addValue("location", bookingCreditTour.getLocation()) does is the following:

    public EnhancedMapSqlParameterSource addValue(String paramName, LocationDV dv)
    {
        String value = dv == null ? null : "ST_MakePoint( " + dv.getLongitude().getValue() + ", " + dv.getLatitude().getValue() + ")";
        return (EnhancedMapSqlParameterSource) super.addValue(paramName, value);
    }

So the addValue method turns the LocationDV into a String ST_MakePoint(longitude, latitude).

Of course, the ST_MakePoint is a postgis function that needs to be evaluated on the db-server, and i think this is where the problem lies, as this does not go together with prepared statements (or at least i don't know how to).

I could turn the sql query into

UPDATE booking_credit_tours SET value = :value, currency = :currency, location = ST_MakePoint(:locationLon, :locationLat) WHERE id_booking = :id

but I would really like to encapsulate this away (especially as longitude and latitude tend to get mixed up).

In essence, the question is if there is a way to include this ST_MakePoint function call into the prepared statement.


Thanks for your help