Sunday, December 28, 2014

Apache JMeter - Testing RESTful WebServices

In this Tutorial let us see how to use Apache's JMeter to test the RESTful WebServices we developed in my previous post RESTful-web-service-with-spring-3x

1. Download JMeter Click here to download
2. This version requires Java 6 or later
3. Create a Thread Group and give a name, e.g "RestServiceTestingGroup"

4. Set "Number of Threads (Users)" to 5
5. Set "Ramp-up period (in seconds) to 1
6. Set "Loop Count" to 2

7. Right Click on RestServiceTestingGroup and choose Add->Sampler->HTTP Request

8. Give "getStockInformation" as Name
9. Set "localhost" in the "Server Name or IP" field
10. Give 8080 as "Port Number" value
11. Set "http" as Protocol
12. Choose "Post" as Method
13. Type "stock-service-war/stockService/getStockInformation" in the "Path" field
14. Open the "Body Data" Tab and paste the following XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?><stockRequest xmlns="http://com.mycompany.model.demo/stockRequest" xmlns:ns2="http://com.mycompany.model.demo/stockResponse"><stockSymbol>AAPL</stockSymbol><stockSymbol>GOOG</stockSymbol><stockSymbol>IBM</stockSymbol></stockRequest>

15. Right Click on RestServiceTestingGroup and choose Add->Listener->View Results Tree

16. Type "stockInfoServiceResult" in the Name field
17. Right Click on RestServiceTestingGroup and choose Add->Config Element->HTTP Header Manager

18. Leave the Name field as it's, if you want you can give a name
19. Clikc on "Add" button at the bottom
20. Type "Content-Type" in the "Name" field and "application/xml" in the Value field
21. Right Click on RestServiceTestingGroup and choose Add->Listener->View Results in Table

22. Now Click on "Start" button

23. To see the Request/Response Click on the "stockInfoServiceResult" on the Left navigation, click on the "getStockInformation" on the Right side panel and open "Request" & "Response" Tabs on the Right side

24. To see Timing statistics click on the "View Results in Table" on the Left navigation bar, this View shows How many times the service is invoked, What is the time taken for each service call, What is the average time of service call etc..
  
25. To get the JSON response from the Service choose "HTTP Header Manager" on the Left panel, Click the "Add" button on the bottom right side, Type "accept", "applicatoin/json" respectively in the Name and Value fields
26. Now hit the "Start" button, you can clear the existing results by clicking on "Clear" & "Clear All" buttons on the Tool Bar. The "stockInfoServiceResult" shows the JSON Response




Posted by at No comments:
Labels: , , ,

Monday, December 22, 2014

RESTful Web Service with Spring 3.x


In this Article let us see how to develop a RESTful Web Service with Spring 3.x

Tools I used:
  1. Eclipse 4.4 (Luna)
  2. jdk1.7.0_45
  3. apache-maven-3.2.1
  4. apache-tomcat-7.0.47

The requirement:
Write a Restful Webservice which accepts a list of Stock symbols and return the following for each of them
1. Stock Name
2. High Price
3. Low Price
4. Closing Price
5. Volume
6. Change in Price
6. Change in Percentage

Step 1:
1.1 Create a Java Project using the Maven command given below. This is where we are going to define the XSDs for input and output of the Service.
mvn archetype:generate -DgroupId=com.mycompany.model.demo -DartifactId=stock-model-jar -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
1.2 Switch to the folder stock-model-jar and run "mvn clean install" followed by "mvn eclipse:eclipse"
1.3 Import the Project stock-model-jar into Eclipse Workspace

1.4 Create a folder called xsd under src/main
1.5 Create two XSDs viz stockRequest.xsd & stockResponse.xsd
1.6 Add the following in the pom.xml

1.7 Execute the commands "mvn clean install", this generates Annotated Jaxb classes. The generated classes could be found under <your-workspace>\stock-model-jar\target\generated-sources\jaxb\com\mycompany\model\demo\oxm. The classes also bundled in the jar file.

Link to Source code

Step 2: Setting up the Spring MVC Web Application
2.1 Create a Maven Web application project called "stock-service-war" using the command
mvn archetype:generate -DgroupId=com.mycompany.rest.demo   -DartifactId=stock-service-war   -DarchetypeArtifactId=maven-archetype-webapp   -DinteractiveMode=false
2.2 Open the pom.xml and add the following


2.3 Switch to the directory stock-service-war and run "mvn clean install" followed by "mvn eclipse:eclipse"
2.4 Import the Project stock-service-war into Eclipse Workspace
2.5 If you have configured Web servers such as Apache Tomcat in your Eclipse just deploy the project and you could see the index.jsp rendered while invoking http://localhost:8080/stock-service-war/
2.6 Edit the web.xml and configure Spring MVC Servlet as follows


2.7 Create a file called "stockSDispatcher-servlet.xml" under the directory WEB-INF. The file name should be <Spring MVC Servlet name given in web.xml>-servlet.xml

2.8 Copy the following into stockSDispatcher-servlet.xml


2.9 Create a folder java under src/main and add this as a Source Folder by going to "Java Build Path" settings
2.10 Create a package "com.mycompany.rest.demo.controller" and a class in it called "StockServiceController.java"
2.11 Add the following into the Controller class


2.12 Deploy the Application in the Tomcat server
Link to Source code

Testing:
1. Open the Poster plug-in (Tools->Poster or Ctrl+Alt+P), if installed already in Firefox
2. Paste the following URL in the URL field "http://localhost:8080/stock-service-war/stockService/getStockInformation"
3. Paste the following XML in the "Content to Send" Tab's Text Area


4. Type "application/xml" in the "Content Type" Text field

5. Click on "Post" button. In this case the input is XML and the Output is XML as well
6. The following is the XML Response

Getting the JSON Response:
1. Open the "Headers" Tab
2. Type "accept" in the Name field, application/json in the Value field and Click on "Add/Change" button
3. Now click on "Post" button
4. The following is the JSON response
Submitting JSON Request:
1. Paste the following JSON input in the "Content to Send" Tab's Text Area
{"stockSymbol":["AAPL","GOOG","IBM"]}
2. Change the Content Type to "application/json"

3. Click on "Post" button
4. The "accept" header's value determines the Response format. If it's set to "application/xml" the service returns XML, if set to "application/json" then the service returns a JSON response.

Posted by at No comments:
Labels: , ,

Friday, August 29, 2014

Pre-defined Collection table in Oracle

select owner, type_name, coll_type, elem_type_name, length from dba_coll_types
where TYPE_NAME IN ('DBMS_DEBUG_VC2COLL', 'KU$_VCNT');


SELECT COLUMN_VALUE FROM TABLE(SYS.DBMS_DEBUG_VC2COLL('123', 'ABCD', 'LMN'));


SELECT COLUMN_VALUE FROM TABLE(SYS.KU$_VCNT('123', 'ABCD', 'LMN'));

Original post:
http://awads.net/wp/2005/10/13/pre-defined-collection-types-in-oracle
Posted by at No comments:
Labels:

36 Character GUID generation in Oracle using SYS_GUID

SELECT REGEXP_REPLACE(sys_guid(), '(........)(....)(....)(....)(............)','\1-\2-\3-\4-\5') FROM DUAL;
Posted by at No comments:
Labels: , , ,

Saturday, May 24, 2014

SQL Joins in Oracle

In this post let us look at different joins in Oracle SQL, as this is ANSI standard the same join should hold good for other databases too.
The following are the joins we are going to look at:
1. Equijoin
2. Outer Joins
2.1. Left Outer Join
2.2. Right Outer Join
2.3. Full Outer Join 
3. Self Join
Sample tables required for this exercise:
CREATE TABLE customer(customer_id varchar2(10), customer_name varchar2(20),
CONSTRAINT CID_PK PRIMARY KEY (customer_id));

CREATE TABLE contact(customer_id varchar2(10), phone varchar2(12),
CONSTRAINT cid_fk FOREIGN KEY (customer_id) REFERENCES customer(customer_id));

--Values for customer table
INSERT INTO customer (customer_id, customer_name) VALUES ('123', 'Jones')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('124', 'Smith')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('125', 'Martin')
/
INSERT INTO customer (customer_id, customer_name) VALUES ('126', 'Blake')
/

--Values for contact
INSERT INTO contact (customer_id, phone) VALUES ('123', '123-456-7890')
/
INSERT INTO contact (customer_id, phone) VALUES ('124', '123-456-0000')
/
INSERT INTO contact (customer_id, phone) VALUES (NULL, '222-456-7890')
/
INSERT INTO contact (customer_id, phone) VALUES (NULL, '333-456-7890')
/

The two customers Martin & Blake doesn't have any phone numbers assigned
The two phone numbers 222-456-7890 & 333-456-7890 has not been assigned to any customers

1. Equijoin:
Return those customers who have a phone numbers assigned
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust, contact ct WHERE cust.customer_id = ct.customer_id;

2. Outer Joins:
2.1. Left Outer Join:
Retrieve all Customers irrespective of whether they have a phone number assigned,
i.e. Customers who have a phone number assigned and those who have not a phone number assigned.
All the records from the table on the Left and only matching records from the table on the Right.
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust LEFT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;
   
Retrieving the same result with Right Outer Join:
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM contact ct RIGHT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;   
   
2.2. Right Outer Join:
Retrieve all Phone numbers irrespective of whether the phone number is assigned to a customer or not:
All the records from the table on the Right (i.e. contact table) and only matching records from the table on the Left (i.e. Customer table).
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust RIGHT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

Retrieving the same result with Left Outer Join:
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM contact ct LEFT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;   
   
2.3. Full Outer join:
Retrieves all the Customers (whether a phone number is assigned or not) as well as all the Phone numbers (whether the phone number is assigned to a Customer or not)
SELECT  cust.customer_id, cust.customer_name, ct.phone
    FROM customer cust FULL OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

3. Self Join:
In the employee table the Manager is also an record in the same table i.e. manager_id is one of the emp_id.
For an employee if there is no manager then the manager_id is left blank.

create table employee(emp_id number, name varchar2(25), manager_id number)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(100,'Jones',null)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(101,'Smith',100)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(102,'Martin',100)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(103,'Blake',null)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(104,'Adams',103)
/
INSERT INTO employee (emp_id, name, manager_id) VALUES(105,'James',103)
/

The Self join query retrieve the Employee information together with Manager name.
SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1, employee t2
where t1.manager_id = t2.emp_id;

What if I were to retrieve all employees who have a Manager assigned or not, we need to use the Outer Join
Here is the Query:
SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1 LEFT OUTER JOIN employee t2
ON t1.manager_id = t2.emp_id;

Here is the sample Output:
SQL> CREATE TABLE customer(customer_id varchar2(10), customer_name varchar2(20),
  2  CONSTRAINT CID_PK PRIMARY KEY (customer_id));

Table created.

SQL> CREATE TABLE contact(customer_id varchar2(10), phone varchar2(12),
  2  CONSTRAINT cid_fk FOREIGN KEY (customer_id) REFERENCES customer(customer_id));

Table created.

SQL> --Values for customer table
SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('123', 'Jones')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('124', 'Smith')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('125', 'Martin')
  2  /

1 row created.

SQL> INSERT INTO customer (customer_id, customer_name) VALUES ('126', 'Blake')
  2  /

1 row created.

SQL> --Values for contact
SQL> INSERT INTO contact (customer_id, phone) VALUES ('123', '123-456-7890')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES ('124', '123-456-0000')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES (NULL, '222-456-7890')
  2  /

1 row created.

SQL> INSERT INTO contact (customer_id, phone) VALUES (NULL, '333-456-7890')
  2  /

1 row created.

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust, contact ct WHERE cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust LEFT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
125        Martin                                                              
126        Blake                                                               

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM contact ct RIGHT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
125        Martin                                                              
126        Blake                                                               

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust RIGHT OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM contact ct LEFT OUTER JOIN customer cust ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   

SQL> SELECT  cust.customer_id, cust.customer_name, ct.phone
  2      FROM customer cust FULL OUTER JOIN contact ct ON cust.customer_id = ct.customer_id;

CUSTOMER_I CUSTOMER_NAME        PHONE                                          
---------- -------------------- ------------                                   
123        Jones                123-456-7890                                   
124        Smith                123-456-0000                                   
                                222-456-7890                                   
                                333-456-7890                                   
125        Martin                                                              
126        Blake                                                               

SQL> create table employee(emp_id number, name varchar2(25), manager_id number)
  2  /

Table created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(100,'Jones',null)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(101,'Smith',100)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(102,'Martin',100)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(103,'Blake',null)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(104,'Adams',103)
  2  /

1 row created.

SQL> INSERT INTO employee (emp_id, name, manager_id) VALUES(105,'James',103)
  2  /

1 row created.

SQL> SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1, employee t2
  2  where t1.manager_id = t2.emp_id;

    EMP_ID NAME                      MANAGER_NAME                              
---------- ------------------------- -------------------------                 
       102 Martin                    Jones                                     
       101 Smith                     Jones                                     
       105 James                     Blake                                     
       104 Adams                     Blake                                     

SQL> SELECT t1.emp_id, t1.name, t2.name AS manager_name FROM employee t1 LEFT OUTER JOIN employee t2
  2  ON t1.manager_id = t2.emp_id;

    EMP_ID NAME                      MANAGER_NAME                              
---------- ------------------------- -------------------------                 
       102 Martin                    Jones                                     
       101 Smith                     Jones                                     
       105 James                     Blake                                     
       104 Adams                     Blake                                     
       103 Blake                                                               
       100 Jones                                                               

6 rows selected.

Posted by at No comments:
Labels: , , , , ,

Thursday, April 10, 2014

Oracle BULK COLLECT INTO using EXECUTE IMMEDIATE

SET SERVEROUTPUT ON;

--Create a sample table
CREATE TABLE EMP(EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/

--Insert a few records
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (100, 'Regina Hall', 25)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (101, 'Morris Chestnut', 35)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (102, 'Terrence Howard', 37)
/
INSERT INTO EMP (EMP_ID, EMP_NAME, AGE) VALUES (103, 'Sanaa Lathan', 25)
/

--Create User defined Objects to use in BULK COLLECT
CREATE TYPE EMP_OBJ IS OBJECT (EMP_ID NUMBER, EMP_NAME VARCHAR2(20), AGE NUMBER(3))
/
CREATE TYPE EMP_TAB IS TABLE OF EMP_OBJ
/

--Anonymous block demonstrate the use of EXECUTE IMMEDIATE and BULK COLLECT INTO
DECLARE
    c_emp EMP_TAB;
    age_v NUMBER(3) :=25;
    query VARCHAR2(4000) := 'SELECT EMP_OBJ(EMP_ID, EMP_NAME, AGE) FROM EMP WHERE AGE=';
BEGIN
    query := query||age_v;
    EXECUTE IMMEDIATE query BULK COLLECT INTO c_emp;
    DBMS_OUTPUT.PUT_LINE('The number of employees in the Collection->'||c_emp.COUNT);
    FOR i IN c_emp.FIRST..c_emp.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(c_emp(i).emp_id||','||c_emp(i).emp_name||','||c_emp(i).age);
    END LOOP;
END;
/

Here is the output:
The number of employees in the Collection->2
100,Regina Hall,25
103,Sanaa Lathan,25

PL/SQL procedure successfully completed.
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)