Post explain how to connect to Postgres[1] database (Sophisticated open-source Object-Relational DBMS) from WSO2 Data Services Server[2]. In here we will be using wso2 DSS 3.0.1and Postgres 9.2
1. Get WSO2 Data Services Server (DSS) from here
2. Unzip and get Postgres jdbc driver (postgresql-9.2-1002.jdbc3.jar) and put it in <wso2dss_home>\repository\components\lib\
3. Start DSS from wso2server.bat in ‘bin’
4. Create Table in Postgres (In here I have created database called “sample” and table called “test” with 3 columns) as below
5. Now Go to “https://localhost:9443/carbon/” and login using “admin” and “admin”
6. Go to “Home > Manage > Services > Add > Data Service > Create”
7. Give “PostgresDataService” as Data Service Name and click Next
8. Then Add New Data Source By click on that link
9. Fill the for as below (URL will be like this jdbc:postgresql://[HOST]:[PORT5432]/[database])
10. Click Test Connection for testing DB connection
11. Then Click “save” and “Next”
12. Add New Query
Here is sample Query “select * from test where id=:id”
13. Then do Input Mappings
By click on "Add New Input Mapping"
Add as below
Click and "add" then "Main Configuration"
14. Then do Result (Output Mapping)
Fill the form as above and then click on "Add New Output Mapping"
then fill again as below
Click and "add" then "Main Configuration"
Do this for all fields that in results.
15. Then you see all the input and output map like this
16.Click “save”. if you need any query you can add it. After that click on “next”
17. Then Add New Operation as below
18. Click “save” and “next”
19. Then click “finish”
20. Console fill show below logs
In service list your will see the services that we create now, Click on tryit
and try it out.
Here I am trying out
Here is my full dbs file
<data name="PostgresDataService">
<config id="postGregsTest">
<property name="driverClassName">org.postgresql.Driver</property>
<property name="url">jdbc:postgresql://localhost:5432/sample</property>
<property name="username">postgres</property>
<property name="password">admin</property>
</config>
<query id="query1" useConfig="postGregsTest">
<sql>select * from test where id=:id</sql>
<result defaultNamespace="age" element="age" rowName="age">
<element column="name" name="name" namespace="name" xsdType="string"/>
<element column="id" name="id" namespace="id" xsdType="integer"/>
<element column="age" name="age" namespace="age" xsdType="string"/>
</result>
<param name="id" sqlType="INTEGER"/>
</query>
<operation name="getStudent">
<call-query href="query1">
<with-param name="id" query-param="id"/>
</call-query>
</operation>
</data>
Enjoy wso2 DSS with Postgres!!
Add a comment