Example 13 - Native SQL Selects

This example shows how to run native SQL statements.

Native SQL shares the syntax with Regular SQL and Dynamic 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.SQLException;
import java.util.List;

import daos.NonVIPClient;
import daos.VehiclePrice;
import daos.primitives.GeneralOperations;

/**
 * Example 13 - Native SQL
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example13 {

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

    System.out.println("=== Running Example 13 - Native SQL ===");

    // 1. Limiting the Number of Rows at the DB Level
    // Example: retrieve non-VIP clients, with a maximum of 4 returned rows

    List<NonVIPClient> nv = NonVIPClient.select(4);
    System.out.println(" ");
    System.out.println("1. Limiting the Number of Rows at the DB Level - rows=" + nv.size());
    for (NonVIPClient c : nv) {
      System.out.println(" client ID=" + c.getId());
    }

    // 2. Using a Native SQL Function
    // Example: using the IFNULL non-standard function

    List<VehiclePrice> vp = VehiclePrice.select("Yamaha");
    System.out.println(" ");
    System.out.println("2. Using a Native SQL Function - rows=" + vp.size());
    for (VehiclePrice v : vp) {
      System.out.println(" vehicle ID=" + v.getId() + "  price=" + v.getPrice());
    }

    // 3. Inserting on a Native Column Type

    int rows = GeneralOperations.insertPreferredColors(1, "orange", "indigo", "turquoise");
    System.out.println(" ");
    System.out.println("3. Inserting on a Native Column Type - Affected rows=" + rows);

    // Can also be used to:
    // * select for update
    // * recursive selects

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

  }

}

How to Run this example

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

Case #1: Limiting the number of returned rows

The SQL select uses the native SQL extension LIMIT <N> to limit the returned number of rows. The configuration file includes the following section:

  <select java-class-name="NonVIPClient">
  <![CDATA[
    select * from client
      {* 
      where vip = false
      limit #{maxRows,javaType=java.lang.Integer,jdbcType=NUMERIC}
      *}
  ]]>
  </select>

The SQL select can be run using the select(java.lang.Integer maxRows) Java method on the DAO NonVIPClient .

Case #2: Using a non-standard SQL function

The SQL select to uses the SQL function IFNULL(value, value) . The configuration file includes the following section:

  <select java-class-name="VehiclePrice">
  <![CDATA[
    select id, ifnull(list_price, '0') as price from vehicle
      {*
      where brand = #{brandName,javaType=java.lang.String,jdbcType=VARCHAR}
      *}
  ]]>
  </select>

The SQL select can be run using the select(java.lang.Integer maxRows) Java method on the DAO NonVIPClient .

Case #3: Inserting on a native database column type

The SQL select to uses injects valued on a non-standard SQL syntax for the ARRAY of VARCHAR column type. The configuration file includes the following section:

  <query java-method-name="insertPreferredColors">
  <![CDATA[
    insert into preferred_colors (id, colors) values (
      #{id,javaType=java.lang.Integer, jdbcType=NUMERIC},
      (
        #{color1,javaType=java.lang.String, jdbcType=VARCHAR},
        #{color2,javaType=java.lang.String, jdbcType=VARCHAR},
        #{color3,javaType=java.lang.String, jdbcType=VARCHAR}
      )
    )
  ]]>
  </query>

The SQL select can be run using the insertPreferredColors(Integer id, String color1, String color2, String color3) Java method on the DAO GeneralOperations .