Datasource Programming: New SQL Classes - Back End

Sql

By convention in this document, Sql will refer to the class com.archibus.db.Sql and SQL will refer to the statement sent to the database management system (DBMS) to request or update data. In general Sql represents SQL with any associated bind variable values and replaces the use of java.lang.String for this purpose.

  • A Sql object is assumed to be free from SQL Injection.

  • Sql is immutable, thread safe, and can be used as a constant or template.

  • Sql is built from query fragments. This means appending Sql to Sql preserves the association of bind variable values with their placeholders in a query fragment, and a query fragment can be used multiple times in multiple queries.

  • Sql validates inputs, checking the type of values against an allowed list and the format of field names when the query is constructed, in many case producing an error closer to the cause.

  • Underlying core functionality will always use prepared statements to execute Sql, even if there are no bind variables.

Fragmenting

A query like this:

  • SELECT ‘A’, user_name, ${user.name} FROM afm.afm_flds

Might be fragmented like this, separating OGNL, literal text and DB ids from the query.

  • [SELECT ][‘A’][, ][user_name][, ][${user.name}][ FROM ][afm.afm_flds]

Results in:

  • SELECT ‘A’, [user_name], ? FROM [afm].[afm_flds]

Upgrade Caveats

  • Some methods that used to return String now return Sql. Custom code using these methods should handle Sql objects. For many of these methods, the old version of the method exists with a suffix “Unsafe”, but these methods and others have been deprecated.

  • Use of com.archibus.db.DbValueConversionUtil is recommended for converting strings to valid bind variable types. BigInteger and BigDecimal are supported.

Examples

Simplest

Sql sql = new Sql(“SELECT field FROM table WHERE pkey=1”)

SqlUtils.executeQuery(tableName, fieldNames, sql); // Executes SQL query

With Bind Variables

new Sql(“SELECT f FROM t WHERE p1=? AND p2=?”, pkValue1, pkValue2)

This accepts an indefinite number of bind variables, but there must be equally as many ? in the query outside of quotes.

Bind Variable values must be of Java type: String, Character, Number, Date, Enum, Boolean, or InputStream.

  • Recommend against using Boolean.

  • Recommend against using oracle.sql.DATE, use Date.

  • java.sql.Date and java.sql.Time are subclasses of java.util.Date.

  • Recommend using com.archibus.db.DbValueConversionUtil to parse values.

With Binding Sources (OGNL Notation)

new Sql(“SELECT ‘A’ ${sql.concat} ‘B’”, OgnlNotation.AXVW)

This query will be parsed into fragments, separating the OGNL and quoted text from SQL. You can specify one OgnlNotation. The default OgnlNotation is JSP. AXVW notation is what is commonly used in data sources and user inputs. OGNL is used internally.

OGNL Notations examples

OgnlNotation.AXVW ${sql.concat}
OgnlNotation.JSP %{sql.concat}
OgnlNotation.OGNL #{sql.concat}

Note: Expressions like ${user.name} and ${role.name} will use bind variables. so, you must not wrap them in quotes.

Note: If you call Sql.buildQuery() without first processing the binding sources, you should expect OGNL notation in the resulting string. Sql will use OgnlNotation.OGNL internally.

Sql.Builder

Construct an object of class com.archibus.Sql.Builder to build Sql efficiently within a thread. It provides the ability to construct Parameterized Sql without frequently allocating memory for SQL strings as fragments are appended.

Construction:

new Sql.Builder(“SELECT “)

.field(fieldName)

.append(“ FROM “)

.table(tableName)

.append(“ WHERE “)

.field(pkFieldName)

.append(“=”)

.literal(pkValue)

.toSql();

  • new Sql.Builder(“SELECT “) : Create a new Sql.Builder starting the query. Can start from a String or Sql.

  • field(fieldName) : Add a field name. It’ll also accept a collection of names which will be comma delimited in the query.

  • append(“ FROM “) : Add more literal SQL. You can also call append() with another Sql or Sql.Builder object to avoid parsing and reuse SQL. You can optionally specify OgnlNotation to control how expressions in the string are parsed. Different calls to append for the same Sql object may use different notation.

  • literal(pkValue) : Add a literal value. It will be added to the query as a ? and a bind variable will be added for pkValue. It will not accept ? as a value or a value beginning and ending with single quotes to prevent developer errors. It will accept valid bind variable types. Like field() and alias(), it’ll also accept a list of values or Sql which will be comma delimited. Note: this is functionally equivalent to append(“?”,pkValue).

  • toSql() : Build a Sql object from the Sql.Builder

Example

new Sql.Builder(“UPDATE ”)

.table(WR_SYNC_TABLE).append(“ SET “)

.append("wr_sync.is_req_supervisor=1,")

.append(“ FROM “)

.append(WR_TABLE)

.append(“ WHERE ”)

.append(" wr.supervisor=").literal(emId) // emId is a literal value

.toSql();

SqlTemplates

com.archibus.db.SqlTemplates, Provides templates for some SQL patterns.

new Sql(“SELECT #{0} FROM #{1} WHERE #{2}”, OgnlNotation.OGNL).formatTemplate(sql1, sql2, sql3);

Will take any number of Sql parameters.

Can be mixed with bind variables and OGNL expressions.

SQL fragments aren’t concatenated until the query is built.

Example

Sql sql = new Sql("UPDATE WR_SYNC_TABLE SET wr_sync.is_req_supervisor = 0

WHERE wr.supervisor = #{0}", OgnlNotation.OGNL).formatTemplate(Sql.valueOfLiteral(emId));

SqlUtils.executeUpdate(“WR_SYNC_TABLE”, sql);

Blobs

com.archibus.db.BlobStreamListener is now used by APIs for reading InputStreams for blobs (mostly documents). It offers a method useResults (InputStream inputStream) to implement to read the stream while the record is retrieved.