The SQL created through RQL may in some cases not be optimized for your needs. Should such a situation arise, you can use the atg.adapter.gsa.query.SqlPassthroughQuery
class. A SqlPassthroughQuery
is used with the QueryBuilder
to specify the SQL statement to pass directly to the database. Note that the SQL repository cannot use the results of arbitrary queries to generate repository items. The query must return the ID column or columns in their declared order from the item descriptor’s id-column-names
attribute.
Here is an example of how a SqlPassthroughQuery
might be used in a page. Note that the SQL statement is sent to the database “as is.”
import atg.adapter.gsa.query.*; GSARepository repo = (GSARepository)request.resolveName("/examples/TestRepository"); RepositoryView view = repo.getView("canard"); Object params[] = new Object[4]; params[0] = new Integer (25); params[1] = new Integer (75); params[2] = "french"; params[3] = "greek"; Builder builder = (Builder)view.getQueryBuilder(); String str = "SELECT * FROM usr_tbl WHERE (age_col > 0 AND age_col < 1 AND EXISTS (SELECT * from subjects_tbl where id = usr_tbl.id AND subject IN (2, 3)))"; RepositoryItem[] items = view.executeQuery (builder.createSqlPassthroughQuery(str, params)); if (items == null) out.println(" Is null."); else{ out.println(" Is not null: " + items.length + "<p>"); for (int i = 0; i < items.length; i++){ out.println(items[i].toString() + "<br>"); } }