Advanced Database Persistence for Java

Examples & Reference Manual
 

Example 1 - CRUD Operations on Tables

This example shows the basic out-of-the-box CRUD (create, read, update, delete) operations on a table of the database. This example shows how to operate on single rows identifying them by the PK (primary key) of the table; the PK is auto-generated as an identity key.

However, if the table did not have a PK, then only the insert operation would be available and the options to perform select, update, and delete would be more limited. We could still use the out-of-the-box select, update, and delete “by example” operations described on examples further on.

DAO/VO class and its properties

For each table Hotrod generates a DAO class that combines the DAO pattern (persistence methods) with the VO pattern (value object that represents a row of a table).

This example uses the VEHICLE table to demonstrate the basic CRUD operations. HotRod created a DAO class VehicleDAO for this table. Its name is configurable but defaults to the table name with the DAO suffix.

For each table column this class includes a property with a name and a specific Java type. Both are configurable: the name defaults to the column name in Java format, an the Java type defaults to a suitable Java type for each column. This Java type heavily depends on the specifics of the RDBMS.

Example Source Code

package examples;

import java.sql.Date;
import java.sql.SQLException;

import daos.VehicleDAO;

/**
 * Example 01 - Basic CRUD Operations on Tables
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example01 {

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

    System.out.println("=== Running Example 01 - Basic CRUD Operations on Tables ===");

    // List all vehicles

    Utilities.displayAllVehicles("Example 01 - Basic CRUD Operations on Tables - Initial Data:");
    System.out.println(" ");

    // 1. Insert a vehicle

    VehicleDAO skoda = new VehicleDAO();
    // skoda.setId(...); // ignored: the DB will generate the PK
    skoda.setBrand("Skoda");
    skoda.setModel("Octavia");
    skoda.setType("CAR");
    skoda.setVin("VN4408");
    skoda.setEngineNumber("EN9401");
    skoda.setMileage(12100);
    skoda.setPurchasedOn(Date.valueOf("2017-02-17"));
    skoda.setBranchId(102);
    skoda.setListPrice(22400);
    skoda.setSold(false);
    int rows = skoda.insert();
    System.out.println("1. New vehicle Skoda added. New ID=" + skoda.getId() + ". Rows inserted=" + rows);

    // 2. Select by PK: retrieve the DeLorean row (id=3)

    VehicleDAO delorean = VehicleDAO.select(3);
    System.out.println("2. DeLorean " + (delorean == null ? "not " : "") + "found.");

    // 3. Update by PK: set the mileage of the DeLorean to 270500

    if (delorean != null) {
      delorean.setMileage(270500);
      rows = delorean.update();
      System.out.println("3. DeLorean mileage updated. Rows updated=" + rows);
    } else {
      System.out.println("3. Could not update DeLorean. Car not found.");
    }

    // 4. Delete by PK: delete the Peterbilt 379 (id=2)

    VehicleDAO truck = new VehicleDAO();
    truck.setId(2); // set the PK value
    rows = truck.delete(); // delete by PK
    if (rows > 0) {
      System.out.println("4. Peterbilt 379 deleted.");
    } else {
      System.out.println("4. Could not delete Peterbilt 379. Vehicle not found.");
    }

    // List all vehicles again

    Utilities.displayAllVehicles("Example 01 - Basic CRUD Operations on Tables - Final Data:");
    System.out.println("=== Example 01 Complete ===");

  }

}

How to Run this example

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

Insert

In this example, the section below shows how to insert a new row in the table VEHICLE : 

    VehicleDAO skoda = new VehicleDAO();
    skoda.setBrand("Skoda");
    skoda.setModel("Octavia");
    skoda.setUsed(false);
    skoda.setCurrentMileage(7);
    skoda.setPurchasedOn(new Date(System.currentTimeMillis()));
    int rows = skoda.insert();
    System.out.println("New vehicle Skoda added. New ID=" + skoda.getId()
      + ". Rows inserted=" + rows);

The insert() method is always available, regardless if the table has or has not a PK: you can always insert.

It first instantiates a new object of the class VehicleDAO , then it set the property values for the row we want to insert, and finally it performs the insert() operation.

There are a few things to note here:

Select by PK

In this example, the section below shows how to select a row using the PK.

    VehicleDAO delorean = VehicleDAO.select(3);

If the table has a PK, the DAO includes the select() method that retrieves a row by a PK value. This method is not present if the table does not have a PK.

There are a few things to note here:

Update by PK

In this example, the section below shows how to update a row using the PK.

    VehicleDAO delorean = VehicleDAO.select(3);

    // Update by PK: set the mileage of the DeLorean to 270500

    if (delorean != null) {
      delorean.setCurrentMileage(270500);
      rows = delorean.update();
      System.out.println("DeLorean updated. Rows updated=" + rows);
    } else {
      System.out.println("Could not update DeLorean. Car not found.");
    }

If the table has a PK, the DAO includes the update() method that updates a row by the PK value. This method is not present if the table does not have a PK. The key section of this example are the two lines: 

      delorean.setCurrentMileage(270500);
      rows = delorean.update();

This example retrieved a row using a Select by PK method (example above), then modified one property, and finally updated the row.

There are a few things to note here:

Delete By PK

In this example, the section below shows how to delete a row using the PK.

    VehicleDAO toyota = new VehicleDAO();
    toyota.setId(2);
    rows = toyota.delete();

If the table has a PK, the DAO includes the delete() method that deleted a row by the PK value. This method is not present if the table does not have a PK.

It first uses an object of the class VehicleDAO , the sets the PK columns (one or more), and finally deletes the row.

There are a few things to note here: