Example 5 - Delete by Example

This example shows how to delete rows on tables using the out-of-the-box Delete by Example functionality. This functionality is available on all database tables even if they don't have a PK.

Example Source Code

package examples;

import java.sql.SQLException;

import daos.VehicleDAO;

/**
 * Example 05 - Delete by Example
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example05 {

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

    System.out.println("=== Running Example 05 - Delete by Example ===");
    VehicleDAO example;
    int rows;

    // 1. Single Column Criteria Delete
    // Example: Delete all MOTORCYCLES

    example = new VehicleDAO();
    example.setType("TRUCK");
    rows = VehicleDAO.deleteByExample(example);
    Utilities.displayAllVehicles("1. Single Column Criteria Delete (" + rows + " rows deleted):");

    // 2. Multiple Column Criteria Delete
    // Example: Delete all sold cars

    example = new VehicleDAO();
    example.setType("CAR");
    example.setSold(false);
    example.setBranchId(101);
    rows = VehicleDAO.deleteByExample(example);
    Utilities.displayAllVehicles("2. Multiple Column Criteria Delete (" + rows + " rows deleted):");

    // 3. Delete Using Null Values on Criteria
    // Example: Delete all unsold cars without a branch_id

    example = new VehicleDAO();
    example.setSold(false);
    example.setType("CAR");
    example.setBranchId(null);
    rows = VehicleDAO.deleteByExample(example);
    Utilities.displayAllVehicles("3. Delete Using Null Values on Criteria (" + rows + " rows deleted):");

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

  }

}

How to Run this example

The Example 05 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 delete 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 using the table VEHICLE .

Case #2: Filtering by multiple columns

To delete filtering by 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 using the table VEHICLE .

Case #3: Using null values to filter

To delete filtering 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 using the table VEHICLE .