6.1 SQL execution
Configure the SQL executor and database connections through SQL executor before running these scripts.
Declare a fragment with its arguments and SQL, then call it as a function. Its registered name selects the operation and input format.
| Operation | Plain SQL | Dynamic XML | Result |
|---|---|---|---|
| Query | selectSql | selectXml | Query result or page object |
| Insert | insertSql | insertXml | Affected rows |
| Update | updateSql | updateXml | Affected rows |
| Delete | deleteSql | deleteXml | Affected rows |
| Procedure | callSql | callXml | Result sets, counts or selected outputs |
| General execution | executeSql | executeXml | Statement result |
Prefer the operation-specific name when known. executeSql infers the operation from the beginning of the SQL; explicit names are preferable for complex statements.
Sample data
The examples use this H2 schema. Use equivalent DDL on other databases.
CREATE TABLE people (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(100),
age INT,
enabled INT DEFAULT 1
);
INSERT INTO people(name, age) VALUES ('Alice', 25), ('Bob', 30);
Runnable scripts are in dataql/dataql-sqlproc/src/test/resources/sql-manual; the corresponding documentation test package verifies them against H2.
Query
hint FRAGMENT_SQL_OPEN_PACKAGE = 'off';
hint FRAGMENT_SQL_COLUMN_CASE = 'lower';
var find = @@selectSql(minAge)<%
SELECT id, name, age FROM people WHERE age >= #{minAge} ORDER BY id
%>;
return find(25);
This returns Alice and Bob. off preserves a list and lower normalizes column names. See Results.
Insert, update and delete
var add = @@insertSql(name, age)<% INSERT INTO people(name, age) VALUES (#{name}, #{age}) %>;
var change = @@updateSql(name, age)<% UPDATE people SET age = #{age} WHERE name = #{name} %>;
var find = @@selectSql(name)<% SELECT age FROM people WHERE name = #{name} %>;
var remove = @@deleteSql(name)<% DELETE FROM people WHERE name = #{name} %>;
var inserted = add('Carol', 20);
var updated = change('Carol', 21);
var age = find('Carol');
var deleted = remove('Carol');
return {'inserted': inserted, 'updated': updated, 'age': age, 'deleted': deleted};
Each write returns its affected-row count. These calls execute separately; use a transaction when they must be atomic.
Batch calls
var add = @@insertSql[](person)<%
INSERT INTO people(name, age) VALUES (#{person.name}, #{person.age})
%>;
return add([{'name':'Carol','age':20}, {'name':'Dave','age':22}]);
The [] suffix processes list arguments by matching index. All argument lists must have the same size. The result is [1, 1]. This implementation calls JDBC for each item rather than using JDBC executeBatch; wrap the whole call in a transaction for atomicity.
Execution options
hint timeout = 5;
hint fetchSize = 100;
var find = @@selectSql()<% SELECT id, name FROM people %>;
return find();
PreparedStatement is the default. Timeout is measured in seconds; fetch size is a driver hint. See SQL hints. Execution failures propagate to the DataQL caller.
Parameter scopes
Outside a fragment, DataQL ${id} reads a script parameter. Inside SQL, #{id} binds a fragment argument and ${id} performs text substitution. See Parameters.