| [Documentation Home] | [Release Note]
Relational Databases are one of the mainly used data sources in data services. Here in this sample, we would be using an embedded H2 database
in demonstrating the use of an RDBMS data source with WSO2 Data Services Server.
The sample data services should be deployed as per the instructions mentioned in Deploying Samples section.
Running the Sample
The sample service "RDBMSSampleService", can be run using the "tryit" tool, which is bundled with WSO2 Data Services Server, or a code-generated java client sample using this service is demonstrated in the Data Services Clients Sample section.
The scenario is based on a possible database used in a small company to handle its internal affairs. Such as keeping employee and customer records, and information about the products that are produced in the company.
There are seperate queries/operations written for specific tasks of the company, the operations implemented in the service are listed as follows.
- customersInBoston - Returns all the customers in the Boston area. This operation is linked to a query, which has the following simple SQL statement, "select * from Customers where city = 'Boston' and country = 'USA'".
- employeesByNumber - Returns employee details, given the employee number. This is an example of a data service operation, which takes in a parameter. The linked query's SQL statement is written in the following way, "select * from Employees where employeeNumber = ?", where the "?" represents the parameter which is defined in the query.
- customerAddress - Returns the customer's address, given the person's name.
- productsInfo - This operation lists information on all the products that are produced in the company.
- setEmployeeSalary - Sets the salary of an employee, given the employee number and the revised salary.
- setSalaryForEmployees - Sets the given salary to a given set of employees, here the usage of array data type is demonstrated.
Where the employee numbers is an INTEGER ARRAY type.
- incrementEmployeeSalary - Increment the given employee's salary with the given amount.
- addEmployee - Adds a new employee records with the given information.
Boxcarring support is enabled in this service. By enabling the boxcarring option, the service will contain "begin_boxcar", "end_boxcar" and "abort_boxcar" control operations. Boxcarring is roughly analogous to transactions we see in database systems. The usage of boxcarring can be demonstrated by executing the following operations using the "tryit" tool. NOTE: An employee with employeeNumber=1002 is already in the system, we will be using that for the demonstration.
- Run "employeesByNumber" operation with the "employeeNumber" 1002. And we will see an output as seen below,
Figure 1: Normal service invocation, without boxcarring.
- Run the "begin_boxcar" operation, now the service will switch to a boxcarring session.
- Run the "incrementEmployeeSalary" operation and give the values "1000" for increment and "1002" for the employeeNumber fields.
- Open up a second different type of a browser, this is done for the service to have a fresh session, so it wont be in the same boxcarring session as earlier. After switching browsers, run the "employeesByNumber" operation with employeeNumber=1002 to check the employee information, and you will see that the
salary has not been incremented. This is because, when the earlier request was made in boxcarring mode, the requests are not executed immediately, but will be executed when
the boxcarring session is ended.
- Go back to the original browser, run the "incrementEmployeeSalary" operation another time and give the values "500" for increment and "1002" for the employeeNumber fields, and finally run the "end_boxcar" operation.
- Now in any of the two browsers, run the "employeesByNumber" operation with employeeNumber=1002 to check the information. The output will be similar to the following,
Figure 2: Service invocation after a boxcarring session.
As we can see, both "incrementEmployeeSalary" operations has been executed at once when the boxcarring session was ended with "end_boxcar", thus resulting in the salary value 3500. If there's any error in any of the operations inside a boxcarring session, non of the operations will be executed and the service calls will be rolled back. A boxcarring session can also be explicitely cancelled by calling the "abort_boxcar" operation.
This service use validators, in places where the validity of the input parameters have to be checked. This is shown in the "addEmployeeQuery" operation,
by having a length validator for the "lastName" field, where the length of the value should be between 3 and 20, and also the "email" field is validated using a pattern validator which uses an regular expression to check if it's a real email address. Follow the below steps to demonstrate this functionality,
- Run the "addEmployee" operation with the parameters, employeeNumber=6001; lastName=AB; firstName=Nick; firstname.lastname@example.org; salary=1500. After running the operation, run the "employeesByNumber" operation with the parameter employeeNumber=6001, and you will see that you are not getting any results. This is because, the earlier "addEmployee" operation has failed, because of a validation failure, which is where lastName was only 2 characters long.
- Run the "addEmployee" with parameters, employeeNumber=6001; lastName=ABC; firstName=Nick; email=test-test.com; salary=1500. Here also you will see that the entry has not been added by checking with the "employeesByNumber" operation, because here, the email address is not valid.
- Run the "addEmployee" with parameters, employeeNumber=6001; lastName=ABC; firstName=Nick; email@example.com; salary=1500. Here the entry will be successfully added and you will get a result as the following when you call the "employeesByNumber" operation with the employeeNumber=6001.
Figure 3: Successful service invocation with validators.
Default Values Demo
The operation "addEmployee" uses the facility to give default values to certain fields, if they were chosen not to be given an explicit value.
Here the parameter "salary" is given the default value 1500. This can be checked by calling the "addEmployee" operation by giving the parameter values leaving out the "salary" value. For example, if the parameter values are employeeNumber=6002; lastName=Smith; firstName=John; firstname.lastname@example.org, a call to the operation "employeesByNumber" with employeeNumber=6002 will result in the following,
Figure 4: Service invocation with default values.
As you see, alongside the given parameters, the "salary" value also have been set to 1500, which is the default value for that field, when the parameter value is not given.
Query Result Export Demo
The operations "thousandFive" and "incrementEmployeeSalaryEx" are used in demonstrating the query result export functionality in a data service. The query used in "thousandFive" operation exports the a result value with the name "increment". This increment value is taken in as a parameter by the query in the "incrementEmployeeSalaryEx" operation. Figure 5 shows the dbs fragment that defines this behaviour.
Figure 5: Query result export sample dbs fragment.
This scenario can be tested using the try-it tool. Run the following operations sequentially.
You will notice that the return values from the first "employeeByNumber()" call and the second will have a difference of 1500, which is the amount that was incremented within the boxcarring session, where the 1500 value was passed into "incrementEmployeeSalaryEx" operation by the "thousandFive" operation.