Loading DataSource and Restrictions

Data Sources defined in any view file can be loaded directly from view files:

load the DataSource from the view file

final DataSource dataSource = DataSourceFactory.loadDataSourceFromFile(VIEW_NAME, dataSourceName);

Custom SQL Queries with new Sql Classes

You can use DataSource to execute custom SQL queries,

Examples:

DataSource ds = DataSourceFactory.createDataSource();

ds.addTable("wr");

ds.addField("wr_id");

ds.addField("status");ds.addField("date_requested");

ds.addVirtualField("total_requests", DataSource.DATA_TYPE_NUMBER);

ds.addQuery(new Sql("SELECT status, COUNT(*) AS total_requests FROM wr GROUP BY status"));

Restrictions:

You can also add custom restrictions to the data source using a new Sql class. below, we are adding the building 'AB' and site 'BEDFORD' restrictions.

dataSource.addRestriction(Restrictions.sql("bl_id=? AND bl.site_id=?", "AB", "BEDFORD"));

Sql bldRestriction = new Sql.Builder("(wr.bl_id=").literal(blId)

.append(" AND wr.status='I' )")

.toSql();

dataSource.addRestriction(Restrictions.sql(bldRestriction));

see, Custom SQL Queries

Executing SQL Update with new Sql Classes

The SQL update query inserts, updates, or deletes database records. You can execute an SQL update using the SqlUtils.executeUpdate() static method:

Examples:

SqlUtils.executeUpdate("pmpsum", new Sql("DELETE FROM pmpsum"));

Using Sql.Builder

Sql sql = new Sql.Builder(“ UPDATE ”)

.table(WR_SYNC_TABLE).append(“ SET “)

.append("wr_sync.is_req_supervisor=1,")

.append(“ FROM “)

.append(WR_TABLE)

.append(“ WHERE ”)

.append(" wr.supervisor=").literal(emId) // emId is a literal value

.toSql();

SqlUtils.executeUpdate(WR_SYNC_TABLE, sql);

Sql sql = new Sql.Builder(“ UPDATE ”)

.table(WR_SYNC_TABLE).append(“ SET “)

.append("wr_sync.is_req_supervisor=1,")

.append(“ FROM “)

.append(WR_TABLE)

.append(“ WHERE ”)

.append(" wr.supervisor=").literal(emId) // emId is a literal value

.toSql();

SqlUtils.executeUpdate(WR_SYNC_TABLE, sql);

Using SqlTemplates

Sql sql = new Sql("UPDATE WR_SYNC_TABLE SET wr_sync.is_req_supervisor = 0

WHERE wr.supervisor = #{0}", OgnlNotation.OGNL).formatTemplate(Sql.valueOfLiteral(emId));

SqlUtils.executeUpdate(“WR_SYNC_TABLE”, sql);

Also see:

• Executing SQL Update

• New Sql Classes