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>

Display DATE columns from Oracle JDBC

If we display a DATE column from a Oracle JDBC program we may not get the desired output. This basic demo shows how to format the display to meet your own needs using the toText method from a oracle.sql.DATE object.

Code

package pas.au.jdbc.dates;

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

import java.util.Date;

import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.pool.OracleDataSource;

import oracle.sql.DATE;


public class FormatDateJDBC
{
  private OracleDataSource ods = null;
  private final String userId = "scott";
  private final String password = "tiger";
  private final String url = 
    "jdbc:oracle:thin:@beast.au.oracle.com:1524/linux11gr2";
  
  public FormatDateJDBC() throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(userId);
    ods.setPassword(password);
    ods.setURL(url);
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }

  public void run()
  {
    Connection conn = null;
    OracleStatement stmt = null;
    OracleResultSet rset = null;
    
    try
    {
      conn = getConnection();
      System.out.println(conn);
      stmt = (OracleStatement)conn.createStatement();
      rset = (OracleResultSet) stmt.executeQuery("select SYSDATE from dual");
      rset.next();
      if (rset != null)
      { 
        DATE todaysDate = rset.getDATE(1);
        System.out.println
          (String.format("Todays date is %s", 
                         todaysDate.toText("DD/MM/YY HH:MM:SS", null)));
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    finally
    {
      /* 
       * if exception occurs here no real need to log it just catch it and continue
       */
      if (rset != null)
      {
        try
        {
          rset.close();
        }
        catch (SQLException s)
        {}
      }
      
      if (stmt != null)
      {
        try
        {
          stmt.close();
        }
        catch (SQLException s)
        {}
      }     

      if (conn != null)
      {
        try
        {
          conn.close();
        }
        catch (SQLException s)
        {}
      }
    }
  }
  
  public static void main(String[] args)
  {
    FormatDateJDBC demo = null;
    try
    {
      System.out.println("Started JDBC display date at " + new Date());
      demo = new FormatDateJDBC();
      demo.run();
      System.out.println("Completed JDBC display date at " + new Date());
    }
    catch (SQLException e)
    {
      e.printStackTrace();
      System.exit(-1);
    }
  }
}

Output

Started JDBC display date at Tue Apr 26 11:07:59 EST 2011
oracle.jdbc.driver.T4CConnection@c24c0
Todays date is 26/04/11 10:04:38
Completed JDBC display date at Tue Apr 26 11:08:00 EST 2011

JRuby OTN How to's With FMW and Oracle RDBMS

A couple of OTN how to's which show how to use JRuby with some of the Oracle Fusion Middleware products as well as single instance and RAC oracle databases.

1. Using JRuby with Oracle Database
http://www.oracle.com/technetwork/articles/dsl/jruby-oracle11g-330825.html

2. Use JRuby with JMX for Oracle WebLogic Server 11g
http://www.oracle.com/technetwork/articles/oem/jruby-wls-jmx-356114.html

3. Use a JRuby Script to Verify an Oracle RAC Setup using SCAN
http://www.oracle.com/technetwork/articles/oem/jruby-ucp-rac-355460.html