Fabric provides a Java Db class to use the methods and functions that invoke database (SQL) queries and statements and Fabric commands.
The following Fabric UserCode methods can be used to create a Db object:
DB Class Methods - Common Use Cases
The following table describes common use cases when working with DB interfaces.
To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html
In specific cases, a query or statement may require input parameters. For example, Select all Customers by Customer Status.
When using prepared statement queries always use binding parameters:
See the example in the table above.
DB.ROWS and DB.ROW Classes
The fetch() method returns the Db.Rows object which represents a result set of a query.
You can iterate the result set and get a Db.Row object for each record, or alternatively, get the first value or the first row of the result set using firstValue() and firstRow() methods.
Notes
To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html
The following methods can be used to loop a result set:
Example 1
Iterating the result set and checking the column name and value for each record:
Db.Rows rows = db(..).fetch(...);
row.forEach(r->{
// each r is a map that represents a row.
// Iteration order is guaranteed to follow the column order.
r.forEach((col,value)->{
...
});
});
Example 2
Iterating the result set and yielding the values of each record:
Db.Rows rows = db(..).fetch(...);
rows.each(row-> yield(row.cells()));
try (Db.Rows rows = db(..).fetch(...)) {
for (Db.Row row:rows) {
}
}
Note that since you are not using the each/forEach method the ResultSet will not close at the end of the loop and is deferred to the end of the current execution (sync/webservice/job).
String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);
Integer noOfOrders = Integer.parseInt(rows.firstValue().toString());
Note that calling firstValue also closes the result set.
String sql = "SELECT * from CONTRACT WHERE CONTRACT_ID = ? “;
Db.Row row = ludb().fetch(sql, contractId).firstRow();
Note that calling firstRow also closes the result set.
String sql = "SELECT ACTIVITY_ID, CASE_ID, CASE_DATE, CASE_TYPE, STATUS FROM CASES where activity_id = ?";
Db.Rows rows = db("CRM_DB").fetch(sql, input);
// Option 1 - using each method
rows.each(row-> yield(row.cells()));
// Option 2- using for loop
for (Db.Row row:rows) {
yield(row.cells());
}
Db.Rows rows = ludb().fetch(sql);
for (Db.Row row : rows) {
if (row.cell(0) != null) { // check the value of first column of the select statement
values.append("\"" + row.cell(0) + "\",");
}
}
Fabric has the following set of deprecated methods for handling DB interfaces:
Although they work, a warning message is displayed when deprecated methods are used in Fabric code. It is recommended to use the new Fabric methods to invoke DB interfaces instead of working with deprecated methods.
Fabric provides a Java Db class to use the methods and functions that invoke database (SQL) queries and statements and Fabric commands.
The following Fabric UserCode methods can be used to create a Db object:
DB Class Methods - Common Use Cases
The following table describes common use cases when working with DB interfaces.
To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html
In specific cases, a query or statement may require input parameters. For example, Select all Customers by Customer Status.
When using prepared statement queries always use binding parameters:
See the example in the table above.
DB.ROWS and DB.ROW Classes
The fetch() method returns the Db.Rows object which represents a result set of a query.
You can iterate the result set and get a Db.Row object for each record, or alternatively, get the first value or the first row of the result set using firstValue() and firstRow() methods.
Notes
To view the list of Fabric APIs, click http://[Fabric IP address]:3213/static/doc/user-api/index.html
The following methods can be used to loop a result set:
Example 1
Iterating the result set and checking the column name and value for each record:
Db.Rows rows = db(..).fetch(...);
row.forEach(r->{
// each r is a map that represents a row.
// Iteration order is guaranteed to follow the column order.
r.forEach((col,value)->{
...
});
});
Example 2
Iterating the result set and yielding the values of each record:
Db.Rows rows = db(..).fetch(...);
rows.each(row-> yield(row.cells()));
try (Db.Rows rows = db(..).fetch(...)) {
for (Db.Row row:rows) {
}
}
Note that since you are not using the each/forEach method the ResultSet will not close at the end of the loop and is deferred to the end of the current execution (sync/webservice/job).
String sql = "SELECT COUNT(*) AS TOT_NUM_OF_ORDERS FROM ORDERS WHERE CONTRACT_ID = ? and ORDER_STATUS != ?";
Db.Rows rows = ludb().fetch(sql, contractId, orderStatus);
Integer noOfOrders = Integer.parseInt(rows.firstValue().toString());
Note that calling firstValue also closes the result set.
String sql = "SELECT * from CONTRACT WHERE CONTRACT_ID = ? “;
Db.Row row = ludb().fetch(sql, contractId).firstRow();
Note that calling firstRow also closes the result set.
String sql = "SELECT ACTIVITY_ID, CASE_ID, CASE_DATE, CASE_TYPE, STATUS FROM CASES where activity_id = ?";
Db.Rows rows = db("CRM_DB").fetch(sql, input);
// Option 1 - using each method
rows.each(row-> yield(row.cells()));
// Option 2- using for loop
for (Db.Row row:rows) {
yield(row.cells());
}
Db.Rows rows = ludb().fetch(sql);
for (Db.Row row : rows) {
if (row.cell(0) != null) { // check the value of first column of the select statement
values.append("\"" + row.cell(0) + "\",");
}
}
Fabric has the following set of deprecated methods for handling DB interfaces:
Although they work, a warning message is displayed when deprecated methods are used in Fabric code. It is recommended to use the new Fabric methods to invoke DB interfaces instead of working with deprecated methods.