Database access API

List Expansion

Using the GString version of the zero.data.groovy.Manager query methods, you can let the Manager dynamically expand lists. This is useful with IN predicates. For example, the following code illustrates that without the list expansion shortcut, query code can be fairly ugly:

def list = [1,2,3]
def results = queryList("SELECT * FROM APP.TEST WHERE id IN (?,?,?)", list)

// or
def otherresults = queryList("SELECT * FROM APP.TEST WHERE id IN ($list[0],$[list[1],$list[2])")

In both cases, the SQL is hard coded for three arguments. If the list changes with input from a form, for instance, this SQL would not support changes to the number elements in the list. However, using list expansion, as follows, it is possible to accommodate dynamic sized lists:

def list = [1,2,3]
def results = queryList("SELECT * FROM APP.TEST WHERE id IN $list")

At runtime, the SQL is modified and the $list parameter is expanded to (?, ?, ?). Thus a PreparedStatement is still used and the basic protection they offer for SQL injection is maintained.

Read this if you are using Groovy

In Groovy, a Java String and a GString are different concepts and created differently. Java Strings behave just as expected in Java and are created using single quotes for example: 'I am a String'.

GStrings, on the other hand, allow for variable replacement and other expressions and are created using double quotes. For example: "I am a GString".

There is no reason to use a GString with the zero.data.groovy.Manager unless variable or expression replacement is desired. Otherwise, Groovy will perform unnecessary processing. This can be somewhat confusing at first for Java developers but this is an important distinction because zero.data.groovy.Manager cannot make use of a PreparedStatement unless a GString is passed.

In Groovy, do not concatenate GString. Use multi-line GString instead:

// DO NOT DO THIS
def sql = "SELECT * FROM table"
          + "WHERE id = $id"

// do this...
def sql = """SELECT * from table
             WHERE id = $id"""

The reason concatenation with GString is heavily discouraged is because Groovy evaluates the GString prior to concatenation and will bypass PreparedStatement optimizations and protection.

Ordinal parameters - "?"

Ordinal parameters are unnamed, as indicated by the anonymous ? in traditional PreparedStatement fashion. This requires a correlation order between the order of ? and the method parameters. Variable arguments are supported on all methods. However, when accessing from Groovy, a List construction is required as follows:

// java
Person person = data.queryFirst("SELECT * FROM person WHERE firstname=? AND lastname=? AND age > ?", Person.class, "George", "Bush", 65);

// groovy
def person = data.queryFirst('SELECT * FROM person WHERE firstname=? AND lastname=? AND age > ?', Person, ["George", "Bush", 65]);

Named parameters - ":columnName" or "?.columnName"

Named parameters are useful because ordered correlation between SQL parameters and method parameters is not essential. Instead of the PreparedStatement syntax using ? as a parameter placeholder, the symbol :<key> is used. Supported named parameters can be passed in as a bean or a map.

Using a map to pass in SQL parameters

The following example shows a map being used to pass in SQL parameters:

// java
Map paramMap = new HashMap();
paramMap("name","Brian");
Person person = data.queryFirst("SELECT * FROM person WHERE person.name=:name", Person.class, paramMap);

// groovy params as list
def paramMap = [name: 'Brian']
def person = data.queryFirst('SELECT * FROM person WHERE person.name=:name', Person, [paramMap])

// groovy gstring
def paramMap = [name: 'Brian']
def person = data.queryFirst("SELECT * FROM person WHERE person.name=${paramMap.name}", Person)

Before the SQL query is run, the named parameters from the SQL string are bound to values in the map.

Using a bean to pass in SQL parameters

A bean can be used to contain values (just as a map was used in the previous example). The following example uses a bean to pass SQL parameters:

// java
Person person = new Person();
person.setName("Brian");
person = data.queryFirst("SELECT * FROM person WHERE person.name=:name", Person.class, person);

// groovy params as list
def person = data.queryFirst('SELECT * FROM person WHERE person.name=:name', Person, [person]);

// groovy gstring
def person = data.queryFirst("SELECT * FROM person WHERE person.name=${person.name}", Person);

Ordinal named parameters - "?2.columnName"

Parameters for a single query can be bound from several objects. For example, order data might come from a Map where the customer data comes from an instance of Person. To address such use cases, the following syntax is supported to combine named parameters with order:

// java
Map order = // obtained perhaps through JSON HTTP input
Person customer = // obtained somehow
data.update("INSERT INTO orders (orderId, customerId, itemId, quantity, price, address) VALUES(?1.orderNum, ?2.custId, ?1.itemNum, ?1.quantity, ?1.price, ?2.address", order, customer);

// groovy with parameters as list
def order = // obtained perhaps through JSON HTTP input
def customer = // obtained somehow
data.update('INSERT INTO orders (orderId, customerId, itemId, quantity, price, address) VALUES(?1.orderNum, ?2.custId, ?1.itemNum, ?1.quantity, ?1.price, ?2.address', order, customer)

// groovy with gstring
def order = // obtained perhaps through JSON HTTP input
def customer = // obtained somehow
data.update("INSERT INTO orders (orderId, customerId, itemId, quantity, price, address) VALUES(${order.orderNum}, ${customer.custId}, ${order.itemNum}, ${order.quantity}, ${order.price}, ${customer.address}")

The code snippet in the previous example illustrates that you can use ordinal named parameters to pass in both a Person object and a Map and that the SQL references can reference parameters out of order.

UPDATE and DELETE statements

The UPDATE and DELETE SQL statements are driven through the update() method, whereas INSERT statements should be run using the insert() method. The update method returns the number of affected rows while the insert method returns a generated key. The parameter passing rules are the same for the query methods, including Groovy's GString parameter passing for zero.data.groovy.Manager, list expansion, and Java's variable arguments and named arguments with beans and maps.

An UPDATE and DELETE example

// java
int rowsAffected = data.update("UPDATE APP.test SET initials=?,name=?", 'bjs', 'Brandon Smith');

// groovy with list parameters
def rowsAffected = data.update('UPDATE APP.test SET initials=?,name=?', ['bjs', 'Brandon Smith'])

// groovy with gstring
def initials = 'bjs'
def name = 'Brandon Smith'
def rowsAffected = data.update("UPDATE APP.test SET initials=$initials,name=$name")

INSERT statement and auto generated keys

The insert method is provided to support a shortcut to obtaining auto generated keys. There are more differences than other portions of the API between zero.data.groovy.Manager and zero.data.Manager so this documentation is separated. Common to both, however, is that the column name for the autogenerated key is passed.

// groovy using GString to pass parameters
def id = data.insert("INSERT INTO person (name, phone, email) VALUES ($name, $phone, $email)", ['id'])

// groovy using List to pass parameters
def id = data.insert('INSERT INTO person (name, phone, email) VALUES (?,?,?)', ['id'], [name, phone, email])

// java int id = data.insert("INSERT INTO person (name, phone, email) VALUES (?,?,?)", int.class, new String[] {"id"}, name, phone, email);

The major difference between zero.data.groovy.Manager and zero.data.Manager is that the Groovy version assumes, for simplicity sake, that a java.lang.Number is always returned. However, in zero.data.Manager, the return value of the method is the key itself cast to a class you define. In the above example, int.class is used to indicate to the engine to return the generated key as a Java int. The subsequent examples illustrate further uses of the zero.data.Manager insert method and assumes the following table:

CREATE TABLE catalog_entry (
    id int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name varchar(30)
);

Person bean = new Person();
bean.setInitials("hp");
bean.setName("Henry Proctor");

// java
int id = data.insert("insert into APP.TEST (INITIALS, NAME) values (:initials,:name)", int.class, new String[] { "id" }, bean); assertEquals(1, id);

// java
Number id = data.insert(sql, Number.class, new String[] { "id" }, bean); assertEquals(1, id.intValue());

// java
String id = data.insert(sql, String.class, new String[] { "id" }, bean); assertEquals("1", id);

inTransaction()

Local database transactions are supported using the Manager.inTransaction() method. This method provides the convenience to implicitly demarcate the transaction boundaries. The zero.data.Manager version of inTransaction() takes as an argument an implementation of zero.data.LocalTransaction whereas the zero.data.groovy.Manager version simply takes a closure as shown in the following example:

import zero.data.LocalTransaction;
import zero.data.Manager;

// Java code:
Manager voteDB = Manager.create("votingDB");
voteDB.inTransaction(new LocalTransaction() {
    public void execute() {
        voteDB.update("INSERT...");
        voteDB.update("UPDATE...");
        voteDB.update("DELETE...");
    }
});

// Groovy code:
def voteDB = zero.data.groovy.Manager.create('votingDB')
voteDB.inTransaction {
    voteDB.update("INSERT...")
    voteDB.update("UPDATE...")
    voteDB.update("DELETE...")
}

During the inTransaction demarcation, all query calls use the same Connection. If an exception is thrown either from query calls or explicitly by the application, the local transaction is rolled back. Otherwise, the transaction is committed and the Connection is closed. For example, the following code illustrates manually throwing an exception based on a condition:

// Java code:
import zero.data.Manager;
...
Manager voteDB = Manager.create("votingDB");
voteDB.inTransaction(new LocalTransaction() {
    public void execute() {
        voteDB.update("INSERT...");
        voteDB.update("UPDATE...");
        if (/* some condition */)
            throw new Exception("...");
    }
});

// Groovy code:
def voteDB = zero.data.groovy.Manager.create('votingDB')
voteDB.inTransaction {
    voteDB.update("INSERT...") voteDB.update("UPDATE...")
    if (/* some condition */)
        throw new Exception("...");
}

Manual transactions

Transaction demarcation can also be done directly calling transaction methods. The following methods are provided:

beginTransaction()
commitTransaction()
rollbackTransaction()

The implicit local transaction shown in the previous example can also be achieved by doing the following:

data.startTransaction();
try {
    data.update("INSERT...");
    data.update("UPDATE...");
    data.update("DELETE...");
    data.commitTransaction();
} catch (Exception e) {
    data.rollbackTransaction();
} finally {
    data.endTransaction();
}

Using JoinResultHandler

JPA, Hibernate, and even iBATIS provide functionality to map relational joins into a hierarchy of beans. JoinResultHandler does just that given a constrained set of assumptions about the SELECT statement and beans. If your use case meets these constraints, this handler can save a lot of coding efforts.

import com.ibm.pdq.annotation.Id;
public class Address {
    @Id private int address_id;
    private String name;
    public Address() {}
    // standard getters/setters removed for brevity
}

import com.ibm.pdq.annotation.Id;
import com.ibm.pdq.annotation.Column;
import zero.data.handler.Join;
public class Person {
    @Id private int person_id;
    @Column(name="firstname") private String name;
    @Join private Address address;
    public Person() {}
    // standard getters/setters removed for brevity
}

import com.ibm.pdq.annotation.Id;
public class Address {
    @Id private int address_id;
    private String name;
    public Address() {}
    // standard getters/setters removed for brevity
}

import com.ibm.pdq.annotation.Id;
import com.ibm.pdq.annotation.Column;
import zero.data.handler.Join;
public class Person {
    @Id private int person_id;
    @Column(name="firstname") private String name;
    @Join private List<Address> addresses;
}

Paging query results

They use paging to show the result little by little, usually in multiples of 5. Paging means showing your query result in multiple pages instead of just put them all in one long page. By splitting the query result in multiple pages you can save processing and download time plus you don't have much scrolling to do. Some database vendors support paging natively in their SQL implementation. Some are simple, others a bit verbose. Check your vendor's documentation for more details. Following are examples from more popular databases.

data.beginTransaction()
def result = data.query("SELECT * FROM pager", new IteratorPagingResultHandler(new MapRowHandler(), 51, 75))
// work with result which is Iterator<Map<String,Object>> representing rows 51 through 75
data.endTransaction()

IteratorPagingResultHandler(RowHandler handler, int start, int end)
IteratorPagingResultHandler(int pageNumber, int pageSize, RowHandler handler)

data.beginTransaction()
def result = data.query("SELECT * FROM pager", new IteratorPagingResultHandler(3, 25, new MapRowHandler()))
// work with result which is Iterator<Map<String,Object>> representing rows 51 through 75
data.endTransaction()