SQL Server Data Services Meet Java

  • [アーティクル]

In my last post I covered a simple example of querying SSDS using C#, HttpWebRequest and XLinq.  Today, I thought I'd try to use another language (one we at Microsoft didn't have a hand in creating) Java. 

As I did in my last post I'm going to start with a little bit of infrastructure.  It's worth noting at this point that I'm using the HttpClient libraries that are freely available from the Jakarta Commons project of Apache.  This makes working with REST services pretty painless in the Java development environment.

Making Requests

The first thing that we'll need to construct is a way for us to issue requests to the SSDS service.  As I did previously, I've created a simple helper method to make this easier for me to do my work.  This method simply takes in the HTTP Method, the URI, and finally a string to use as the request body.  It will return to the caller the body of the response that we received back from the server.

     private static String IssueRequest(String method,
                                       String url,
                                   String body) throws Exception
    {
       String response = "";
       
        // Define the HTTP client that we'll use.
        HttpClient client = new HttpClient();
  HttpMethod requestMethod = null;
        
    // Choose the method we'll be using to issue the request.
   if(method.equals("GET"))
    {
            requestMethod = new GetMethod(url);
    }
   else if (method.equals("POST"))
 {
            requestMethod = new PostMethod(url);
            ((PostMethod)requestMethod).setRequestBody(body);
            ((PostMethod)requestMethod).setRequestHeader("Content-Type", XmlContentType);
 }
   else if(method.equals("PUT"))
   {
            requestMethod = new PutMethod(url);

             ((PutMethod)requestMethod).setRequestBody(body);
            ((PutMethod)requestMethod).setRequestHeader("Content-Type", XmlContentType);
    }
   else if(method.equals("DELETE"))
    {
            requestMethod = new DeleteMethod(url);
 }
   else
    {
            throw new Exception("Unsupported method type provided");
  }
          
 URL requestUrl = new URL(url);
  try
 {
       // Next, set the credentials we'll be using on the request.
            Credentials defaultcreds = new UsernamePasswordCredentials(UserId, UserPw);
            client.getState().setCredentials(
             new AuthScope(requestUrl.getHost(), 80, AuthScope.ANY_REALM), defaultcreds);
            
        // Then, state that we do want to do authentication on these requests.
            requestMethod.setDoAuthentication(true);

            // Provide custom retry handler if a retry is necessary.. Quite Nice actually
            requestMethod.getParams().setParameter(
      HttpMethodParams.RETRY_HANDLER,
        new DefaultHttpMethodRetryHandler(3, false));

            // Execute the method.
            int statusCode = client.executeMethod(requestMethod);
            if (statusCode != HttpStatus.SC_OK)
            {
                System.err.println("Method failed: "
                    + requestMethod.getStatusLine());
            }

            // Read the response body.
            byte[] responseBody = requestMethod.getResponseBody();

            // NOTE: Simplifying assumption.  Assume all returned content at this
            //       is string data.
            response = new String(responseBody);
         } 
         catch (HttpException e)
         {
             return e.getMessage();
         } 
         catch (IOException e)
         {
             return e.getMessage();
         } 
         finally
         {
             // Release the connection.
             requestMethod.releaseConnection();
         }
     
         return response;
    }

It's all in the presentation

Okay, so now we can make requests to the service and we can read in our responses fine let's go ahead try to make something a bit more interesting. 

In the last example you might recall the data model that we used had a Northwind origin if you like (read more here).  I've decided to keep with this basic storage model and construct a JFC (I still think of it as Swing) application which will allow me to issue ad-hoc queries against my service.  I've inserted a screen shot of this below, forgive the UI my JFC skills aren't quite as sharp as they used to be ;-)

image

What I've got here is a data bound combobox which allows me to determine the container  I'd like to issue my query against.  Next to that is a textbox whose text value will be used for my query text.  Then, as you might imagine, after I execute the query the results (in wire format) will be displayed in the results TextArea.

Let's start with how we retrieve the container Id's.  Listed below is the code I'm using to retrieve the container id's.

     private static String[] retrieveCustomerIds()
    {
        // Construct the URI with our query in that will be used to retrieve
        // all of the containers.  In this case, since the query is empty we will
       // retrieve all the containers within the, "mix08-demo" authority.
      String customerQuery = "https://mix08-demo.data.sitka.microsoft.com/v1/?q=";
     String[] customerIds = null;
        
        try
     {

             // Next, issue the GET request to retrieve the data.  No body is necessary
            // in the request (nor is it allowed).
            String response = IssueRequest("GET", customerQuery, "");

            DocumentBuilderFactory factory =  DocumentBuilderFactory.newInstance();
            factory.setNamespaceAware(true);
            DocumentBuilder docBuilder = factory.newDocumentBuilder();
            InputSource inStream = new InputSource();
            inStream.setCharacterStream(new StringReader(response));
           
            // Read the response we got back from the server into a document.
            Document responseDoc = docBuilder.parse(inStream);

            // Create an XPath expression to retrieve just the customer id's from the set of the containers
            // that were returned.
            XPathFactory xpathFactory = XPathFactory.newInstance();
            XPath xpath = xpathFactory.newXPath();

            MyNamespaceContext ctx = new MyNamespaceContext();
            xpath.setNamespaceContext(ctx);

            XPathExpression expr 
             = xpath.compile("/s:EntitySet/s:Container/s:Id/text()");

            // execute the XPath expression and copy the results into a string array for data binding.
            Object result = expr.evaluate(responseDoc, XPathConstants.NODESET);
            NodeList nodes = (NodeList) result;
            int containerCount = nodes.getLength();
            customerIds = new String[containerCount];
            
            // finally, add each of the container id's to the list of strings that
            // we will return to the caller.
            for (int i = 0; i < containerCount; i++) 
            {
             customerIds[i] = nodes.item(i).getNodeValue();
            }
     }catch(Exception ex)
        {
           String errorMsg = ex.getMessage();
          // Do something more interesting here later.
            System.out.println(errorMsg);
       }

       return customerIds;
    }

Pretty easy stuff, most of the work is actually in processing the response (this is where the JAXB strongly typed sample I referred to last time would be quite handy).  Anyway, keeping things relatively simple we simply read the response into a XML Document, compose a XPath expression over it extracting out just the Id's of the containers and then place each of these into a String array which is then returned back to the caller to later be used for data binding the combo box.  Not that bad.

It's all in the Query

Now, that we can select the container that we wish to query over let's go ahead implement the code which actually does the query.  The code listing below illustrates how to do this.

     private static void processQueryRequest()
    {
       String queryUrl = String.format("%s%s?q='%s'", 
             "https://mix08-demo.data.sitka.microsoft.com/v1/", 
              containerId, java.net.URLEncoder.encode(txtQuery.getText()));
       
        try
     {
           String response = IssueRequest("GET", queryUrl, "");
            
            txtResults.setText(response);
           
        }catch(Exception ex)
        {

             // Do something more interesting here...
           System.out.println(ex.getMessage());
        }
    }

The first thing we do is to construct the URI that will be used on our request.  There's a couple of components to this.  The first is the location of the service (prefixed by our authority id), the second bit is the id of the container that we wish to query and the final piece is the encoded query text that we wish to use for the query.

Next, since we have the URI properly formatted we then go ahead and issue the GET request to the server and receive back a response.  The text of the response body is then inserted directly into the results area.  I've included some sample screenshots here illustrating requests that were valid and some that I made which were intentionally invalid just to, hopefully, illustrate that everything is really working here.

Query of the entire container

image

Query for just the customer within a container

image

Where's the Where

image 

Hopefully, this post illustrates a bit of what you can do with SQL Server Data Services with Java.  As I mentioned I'll try to post a Strong Typing example here using JAXB shortly.  I'll do the same for C# as well if Jason doesn't beat me to it :-)

--Jeff--

Comments