Example 14 - Dynamic SQL

This example shows how to run dynamic SQL statements.

Dynamic SQL is a powerful extension provided by the MyBatis persistence layer. It can dramatically reduce the effort of developing and debugging complex SQL statements that vary depending on the runtime parameter values, all without writing any Java code.

Dynamic SQL shares the syntax with Regular SQL and Native SQL. They can be combined all at once when creating SQL statements.

SQL selects can include <column> tags to force the Java name and Java type of the result set. When included they need to be included outside the CDATA section, if any.

Example Source Code

package examples;

import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import daos.SearchedClient;
import daos.primitives.GeneralOperations;

/**
 * Example 14 - Dynamic SQL
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example14 {

  public static void main(String[] args) throws SQLException {

    int rows;

    System.out.println("=== Running Example 14 - Dynamic SQL ===");

    // 1. Searching by Combined Conditions

    // Example A: search clients from the VA state, created since 2017-01-13

    Date since = Date.valueOf("2017-01-13");
    List<SearchedClient> c1 = SearchedClient.select(null, "VA", since);
    System.out.println(" ");
    System.out.println("1.A. Searching by Combined Conditions (state. since) - rows=" + c1.size());
    for (SearchedClient c : c1) {
      System.out.println(" Client ID=" + c.getId());
    }

    // Example B: search clients with a minimum of 1 purchase, created since
    // 2017-01-13

    List<SearchedClient> c2 = SearchedClient.select(1, null, since);
    System.out.println(" ");
    System.out.println("1.B. Searching by Combined Conditions (#purchases, since) - rows=" + c2.size());
    for (SearchedClient c : c2) {
      System.out.println(" Client ID=" + c.getId());
    }

    // 2. Dynamic SQL - Update by Combined Conditions

    // Example A: Discount $100 on Price of Any Unsold Vehicle with 50000 miles
    // or more

    rows = GeneralOperations.applyDiscountToVehicles(100, true, 50000, null);
    System.out.println(" ");
    System.out.println("2.A. Update by Combined Conditions (unsold, mileage) - rows=" + rows);

    // Example B: Discount $500 on Price on Unsold Trucks with any mileage

    rows = GeneralOperations.applyDiscountToVehicles(500, true, null, "TRUCK");
    System.out.println(" ");
    System.out.println("2.B. Update by Combined Conditions (unsold, type) - rows=" + rows);

    System.out.println(" ");
    System.out.println("=== Example 14 Complete ===");

  }

}

How to Run this example

The Example 14 is included in the download package. To run this example please refer to the section How to Run the Examples above.

Case #1: Searching by a dynamic criteria

The SQL select is extended using XML tags that define sections that are dynamically included or excluded depending on the runtime parameter values. The configuration file includes the following section:

  <select java-class-name="SearchedClient">
  <![CDATA[
    select c.* from client c
      {*
      <where>
        <if test="#{minPurchases,javaType=java.lang.Integer,jdbcType=NUMERIC} != null">
          and (select count(*) from purchase p where p.client_id = c.id ) >= #{minPurchases}
        </if>
        <if test="#{state,javaType=java.lang.String,jdbcType=VARCHAR} != null">
          and c.state = #{state}
        </if>
        <if test="#{createdSince,javaType=java.sql.Date,jdbcType=DATE} != null">
          and created_at > #{createdSince}
        </if>
      </where>
      *}
  ]]>
  </select>

The example runs the SQL twice with different parameters to show different sections being activated for each search.

The SQL select can be run using the select(Integer minPurchases, String state, java.sql.Date createdSince) Java method on the DAO SearchedClient .

Case #2: Update using a dynamic criteria

The SQL update is extended using XML tags that define sections that are dynamically included or excluded depending on the runtime parameter values. The configuration file includes the following section:

  <query java-method-name="applyDiscountToVehicles">
  <![CDATA[
    update vehicle set
        list_price = list_price - #{discount,javaType=java.lang.Integer,jdbcType=NUMERIC}
      <where>
        <if test="#{unsold,javaType=java.lang.Boolean,jdbcType=BOOLEAN} != null">
          and not sold
        </if>
        <if test="#{minMileage,javaType=java.lang.Integer,jdbcType=NUMERIC} != null">
          and mileage > #{minMileage}
        </if>
        <if test="#{type,javaType=java.lang.String,jdbcType=VARCHAR} != null">
          and type = #{type}
        </if>
      </where>
  ]]>
  </query>

The example runs the SQL twice with different parameters to show different sections being activated for the update.

The SQL update can be run using the applyDiscountToVehicle(Integer discount, Boolean unsold, Integer mileage, String type ) Java method on the DAO GeneralOperations .