Example 12 - Regular SQL Selects

This example shows how to run regular (free) SQL selects.

SQL selects must be configured in the configuration file. See the reference section for details on their syntax and how to define their parameters.

SQL selects are exposed in the DAOs as simple java methods with the configured java parameters.

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

import daos.ClientNeverOfferedDiscount;
import daos.ClientWithPurchase;
import daos.CreatedClient;
import daos.DailyTotal;

/**
 * Example 12 - Regular SQL Selects
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example12 {

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

    System.out.println("=== Running Example 12 - Regular SQL Selects ===");

    // 1. Simple Select
    // Example: retrieve clients created between two dates

    Date since = Date.valueOf("2017-01-01");
    Date until = Date.valueOf("2017-03-31");
    List<CreatedClient> c1 = CreatedClient.select(since, until);
    System.out.println(" ");
    System.out.println("1. Simple Select - rows=" + c1.size());
    for (CreatedClient c : c1) {
      System.out.println(" client ID=" + c.getId());
    }

    // 2. Select Returns Fully-Typed Columns From Multiple Tables
    // Example: clients with purchases on a specific date

    Date today = Date.valueOf("2017-02-28");
    List<ClientWithPurchase> c2 = ClientWithPurchase.select(today);
    System.out.println(" ");
    System.out.println("2. Select Returns Fully-Typed Columns From Multiple Tables - rows=" + c2.size());
    for (ClientWithPurchase c : c2) {
      System.out.println(" client ID=" + c.getId());
    }

    // 3. Select Grouping Data Adding Counter
    // Example: Compute Daily Totals

    Date from = Date.valueOf("2017-01-01");
    Date to = Date.valueOf("2017-03-31");
    List<DailyTotal> totals = DailyTotal.select(from, to);
    System.out.println(" ");
    System.out.println("3. Select Grouping Data Adding Counter - rows=" + totals.size());
    for (DailyTotal dt : totals) {
      System.out.println(" Date: " + dt.getPurchaseDate() + " - Number of Purchases=" + dt.getNumberOfPurchases()
          + " - Total Revenue=" + dt.getRevenue());
    }

    // 4. Select Using Sub Queries
    // Example: Client Never Offered a Discount

    List<ClientNeverOfferedDiscount> c4 = ClientNeverOfferedDiscount.select();
    System.out.println(" ");
    System.out.println("4. Select Using Sub Queries - rows=" + c4.size());
    for (ClientNeverOfferedDiscount c : c4) {
      System.out.println(" client ID=" + c.getId());
    }

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

  }

}

How to Run this example

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

Case #1: Columns from a single table

The configuration file includes the SQL select that retrieves clients created between two dates using the following SQL statement:

  <select java-class-name="CreatedClient">
  <![CDATA[
    select * from client
      {* 
      where created_at between #{since,javaType=java.sql.Date,jdbcType=DATE}
                           and #{until,javaType=java.sql.Date,jdbcType=DATE}
      *}
  ]]>
  </select>

The SQL select can be run using the select(java.sql.Date since, java.sql.Date until) Java method on the DAO CreatedClient .

Case #2: Join returns fully-typed, fully-named columns from multiple tables

A new DAO Java class is generated to include the fully-typed, fully-named returned columns for both tables on the SQL select. The configuration file includes the SQL select performs the SQL join:

  <select java-class-name="ClientWithPurchase">
  <![CDATA[
    select p.*, c.vip, c.state from purchase p
      join client c on (c.id) = (p.client_id)
      {*
      where p.purchase_date =
        #{purchaseDate,javaType=java.sql.Date,jdbcType=DATE}
      *}
  ]]>
  </select>

The SQL select can be run using the select(java.sql.Date purchaseDate) Java method on the DAO ClientWithPurchase .

Case #3: Grouping and adding extra columns

A new DAO Java class is generated to include the fully-typed, fully-named returned columns from the SQL select. The configuration file includes the following SQL select:

  <select java-class-name="DailyTotal">
  <![CDATA[
    select 
        purchase_date, 
        count(*) as number_of_purchases, 
        sum(vehicle_price) as price, 
        sum(extras_price) as extras, 
        sum(discount) as discount, 
        sum(tax) as taxes, 
        sum(final_price) as revenue
      from purchase
      {*
      where purchase_date between #{from,javaType=java.sql.Date,jdbcType=DATE}
                              and #{to,javaType=java.sql.Date,jdbcType=DATE}
      group by purchase_date
      *}
  ]]>
  </select>

The SQL select can be run using the select(java.sql.Date from, java.sql.Date to) Java method on the DAO DailyTotal .

Case #4: Subqueries

A new DAO Java class is generated to include the fully-typed, fully-named returned columns from the SQL select. The configuration file includes the following SQL select:

  <select java-class-name="ClientNeverOfferedDiscount">
  <![CDATA[
    select * from client where id not in 
      (select client_id from purchase where discount > 0)
  ]]>
  </select>

The SQL select can be using the select() Java method on the DAO ClientNeverOfferedDiscount .