Point Geo Fencing - Sample Code

Point Geo Fencing is easier than polygon Geo Fencing.

As described in my first blog (read it here), you just have to calculate the distance between the point and your current location, and compare it with the radius of your point (circle).

First we need an object for representing our Point Geo Fence:


When your radius is defined in meters, you will need the Haversine formula.  This formula will calculate the distance between two points (in meters) while taking into account the earth curvation:


Now, we can calculate if we're inside the Geo Fence or not:


X and Y are your current longitude and -latitude.
The method will return true, when we're inside the Geo Fence and false when we're not.

More information can be found here:
- http://en.wikipedia.org/wiki/Haversine_formula
- http://stefanbangels.blogspot.be/2012/12/for-several-years-now-i-have-been.html
- http://stefanbangels.blogspot.nl/2013/10/geo-fencing-sample-code.html

Named placeholders for SQL parameters

JDBC is great.

But there seems to be a useful feature missing:

"Named placeholders for SQL parameters"

PreparedStatement provides a precompiled Statement that can be executed multiple times. The statement can be created by calling the factory method on the java.sql.Connection class:

public PreparedStatement prepareStatement(String sql) 
  throws SQLException;

This method takes an SQL String as an argument, which can contain (?) place holders, for example:

"SELECT name FROM users WHERE id = ?"

You can change the value of the place holders, by parameter index using methods:

public void setString(int parameterIndex, String theString)
  throws SQLException;

public void setLong(int parameterIndex, long theLong) 
  throws SQLException;

...

Very nice indeed.

But shouldn't it be easier if we (additionally) could use NAMED placeholders like this:

"SELECT name FROM users WHERE id = ?{ID}"

And set the place holders values like this:

public void setString(String parameterName, String theString)
  throws SQLException;

public void setLong(String parameterName, int theLong) 
  throws SQLException;

...

Using indexes is error prone. You often need to re-count which index number is used for what parameter. Especially when you're refactoring, bug-fixing or implementing new functionality. Also, when using parameter indexes, you sometimes need to duplicate parameter values, which makes it even less readable.

In my projects, I prefer using my own helper class for adding functionality to Prepared Statements.

Some sample code:

public class PreparedStatementTemplate {

    private enum ParameterType {LONG, STRING}

    private final Pattern placeHolderPattern = Pattern.compile(
        "(\\?\\{([A-Za-z0-9]*)\\})"
    );

    private final PreparedStatement preparedStatement;

    private List<String> placeHolders;

    public PreparedStatementTemplate(
      Connection connection, String sql) throws SQLException {
        this.preparedStatement = connection.prepareStatement(
            extractPlaceHolders(sql)
        );
    }

    private String extractPlaceHolders(String sql) {
        List<String> placeHolders = new LinkedList<String>();
        Matcher matcher = placeHolderPattern.matcher(sql);
        while (matcher.find()) {
            placeHolders.add(matcher.group(2));
        }
        this.placeHolders = placeHolders;
        return matcher.replaceAll("?");
    }

    public void setString(String key, String value) throws SQLException {
        setParameterValue(key, value, ParameterType.STRING);
    }

    public void setLong(String key, long value) throws SQLException {
        setParameterValue(key, value, ParameterType.LONG);
    }
    private void setParameterValue(
        String key, Object value, ParameterType type) throws SQLException {
        for (Integer index : findPlaceHolderIndexes(key)) {
            if (type == ParameterType.LONG) {
                preparedStatement.setLong(index, (Long) value);
            } else if (type == ParameterType.STRING) {
                preparedStatement.setString(index, (String) value);
            } else {
                throw new SQLException("unknown parameter type: " + type);
            }
        }
    }

    public List<Integer> findPlaceHolderIndexes(String key) {
        List<Integer> indexes = new LinkedList<Integer>();
        int n = 1;
        for (String placeHolder : placeHolders) {
            if (key.equalsIgnoreCase(placeHolder)) {
                indexes.add(n);
            }
            n++;
        }
        return indexes;
    }

    public ResultSet executeQuery() throws SQLException {
        return preparedStatement.executeQuery();
    }

    public int executeUpdate() throws SQLException {
        return preparedStatement.executeUpdate();
    }

    public void close() throws SQLException {
        preparedStatement.close();
    }

}

You can use it like this:

PreparedStatementTemplate template = new PreparedStatementTemplate(
    connection, "SELECT name FROM users WHERE id = ?{ID}"
);
try {
    template.setLong("ID", userId);
    ResultSet rs = template.executeQuery();
    try {
        while (rs.next()) {
            System.out.println(rs.getString("NAME"));
        }
    } finally {
        rs.close();
    }
} finally {
    template.close();
}
        
Why isn't this feature embedded in the standard JDBC?  It would make life much easier!

More information about JDBC Prepared Statements can be found here:
http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
http://www.tutorialspoint.com/jdbc/jdbc-statements.htm