Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
You can specify a query on multiple tables in a QueryDataSet
and JBuilder can resolve changes to such a DataSet
. SQLResolver
is able to resolve SQL queries that have more than one table reference. The metadata discovery will detect which table each column belongs to, and suggest a resolution order between the tables. The properties set by the metadata discovery are:
Column
- columnName
Column
- schemaName
Column
- serverColumnName
StorageDataSet
- tableName
StorageDataSet
- resolveOrder
The tableName
property of the StorageDataSet
is not set. The tableName
is identified on a per column basis.
The property resolveOrder
is a String array that specifies the resolution order for multi-table resolution.
INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order.
If a table is removed from the list, the columns from that table will not be resolved.
There is exactly one record in T2 that corresponds to a record in T1 and vice versa. A relational database may have this layout for certain tables for either clarity or a limitation of the number of columns per table.
There can be several records in T2 that correspond to a record in T1, but only one record in T1 corresponds to a record in T2. Example: each customer can have several orders.
There is exactly one record in T2 that correspond to a record in T1, but several records in T1 may correspond to a record in T2. Example: each order may have a product id, which is associated with a product name in the products table. This is an example of a lookup expressed directly in SQL.
The most general case.
It is generally uninteresting to replicate the master fields for each detail record in the query. Instead, create a separate detail dataset, which allows correct resolution of the changes.
These should generally be handled using the lookup mechanism. However if the lookup is for display only (no editing of these fields), it could be handled as a multi-table query. For at least one column, mark the rowId
property from the table with the lookup as not resolvable.
This table relationship arises very infrequently, and often it appears as a result of a specification error.
A query string may include table references and column references or aliases.
SELECT A.a1, A.a2, B.a3 FROM Table_Called_A AS A, Table_Called_B AS B
Column references are usually used to give a calculated column a name, but may also be used to differentiate columns with the same name originating from different tables.
SELECT T1.NO AS NUMBER, T2.NO AS NR FROM T1, T2
If a column alias is present in the query string, it becomes the columnName
of the Column
in JBuilder. The physical name inside the original table is assigned to the serverColumnName
property. The QueryResolver
uses serverColumnName
when generating resolution queries.
If a table alias is present in the query string, it is used to identify the tableName
of a Column
. The alias itself is not exposed through the JBuilder API.
The tableName
, schemaName
, and serverColumnName
properties are set by the QueryProvider
for a QueryDataSet
unless the metaDataUpdate
property does not include metaDataUpdate.TABLENAME
.
rowId
in a certain table of a query, all the updates to this table are not saved with the saveChanges()
call.
For a multi-table query, one of the tables can be updateable when the other is not. The StorageDataSet
property resolveOrder
is a String array that specifies the resolution order for multi-table resolution.
INSERT and UPDATE queries use the order of this array, DELETE queries use the reverse order.
If a table is removed from the list, the columns from that table will not be resolved.
For a single table, set the metaDataUpdate
property to NONE, and do not set any of the resolving properties (rowID
, tableName
, etc.).