Skip to content

Latest commit

 

History

History
264 lines (190 loc) · 10.5 KB

nitro-dynamic-sql.md

File metadata and controls

264 lines (190 loc) · 10.5 KB
Raw

Dynamic SQL

Dynamic SQL can be used in all Nitro queries to include or exclude fragments of the SQL query at runtime based on the parameter values. In short, all <select> and <query> tags can include Dynamic SQL.

A typical use of Dynamic SQL is to include or exclude sections of a query based on parameter values. For example:

<select method="getVIPProviders" vo="ProviderVO">
  <parameter name="branchId" java-type="Long" />
    select * from providers
    where type = 'VIP
    <if test="branchId != null">
      and branch_id = #{branchId}
    </if>
</select>

In this case the segment and branch_id = #{branchId} will be included only when the parameter branchId is not null.

Dynamic SQL can alter the SQL fragments by applying or injecting parameter values, by trimming sections of them, or by replicating them with iterators.

The design of the Dynamic SQL tags was influenced by the MyBatis framework (see MyBatis Dynamic SQL) but HotRod models it independently, so any generator or underlying JDBC technology can use it.

Applying Parameters vs Injecting Parameters

Applying parameters is the safe way of using a parameter in a SQL query and is implemented using the #{param} sequence. This is the recommended way of using parameters that is known as prepared statements in many programming languages.

Injecting parameters – essentially concatenating parameters to the query as strings values – is an alternative way of using parameters that may be susceptible to SQL Injection. It's implemented using the ${param} sequence. Notice the small syntax difference.

Depending on the specifics of a query, sometimes it's not possible to apply parameters but only to inject them as strings. Parameter injection should be avoided if possible; if unavoidable, it needs to be considered with extreme care to make sure the parameters are not coming unfiltered from an external source such as a browser or an externally exposed API.

OGNL - The Ogonal Engine

Dynamic SQL decides to include or exclude SQL fragments based on boolean logic evaluated at runtime according to the supplied parameters. The OGNL expression language is described at the OGNL Engine.

For example, the following expressions are written in OGNL:

  • name != null
  • phase == 'C' or amount > minAmount
  • orderDate != null ? status in (1, 3, 4) : status in (null, 2)

Typically they need to evaluate to a boolean value – either true or false– since they are commonly used to decide if a query segment should be included or not in the resulting SQL statement. However, they can evaluate to any Java type, as needed: for example, the <bind> tag can use any resulting type. In the examples above, the variables such as name, phase, amount, etc. correspond to runtime parameters of the query, specified using <parameter> tags.

The <if> Tag

The <if> tag includes the inner SQL segment depending on the value test condition evaluated at runtime. If it evaluates to true the inner segment is included; otherwise it's ignored.

For example:

<select method="searchPendingOrders" vo="OrderVO">
  <parameter name="minPrice" java-type="Double" />
  select * from orders
  where status = 'PENDING'
  <if test="minPrice != null">and order_price >= #{minPrice}</if>
</select>

The above query searches for pending orders. If the supplied parameter minPrice is not null, the query adds the extra condition and order_price >= #{minPrice} to the search predicate that, otherwise, won't be included.

The <choose>, <when>, and <otherwise> Tags

The <choose> tag is a variation of the <if> tag that allows multiple exclusive conditions to be evaluated sequentially. Each fragment is enclosed in a <when> tag that includes a condition. The first <when> tag with a matching condition is selected and its SQL fragment is added to the SQL statement. The remaining fragments are not evaluated. If no <when> tag is selected, the <otherwise> segment is selected, if present.

For example:

<select method="searchPendingOrders" vo="OrderVO">
  <parameter name="searchType" java-type="String" />
  <parameter name="minPrice" java-type="Double" />
  <parameter name="orderDate" java-type="java.util.Date" />
  select * from orders
  where status = 'PENDING'
  <choose>
    <when test="type == 'PRICE'">and order_price >= #{minPrice}</when>
    <when test="type == 'DATE'">and order_date = #{orderDate}</when>
    <otherwise>and channel = 'ONLINE'</otherwise>
  </choose>
</select>

The above query searches for pending orders. If the type parameter has the value PRICE it uses the minPrice parameter to search for orders; otherwise, if the type parameter has the value DATE it uses the orderDate parameter to search for orders; if none of these options are used it defaults to searching by channel = 'ONLINE'.

The <where> Tag

The <where> tag encloses multiple inner tags. If at least one of them is included it does two things:

  • It prepends the whole section with a WHERE clause.
  • It removes any AND or OR from the first selected inner fragment. Therefore, if multiple fragments are included they will render appropriately.

For example:

  <select method="searchInvoices" vo="InvoiceVO">
    <parameter name="branchId" java-type="Integer" />
    <parameter name="clientId" java-type="Integer" />
    <parameter name="minAmount" java-type="Double" />
    select * from invoice
    <where>
      <if test="branchID != null">and branch_id = #{branchId}</when>
      <if test="clientID != null">and client_id = #{clientId}</when>
      <if test="minAmount != null">and amount >= #{minAmount}</when>
    </where>
  </select>

If the caller supplies the values (branchId = 301, clientId = null, minAmount = 20) the query will be assembled as:

select * from invoice
where
  branch_id = 301
  and amount >= 20

Notice:

  • The WHERE clause was included, since at least one inner tag was included.
  • The AND in and branch_id = 301 was removed, since this is the first included tag.
  • The AND in and amount >= 20 was not removed, since this is not the first included tag.
  • The second inner tag was not included, since its condition was not met.

The <set> Tag

The <set> tag has a very similar functionality as the <where> tag but it's tailored for UPDATE SQL statements.

If at least one of the inner fragments is included it does two things:

  • It prepends the whole section with a SET clause.
  • It removes any , from the first selected inner fragment. Therefore, if multiple fragments are included they will render appropriately.

For example:

<query method="markOutstandingInvoices">
  <parameter name="newStatus" java-type="String" />
  <parameter name="dueDate" java-type="java.util.Date" />
  update invoice
  <set>
    <if test="newStatus != null">, invoice_status = #{newStatus}</when>
    <if test="dueDate != null">, invoice_due_date = #{dueDate}</when>
  </set>
  where total_amount_due > amount_paid      
</query>

If the caller supplies the values (newStatus = null, dueDate = 2020-12-01) the query will be assembled as:

update invoice
set
  invoice_due_date = '2020-12-01'
where total_amount_due > amount_paid

Notice that:

  • The SET clause was included, since at least one inner tag was included.
  • The , in , invoice_due_date = '2020-12-01' was removed, since this is the first included tag.
  • The first inner tag was not included, since its condition was not met.

The <trim> Tag

The <trim> tag is a generic form of the <where> and <set> tags. The developer can use the generic form to specify:

  • The prefix to prepend to the whole fragment, when at least one inner fragment is included.
  • The suffix to append to the whole fragment, when at least one inner fragment is included.
  • The prefixOverrides indicates the prefixes to remove from the first selected inner fragment; the list is separated by the | (pipe) character.
  • The suffixOverrides indicates the suffixes to remove from the last selected inner fragment; the list is separated by the | (pipe) character.

Therefore:

  • A <trim prefix='WHERE' prefixOverrifes="AND|OR"> tag is equivalent to a <where> tag.
  • A <trim prefix='SET' prefixOverrifes=","> tag is equivalent to a <set> tag.

The <foreach> Tag

The <foreach> tag iterated over a java.util.Collection or an array and includes the inner SQL fragment once for each iterated element.

For example:

<select method="findEmployees" vo="EmployeeVO">
  <parameter name="ids" java-type="java.util.List&lt;Integer>" jdbc-type="NUMERIC" />
  <parameter name="names" java-type="java.util.List&lt;String>" jdbc-type="VARCHAR" />
  select *
  from employee
  <complement>
    where branch_id in
    <foreach item="id" collection="ids" open="(" separator=", " close=")">
      #{id}
    </foreach>
    or name in
    <foreach item="name" collection="names" open="(" separator=", " close=")">
      #{name}
    </foreach>
  </complement>
</select>

Depending on the specific parameters the query will change. If the first list has three values and the second one two, the query will be assembled as:

select * from employee
where branch_id in (?, ?, ?)
  and name in (?, ?)

The parameters that will be applied to the query could in this case be:

101 (Integer), 102 (Integer), 200 (Integer), Alice (String), Steve (String)

These parameter values can be displayed by enabling the DEBUG level in the logging of the query.

Finally, there's of course a performance penalty when using large collections or arrays. Also, in the case of large collections or arrays, some database engines and JDBC drivers may place a limit in the size of the SQL statement. Most database engines will accept 1000-character long SQL statements, but may reject 10000-character long SQL statements.

The <bind> Tag

The <bind> tag allows the developer to set temporary variables in the Dynamic SQL scope that can help the writing of complex expressions.

For example:

<select method="findClientsByPartialName" vo="ClientVO">
  <parameter name="partialName" java-type="String" />
  <bind name="namePattern" value="'%' || partialName || '%'" />
  select * from client
  where name like #{namePattern}
</select>

If the supplied parameters at runtime are (partialName = "smith") the query will be assembled as:

select * from client
where name like '%smith%'