Example 4 - Update by Example

This example shows how to update rows on tables using the out-of-the-box Update 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.Date;
import java.sql.SQLException;

import daos.VehicleDAO;

/**
 * Example 04 - Update by Example
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example04 {

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

    System.out.println("=== Running Example 04 - Update by Example ===");
    VehicleDAO example;
    VehicleDAO updateValues;
    int rows;
    Utilities.displayAllVehicles("Initial Data on Table VEHICLE:");

    // 1. Single Column Update
    // Example: Set mileage to 10 to all cars on branch 101.

    example = new VehicleDAO();
    example.setBranchId(103); // search branch_id = 103
    updateValues = new VehicleDAO();
    updateValues.setMileage(10); // set mileage to 10
    rows = VehicleDAO.updateByExample(example, updateValues);
    Utilities.displayAllVehicles("1. Single Column Update (" + rows + " rows updated):");

    // 2. Multiple Column Update
    // Example: Set all cars of branch 101 as unsold and set price to zero.

    example = new VehicleDAO();
    example.setType("CAR"); // set type = 'CAR'
    example.setBranchId(101); // search branch_id = 101
    updateValues = new VehicleDAO();
    updateValues.setSold(false); // set sold to false
    updateValues.setListPrice(0); // set price to zero
    rows = VehicleDAO.updateByExample(example, updateValues);
    Utilities.displayAllVehicles("2. Multiple Column Update (" + rows + " rows updated):");

    // 3. Searching & Update Using Null Values
    // Example: To all unsold vehicles with no branch set mileage to zero and
    // price to null.

    example = new VehicleDAO();
    example.setSold(false); // search sold = false
    example.setBranchId(null); // search branch_id is null
    updateValues = new VehicleDAO();
    updateValues.setMileage(0); // set mileage to zero
    updateValues.setListPrice(null); // set price to null
    rows = VehicleDAO.updateByExample(example, updateValues);
    Utilities.displayAllVehicles("3. Searching & Update Using Null Values (" + rows + " rows updated):");

    // 4. Update with no condition (all rows)
    // Set Purchase Date to 2017-01-01 to all vehicles

    example = new VehicleDAO();
    updateValues = new VehicleDAO();
    updateValues.setPurchasedOn(Date.valueOf("2017-01-01"));
    rows = VehicleDAO.updateByExample(example, updateValues);
    Utilities.displayAllVehicles("4. Update with no condition, i.e all rows (" + rows + " rows updated):");

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

  }

}

How to Run this example

The Example 04 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 update 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. Instantiate a second DAO to set the new values you want to update.

This is demonstrated using the table VEHICLE .

Case #2: Filtering by and updating multiple columns

To update 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. Instantiate a second DAO to set the new values you want to update.

This is demonstrated using the table VEHICLE .

Case #3: Using null values to filter and/or update

To update 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.

Instantiate a second DAO to set the new values you want to update, including the null values to be set.

This is demonstrated using the table VEHICLE .

Case #4: Update all rows

To update all rows of the table just don't set any property of the DAO used as the example.

This is demonstrated using the table VEHICLE .