6.8.9.1 Using Bind Variables

There are two reasons for using bind variables:

• It protects against query injection.
• It speeds up query execution because the same bind variables can be set multiple times without requiring recompilation of the query.

To create a prepared statement, use one of the following two methods:

• PgxGraph.preparePgql(String query) : PgxPreparedStatement
• PgxSession.preparePgql(String query) : PgxPreparedStatement

The PgxPreparedStatement (package oracle.pgx.api) returned from these methods have setter methods for binding the bind variables to values of the designated data type.

Consider the following example:

PreparedStatement stmnt = g.preparePgql(
  "SELECT v.id, v.dob " +
  "FROM MATCH (v) " +
  "WHERE v.firstName = ? AND v.lastName = ?");
stmnt.setString(1, "Camille");
stmnt.setString(2, "Mullins");
ResultSet rs = stmnt.executeQuery();

Each bind variable in the query needs to be set to a value using one of the following setters of PgxPreparedStatement:

• setBoolean(int parameterIndex, boolean x)
• setDouble(int parameterIndex, double x)
• setFloat(int parameterIndex, float x)
• setInt(int parameterIndex, int x)
• setLong(int parameterIndex, long x)
• setDate(int parameterIndex, LocalDate x)
• setTime(int parameterIndex, LocalTime x)
• setTimestamp(int parameterIndex, LocalDateTime x)
• setTimeWithTimezone(int parameterIndex, OffsetTime x)
• setTimestampWithTimezone(int parameterIndex, OffsetDateTime x)
• setArray(int parameterIndex, List<?> x)

Once all the bind variables are set, the statement can be executed through:

• PgxPreparedStatement.executeQuery()
  • For SELECT queries only
  • Returns a ResultSet
• PgxPreparedStatement.execute()
  • For any type of statement
  • Returns a Boolean to indicate the form of the result: true in case of a SELECT query, false otherwise
  • In case of SELECT, the ResultSet can afterwards be accessed through PgxPreparedStatement.getResultSet()

In PGQL, bind variables can be used in place of literals of any data type, including array literals. An example query with a bind variable to is set to an instance of a String array is:

List<String> countryNames = new ArrayList<String>();
countryNames.add("Scotland");
countryNames.add("Tanzania");
countryNames.add("Serbia");

PreparedStatement stmnt = g.preparePgql(
  "SELECT n.name, n.population " +
  "FROM MATCH (c:Country) " +
  "WHERE c.name IN ?");

ResultSet rs = stmnt.executeQuery();

Finally, if a prepared statement is no longer needed, it is closed through PgxPreparedStatement.close() to free up resources.