Custom SQL Queries
Custom SQL Queries
You can use DataSource to execute custom SQL queries, while still employing all standard DataSource functions. If the SQL query includes calculated columns, you must declare them as "virtual" fields. For each virtual field you need to specify:
- Field name, matching the name of the calculated column in SQL
- Data type (text, number, date or time) which is used to parse the SQL results into corresponding Java objects.
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("SELECT status, COUNT(*) AS total_requests FROM wr GROUP BY status");
If the SQL query must use database-specific syntax, you can provide database-specific query variants to the DataSource. At runtime, the DataSource will choose the most specific query depending on the actual database server connection.
You can provide SQL query variants for the following SQL dialects:
generic
,
oracle
, and
sqlServer
. The generic variant is used when no specific variant for the current database server is found. For example, if your SQL query can work on SQL Server but not on Oracle, you need to specify two variants: generic and oracle.
When adding a virtual field, specify the number of decimals to be displayed in the user interface (the last parameter to the
addVirtualField()
method). If you specify 0, the value will be displayed as an integer.
ds.addQuery("SELECT status, COUNT(*) AS total_requests FROM wr GROUP BY status",
DataSource.DIALECT_GENERIC
);
ds.addQuery("SELECT status, COUNT(*) total_requests FROM wr GROUP BY status",
DataSource.DIALECT_ORACLE
);