RESTful SQL Query Service using XML SQL Utility (XSU) API

A little demo showing how easily we can create a SQL query utility using Oracle's XML SQL Utility (XSU) within a JAX-RS Web Service. Although basic you can easily create more query parameters to take full advantage of the XML SQL Utility (XSU) API.

1. Create a JAX-RS Web Service as follows.

package pas.au.xmlsql.services;

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.QueryParam;
import javax.ws.rs.core.MediaType;

import oracle.xml.parser.v2.XMLDocument;
import oracle.xml.sql.query.OracleXMLQuery;

@Path ("/queryservice") 
public class QueryService
{
  private DataSource ds = null;
  private final String dataSourceLocation = "jdbc/scottDS";
  
  public QueryService()
  {
    Context context;  
    try  
    {  
      context = getInitialContext();  
      // lookup data source at this point
      ds = (DataSource) context.lookup (dataSourceLocation);
    }  
    catch (NamingException e)  
    {  
      throw new RuntimeException (e.getMessage());  
    } 
  }

  private Connection getConnection () throws SQLException
  {
    return ds.getConnection();  
  }
  
  private static Context getInitialContext() throws NamingException  
  {  
    return new InitialContext();  
  }

  @GET
  @Path("/query")
  @Produces(MediaType.APPLICATION_XML)
  public XMLDocument runQuery (@QueryParam("sql")String querySql)
  {
    Connection conn = null;
    OracleXMLQuery qry = null;
    XMLDocument domDoc = null;
    
    try
    {
      conn = getConnection();
      System.out.println("Query is -> " + querySql);
      qry = new OracleXMLQuery(conn, querySql);
      domDoc = (XMLDocument)qry.getXMLDOM();
      return domDoc;
    }
    catch (Exception e)
    {
      throw new RuntimeException
        (String.format("Error running query [%s]", querySql), e);
    }
    finally
    {
      /* 
       * if exception occurs here no real need to log it just catch it and continue
       */

      if (qry != null)
      {
        qry.close();
      }
      
      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException s)
        {}
      }
    }
    
    
  }
}  

Note: From the code above we lookup a data source using the JNDI name "jdbc/scottDS" which exists for the Integrated Server. You will need to create that for this demo to work.

2. Ensure you add the required libraries within JDeveloper. Apart from the Jersey libraries for JAX-RS you will need the 3 extra libraries as shown below.

3. Run Query Service and eventually it should show it's ready as follows.

[01:28:26 PM] ----  Deployment finished.  ----
Run startup time: 4461 ms.
[Application XMLSQLUtility deployed to Server Instance IntegratedWebLogicServer]

Target Application WADL -- http://localhost:7101/xmlsql-service/jersey/application.wadl

Target URL -- http://localhost:7101/xmlsql-service/jersey/queryservice/query?sql=string

4. Now using "curl" run some queries as shown below. Of course it only accepts SQL queries but still useful enough.

Query: select user from dual

[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select user from dual"

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROWSET>
  <ROW num="1"><USER>SCOTT</USER></ROW>
</ROWSET>

Query: select * from dept

[oracle@beast pas]$ curl http://paslap-au.au.oracle.com:7101/xmlsql-service/jersey/queryservice/query?sql="select * from dept"

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<ROWSET>
<ROW num="1"><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC</ROW>
<ROW num="2"><DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></ROW>
<ROW num="3"><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC></ROW>
<ROW num="4"><DEPTNO>40</DEPTNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC></ROW></ROWSET>