Introduction
In this blog post I will be creating a sample data service which has two operations to get all employees under a one department by giving department id and insert employee details. I will be covering following steps in order to create sample.
- Prerequisites
- Create Database
- Create data service as a .car using WSO2 developer studio
- Deploy data service on WSO2 data service server.
Prerequisites
For this example I have provided two sql scripts, mysql.sql which can be used to create database and relevant tables, and data.sql to insert sample data into tables created. In order to create database used in this example please follow given steps below.
1. Enter following command in the terminal, where username is the username you want to use to access the database.
mysql> mysql -u username -p
2. When prompted enter the password of the specified user name.
3. Source mysql.sql file downloaded using below command.
mysql> source <path to mysql.sql file>/mysql.sql
4. Create a new database user given username as 'sewmi' and password as password.
5. Give all privileges to database 'finance' for created user sewmi.
mysql> CREATE USER 'sewmi'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON finance . * TO 'sewmi'@'localhost';
mysql> FLUSH PRIVILEGES;
6. Source data.sql file downloaded using below command
mysql> source <path to data.sql file>/data.sql
7. To check database finance is created use below command
mysql> show databases;
8. To check whether the tables are created use;
mysql> use finance;
mysql> show tables;
9. Output of query 7 and 8 should be as below.
10. To check whether data is being successfully inserted;
mysql> select * from <table name>;
If data.sql scrip successfully executed user should be able to see data in created data tables.
Employee table with sample data
Create a carbon deployable data service
1. Open Eclipse IDE
2. Select developer studio > Open Dashboard.
3. Select composite application project.
4. In the wizard give project name as 'TestApp' and click 'finish'.
5. Right click on project and select TestApp > new > Data service project.
6. Data service artifact creation operation wizard will open. Select 'create new data service' radio button and click 'next'.
7. Give project name as 'RdbmsDataServiceSample' and data service name as EmployeeDataService and click 'next'
8. Configure datasource given FinanceDB as datasource id, 'RDBMS' as datasource type, JDBC URL, username as sewmi password as 'password' and click 'finish'.
9. Right click on 'EmployeeDataService' and select Add Query.
10. Give query is as 'getEmployeesUnderSameDepartment' and save. (Data source should be already selected as FinanceDB)
11. Right click on added query in the previous and select 'Add Sql'. Editor will open to enter sql query. Enter below query as shown in the screenshot and save.
SELECT department.dept_name, employee.first_name, employee.last_name FROM department INNER JOIN Dept_Emp ON department.dept_no = Dept_Emp.dept_no INNER JOIN employee ON Dept_Emp.emp_no = employee.emp_no WHERE department.dept_no = ?
12. Right click on the query and select Add input mapping.
Note: We use an input mapping when ever we have a variable to pass in order to execute
query. In this example in above step department number should be given when invoking the service therefore we need to add properties of the variable as an input mapping. As shown below mapping name can be any name which represent the parameter we pass. Parameter type is given as SCALR since it is a single value. (Not an array). sql type is given as integer since parameter is an integer.
13. Right click on added query and select 'Add Output Mapping'.
14. Enter employees 'In Group by Element' field, employee in 'Row Name' and save.
15. Right click on the query and select added output mapping, > Add Output Mapping > Add Element.
Note : As shown in the screenshot below we need to configure elements for the information return by the query. In this example when user pass department id he should get department name, user's first name and last name. therefore we need to add three elements for department name, first name and last name, under result which specifies a mapping between actual database column name and name which should be display as output field name.
Element - 01
Element - 02
Element - 03
16. Right click on 'EmployeeDataService' and select Add operation.
17. Click on newly added operation and give it a meaningful name.
Click on the arrow head in front of the operation, when the list expands select 'call-query' element. You will get an option to incorporate query you created in the previous step with the newly added operation. Select correct query relate to operation. If you
18. Right click on 'EmployeeDataService' and select Add resource. Give resource method as 'get' Specify resource path; in this example i have used resource1 as the resource path. Then click on the query under resource in the project outline window. select 'getEmployeeDetails' as the query Id.
Note : Adding a resource is not a compulsory step. Using resources we can expose the data service as a rest service.
Now we can build the project in order to get the .car deployable. When building the project,
1. Build the data service pom file.
2. Once the project is build then build the pom file of the main project.
After successfully building you will be able to find the .dbs file under the target folder of the data service and the .car file under the target folder of the main project.
Deploy data service under WSO2 data service server.
1. Unzip wso2 data serve server (DSS).
2. Copy downloaded jdbc connector jar into <DSS_home>/repository/components/libs
3. Go to <DSS_home>/bin and run ./wso2server.sh in the command line.
4. Once server is up you will see "WSO2 Carbon started in 15 sec" and the URL to access DSS in the log.
5. go to https://192.168.1.7:9443/carbon/.
6.Login to the mgt console given username and password as admin.
7. Then go to main > carbon applications > Add
8. Select .car deployable file we created and upload.
9. Once it is uploaded data service will be deployed on the server and to view it go to main > services > list.
