|
|
|
Common query patterns
The queryFirst methods can return a java.util.Map or a Java bean, while the queryList methods can return a java.util.List of Maps or Java beans. See extending data access to learn how you can customize the return types of query methods.
Iterate over results using eachRow
The zero.data.groovy.Manager.eachRow() function provides a quick and easy way to perform logic against each row. For example, the following code creates XML quite easily:
def data = zero.data.groovy.Manager.create('mydb')
def xml = new groovy.xml.MarkupBuilder(request.writer)
xml.records() {
data.eachRow('SELECT * FROM table') { row ->
item(id: row['id'], name: row.name)
}
}
// creates the following XML:
// <records>
// <item id="10" name="Robert" />
// <item id="11" name="Bill" />
// <item id="15" name="Aaron" />
// </records>
Retrieving a map
The following query returns a Map<String,Object>:
// Java code:
String sql = "SELECT * FROM person WHERE person.name=?";
Map<String,Object> brian = data.queryFirst(sql, "Brian");
System.out.println(brian.get("id"));
// Groovy, with a list of parameters:
def sql = 'SELECT * FROM person WHERE person.name=?'
def brian = data.queryFirst(sql, ['Brian'])
println brian.id
// Groovy, using GString parameters:
def name = 'Brian'
def brian = data.queryFirst("SELECT * FROM person WHERE name=$name")
println brian.id
Map key is the column name, and the value(s) are the column values in the result set. Generally, a Map of this parameterized type is sufficient to pass through to some type of serialization code; most serialization code defaults to the use toString() for serializing objects, although the lack of type makes these values opaque. If further operation using this data structure is necessary, you can cast the Object to the proper type.
The queryFirst series of methods are useful when working against the first row of data; all other rows are ignored. If you want to return all rows, you can obtain a List of Map using queryList.
// Java code:
String sql = "SELECT * FROM person WHERE name LIKE ?";
List<Map<String,Object>> people = data.queryList(sql, "Br%");
for (Map<String, Object>> person : people)
System.out.println(person.get("id");
// Groovy, with a list of parameters:
def people = data.queryList('SELECT * FROM person WHERE name LIKE ?', ['Br%'])
// Groovy, using GString parameters:
def name = 'Br%'
def people = data.queryList("SELECT * FROM person WHERE name LIKE $name")
people.each { item ->
println item.id
}
List of Map or all rows in person in which the name starts with Br. Each Map has column values keyed by column names, just as with queryFirst.
Retrieving a Java bean
To retrieve a Java bean instead of a Map, call the queryFirst method and pass in the desired class as the second parameter:
// Java code:
String sql = "SELECT * FROM person WHERE person.name=?";
Person brian = data.queryFirst(sql, Person.class, "Brian");
System.out.println(brian.getId());
// Groovy, with a list of parameters:
def sql = "SELECT * FROM person WHERE person.name=?"
def brian = data.queryFirst(sql, Person, ['Brian'])
println brian.id
// Groovy, using GString parameters:
def name = 'Brain'
def brian = data.queryFirst('SELECT * FROM person WHERE name=$name', Person)
println brian.id
queryList method to retrieve a list of beans:
// Java code
List<Person> people = data.queryList("SELECT * FROM person", Person.class);
for (Person person : people)
System.out.println(person.getId());
// Groovy, with a list of parameters:
def sql = 'SELECT * FROM person WHERE name LIKE ?'
def people = data.queryList(sql, Person, ['Br%'])
// Groovy, using GString parameters:
def name = 'Br%'
def people = data.queryList("SELECT * FROM person WHERE name LIKE $name", Person)
people.each { item ->
println item.id
}
query methods take [0..*] parameters and are therefore optional, depending on the needs of the given SQL string.
Retrieving simple strings
Some queries only return a single column. In this case, you avoid a more complex data structure and just get the scalar value:
import com.ibm.pdq.runtime.data.handlers.ConstantRowHandler;
// Java code:
String sql = "SELECT name FROM person WHERE name=?";
String name = data.queryFirst(sql, new ConstantRowHandler<String>(String.class), "Robert");
// Groovy, with a list of parameters:
def sql = 'SELECT name FROM person WHERE name=?'
def name = data.queryFirst(sql, new ConstantRowHandler(String), 'Robert')
// Groovy, with GString parameters:
def param = 'Robert'
def name = data.queryFirst("SELECT name FROM person WHERE name=$param", new ConstantRowHandler(String))
queryFirst to queryList.
Retrieving primitive values
An aggregate function and other scenarios return a single primitive data type as shown in the following examples:
import com.ibm.pdq.runtime.data.handlers.ConstantRowHandler;
// Java code:
String sql = "SELECT COUNT(*) AS numrows FROM person";
int numrows = data.queryFirst(sql, new ConstantRowHandler<Integer>(Integer.class));
// Groovy code:
def sql = 'SELECT COUNT(*) AS numrows FROM person'
def numrows = data.queryFirst(sql, new ConstantRowHandler(Integer))
List<Integer> allIDs = data.queryFirst("SELECT id FROM person", new ConstantRowHandler<Integer>(Integer.class));
Query parameters with Groovy
Groovy supports the parameter styles described in this document. Using zero.data.groovy.Manager, Groovy's GString enables a streamlined way to pass parameters into the query engine yet still retain the efficiency and protection of a PreparedStatement.
For example, the following code snippets show a Groovy implementation and then a Java implementation of equivalent queries:
// groovy
def data = zero.data.groovy.Manager.create('mydb')
def city = 'Raleigh'
def hash = [name: 'Robert', age: 24]
def results = data.queryList("SELECT * FROM table WHERE age < ${hash.age} AND city=$city")
// java
Manager data = Manager.create("mydb");
String city = "Raleigh";
Map hash = new HashMap();
hash.put("name", "Robert");
hash.put("age", 24);
List results = data.queryList("SELECT * FROM table WHERE age < ? AND city=?", hash.get("age"), city);
Groovy Shortcuts
The Groovy Manager takes advantage of the Groovy syntax to provide several SQL shortcuts.
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])", list)
def list = [1,2,3]
def results = queryList("SELECT * FROM APP.TEST WHERE id IN $list")
$list parameter is expanded to (?, ?, ?). Thus PreparedStatements are 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 unless expression replacement is being done. 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.
More information:
Query parameters
There are several styles of SQL parameters supported by the API for different situations:
- Ordinal parameters
- The classic JDBC style of binding values to delimiter placeholders,
?, in the SQL.
- Named parameters
- Enables you to embed named placeholders in the SQL. For example,
:authorId can bind to values from a bean or a map.
- Ordinal and named parameters in combination
- Enables you to send multiple complex types as parameters in the form of
?2.authorId.
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 : 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)
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 JSONObject object 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
JSONObject order = // obtained perhaps through defaultJsonInput
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 defaultJsonInput
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 defaultJsonInput
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}")
Person object and a JSONObject object (which extends Map) and that the SQL references can reference parameters out of order.
Handling exceptions
Many of the methods throw the unchecked runtime exception com.ibm.pdq.runtime.exception.DataRuntimeException. You can choose to catch this exception if you want to handle the error condition.
|
