Example 3 - Select by Example

This example shows how to select rows from tables using the out-of-the-box Select by Example functionality. This functionality is available on all database tables (and views) even if they don't have a PK.

Example Source Code

package examples;

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

import daos.VehicleDAO;
import daos.primitives.VehicleDAOPrimitives.VehicleDAOOrderBy;

/**
 * Example 03 - Select by Example
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example03 {

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

    System.out.println("=== Running Example 03 - Select by Example ===");
    VehicleDAO example;

    // 1. select by a single column - (type 'CAR')

    example = new VehicleDAO();
    example.setType("CAR");
    List<VehicleDAO> cars = VehicleDAO.selectByExample(example);
    Utilities.displayVehicles("1. Select All Vehicles of type 'CAR':", cars);

    // 2. select by multiple columns (BRAND & MODEL)

    example = new VehicleDAO();
    example.setBrand("Toyota");
    example.setModel("Tercel");
    List<VehicleDAO> tercel = VehicleDAO.selectByExample(example);
    Utilities.displayVehicles("2. Select All Vehicles 'Toyota Tercel':", tercel);

    // 3. select using null values (BRAND + MODEL + no BRANCH_ID)

    example = new VehicleDAO();
    example.setBrand("Toyota");
    example.setModel("Tercel");
    example.setBranchId(null); // This forces to search for null branch_id
    List<VehicleDAO> tercelNoBranch = VehicleDAO.selectByExample(example);
    Utilities.displayVehicles("3. Select All Vehicles 'Toyota Tercel' with no branch:", tercelNoBranch);

    // 4. Select unsold vehicles, with order
    // Sort ascending by type, then descending by brand, then ascending by model

    example = new VehicleDAO();
    example.setSold(false);
    List<VehicleDAO> unsold = VehicleDAO.selectByExample(example, VehicleDAOOrderBy.TYPE, VehicleDAOOrderBy.BRAND$DESC,
        VehicleDAOOrderBy.MODEL);
    Utilities.displayVehicles("4. Select unsold vehicles, with order:", unsold);

    // 5. Select unsold vehicles, with case insensitive order
    // Sort case-insensitive by brand, then case-insensitive by type descending

    example = new VehicleDAO();
    example.setSold(false);
    List<VehicleDAO> unsold2 = VehicleDAO.selectByExample(example, VehicleDAOOrderBy.BRAND$CASEINSENSITIVE,
        VehicleDAOOrderBy.TYPE$DESC_CASEINSENSITIVE);
    Utilities.displayVehicles("5. Select unsold vehicles, with case insensitive order:", unsold2);

    // 6. Select unsold vehicles, with case insensitive stable-forward order
    // Sort case-insensitive and stable-forward by brand

    example = new VehicleDAO();
    example.setSold(false);
    List<VehicleDAO> unsold3 = VehicleDAO.selectByExample(example,
        VehicleDAOOrderBy.BRAND$CASEINSENSITIVE_STABLE_FORWARD);
    Utilities.displayVehicles("6. Select unsold vehicles, with case insensitive stable-forward order:", unsold3);

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

  }

}

How to Run this example

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

Case #1: Filtering by a single column

To select using a single column you must instantiate a new DAO to use as the example and set the desired value on the property you want to filter by. Leave the rest of the properties untouched.

This is demonstrated selecting from the table VEHICLE all the rows with type ' CAR '.

Case #2: Filtering by multiple columns

To select using multiple columns you must instantiate a new DAO to use as the example and set the desired values on the properties you want to filter by. An AND logic condition is used to filter by all the columns. Leave the rest of the properties untouched.

This is demonstrated selecting from the table VEHICLE all the rows with BRAND ' Toyota ' and MODEL ' Tercel '.

Case #3: Using null values

To select using null values you must explicitly set the null value into the properties(s) you want to filter by. An AND logic condition is used to filter by all the columns. Leave the rest of the properties untouched.

This is demonstrated selecting from the table VEHICLE all the rows with BRAND ' Toyota ' and MODEL ' Tercel ', with a null value on the BRANCH_ID column.

Case #4: Adding ordering

To specify the order of the returned columns you can add extra parameters when calling the selectByExample() method.

This is demonstrated selecting from the table VEHICLE by adding values from the generated VehicleDAOOrderBy enum.

Case #5: Using case-insensitive ordering

Case-insensitive ordering is available for String-like columns. The generated enum for the DAO includes a variant for case-insensitivity.

This is demonstrated selecting from the table VEHICLE by adding values from the generated VehicleDAOOrderBy enum.

Case #6: Using stable case-insensitive ordering

Case-insensitive ordering is available for String-like columns. The generated enum for the DAO includes a variant for stable case-insensitivity. This variant sorts by the case-insensitive value, and additionally sort by plain value.

This is demonstrated selecting from the table VEHICLE by adding values from the generated VehicleDAOOrderBy enum.