Property Summary | |
---|---|
QBLogicalCondition | #and Create an AND-condition to add conditions to. |
QBColumns | #columns Get columns from query |
QBFunctions | #functions Get the functions clause from a query, used for functions that are not tied to a column. |
QBGroupBy | #groupBy Get the group by clause from a query <pre> // SELECT val2, COUNT(val2) FROM tab GROUP BY val2 ORDER BY COUNT(val2) DESC query. |
QBLogicalCondition | #having Get the having-part of the query, used to add conditions. |
QBJoins | #joins Get the joins clause of this table based clause. |
QBLogicalCondition | #or Create an OR-condition to add conditions to. |
QBParameters | #params Get the named parameters from a query. |
QBTableClause | #parent Get query builder parent table clause, this may be a query or a join clause. |
QBResult | #result Get the result part of the query, used to add result columns or values. |
QBSelect | #root Get query builder parent. |
QBSorts | #sort Get the sorting part of the query. |
QBLogicalCondition | #where Get the where-part of the query, used to add conditions. |
Method Summary | |
---|---|
QBCondition | #exists() Get an exists-condition from a subquery <pre> // where exists (select 1 from tab where flag = ?) query. |
QBColumn | #getColumn(name) Get a column from the table. |
QBColumn | #getColumn(columnTableAlias, name) Get a column from the table with given alias. |
QBParameter | #getParameter() Get or create a parameter for the query, this used to parameterize queries. |
QBCondition | #not(cond) Create an negated condition. |
QBCondition | #not(cond) |
Property Details |
---|
and |
Create an AND-condition to add conditions to. <pre> // where (flag = ? and order_date is null) or (flag = ? and order_date > ?) query.where().add( query.or() .add( query.and() .add(query.getColumn("flag").eq(new Integer(1))) .add(query.getColumn("order_date").isNull()) ) .add( query.and() .add(query.getColumn("flag").eq(new Integer(2))) .add(query.getColumn("order_date").gt(new Date())) ) ); </pre> |
Returns QBLogicalCondition |
Samplequery.where.add( query.or .add( query.and .add(query.columns.flag.eq(1)) .add(query.columns.order_date.isNull) ) .add( query.and .add(query.columns.flag.eq(2)) .add(query.column.order_date.gt(new Date())) ) ); |
columns |
Get columns from query |
Returns QBColumns |
Samplefoundset.getQuery().columns |
functions |
Get the functions clause from a query, used for functions that are not tied to a column. <pre> // select pk from tab where floor(val / ?) > pk [1999] IQueryBuilder query = queryFactory.createSelect(table.getDataSource()).result().addPk().getParent(); query.where() .add(query.functions().floor(query.getColumn(val.getName()).divide(new Integer(1999))).gt(query.getColumn(id.getName()))) .getRoot().sort().add(query.getColumn(id.getName()).asc()); </pre> |
Returns QBFunctions |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') //$NON-NLS-1$ query.where.add(query.columns.shipname.upper.eq(query.functions.upper('servoy'))) //$NON-NLS-1$ foundset.loadRecords(query) |
groupBy |
Get the group by clause from a query <pre> // SELECT val2, COUNT(val2) FROM tab GROUP BY val2 ORDER BY COUNT(val2) DESC query.result().add(query.getColumn(val2.getName())).add(query.getColumn(val2.getName()).count()) .getParent().groupBy().add(val2.getName()) .getParent().sort().add(query.getColumn(val2.getName()).count().desc()); </pre> |
Returns QBGroupBy |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries .root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0)) foundset.loadRecords(query) |
having |
Get the having-part of the query, used to add conditions. The conditions added here are AND-ed. <pre> // select value from tab group by value having count(value) > 1 query.result().add(query.getColumn("value")) .getParent().groupBy().add("value") .getParent().having().add("value").count().gt(Integer.valueOf(1))); </pre> |
Returns QBLogicalCondition |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries .root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0)) foundset.loadRecords(query) |
joins |
Get the joins clause of this table based clause. <p>Joins added to this clause will be based on this table clauses table. |
Returns QBJoins |
Samplefoundset.getQuery().joins |
or |
Create an OR-condition to add conditions to. <pre> // where custid = ? and (order_date is null or order_date > ?) query.where().add(query.getColumn("custid").eq(new Integer(200))) .add( query.or() .add(query.getColumn("order_date").isNull()) .add(query.getColumn("order_date").gt(new Date())) ); </pre> |
Returns QBLogicalCondition |
Samplequery.where.add( query.or .add( query.and .add(query.columns.flag.eq(1)) .add(query.columns.order_date.isNull) ) .add( query.and .add(query.columns.flag.eq(2)) .add(query.column.order_date.gt(new Date())) ) ); |
params |
Get the named parameters from a query. |
Returns QBParameters |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid'))) // load orders where contact_id = 100 query.params['mycontactid'] = 100 foundset.loadRecords(query) // load orders where contact_id = 200 query.params['mycontactid'] = 200 foundset.loadRecords(query) |
parent |
Get query builder parent table clause, this may be a query or a join clause. |
Returns QBTableClause |
Sample/** @type {QBSelect<db:/example_data/person>} */ var query = databaseManager.createSelect('db:/example_data/person') query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john')) foundset.loadRecords(query) |
result |
Get the result part of the query, used to add result columns or values. <pre> query.result().add(query.getColumn("id")).add.query.getColumn("note"); // select id, note from tab </pre> |
Returns QBResult |
Samplequery.result.add(query.columns.company_id).add(query.columns.customerid) |
root |
Get query builder parent. |
Returns QBSelect |
Sample/** @type {QBSelect<db:/example_data/order_details>} */ var subquery = databaseManager.createSelect('db:/example_data/order_details') /** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.where.add(query .or .add(query.columns.order_id.not.isin([1, 2, 3])) .add(query.exists( subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root )) ) foundset.loadRecords(query) |
sort |
Get the sorting part of the query. <pre> query.sort().add(query.getColumn("note").desc()).add(query.getColumn("id")); // order by note desc, id asc </pre> |
Returns QBSorts |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.sort .add(query.joins.orders_to_order_details.columns.quantity.desc) .add(query.columns.companyid) foundset.loadRecords(query) |
where |
Get the where-part of the query, used to add conditions. The conditions added here are AND-ed. <pre> query.where().add(query.getColumn("flag").eq(new Integer(1)).add(query.getColumn("foo").isNull(); // where flag = 1 and foo is null </pre> |
Returns QBLogicalCondition |
Samplevar query = foundset.getQuery() query.where.add(query.columns.flag.eq(1)) |
Method Details |
---|
exists |
QBCondition exists () |
Get an exists-condition from a subquery // or simple variant: adds 'select 1' and calls getRoot() |
Returns QBCondition |
Samplefoundset.query.where.add(query.exists(query2)) |
getColumn |
QBColumn getColumn (name) |
Get a column from the table. |
Parameters {String} name – the name of column to get |
Returns QBColumn |
Samplefoundset.getQuery().getColumn('orderid') |
getColumn |
QBColumn getColumn (columnTableAlias, name) |
Get a column from the table with given alias. <p>The alias may be of the main table or any level deep joined table. |
Parameters {String} columnTableAlias – the alias for the table {String} name – the name of column to get |
Returns QBColumn |
Samplefoundset.getQuery().getColumn('orderid', 'opk') |
getParameter |
QBParameter getParameter () |
Get or create a parameter for the query, this used to parameterize queries. <pre> query.where().add(query.getColumn("flag").eq(query.getParameter("myvar")); query.getParameter("myvar").setvalue(new Integer(1)); </pre> |
Returns QBParameter |
Sample/** @type {QBSelect<db:/example_data/orders>} */ var query = databaseManager.createSelect('db:/example_data/orders') query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid'))) // load orders where contact_id = 100 query.params['mycontactid'] = 100 foundset.loadRecords(query) // load orders where contact_id = 200 query.params['mycontactid'] = 200 foundset.loadRecords(query) |
not |
QBCondition not (cond) |
Create an negated condition. <pre> // where not (order_date is null or order_date > ?) query.where().add(query.not( query.or() .add(query.getColumn("order_date").isNull()) .add(query.getColumn("order_date").gt(new Date())) ) ); </pre> |
Parameters {QBCondition} cond – the condition to negate |
Returns QBCondition |
Samplefoundset.query.where.add(query.not(query.columns.flag.eq(1))) |
not |
QBCondition not (cond) |
Replace with description |
Parameters {QBLogicalCondition} cond – the logical condition to negate |
Returns QBCondition |
Samplefoundset.query.where.add(query.not(query.columns.flag.eq(1))) |