Example 10 - Regular SQL Queries

This example shows how to run regular SQL queies that do not return a result set. For SQL selects see example 12.

Regular SQL include DML (update, delete, insert, truncate, etc.) as well as DDL (create, drop, alter, etc.) SQL statements.

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

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

Regular SQL Queries share the syntax with Native SQL and Dynamic SQL. They can be combined all at once when creating SQL statements.

Example Source Code

package examples;

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

import daos.ClientDAO;

/**
 * Example 10 - Regular SQL Queries
 * 
 * @author Vladimir Alarcon
 * 
 */
public class Example10 {

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

    System.out.println("=== Running Example 10 - Regular SQL Queries ===");
    System.out.println(" ");

    int rows;

    // 1. Update total_purchased for all clients by adding their purchases

    rows = ClientDAO.computeTotalPurchased();
    System.out.println("1. Update total_purchased. Affected rows=" + rows);

    // 2. Upgrade to VIP all clients who have bought at least N cars

    rows = ClientDAO.upgradeToVIP(2);
    System.out.println("2. Upgrade clients to VIP. Affected rows=" + rows);

    // 3. Delete clients who haven't bought anything, created long time ago, and
    // haven't referred anyone.

    rows = ClientDAO.deleteInactiveClients(Date.valueOf("2017-03-01"));
    System.out.println("3. Delete Inactive Clients. Affected rows=" + rows);

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

  }

}

How to Run this example

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

Case #1: Update with no parameters

This case runs a SQL update to update a column on the client table. It's exposed as the computeTotalPurchased() method on the ClientDAO .

Case #2: Complex update using parameters

All the complexity of the SQL is hidden from the java code. It's simply exposed as the upgradeToVIP(Integer minPurchases) method on the ClientDAO .

Case #3: Delete using parameters

All the complexity of the non-trivial SQL delete is hidden from the java code. It's exposed as the deleteInactiveClients(java.sql.Data createdBefore) method on the ClientDAO .