SQL DataDescriptor - Learn JBStrap

JBStrap SQL DataDescriptor

SQL DataDescriptors allow you to handle data stored in an SQL database. This data descriptor also uses custom SQL statements to read and update data, so it has native data access. Native data access enables you to use unique syntactic elements and functions of the database, otherwise hidden from developers by the JPA layer.

Native data access also means that there are no entity classes for the data descriptor. When creating a data descriptor, you have to specify the SQL statements for a given operation. If you don't specify an SQL statement for an operation, that operation cannot be used with the data descriptor.

To create a data descriptor, you have to specify the following SQL statements.

Select statement (selectSQL)

Use this setting to specify the SQL statement to be executed by the data descriptor to fetch the data. The set of records returned by this query is the entire data set known to the data descriptor. Search criteria specified for the data descriptor can only narrow this data set down, but cannot expand it.

Example:

Insert statement (insertSQL)

Use this setting to specify the SQL statement to be used by the data descriptor if a new record is inserted into the database. In the SQL statement, you can use the columns in the data descriptor and reference them in the statement after a : character.

Example:

        insert into supplier (supplierId, supplierName, supplierCountry) 
values (:supplierId, :supplierName, :supplierCountry)
       

Update statement (updateSQL)

Use this setting to specify the SQL statement to be executed by the data descriptor when updating a record. In the SQL statement, use the columns listed in the data descriptor. You can reference data descriptor columns by inserting a : character before the column name.

Example:

        update supplier
set supplierName = :supplierName,
supplierCountry = :supplierCountry
where supplierId = :supplierId
       

Delete statement (deleteSQL)

Use this setting to specify the SQL statement to be executed when a record is deleted. In the SQL statement, you can also use the columns listed in the data descriptor. You can reference a data descriptor column by inserting a : character before its name.

Example:

You can also create this data descriptor from an XML file or you can create it programmatically.

Creating a simple SQL DataDescriptor with an XML structure:

        <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xml>
<datadescriptor name="Example" connectorType="SQL">
 <access>
   <type>Public</type>
 </access>
 <params>
   <param name="selectSQL">select supplierId, supplierName, supplierCountery from suppler</param>
   <param name="insertSQL">insert into supplier (supplierId, supplierName, supplierCountry) values (:supplierId, :supplerName, :supplerCountry)</param>
   <param name="updateSQL">update supplier set supplerName = :supplierName, supplierCountry = :supplierCountry where suppoerId = :supplierId</param>
   <param name="deleteSQL">delete from supplier where supplierId = :supplierId</param>
 </params>
 <column name="supplierId" datatype="NUMBER" primaryKey="true" nullable="false" />
 <column name="supplierName" datatype="VARCHAR2" length="255" />
 <column name="supplierCountry" datatype="VARCHAR2" length="40" />
</datadescriptor>
       

Programmatically creating an SQL DataDescriptor that corresponds to the above XML:

        SQLDataDescriptor dataDescriptor = new SQLDataDescriptor("Example")
.setAccess(AccessType.PUBLIC)
.setSelectStatement("select supplierId, supplierName, supplierCountry from supplier")
.setInsertStatement("insert into supplier (supplierId, supplierName, supplierCountry) values (:supplierId, :supplierName, :supplierCountry)")
.setUpdateStatement("update supplier set supplierName = :supplierName, supplierCountry = :supplierCountry where supplierId = :supplierId")
.setDeleteStatement("delete from supplier where supplierId = :supplierId")
.addColumn(new DataDescriptorColumn("supplierId", DataDescriptorColumnType.LONG), "Example")
.setPrimaryKey(true)
.setNullable(false)
)
.addColumn(new DataDescriptorColumn("supplierName", DataDescriptorColumnType.TEXT), "Example")
.setLength(255)
)
.addColumn(new DataDescriptorColumn("supplierCountry", DataDescriptorColumnType.LONG), "Example")
.setLength(40)
);
       

You can perform any sort of data handling operation by using the SQL DataDescriptor: reading data from the database, updating and removing existing data.

Fetching all records from the database by using a data descriptor:

Fetching all records satisfying the specified criteria using a data descriptor:

        List<DataRecord> result = dataDescriptor.find(new Criteria("supplierCountry", OperatorType.EQUALS, "USA"));
       

Fetching a record using its primary key:

Saving a record to the database using a data descriptor:

Removing a record from the database using a data descriptor:

Data descriptors also support transaction handling. To handle a transaction, you have to specify an SQLTransaction for the DataDescriptor.

Fetching a record, updating and saving it within a single transaction:

        // Start new transaction
SQLTransaction transaction = new SQLTransaction();
// Read record from the database
DataRecord supplier = dataDescriptor.findById(transaction, null, 25L);
// Modify the record value
supplier.setValue("supplierName", "New Supplier Name");
// Save the modified record to the database
dataDescriptor.save(supplier, null, transaction);
// Commit the changes in the database
transaction.commit();
       